If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query with parameters from a form
I have access 2003.
I have a form and a subform (FOrders & FOrdersTx) I have created a query with parameters from the above form : SELECT xxxxxx FROM [table] WHERE field1 = Forms!FOrders!Factory AND field2 = Forms!FOrdersTx!StorageItem The query recognizes the Forms!FOrders!Factory but not the Forms!FOrdersTx!StorageItem and asks for the value. Thanks in advance Dimitris Nikolakakis |
#2
|
|||
|
|||
Dimitri,
FOrdersTx is opened as a subform in FOrders, therefore you have to reference it accordingly: SELECT xxxxxx FROM [table] WHERE field1 = Forms!FOrders!Factory AND field2 = Forms!FOrders!FOrdersTx!StorageItem HTH, Nikos Dimitris Nikolakakis wrote: I have access 2003. I have a form and a subform (FOrders & FOrdersTx) I have created a query with parameters from the above form : SELECT xxxxxx FROM [table] WHERE field1 = Forms!FOrders!Factory AND field2 = Forms!FOrdersTx!StorageItem The query recognizes the Forms!FOrders!Factory but not the Forms!FOrdersTx!StorageItem and asks for the value. Thanks in advance Dimitris Nikolakakis |
#3
|
|||
|
|||
My query:
SELECT xxxxxxxxx FROM tables WHERE Orders.ClientID=Forms!FOrdersInsert!ClientID And OrdersTx.StorageItem=Forms!FOrdersInsert!FOrdersIn sertTx!ItemCode When I run the query I am asked to insert the parameter value Forms!FOrdersInsert!FOrdersInsertTx!ItemCode Thanks in advance Dimitris Ο "Dimitris Nikolakakis" έγραψε στο μήνυμα ... I have access 2003. I have a form and a subform (FOrders & FOrdersTx) I have created a query with parameters from the above form : SELECT xxxxxx FROM [table] WHERE field1 = Forms!FOrders!Factory AND field2 = Forms!FOrdersTx!StorageItem The query recognizes the Forms!FOrders!Factory but not the Forms!FOrdersTx!StorageItem and asks for the value. Thanks in advance Dimitris Nikolakakis |
#4
|
|||
|
|||
Dimitri,
I'm afraid the wrong reference was so obvious in your original post that it acted like the tree that hid the forrest! Sorry to have misled you. I suppose for each record in the main form there are several records in the subform, so you need a different approach to use them all as your filter; a subquery s what's required here. For instance, if the main form and the subform are "fed" by tables, say, tblOrders & tblOrdersTx respectively, with OrderID being the common field, and txtOrderID being the control on the main form bound to that field, the WHERE clause of your query should look something like: SELECT xxxx FROM yyyyyyy WHERE field1 = Forms!FOrders!Factory AND field2 In (SELECT StorageItem FROM tblOrdersTx WHERE OrderID = Forms!FOrders!txtOrderID) Hope this is clear now. Regards, Nikos |
#5
|
|||
|
|||
Perhaps I was not clear at the beginning.
What I want is to see the history of a StorageItem for the certain customer In the main form I have the orderID and the customer and in the subform I have the records of the order (connected with OrderID). the fields of the subform are StorageItem, Quantity, Price and I want on the dbclick on the price to open the form to find out what is the history of the StorageItem of the line. The first solution you had proposed seemed correct but I could not understand why Access does not accept the "Forms!FOrdersInsert!FOrdersInsertTx!ItemCode" as parameter in my query. Regards Dimitris Ο "Nikos Yannacopoulos" έγραψε στο μήνυμα ... Dimitri, I'm afraid the wrong reference was so obvious in your original post that it acted like the tree that hid the forrest! Sorry to have misled you. I suppose for each record in the main form there are several records in the subform, so you need a different approach to use them all as your filter; a subquery s what's required here. For instance, if the main form and the subform are "fed" by tables, say, tblOrders & tblOrdersTx respectively, with OrderID being the common field, and txtOrderID being the control on the main form bound to that field, the WHERE clause of your query should look something like: SELECT xxxx FROM yyyyyyy WHERE field1 = Forms!FOrders!Factory AND field2 In (SELECT StorageItem FROM tblOrdersTx WHERE OrderID = Forms!FOrders!txtOrderID) Hope this is clear now. Regards, Nikos |
#6
|
|||
|
|||
It works for me, as long as I enclose all object names in square
brackets, like: [Forms]![FOrdersInsert]![FOrdersInsertTx]![ItemCode] Have you tried that? If that doesn't work either, make sure there is no spelling mistake; also, make sure the name of the control on the form is indeed ItemCode, rather than TextX or something. Dimitris Nikolakakis wrote: Perhaps I was not clear at the beginning. What I want is to see the history of a StorageItem for the certain customer In the main form I have the orderID and the customer and in the subform I have the records of the order (connected with OrderID). the fields of the subform are StorageItem, Quantity, Price and I want on the dbclick on the price to open the form to find out what is the history of the StorageItem of the line. The first solution you had proposed seemed correct but I could not understand why Access does not accept the "Forms!FOrdersInsert!FOrdersInsertTx!ItemCode" as parameter in my query. Regards Dimitris Ο "Nikos Yannacopoulos" έγραψε στο μήνυμα ... Dimitri, I'm afraid the wrong reference was so obvious in your original post that it acted like the tree that hid the forrest! Sorry to have misled you. I suppose for each record in the main form there are several records in the subform, so you need a different approach to use them all as your filter; a subquery s what's required here. For instance, if the main form and the subform are "fed" by tables, say, tblOrders & tblOrdersTx respectively, with OrderID being the common field, and txtOrderID being the control on the main form bound to that field, the WHERE clause of your query should look something like: SELECT xxxx FROM yyyyyyy WHERE field1 = Forms!FOrders!Factory AND field2 In (SELECT StorageItem FROM tblOrdersTx WHERE OrderID = Forms!FOrders!txtOrderID) Hope this is clear now. Regards, Nikos |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Passing parameters from a form built on a crosstab query | ihrigt | General Discussion | 0 | April 25th, 2005 04:13 AM |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
How to get a field on a form to reflect a certain record of a query? | General Discussion | 0 | December 11th, 2004 12:56 AM | |
dlookup | miaplacidus | Using Forms | 9 | August 5th, 2004 09:16 PM |