Passing Multi-Select field parameters to a query

[blees10]

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!

[MMcCarthy]

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"

 
Private Sub cmdSearch_Click()
Dim val As Variant
Dim strValues As String
 
  ' to get all the values selected into a string
  strValues=""
  For Each val In Me.List11.ItemsSelected
	strValues = strValues & "'" & Me.List11.ItemData(val) & "',"
  Next i
 
  ' remove the last comma
  strValues = Left(strValues, Len(strValues)-1)
 
  Me.Filter = "[FieldNameAsList] IN (" & strValues & ")"
  Me.FilterOn = True
 
  Me.Requery
 
End Sub

[blees10]

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"

 
Private Sub cmdSearch_Click()
Dim val As Variant
Dim strValues As String
 
  ' to get all the values selected into a string
  strValues=""
  For Each val In Me.List11.ItemsSelected
	strValues = strValues & "'" & Me.List11.ItemData(val) & "',"
  Next i
 
  ' remove the last comma
  strValues = Left(strValues, Len(strValues)-1)
 
  Me.Filter = "[FieldNameAsList] IN (" & strValues & ")"
  Me.FilterOn = True
 
  Me.Requery
 
End Sub

[MMcCarthy]

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.