Query & Multiselect boxes... 1

[sucoyant]

I wasn't too sure where to put this. Sorry!

I'll try to explain my situation as best I can.

I have a "Processor" report that gets its info from a query named "qry_OUT_Date&Processor" (The query below)

The query looks like this:
SELECT Outprocessing.Employee, Outprocessing.[Account#], Outprocessing.Date, Outprocessing.Reviewer, Outprocessing.Comments, Outprocessing.Sub_sent_per_delivery_ins, Outprocessing.ACRM_updated
FROM Outprocessing
WHERE (((Outprocessing.Employee) Like "*" & [Processor Name:] & "*&quot AND ((Outprocessing.Date) Between [Forms]![Menu]![txtLA_PRfrom] And [Forms]![Menu]![txtLA_PRto]));

As you can see, the query is pulling the name from a popup box, and the dates from 2 text boxes.

I have a list box setup with an extended Multiselect, that contains employee names. What I would like to do, is when a user clicks the command button that executes the report(and this query) I would like it somehow to cycle thru the names on the list and bring up a new “Processor” report for each selected employee.

Could someone offer some guidance?

Thanks in advance!!

 

[klyon4]

I hope this code will help you... I put a multi-select list box on a form bound to data in an employee table. I have a button which says "Get Selected" which runs the following code when clicked:

Private Sub cmdGetSelected_Click()
Dim ctlEmp As Control
Dim intCurrentRow As Integer

Set ctlEmp = Me.lstEmp

For intCurrentRow = 0 To ctlEmp.ListCount - 1
If ctlEmp.Selected(intCurrentRow) Then
Debug.Print "Row: " & intCurrentRow & ", Value: " & ctlEmp.Column(0, intCurrentRow)
End If
Next

Set ctlEmp = Nothing
End Sub

As written, the code will print (to the Immediate window) each selected row, and the value of the first column for each selected row.

Hopefully you can take this and adapt it to your needs.

Let me know if you need further help.

Kevin

 

[dhookom]

There is a faq on this that might help. I use the same function for all my multi-select list boxes. faq703-3936

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

 

[sucoyant]

I was thinking about doing a bit of a runaround like this:

I tried a run-around solution where i would set a textbox equal to whatever is selected in the listbox, and the query would grab the employee name from the textbox. But its not working...

Here is the code:

For Each X In lstLA_PR.ItemsSelected
txtLA_PRname = lstLA_PR.ItemData(X)
DoCmd.OpenReport "Date and Processor - Lease Assignment", acViewPreview
Next

Why isn't it working?

 

[dhookom]

Do you want to open a separate copy of the report for each selected item? If so, you need to add a where clause in your For Next Loop.
DoCmd.OpenReport "Date and Processor - Lease Assignment", acViewPreview,,"SomeField=""" & txtLA_PRname Y """"

My assumption was that you wanted one report that contained records from all the selected items.

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

 

[sucoyant]

I'm a bit confused.. sorry!!

The code:
"SomeField=""" & txtLA_PRname Y """"

What does the Y mean?

I really appreciate you taking the time to help me out!!!

 

[sucoyant]

If by "SomeField=" you mean query field I tried this:

DoCmd.OpenReport "Date and Processor - Lease Assignment", acViewPreview, , "[qryLA_REPORT_Date&Processor]![Employee]=""" & txtLA_PRname

I tried it with the Y """ after txtLA_PRname, but it highlighted the text red... it wouldnt let me do it.

But... with the code above, I get this error:

 

[dhookom]

Sorry, the "Y" was supposed to be and "&". You missed one quote at the end of my suggestion. There should be four quotes at the end.
,,"[Employee] = """ & txtLA_PRname & """"


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