List Box Selection to Run report

[richard]

Hi

I have a list box in which there are 10 fields shown. I wish for the user to
make selections (multiple if required) and then for them to run a report. I
have the following code (mainly extracted from Martin Greens website) which
is allowing the report to run but my criteria is not being used in the record
selection. There is no other criteria in the reports query so I am looking
for help as to why I am not able to run the report correctly displaying the
users selections

Private Sub cmdRunReport_Click()

Dim db As DAO.Database
Dim varItem As Variant
Dim strCriteria As String
Dim strAssistFMJobs As String
Dim stDocName As String
Dim ctrl As Control

Set ctrl = Me.lstAssistFMJobs


If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strAssistFMJobs = strAssistFMJobs & "," &
Me!lstAssistFMJobs.ItemData(varItem) & "'"
Next varItem

strAssistFMJobs = Right(strAssistFMJobs, Len(strAssistFMJobs) - 1)
End If

If Len(strAssistFMJobs) = 0 Then
MsgBox "You did not select anything from the list", vbExclamation,
"Nothing to find!"
Exit Sub
End If


strCriteria = "jobnumber In(" & strAssistFMJobs & ")"

stDocName = "Data Sheet for Clients"
DoCmd.OpenReport stDocName, acViewPreview, strCriteria

End Sub

Thanks

Richard

 

[Rick Brandt]

Hi

I have a list box in which there are 10 fields shown. I wish for the
user to make selections (multiple if required) and then for them to
run a report. I have the following code (mainly extracted from Martin
Greens website) which is allowing the report to run but my criteria
is not being used in the record selection. There is no other criteria
in the reports query so I am looking for help as to why I am not able
to run the report correctly displaying the users selections

Private Sub cmdRunReport_Click()

Dim db As DAO.Database
Dim varItem As Variant
Dim strCriteria As String
Dim strAssistFMJobs As String
Dim stDocName As String
Dim ctrl As Control

Set ctrl = Me.lstAssistFMJobs


If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strAssistFMJobs = strAssistFMJobs & "," &
Me!lstAssistFMJobs.ItemData(varItem) & "'"
Next varItem

strAssistFMJobs = Right(strAssistFMJobs, Len(strAssistFMJobs) - 1)
End If

If Len(strAssistFMJobs) = 0 Then
MsgBox "You did not select anything from the list", vbExclamation,
"Nothing to find!"
Exit Sub
End If


strCriteria = "jobnumber In(" & strAssistFMJobs & ")"

stDocName = "Data Sheet for Clients"
DoCmd.OpenReport stDocName, acViewPreview, strCriteria

End Sub

If these values are numeric then you don't need the trailing single quote that
you are adding after each value. If they are text you need a leading single
quote in addition to the trailing one you already have. As it is now your
string will end up like...

1',2',3',4'

You can also simplify by replacing...

strAssistFMJobs = Right(strAssistFMJobs, Len(strAssistFMJobs) - 1)

....with...

strAssistFMJobs = Mid(strAssistFMJobs, 2)

 

[richard]

Rick

The field was numeric and the code now works after some tweeking with the
query, however I have now realised I need to have two fields for the
criteria, the extra field is SubJobNumber. Could you please advise if this is
possible and if so how??

Thanks

Richard

 

[Rick Brandt]

Rick

The field was numeric and the code now works after some tweeking with
the query, however I have now realised I need to have two fields for
the criteria, the extra field is SubJobNumber. Could you please
advise if this is possible and if so how??

Same looping procedure but populate two variables instead of one. The syntax...

Me!lstAssistFMJobs.ItemData(varItem)

....gives you the bound column from the list so for the second variable you need
to specifically refer to a different column in the ListBox...

Me!lstAssistFMJobs.Column(1, varItem)