Passing Data To A Report From A ListBox

[lhooker]

How can I pass data (an employee's first and last name) to a report ? I captured the employee's name from the listbox, but can't seem to pass it to the report. The desired report will only have the employee's name and records for related fields on the report. The table (contains emloyees' history data), form name, listBox (contains employees' names), and
variable (contains the employee's name) are listed below. Thanks ! ! !

Table_Employee_Detail_History
Form_Employee's Reports
Report_Attendance_Report
stremployee (variable

 

[pbaldy]

Is this what you're trying to do?

http://www.baldyweb.com/multiselect.htm

 

[lhooker]

Paul,

Thanks for responding ! ! ! . . . I looked at 'Baldy Web' code, but not with any sucess. Below is my original code. It works with exception of the 'DoCmd.OpenReport' line of code. How can I call the report with the employee's name and the employee's data records ? Thanks again ! ! !

Dim strCriteria As String
Dim stLinkCriteria As String
Dim stRptName As String

For Each varItem In Me!List3.ItemsSelected
strEmployee = strCriteria & Me!List3.ItemData(varItem)

Next varItem

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

strEmployee = Right(strEmployee, Len(strEmployee) - 0)

If strEmployee = "John Doe" Then
MsgBox strEmployee
strRptName = "Report_Attendance_Report"
DoCmd.OpenReport strRptName, acViewPreview, , "[Employee_Name]='" & strEmployee & "'"
End If

 

[pbaldy]

I see several issues. You use 2 different variables in the loop. Is the listbox multiselect? If so, you'll also need a comma in there to separate values, and use In instead of =.

 

[lhooker]

Paul,

Thanks for your help ! ! ! I finally got the code to work (listed below). I did not need to use the 'Multi Select' listbox. The only problem that I'm currently having is that each record prints on a separate page. How can I fix this ? Thanks again ! ! !


Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.List10.ItemsSelected.Count = 0 Then
MsgBox "Must select an employee"
Exit Sub
End If

'add selected values to string
Set ctl = Me.List10

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
'Use this line if your value is text
'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)

'open the report, restricted to the selected items

strRptName = "Report_Attendance_Report"
DoCmd.OpenReport strRptName, acViewPreview, , "Table_Employee_Detail_History.Employee_Name IN( " & "'" & strWhere & "')"

 

[pbaldy]

That code is overkill for a single select listbox, but it will work. This is simpler if you want to switch:

http://www.baldyweb.com/wherecondition.htm

To your question, make sure the detail section is small enough to show multiple records on a page. Also check Sorting and Grouping and the properties of the headers and footers for "Force New Page".

 

[lhooker]

Paul,

I simplfied the code (as you see below) . . . Thanks ! ! ! I still can't get all records on
the same page. The 'Detail' section has enough space for the detail record, 'Force New Page'
is set to 'None', and 'Sorting/'Grouping' is set to the employee's name. Any more suggestion(s).


Dim ctl As Control
Dim strRptName As String

If Me.List10.ItemsSelected.Count = 0 Then
MsgBox "Must select an employee"
Exit Sub
End If

Set ctl = Me.List10

strRptName = "Report_Attendance_Report"
DoCmd.OpenReport strRptName, acViewPreview, , "Table_Employee_Detail_History.Employee_Name = " & "'" & Me.List10 & "'"

 

[pbaldy]

Can you post the db here?

 

[lhooker]

Paul,

Here ya go . . . Thanks ! ! !

 

[pbaldy]

Like I said, the detail section is too big. Click/drag the bottom of it up to the bottom of the text boxes.

 

[lhooker]

Paul,

You're right . . . I thought you were referring to the width of the report. I corrected this and the report is OK. Thanks for all of your help ! ! !

 

[pbaldy]

Happy to help!

 

[Jhart22889]

Hi I actually have a similar problem, I want to have a summerized report that opens idealy when the report option is selected in a list box (there are 4 reports that will show different totals all from the form with the Project ID being the connection to the information). Right now I just have one report connected to a button control on the form with the simplified code you posted here in the on click event (to figure out what code works best before I try to complicate things) but it gives me a mismatch error. I also only want the code to show the report and not print. I'm sure it is an EBCAK but I'm not sure what I'm doing wrong as I am a beginner.

Edit/Update: okay so I did some changes and this code opens the report in report view but does not restrict the report to the criteria desired:

Private Sub Command297_Click()
DoCmd.OpenReport "DifinitizedProjectSnapshot", acViewReport, "ProjectID = " & Me.ProjectID

'this code is for the button control on click but I would like to list the report names in the list box and have them open the specific report for that particular project(id). I should ask does the "list" code above also work for items that are typed in and not bound to a table?

 

[pbaldy]

You have the where condition in the wrong position.

 

[Jhart22889]

I actually used an on open macro and set the where property "ProjectID="&ProjectID. I figured it wasn't in the proper place I just wasn't sure where to put it.

 

[pbaldy]

VBA help would have been helpful, but try

DoCmd.OpenReport "DifinitizedProjectSnapshot", acViewReport, , "ProjectID = " & Me.ProjectID

 

[Jhart22889]

VBA help would have been helpful, but try

DoCmd.OpenReport "DifinitizedProjectSnapshot", acViewReport, , "ProjectID = " & Me.ProjectID

WORKS PERFECT!!!! Thank you!!!! I think I may stick with the command buttons as It may make things a lot more simple than adding the report names to a list box and having that call the report.

 

[pbaldy]

Happy to help!