List boxes and Requery... 1

[Rjc8513]

I have a form containing two listboxes. In ListBox A are all sales units. ListBox A's data is supplied from the query, "ALL SALES UNITS".

I want to be able to select only the desired sales units in ListBox A and have them appear in ListBox B and then be placed in the table, "SELECTED SALES UNITS".

Thanks for the help.

Richard...

 

[downwitchyobadself]

Add a Yes/No field to your tblSalesUnits, call it IsSelected. Set listA's row source criteria to IsSelected=False, listB's to IsSelected=True. When, say, the user double-clicks on listA, run the following code:

Private Sub listA_DblClick()

    CurrentDb.Execute("UPDATE tblSalesUnit SET IsSelected = Not [IsSelected] WHERE SalesUnitID = " & listA & ";")
    listA.Requery
    listB.Requery

End Sub

You can run it backwards from listB. Might need to put quotes around the listA value if it's not an ID but a string.

 

[downwitchyobadself]

P.S. There's no need for a second table that way. You can just query against all sales units where IsSelected = True.