Change SQL to Code

[PamSt]

I have a customer database and I'm trying to change some SQL in to code. I have a customer form with a subform and I need information from both to open another form. My SQL minis the fields read

FROM Customer INNER JOIN Orders ON Customer.CustomerNumber = Orders.CustomerNumber
WHERE ((([Customer]![CustomerNumber])=[Forms]![NewOrder]![CustomerNumber]) AND (([Orders]![OrdersNumber])=[Forms]![NewOrder].[OrSub].[Form]![OrderNumber]));

I open the form with this query and it works fine but I would like to write it in code. I tried to write it like this:
DoCmd.OpenForm "ExpOrders",,, "CustomerNumber = '"& Me!CustomerNumber& "' And
OrderNumber = '"& Me!OrSub.Form!OrderNumber & "'"
I use this and I keep getting Errors, can anyone see what I'm doing wrong.
Thanks Pam.

 

[SonOfEmidec1100]

Two suggestions to start you off -
Try using a . (decimal point) not the ! (exclamation mark)
and put a space in front of the & characters.

cjw

 

[MichaelRed]

Generally AVOID the practice. Unless it is a pass through query, Ms. A. will parse/compile the sql string every time is is used, causing some delay in processing. A metter approach (in the general case) is perhaps to parameterize the query and pass the parameters during instantiation.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[1127]

"CustomerNumber = '"& Me!CustomerNumber& "' And
OrderNumber = '"& Me!OrSub.Form!OrderNumber & "'"

The CustomerNumber and the OrderNumber are really numbers?

If yes then don't use '

And beatween the CustomerNumber and & leave a space

"CustomerNumber = "& Me!CustomerNumber & " And
OrderNumber = "& Me!OrSub.Form!OrderNumber