list boxes and reports confused by threads 1

[millrat]

Hi all,
I have read some conflicting threads regarding list boxes and reports. Can anyone help?
My form frmSelect has a listbox with row source tblEmployee, I would like to be able to make selections on the listbox, click the command button and open the report for only those selected. My report "Training Report" runs off a query "Training Query".
The OnClick event is below, but returns all records in the report. What am I missing?
thanks,
millrat

Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click

Dim stDocName As String
stDocName = "Training Report"
DoCmd.OpenReport stDocName, acPreview, ,"[Training Query].EmpID=" & Me.lstEmployee.Column(0)

Exit_cmdPreview_Click:
Exit Sub

Err_cmdPreview_Click:
MsgBox Err.Description
Resume Exit_cmdPreview_Click

End Sub

 

[chrisaroundtown]

On the query set the criteria for the field you want to select to [Forms]![Training Form]![List Box] with your form and list box names in there instead. This means that whenever the query tries to run it will have to verify that the field where you have the criteria matches the value in the list box.

The command button should then just raise the report.

Thanks
Chris

 

[millrat]

Thanks for the quick reply,
Do I put the criteria in the field that matches the bound column in the listbox? in this case EmpID, but now get no records in the report.
millrat

 

[chrisaroundtown]

Yes, let me give you an example.

Let's say you have 10 staff, 3 of which are part time and 7 are full time.

On the form you may have a list box with the different employment statuses in it. You might select part time in this box.

The query would have employment status as a field with the criteria set to refer to that list box on the form. Whenever you run the query it will find that list box and use its value as the criteria for the employment status field. Hence, it will only retunr records that are part timers.

Hope this helps.

 

[millrat]

OK,
The query works, ie when I open it its asks for parameter value for Forms!frmSelect!lstEmployee. Same goes for the report. But the listbox still doesn't return records, the bound column is EmpID same as the query criteria, is there anything else I have to do to the listbox or command button that opens the report?
cheers,
millrat

 

[chrisaroundtown]

No, as long as whatever you have selected in the list box is the criteria you want to filter by then it should be fine.

Maybe try it with a Text box first instead of a list box, that way you can type into the text box exactly the way it is in the table.

If that doesn't work paste your SQL for your query and your vb for your comand button.

 

[millrat]

I tried it with a text box, no probs
So here's the SQL for the query

SELECT Employee.EmpID, Employee.Name, [Training].[Date Tested], [Training].Comment, Employee.ID, Crew.Crew, Module.[Module ID], Module.Description, Competency.Competency, Trainer.Trainer_Name FROM Trainer RIGHT JOIN ((Crew RIGHT JOIN Employee ON Crew.ID = Employee.Crew) RIGHT JOIN ([Module] RIGHT JOIN (Competency RIGHT JOIN [Training] ON Competency.ID = [Training].Competency) ON Module.ID = [Training].Module) ON Employee.ID = [Training].Employee) ON Trainer.ID = [Training].Trainer
WHERE (((Employee.ID)=[forms]![frmSelect]![lstEmployee]));

Criteria in Employee.ID field

Here's the row source for the list box

SELECT Employee.ID, Employee.EmpID, Employee.Name, Crew.Crew FROM Employee INNER JOIN Crew ON Crew.ID=Employee.Crew ORDER BY Employee.Crew;

Bound Column 1

Here's the code for the command button,

Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click

Dim stDocName As String

stDocName = "Training Report"
DoCmd.OpenReport stDocName, acPreview
Exit_cmdPreview_Click:
Exit Sub

Err_cmdPreview_Click:
MsgBox Err.Description
Resume Exit_cmdPreview_Click

End Sub

Appreciate the help
millrat

 

[dhookom]

It is impossible to do this with a multiple select list box without some code. Check faq703-3936 for one solution.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[chrisaroundtown]

Apologies, I was under the impression you were only selecting one option in the list box. Go with Duane's suggestion.

 

[millrat]

Hi guys,
I found the right stuff, this works perfect.
Thanks for your help
millrat

Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click

Dim strFilter As String
Dim varItem As Variant
Dim stDocName As String
stDocName = "Training Report"

For Each varItem In Me.lstEmployee.ItemsSelected
strFilter = strFilter & "ID = " & _
Me.List0.ItemData(varItem) & " OR "
Next

If strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
Else
MsgBox "You did not select any employees.", vbOKOnly, "Selection"
Me.lstEmployee.SetFocus
Exit Sub
End If

DoCmd.OpenReport stDocName, acPreview, , strFilter

Exit_cmdPreview_Click:
Exit Sub

Err_cmdPreview_Click:
MsgBox Err.Description
Resume Exit_cmdPreview_Click

End Sub