Query with parameters from a form

  • Thread starter Thread starter Dimitris Nikolakakis
  • Start date Start date
D

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
 
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
 
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
 
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
 
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
 
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.
 
Back
Top