HELP!! how to select multiple criteria in a list box in an access form

[dwrpsych128]

I am trying to create a form that allows the user to select a detective and multiple case statuses (such active, inactive, dna, filing, etc.). The form is intended to allow the user to determine the number of assigned cases. The form is tied to a query and the criteria that I've set up in the query is: [Forms]![DET_CaseManagementFRM]![Combo30] which is under the investigator field. The case status field has similar criteria [Forms]![DET_CaseManagementFRM]![List43].

My problem is that I cant figure out how to allow the user to select multiple criteria in case status field in my form. The research I've done online says you need to specify simple or extended in the Multi Select field under the other tab in the property sheet. However, when I do specify simple it doesnt return any records which I know it not true. Is there VB code that I need for this?

 

[jdraw]

Don't overlook Google or Bing to find things on the internet

see http://allenbrowne.com/ser-50.html

Also, we know nothing of your tables or relationships or exactly WHAT you are trying to accomplish in business terms. A little info about your set up and business can help put posts in context for readers.

 

[boerbende]

What I did: use for example 3 different textboxes in your form. make box2.visible=false and box3.visible=false.
When the user changes box1, write an event in the after change:

(I assume that the field you want to search on is a text field)
private sub box1_afterupdate()
box2.visible = true
updatesubform
end sub

private sub box2_afterupdate()
box3.visible = true
updatesubform
end sub

private sub box3_afterupdate()
updatesubform
end sub

private function updatesubform()
dim sqlstr as string
dim wherestr as string
wherestr = ""

if box1.value<>"" then
wherestr = " WHERE yourfield like '*" & box1.value & *'"
endif
if box2.value<>"" then
wherestr = wherestr & " AND yourfield like '*" & box2.value & *'"
endif

if box3.value<>"" then
wherestr = wherestr & " AND yourfield like '*" & box3.value & *'"
endif

sqlstr = Select Yourfield from Yourtable & wherestr

YourSubform.recordsource = sqlstr

end function

Now it searches with wildcards in the textfield of your table and showes the selected records in your subform

 

[Hagridore]

I'm not sure if this helps, but I used the newly built-in multi-value functionality. In creating both the table where the info is stored and the form that I use to gather the information, I used a list box, either typed in the values I wanted or had the listbox read from a table, and on the last screen of the wizard checked the "Allow Multiple Values" box. My options now show up as check boxes on the form, and multiple values are stored in the table as needed.