Sending info from a list to a report- Please help

[Benjamin Bolduc]

Hi everyone!

I'm having some trouble with a form/report I'm working on. My form is called "Order Check" and right now it has a combobox that's rowsource is the dueorders querie.

I want to be able to double-click on any item in the list and have just that item's info sent to a report. I'm not to familiar with doing this, but this is what I have so far:

Private Sub TheList_DblClick(Cancel As Integer)

Dim strSQL As String
Set DB = CurrentDb
Set DueOrders = DB.OpenRecordset("Orders")

strSQL = "SELECT Orders.[Transaction#], Inventory.[Extra Info], Inventory.Casing, Inventory.Type, Inventory.Flavor, Inventory.Size, Inventory.Brand, [Address Directory].[Email Address], [Address Directory].[Zip Code], [Address Directory].State, [Address Directory].City, [Address Directory].Address, [Address Directory].[Fax #], [Address Directory].[Phone #], [Address Directory].Contact, Orders.[UPC#], Orders.[Product Code], Orders.[PO#], Orders.[Quantity Ordered], Orders.Supplier, Orders.[Shipping Method], Orders.[Date Ordered], Orders.[Shipping Date], Orders.ETA, Orders.[Received Date]" & _

"FROM ([Address Directory] RIGHT JOIN Orders ON [Address Directory].Company=Orders.Supplier) LEFT JOIN Inventory ON Orders.[UPC#]=Inventory.[UPC#]" & _

"WHERE [Transaction#]= TheList.ItemData(TheList.ListIndex)"

DoCmd.OpenReport "Dueordersreport", acViewDesign
Reports![dueordersreport].Report.RecordSource = strSQL


End Sub

-- Instead of referencing the query directly, I just cut and pasted the SQL. "TheList" is the name of my combobox on the "Order Check" form, and "Dueordersreport" is the name of the report I want to send the info to. "Transaction#" is the primary key.

The "WHERE" statment is where I'm getting stuck, I just can't seem to get the syntax right and I'm not even sure if I'm going about this the right way. Access help is useless so if anyone can help me out, it would be very much appreciated.

Thanks!
Ben

 

[Pat Hartman]

Save the query as a querydef without a where clause and use the query as the recordsource for the report.

Then use the where argument of the OpenReport Method to supply the where criteria:

Private Sub TheList_DblClick(Cancel As Integer)
Dim stWhere As String
stWhere = '[Transaction#]= " & Me.TheList
DoCmd.OpenReport "Dueordersreport", acPreview, , stWhere
End Sub

 

[Benjamin Bolduc]

Pat, you never cease to amaze me. I honestly tried to figure that out for hours and you did it in just three lines of code. Bravo!

Thanks!
Ben