List Boxes

[Guest]

I have 2 controls on a form set up as a LIST BOX with Multi-Select property
set to Simple. When I try to enter info into this form (selecting for ex. 3
classes from 1 box and 2 students from the other), the selections are not
logged in, i.e. they do not show up in reports or queries. Everything else
is loggs in correctly on the form.

My tables and relationships are set up correctly. Please advise.

 

[Douglas J Steele]

How are you attempting to use the selections?

Unlike when you don't have Multiselect enabled, a reference to a multiselect
list box doesn't return any values. You need to use VBA code similar to
http://www.mvps.org/access/forms/frm0007.htm at "The Access Web".

 

[Guest]

Sorry about the double posting.

I am sorry, but I am relatively new to access and unfamiliar with coding. I
visited your website and I understand the concept, but I am unsure what to do
next. Where/ how do I enter the code?

Thank you.

 

[Douglas J. Steele]

Presumably you've got a button on your form that has the list box that you
click on to open the report.

Code similar to what's at that page has to be entered in that event.

Assuming you've got something like:

Dim stDocName As String

stDocName = "MyReport"
DoCmd.OpenReport stDocName, acPreview

You need to change it to something like:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strCriteria As String
Dim stDocName As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
For Each varItem In ctl.ItemsSelected
strCriteria = strCriteria & ctl.ItemData(varItem) & ", "
Next varItem

' strCriteria will have an extra ", " at the end:
' trim it off
If Len(strCriteria) > 0 Then
strCriteria="ClassId IN ( " & _
Left$(strCriteria,len(strCriteria)-2)) & ")"
End If

stDocName = "MyReport"
DoCmd.OpenReport stDocName, acPreview, , strCriteria


This assumes that your ClassId is a numeric field. If it's text, use

strCriteria = strCriteria & Chr$(34) & ctl.ItemData(varItem) &
Chr$(34) & ", "

(watch for word-wrap: that's supposed to be all on one line)

 

[Guest]

I do not have a button on the form for the report yet. I was trying to make
sure the form was working properly before I inserted buttons.

So, if I understand what you are saying, what I need to do is create a
button linked to the report. And in that event I need to copy and paste the
below code.

Thank you for you patience.

 

[Douglas J Steele]

Correct.

And if you've got 2 listboxes you're trying to use, it'll be something like:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strCriteria As String
Dim strCriteria1 As String
Dim strCriteria2 As String
Dim stDocName As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox1
For Each varItem In ctl.ItemsSelected
strCriteria1 = strCriteria1 & ctl.ItemData(varItem) & ", "
Next varItem

' strCriteria1 will have an extra ", " at the end:
' trim it off
If Len(strCriteria1) > 0 Then
strCriteria="ClassId IN ( " & _
Left$(strCriteria1,Len(strCriteria1)-2)) & ")"
End If

Set ctl = frm!lbMultiSelectListbox2
For Each varItem In ctl.ItemsSelected
strCriteria2 = strCriteria2 & ctl.ItemData(varItem) & ", "
Next varItem

' strCriteria2 will have an extra ", " at the end:
' trim it off
If Len(strCriteria2) > 0 Then
If Len(strCriteria) > 0 Then
strCriteria = strCriteria & " AND "
End If
strCriteria= strCriteria & "StudentId IN ( " & _
Left$(strCriteria2,Len(strCriteria2)-2)) & ")"
End If

stDocName = "MyReport"
DoCmd.OpenReport stDocName, acPreview, , strCriteria