Query with parameters from a form

[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

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

 

[Nikos Yannacopoulos]

Dimitri,

FOrdersTx is opened as a subform in FOrders, therefore you have to
reference it accordingly:

SELECT xxxxxx FROM

WHERE field1 = Forms!FOrders!Factory AND
field2 = Forms!FOrders!FOrdersTx!StorageItem

HTH,
Nikos

 

[Dimitris Nikolakakis]

My query:

SELECT xxxxxxxxx
FROM tables
WHERE Orders.ClientID=Forms!FOrdersInsert!ClientID And
OrdersTx.StorageItem=Forms!FOrdersInsert!FOrdersInsertTx!ItemCode


When I run the query I am asked to insert the parameter value
Forms!FOrdersInsert!FOrdersInsertTx!ItemCode

Thanks in advance

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

 

[Dimitris Nikolakakis]

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]

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.