Member Avatar for OASys1

Hello!

Now I am trying to make selection of Customers easier for my user. My user's business involves making deliveries on routes. Every customer is assigned a route number, which references the route in the route table.

In my form I want to have two list boxes, side by side. lb1 showing customers already assigned to the route, and the other. YES, all other customer NOT assigned to this route - lb2 ... clever huh ? :-).

Besides the usual buttons ( " < " & " << ", " > " & " >> " )< I would like to be able to click on a name in one list box and drag them to the other list box to assign/unassign them to a route.

Click, drag and drop from lb1 to lb2. OR click, drag and drop from lb2 to lb1, with the respective list boxes being updated....

I have done this in PowerBuilder back at the turn of the millenium..... <lol> as well as more recently in Delphi 2010. I am trying to get used to Access, VB & VBA..... Gads.... did I just say that ?

Names would appear in the other list box, & disappear from the original list box.

Can anyone help me out with code that would do that?

Many thanks.....

OASsy1

  • 2 Contributors
  • 1 Reply
  • 583 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by ChrisPadgham

Dani AI

Generated

Two practical patterns work best for the route/ customer scenario described by : a simple double-click (or move buttons) that updates the Customers.RouteID and requeries the two listboxes, or a true drag-and-drop UX using MSForms.DataObject. The double-click approach is easier to implement, more reliable in multiuser environments, and is exactly the sort of quick solution suggested by . Use bound listboxes (bound column = CustomerID) and run an UPDATE against the Customers table, then Requery both lists. (learn.microsoft.com)

Example: double-click to assign (handles single- and multi-select lists; replace control names as needed)

Private Sub lbAvailable_DblClick(Cancel As Integer)
    Dim varItem As Variant, idList As String, sql As String
    Dim currRouteID As Variant
    currRouteID = Nz(Me!txtRouteID, 0)  ' adjust to the control holding the current route

    If Me.lbAvailable.MultiSelect = 0 Then
        If Not IsNull(Me.lbAvailable.Value) Then
            sql = "UPDATE Customers SET RouteID = " & CLng(Me.lbAvailable.Value) & " WHERE CustomerID = " & CLng(Me.lbAvailable.Value) & ";"
            CurrentDb.Execute sql, dbFailOnError
        End If
    Else
        For Each varItem In Me.lbAvailable.ItemsSelected
            idList = idList & Me.lbAvailable.ItemData(varItem) & ","
        Next
        If Len(idList) > 0 Then
            idList = Left(idList, Len(idList) - 1)
            sql = "UPDATE Customers SET RouteID = " & currRouteID & " WHERE CustomerID IN (" & idList & ");"
            CurrentDb.Execute sql, dbFailOnError
        End If
    End If

    Me.lbAvailable.Requery
    Me.lbAssigned.Requery
End Sub

If a drag-and-drop feel is required, the built-in MSForms.DataObject / StartDrag pattern can be used (example code in Microsoft docs). That gives a natural UX but has caveats: ListBox.AddItem/RemoveItem only work for Value List controls (not bound queries), so when listboxes are bound you still must UPDATE the table and Requery the controls in the drop handler. Implement drag start in MouseMove and handle the drop in BeforeDropOrPaste; update the Customers table there and requery to refresh both lists. (learn.microsoft.com)

Quick troubleshooting notes: if the listbox MultiSelect is Extended, requerying clears selections — plan for that (store keys if you must restore selection). Use dbFailOnError with CurrentDb.Execute and wrap critical multi-row updates in a transaction for safety. If CustomerID is text, build the IN list with quoted values or use a parameterized QueryDef to avoid quoting/SQL-injection issues. (learn.microsoft.com)

Member Avatar for ChrisPadgham

You would be better to put it under a double click event on the list box. ie the user double clicks the name to move it to the other box. it is simple to implement and is faster and easier for the user to use than drag and drop.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.