listbox report error

G

Glen

I have a report that is generated from names in a listbox, it runs fine but
it always gives me a name at the end of the report that I did not select from
the listbox. What could be wrong. Thanks
 
J

John Vinson

I have a report that is generated from names in a listbox, it runs fine but
it always gives me a name at the end of the report that I did not select from
the listbox. What could be wrong. Thanks
Click to expand...

What could be wrong is the way you've set up the report. Since you
don't say how you did so it's more than a bit difficult to say!

How is the report "generated"? What is its Recordsource? Could you
post the code which launches the report?

John W. Vinson[MVP]
 
G

Glen

The report is generated by a query here is what it looks like: query is
"allemployee"
Private Sub CmdPreview_Click()
Dim v As Variant
Dim Frm As Form
Dim ctl As Control
Dim theId As Long
Dim WhereCrit As String

If Me.ByName.ItemsSelected.Count = 0 Then
MsgBox "Please select a Name or two.", vbExclamation, "No Name Selected"
'and then scram.
Exit Sub
End If

Set Frm = Forms!EmployeeByName
Set ctl = Frm!ByName

WhereCrit = "RecordID = "

For Each v In ctl.ItemsSelected
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Coordinates: 1st column (0); row v
'where v changes for each round of the loop.
theId = ctl.Column(0, v)
'Tag on to string.
WhereCrit = WhereCrit & theId & " OR RecordID = "
Next v

WhereCrit = left(WhereCrit, Len(WhereCrit) - 17)
DoCmd.OpenReport "ByNameReport", acViewPreview, , WhereCrit

End Sub

Private Sub Form_Load()

'Fill the listbox using a saved query.

Me.ByName.RowSource = "AllEmployees"

End Sub
 
J

John Vinson

The report is generated by a query here is what it looks like: query is
"allemployee"
Click to expand...

I'd suggest stepping through the code in debug mode, and displaying
the WhereCrit string. If you copy and paste WhereCrit onto the
Criteria line of the query in design view, and then open the query, do
you get the expected results?

Note that you can do this a bit more compactly using the IN() clause:

WhereCrit = "RecordID IN("

For Each v In ctl.ItemsSelected
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Coordinates: 1st column (0); row v
'where v changes for each round of the loop.
theId = ctl.Column(0, v)
'Tag on to string.
WhereCrit = WhereCrit & theId & ","
Next v

WhereCrit = left(WhereCrit, Len(WhereCrit) - 1) & ")"


John W. Vinson[MVP]
 
G

Glen

Thanks A lot, that did it, I replace the string with your and it remove the
name in the report. I still don't quite understand what it did but again
thanks
 
Top