By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Passing Multi-Select field parameters to a query

P: 7
Hi Everyone,

I've seen this question posted before and I believe the answer was to incorporate VB code somewhere into the form.

I am not familiar with VB and just wanted to know where to paste the code to.

I just have a simple query to pull all rows for any values selected in that multi-select field.

Form name is Search
List box name is List11
Query name is Form Query

I am running Access 2003 on Win2K


Thanks for the help!
Nov 12 '06 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534

I just have a simple query to pull all rows for any values selected in that multi-select field.

Form name is Search
List box name is List11
Query name is Form Query
With a multiselect listbox you need to use a command button (cmdSearch for this example).

I am assuming your form is bound to the query "Form Query"

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdSearch_Click()
  3. Dim val As Variant
  4. Dim strValues As String
  5.  
  6.   ' to get all the values selected into a string
  7.   strValues=""
  8.   For Each val In Me.List11.ItemsSelected
  9.     strValues = strValues & "'" & Me.List11.ItemData(val) & "',"
  10.   Next i
  11.  
  12.   ' remove the last comma
  13.   strValues = Left(strValues, Len(strValues)-1)
  14.  
  15.   Me.Filter = "[FieldNameAsList] IN (" & strValues & ")"
  16.   Me.FilterOn = True
  17.  
  18.   Me.Requery
  19.  
  20. End Sub
  21.  
  22.  
Nov 12 '06 #2

P: 7
Thanks for the help. The code below should be placed in the On Click Event Procedure? Should I delete everything thats in there and paste the code below?

Thanks!


With a multiselect listbox you need to use a command button (cmdSearch for this example).

I am assuming your form is bound to the query "Form Query"

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdSearch_Click()
  3. Dim val As Variant
  4. Dim strValues As String
  5.  
  6.   ' to get all the values selected into a string
  7.   strValues=""
  8.   For Each val In Me.List11.ItemsSelected
  9.     strValues = strValues & "'" & Me.List11.ItemData(val) & "',"
  10.   Next i
  11.  
  12.   ' remove the last comma
  13.   strValues = Left(strValues, Len(strValues)-1)
  14.  
  15.   Me.Filter = "[FieldNameAsList] IN (" & strValues & ")"
  16.   Me.FilterOn = True
  17.  
  18.   Me.Requery
  19.  
  20. End Sub
  21.  
  22.  
Nov 13 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for the help. The code below should be placed in the On Click Event Procedure? Should I delete everything thats in there and paste the code below?

Thanks!
If you mean what was created by the wizard I would guess yes. But for now just comment each line out by putting an apostrophe ' in front of each one in case you need to retrieve the code.
Nov 13 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.