Opening Query From Form As Report

[PCHomepage]

Using a button on the form, I am trying to open a report that can be used to print an invoice of the transaction. The query used joins the necessary tables and puts the needed data together but I am not sure how to make submit the ID so that it opens only a single record! Right now the button launches this code:

Private Sub ViewRequests_Click()

DoCmd.OpenReport "Order Invoice", acViewPreview, , _
                 "[ID]=Forms!frmAdd an Order and Details"

End Sub

but it gives

Run-time error '3075';

Syntax error (missing operator) in query expression
'[ID]=Forms!Add an Order and Details'.

I'm very experienced in databases but from a Web-programming point of view so I'm not sure how to do this directly in Access (Access 2007 but the form is Access 2002-2003 format). Any ideas?

Thanks!

Don

 

[dhookom]

Try:

Private Sub ViewRequests_Click()
   Dim strWhere as String
   'if ID is numeric
   strWhere = "[ID]= " & Me.ID
   'if ID is text uncomment this line
   'strWhere = "[ID]= """ & Me.ID & """"
   DoCmd.OpenReport "Order Invoice", acViewPreview, , strWhere
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

What is this supposed to be?
Forms!frmAdd an Order and Details

is this a form named frmAdd and a control called [an Order and Details"

makes no sense to me?

 

[PHV]

Replace this:
"[ID]=Forms!frmAdd an Order and Details"
with this:
"[ID]=Forms![!][[/!]frmAdd an Order and Details[!]][/!]"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Oops, sorry, disregard my reply.

 

[PCHomepage]

Thanks, the brackets make sense as I was wondering how it separated the form name from the rest. With them, though, it still gives the same error but it's good to know this little detail for the next time.

The code supplied by dhookum sounds like what I need. What is the "Me" in Me.OrderID? Should that be changed to the name of my query?

 

[dhookom]

Me." is shorthand for the current form. So "Me.OrderID" is the OrderID of the current form which is the value of OrderID.

Duane
Hook'D on Access
MS Access MVP

 

[PCHomepage]

Thanks for the explanation! That's what I thought but wanted to be sure. It does appear to be working now that I discovered that the ID field is actually OrderID on the form:

Private Sub ViewRequests_Click()
   Dim strWhere as String
    strWhere = "[ID]= " & Me.OrderID
   DoCmd.OpenReport "Order Invoice", acViewPreview, , strWhere
End Sub

Thanks again for the help!

Don