Using a list box to open a report

[Raj Mann]

Hi,

I am working on a simple database where i would like the user to select records from a multiple selection list box and then based on what the user had chosen a report displayed for just those records.

I have some code from a sample database but i cant get it to work.

I have 2 fields in a table called ID NO and Name
I have a form called Sounds

Everytime i press the button i get the following error message

***********************************************
Runtime error "3075"

Syntax error (Missing operator ) In query expression '(ID n)'
***********************************************

I have copied and pasted the code below:

'Run a report displaying only the records chosen
'by the user in the form's listbox.
Dim v As Variant
Dim Frm As Form
Dim ctl As Control
Dim theId As Long
Dim WhereCrit As String

'If nothing is selected, notify user...
If Me.LstFindings.ItemsSelected.Count = 0 Then
MsgBox "Please select a supplier or two.", vbExclamation, "No Supplier Selected"
'and then scram.
Exit Sub
End If

'Assign form and control to object variables.
Set Frm = Forms!Sounds
Set ctl = Frm!LstFindings

'Begin building Where string.
WhereCrit = "ID no = "

'Add each selected item to the WHERE string.
For Each v In ctl.ItemsSelected
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'The first column in the list, holding SupplierID, is hidden.
'See the list's "Column Widths" and "Column Count" properties in
'its property dialog and look up the terms in Help for more info.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Coordinates: 1st column (0); row v --
'where v changes for each round of the loop.
theId = ctl.Column(0, v)
WhereCrit = WhereCrit & theId & " OR ID no = "
Next v
'Loop ends; selected items are now accounted for...
'Test: Uncomment the next line to print the Where string to the Immediate window.
'Debug.Print WhereCrit
'Clean-up the Where string by removing the trailing text.
WhereCrit = Left(WhereCrit, Len(WhereCrit) - 17)

'Test: Uncomment the next line to print the Where string to the Immediate window.
'Debug.Print WhereCrit
'Open the suppliers report using the Where clause to filter it.
DoCmd.OpenReport "report", acViewPreview, , WhereCrit
End Sub


Any help would be much appreciated.
Thanks in Advance

 

[Raj Mann]

oh forgot to mention:

My list box is called LstFindings

 

[pbaldy]

For starters, because of the inadvisable space in your field name, it would need to be bracketed:

[ID no]

 

[pbaldy]

Oh, and it looks like the 17 in your cleanup needs to be adjusted. You want the number of extra characters that will be left over to the right of the last valid value.

 

[Raj Mann]

Thanks for your help Paul

I have taken the space out of Id no and now it is IDno

however now when i press the button it brings up a box asking me "Enter Parameter value" ID _________


Also i dont understand your reply about the cleanup and 17?

Thanks for your help

 

[pbaldy]

The parameter prompt would indicate that isn't the name of the field in the table. What is the actual field name?

I meant the 17 in this line:

WhereCrit = Left(WhereCrit, Len(WhereCrit) - 17)

As it currently stands, it is trimming off the wrong number of characters.

 

[Raj Mann]

i changed the 17 to 10 and it works!

I dont understand the logic, BUT IT WORKS!

Thanks

 

[pbaldy]

No problem. I would suggest studying it so you understand what it does and why you had to change it. Feel free to ask about anything you don't understand.

 

[Raj Mann]

I am having troubles with it.

On some records it will work and the report will display the relevant record. however some records do not display.

Can anyone tell me what is going on here?

 

[Raj Mann]

Also how many charcters should it be trimming off?

why does it need to trim off characters?

 

[gemma-the-husky]

raj mann

WhereCrit = Left(WhereCrit, Len(WhereCrit) - 17)

instead just use

wherecrit = trim(wherecrit)

to remove any amount of LEADING AND TRAILING spaces

 

[gemma-the-husky]

raj mann

WhereCrit = Left(WhereCrit, Len(WhereCrit) - 17)

instead just use

wherecrit = trim(wherecrit)

to remove any amount of LEADING AND TRAILING spaces


---------
before you open the report add temporarily

msgbox(wherecrit)

then you can see what your criteria string looks like

 

[pbaldy]

Gemma, I don't see how Trim will help, given the OP's method of building the string.