Selected items in List Box appear on one report

[Tadynn]

Hi All.

Here's one for all you gurus out there.

I've created a list box that contains a list of all orders placed. The list box name is List0 and it contains 6 fields. In order they are OrderKey (0), StorerKey (1), ExternOrdKey (2), BuyerPO (3), DeliveryDate (4), and OrderDate (5). The list itself is set to multiple selection and it's record source is a query called "OrderHeader".

After selecting a number of lines, I want to next produce a report called "Consolidated Gate Pass" that lists all the Orders as well as quantities and other information pertaining to the orders that have been selected. The recordsource for this report is another query called "orderdetails" and the common reference between the listbox and the field is the "OrderKey" field I have no code developed as yet, because I don't know how to start this one. Incidentally the OrderKey field in the table is formatted to Text and can't be changed because of the ties that it has to another program.

Can anyone help me??

Thanks, Tadynn.

 

[bdmclennan]

Tadynn,

Try this.

Open your report with a where statement
Call fncInList to create an instatement.

for example:

DoCmd.OpenReport stDocName, acPreview, , "[dteDATE]" & fncInList("lstBox", 0)


'=========================
'fncInList
'Place this code in your form
'strControlName = Name of your list box
'intColumn = the column number you want
'==================================


Public Function fncInList(strControlName As String, intColumn As Integer) As String
Dim intLoop As Integer
Dim strOut As String

'=====================
'Loop thru lstbox and locate all
'Selected items
'=====================
For intLoop = 0 To Me(strControlName).ListCount - 1
If Me(strControlName).Selected(intLoop) Then
strOut = strOut & "'" & Me(strControlName).Column(intColumn, intLoop) & "',"
End If
Next

'=====================
'Create an in statement if rows are selected
'=====================

If Len(strOut) > 0 Then
fncInList = "In (" & Mid(strOut, 1, Len(strOut) - 1) & &quot"
Else
fncInList = ""
End If

End Function

 

[Tadynn]

Hi bdmclennan. Thanks for your great input!!

I've put your code into my form and changed whatever needed to be changed. But I'm not sure about my where statement, I've inserted a wherecondition into the button that previews the report. But I'm only getting all records and not the ones that I've selected from the List box. What am I doing wrong??

'=====================
'Print button with Where condition
'=====================

Private Sub PrintButton_Click()
stDocName = "Gate Pass"
DoCmd.Minimize
DoCmd.OpenReport stDocName, acPreview
Wherecondition = "[OrderKey]" & fncInList("List0", 0)

End Sub

'=====================
'Routine in Listbox
'=====================

Public Function fncInList(strControlName As String, intColumn As Integer) As String
Dim intLoop As Integer
Dim strOut As String

'=====================
'Loop thru lstbox and locate all
'Selected items
'=====================
For intLoop = 0 To Me("List0&quot.ListCount - 1
If Me("List0&quot.Selected(intLoop) Then
strOut = strOut & "'" & Me("List0&quot.Column(0, intLoop) & ","
End If
Next

'=====================
'Create an in statement if rows are selected
'=====================

If Len(strOut) > 0 Then
fncInList = "In (" & Mid(strOut, 1, Len(strOut) - 1) & &quot"
Else
fncInList = ""
End If

End Function


Thanks, Tadynn.

 

[bdmclennan]

Tadynn,

Your Openreport statement should read like this:

DoCmd.OpenReport stDocName, acPreview,,"[OrderKey] " & fncInList("List0", 0)

Cheers

Bruce