Trying to let the user select states from one list box Click a button to move the selected states over to a new list box and then use those states selected in a SQL statement. But I want to store the selection so that if someone later wants to use that same selection they can use it. So I have a profile table that stores the selected states, and other such data for the SQL statement.
Okay, we have 2 listboxes. ListboxA keeps the list of States, and ListboxB linked to a table that keeps list of the selected states which I will call it as Table2. Table2 have a field called States, which will keep list of the previously selected States for us.
Add a button and copy this code into its event code:
Dim ADOCon As ADODB.Connection
Dim Selected As Variant
Dim State As String
Set ADOCon = Application.CurrentProject.Connection 'get the current ADO connection
For Each Selected In ListboxA.ItemsSelected 'a loop for every selected item in the listboxA
State = ListboxA.ItemData(Selected) 'get the state name by index number
'check if selected state is already in the table. if it is skip to next state
If Not IsNull(DLookup("states", "table2", "states='" & State & "'")) Then GoTo NextState
'not in the list then add it to the table2
ADOCon.Execute ("INSERT INTO table2 (states) values('" & State & "')")
ListboxB.Requery 'requery listboxB so we can see the transfer
That works. I think I can figure out the rest. I need all selected state codes to be in one record ie AK,AL,MI instead of in different records. Thats all.
That will make the SQL string easier to produce.