Pass Through Query

[LadyDi]

Is there a way to add parameters to a Pass Through query?

 

[khawar]

Your parameters will be on server side or you can change the sql of your query according to your parameters using vba

 

[LadyDi]

Could you explain these two options in more detail? This is the first time I have tried to create a Pass Through Query.

 

[khawar]

For example I have a pass through query named "LedgerQuery" I use a form to provide start and end dates for the report which is based on that query

I use the below mentioned code for this

CurrentDb.QueryDefs("LedgerQuery").SQL = "Select * from Table1 where LedgerDate between " & MyForm!StartDate & " and " & MyForm!EndDate



.

 

[LadyDi]

I tried placing this code on a button:
CurrentDb.QueryDefs("DB2 QUERY").SQL = "SELECT ORNO, ODAT, C.NAME FROM PROD.TDL040100 A JOIN PROD.CUST_CONV B ON A.CUNO = B.CUNO JOIN PROD.CUSTOMER C ON B.CCMF = C.CUST_NUM WHERE ORNO = " & [DB2 FORM]!ORNO
However, I keep getting an error message. The message reads "Microsoft Office Access can't find the field '|' referred to in your expression".


Then I tried shortening the code to this: CurrentDb.QueryDefs("DB2 QUERY").SQL = "SELECT ORNO FROM PROD.TDL040100 WHERE ORNO = " & [DB2 FORM]!ORNO. I still got the same error message.

What am I doing wrong?

 

[khawar]

Add forms! in start of the name of control like

[forms]![DB2 FORM]!ORNO

 

[LadyDi]

This is perfect. It does exactly what I need it to do. However, I have one more question. After I update the query definitions and run the query, I want to use a dlookup to enter the data from that query into my form. I keep getting an error message stating that the fields are not updateable. For some reason, I think it is trying to update the query instead of the form. Here is the code that I have:

CurrentDb.QueryDefs("DB2 QUERY").SQL = "SELECT ORNO, ODAT, C.NAME AS CUST_NAME FROM PROD.TDL040100 A JOIN PROD.CUST_CONV B ON A.CUNO = B.CUNO JOIN PROD.CUSTOMER C ON B.CCMF = C.CUST_NUM WHERE ORNO = " & [Forms]![DB2 Form]!ORNO
DoCmd.OpenQuery "DB2 Query", acViewNormal, acEdit
[Forms]![DB2 Form]!ODAT = DLookup("[ODAT]", "[DB2 Query]", "[ORNO] = '" & Me.[ORNO] & "'")

Can you tell me what I'm doing wrong?