Multi Select List Box - Save Values

[tca_VB]

I've reviewed many threads and FAQs about multi-select list boxes, but I can't get to the next step on how to save the values in multi-select for use in queries/reports/form view.

I have a main form - frmClientMain (writes to ClientMain table)
Client_ID can have multiple Type_ID from table Type.
Also, the user can add additional Type fields at any time.

So, in frmClientMain, I want the user to be able to select multiple types from Type.

I tried to add a subform, but it requires ClientMainType table in which Client_ID, Type_ID, Yes/No selection. To have all type selections appear, I would need to populate Client_ID, Type_ID for each new addition of either.

Is this the best way to continue and have code to update ClientMainType upon addition of Client_ID or Type_ID - if so, how do I update. If not, what direction should I head.

Thanks for the help - I'm new at multi select.

 

[DCrake]

Simple Software Solutions

Hi

If you are using a multi select list box you will need to add the followiing code prior to running the query/Report


Dim MyItems As String

For x = 0 to Me.ListBox.Listcount - 1

If me.ListBox.Selected(x) = True Then

MyItems = MyItems & ", "

End If​

Next

'Next strip off the last comma and space

If MyItems <> "" Then

MyItems = Left(MyItems,Len(MyItems)-2)

Endif

So what we have ended up with is one long string containing all the selected items, such as "Item1, Item2, Item4, etc"

Next we need to pad this out as such:

MyItems = "In(" & MyItems & ")"

MyItems now looks like this In(Item1, Item2, Item4, etc)

Using a recordset to populate underlying query/ report

Dim Rs as DAO.RecordSet
Set Rs = CurrentDb.Openrecordset("Select * From Table Where Field " & MyItems


Using a parameter to pass to a query/report

Create a Function called GetMultiItems
Create a public variable called strMultiItems

Function GetMultiItems() As String

GetMultiItems = strMultiItems

End Function


When you have performed the loop as explained earlier add the following
strMultiItems = MyItems

Then in the design of your query under the column that contains the matching records in the criteria line enter =GetMultiItems()

This will then pass the In(...) statement to the query

Another approach similar to the above is to create the In(...) and then use the QueryDefs property to change the SQL for the query


Dim qDef As QueryDef
Dim NewSQL As String


NewSQL = "SELECT * From Table" & _
"WHERE Field " & MyItems

Set qDef = dbs.QueryDefs("MyQuery")
qDef.SQL = NewSQL

This rewrites the SQL statement of the query and includes the where condition into it.

Hope one of these solutions works for you

Code Master:

 

[Bee]

Hi DCrake

I have tried your second approach with a similar example, but I am getting the error that dbs is not defined. Is that a reserved word? Or is supposed to be something that I am not obviously getting?

Thanks,
B