Linking Forms to Queries (2003)

[BobSullivan]

OK, the subject is kind of minimizing the question.

I'd like to build a form that would accept multiple criteria, and then drive a query. For example, I'd like to have a form which contains a place for a user to select several states from which he would like to see customers. I don't know how many states he would select, but I need to capture them, from one to whatever number he wanted, and then feed those into the criteria of a query.

Can't be a combo box, because that only allows one choice. So I guess first of all I need to know how to design an input area that would accept several choices from the same field.

[GARYPSWANSON]

Bob,

You could use a multiselect list box to do this. The selection(s) from the multiselect list box can be written to temp tables or stored as vairiables in code and then used to drive your form or other objects.

Search on multiselect listboxes and you should see a lot of results. There are plenty of good examples in the forum.

[BobSullivan]

Found the sections on Multiselect list boxes. Thanks.

Now, I guess I need some help in constructing the sql statement to use the information from the multiselect list box.

Let's say I have a listbox called States on a form called PickState. If this was a single selection listbox, then I can put the information Forms![PickState]![States] as the criteria of my query to return all the locations for a particular state. If I change the property of the listbox to multiselect and pick two states, the query returns no results. The underlying SQL of the query shows the following:

SELECT tblRetailer.strRetailerName, tblRetailer.strAddr1, tblRetailer.strAddr2, tblRetailer.strCity, tblRetailer.strRegion, tblRetailer.strPostalCode
FROM tblRetailer
WHERE (((tblRetailer.strRegion)=[Forms]![PickState]![States]));

I know I at least need to change the equal sign in the Where clause to "in". But what else do I need to do to this statement so that it will use all the selected values from the multiselect listbox?

Thanks for any help provided.

[HansV]

You can't use a multi-select list box directly in a query, you have to assemble the criteria (or the entire SQL statement) in VBA code. See <post:=465,456>post 465,456</post:> for an example.

[GARYPSWANSON]

Bob,

You are definitely on the right track. In your example, you need to create the "in" string of the sql clause. To do this, you need to complete the following:

Insert the following code In the after update event of your multi select listbox:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim ctl As ListBox
Dim strsql As String
Dim SelectState as string

'Select Listbox to Work On
Set ctl = Me!List_PickState 'assuming the listbox name is List_PickState

'Create Instring of Items Selected
SelectState = "(" & SelectedItems(ctl) & ")"

MsgBox SelectState 'Remove when compelte. For testing purposes.


Also need to add the following function as a module to create the instring:

Function SelectedItems(ctl As Control) As String
' Returns an IN string for selected items in a listbox

Dim strActivities As String
Dim varItem As Variant

strActivities = ""
If ctl.ItemsSelected.Count > 0 Then
For Each varItem In ctl.ItemsSelected
strActivities = strActivities & "'" & ctl.ItemData(varItem) & "',"
Next varItem
End If
SelectedItems = Left$(strActivities, Len(strActivities) - 1)
ListCount = ctl.ItemsSelected.Count
End Function


Once done, you can refer to SelectState for the instring. I also attached an example database that does this. Just open Form1 and give it a try. Note that I added a function as a module.

[GARYPSWANSON]

One last item,

As Hans said, you will need to build the entire sql string in code to do this.

One other option is to use the MultiSelect List Box to populate temporary tables and then use the temp tables to join to the main table to make the filter work.

[GARYPSWANSON]

Bob,

One last item. To make this work with a query linking to an object on your form (like you said you linked to the combo box or single select listbox), you could write the value of the instring to a text box on the form and then in your query, link to the textbox to get the instring as the filter. HTH

[johnhutchison]

When you open a form using DoCmd.OpenForm etc you have the option of adding a Where clause to the command.
A where clause has the same effect as adding another condition to the query.

This provides great flexibility because you can have a form based on a single query or table, but open it with different data sets without having to modify the query each time.

I attach an example of using a multiselect list box to build a Where clause to show a list of people from one or more states.

Because the state names are Text fields in my example, they need to be surrounded by double quotes in the where clause. I have used chr(34) to do that job. (I find it easier to read that way.)