HELP! using list boxes with multiselect

[tarhim47]

Ok.



Do you have any idea as to why I'm getting an error?

Thanks!

[pbaldy]

Not offhand, though you may be comparing a string wherecondition to a numeric client ID. Can you post the db with this code in it?

[tarhim47]

Hey Paul,

Actually I figured it out...I shouldve had County where I had Client ID.

THANKS for all your help!!!!! Hope I wasnt too much trouble.

Have a good one.

[pbaldy]

That's what I said in post 15. Glad you got it sorted out.

[tarhim47]

Oh...sorry! I guess I was reading quickly.

[tarhim47]

Hi Paul,

I was just wondering if I can ask you another question.

I got the form working with the changes you suggested. However when I add another list box for the type of cow, the form isnt able to filter the query.

Can you tell me what I'm missing here? I basically copied the code for the county and changed it to refer to the type of cow. Why doesnt this work?

Thanks!

My code is as follows:
Option Compare Database
Option Explicit

Private Sub OK_Click()
On Error GoTo Err_OK_Click

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.Enter_county_of_farm_operations.ItemsSelected.C ount = 0 Then
MsgBox "Must select at least 1 county"
Exit Sub
End If
'add selected values to string
Set ctl = Me.Enter_county_of_farm_operations
For Each varItem In ctl.ItemsSelected
'strWhere = strWhere & ctl.ItemData(varItem) & ","
'Use this line if your value is text
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "Report", acPreview, , "County IN(" & strWhere & ")"
'make sure a selection has been made
If Me.Enter_type_of_cows.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 type of cow"
Exit Sub
End If
'add selected values to string
Set ctl = Me.Enter_type_of_cows
For Each varItem In ctl.ItemsSelected
'strWhere = strWhere & ctl.ItemData(varItem) & ","
'Use this line if your value is text
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "Report", acPreview, , "Type_of_cows IN(" & strWhere & ")"

Exit_OK_Click:
Exit Sub
Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click
End Sub

[pbaldy]

The report is static; it's already open with the first criteria, so it won't change with the second. You'd have to build a combined string with an end result like:

"County In('abc', 'def') AND Type_of_cows IN('aaa', 'bbb')"

So basically you build the first one and then add the second.