Multiselect Combo/List Box to generate a report

[jbates]

I’m a novice with Access so would be grateful for some guidance please.

I’m currently designing a database and have the following issue:

I have an ‘Unbound’ Combo Box and an ‘Unbound’ List Box which I’m using to create a report. Basically the user can select numerous ‘part numbers’ from the Combo Box (which is sourced from a table) which can then be (when selected by the user) added to a List Box. The user can then select the ‘part numbers’ that appear in the list box to generate a report. This works well with a code I got from ‘PBaldy’, however the list box shows the ‘ID’ number. What I want to show in the list box is the ‘Part Number’ not its ‘ID’ number and for it to generate a report from there. I’ve managed to get the ‘part number’ into the list box however the report doesn’t recognise my command and appears blank. A majority of the part numbers are numeric however there are some that contain letters too.
The code I have on the ‘Open Report’ command button is:

Private Sub Command25_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made


If Me.MyList.ItemsSelected.Count = 0 Then
Exit Sub
End If

'add selected values to string
Set ctl = Me.MyList
For Each varItem In ctl.ItemsSelected
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 "rptSearch", acViewReport, , "ID IN(" & strWhere & ")"

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click
End Sub

Help with the above would be most appreciated.

Thank you.

[pbaldy]

I'm unclear as to what the problem is. I'd point out that you still refer to the ID field here:

DoCmd.OpenReport "rptSearch", acViewReport, , "ID IN(" & strWhere & ")"

Also, if you look at my link where you got that, there's a different line to use inside the loop for a text value, which yours must be if it can have letters in it.

[jbates]

Many thanks for your prompt reply.

The DoCmd.OpenReport "rptSearch", acViewReport, , "ID IN(" & strWhere & ")" works when the 'ID' of the part number is shown in the List Box but I want to show the actual 'part number' in the list box and then run a report. I did try doing the following DoCmd.OpenReport "rptSearch", acViewReport, , "PartNumber IN(" & strWhere & ")" however when I opened the report a box would appear prompting me to enter the part number.

*Also, if you look at my link where you got that, there's a different line to use inside the loop for a text value, which yours must be if it can have letters in it.

By incorporating that line will it pick up both text and numerical fields? i.e I may have a part number that says 91256AO2. I get the impression that you can only use one or the other.

Thanks again.

[pbaldy]

If you got prompted for part number, it must not be in the report's source query. That prompt is Access telling you it can't find something.

The method depends on the data type of the field, not what it contains. Yours is text, but the method should work even if the value is "12345".

[jbates]

I've checked the report design, and in the 'Data' tab the record source is pointing to my query which has the part number but in the Filter it shows 'ID IN(7)' (which is the ID number of the 'part number', on the 'Filter On Load' it's set to 'No', in the 'Order By' it says [tblECCNLog].[Part Number], 'Order By On Load' is set to 'Yes' and 'Allow Filters' is set to 'Yes'. I changed the query on the 'Open Report command button' on my form to the following:

Set ctl = Me.MyList
For Each varItem In ctl.ItemsSelected
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 "rptSearch", acViewReport, , "Part Number IN(" & strWhere & ")"

Which then gives me an error message saying 'Syntax error (missing operator) in query expression 'Part Number IN ('91*********')'.

Sorry to be incompetent with this but can you advise where the mistake is please.

Thanks again.

[pbaldy]

Because of the inadvisable space, the field name must be bracketed.

[jbates]

Your an absolute star. Thank you so much.

With your help I've managed to resolve the problem.

Thank you again, your help has been most appreciated.

[pbaldy]

Happy to help, and welcome to the site by the way!

[jbates]

Thank you. I would highly recommend this site to Access users