use a result of a query as input criteria for another query

[Guest]

I have a drug list database with fields as prodid, generic name, strength
(dose), form (oral, injection...). I want to design a search screen where
people just put any part of the generic into an input box and the result will
display all the generic drug form & dose. example:
input : 'aspirine'
results: 1/aspirine 300mg oral
2/ aspirine 200mg suppository
3/ aspirine 50mg injection
The next step is to select (double click) on one of the result in step 1
e.g. select 1/ will run another query will show for example location and
quantity on hand..
1/ aspirine 300mg oral store 1 Quantity on Hand : 50
The prodID is unique identifier for generic + form + dose
Thinking of using a parameter query for step 1 but don't know how to use the
result of step 1 as input parameter for step 2 ? Am I in the right direction
? any suggestion ? sort of narrow down the seach ...Thanks

 

[Jamie Collins]

I have a drug list database with fields as prodid, generic name, strength
(dose), form (oral, injection...). I want to design a search screen where
people just put any part of the generic into an input box and the result will
display all the generic drug form & dose. example:
input : 'aspirine'
results: 1/aspirine 300mg oral
2/ aspirine 200mg suppository
3/ aspirine 50mg injection
The next step is to select (double click) on one of the result in step 1
e.g. select 1/ will run another query will show for example location and
quantity on hand..
1/ aspirine 300mg oral store 1 Quantity on Hand : 50
The prodID is unique identifier for generic + form + dose
Thinking of using a parameter query for step 1 but don't know how to use the
result of step 1 as input parameter for step 2 ? Am I in the right direction
? any suggestion ? sort of narrow down the seach ...Thanks

The obvious answer would be, "Whatever it is you use to key the table
with the 'quantity on hand' attribute" i.e. a another parameter query
presumably with three parameters for generic, form and dose
respectively. This assumes you want to hit the database a second time;
alternatively, you could get the whole set first time round as one big
resultset and filter based on user choice which makes 'backing out'
easier e.g. user discovers quantity on hand is zero and wants to try a
different form*. I particularly like ADO's hierarchical recordsets for
this but AFAIK Access doesn't provide any data bound hierarchical
controls.

* Where you use the name 'form' (a reserved word in Access) we use the
name 'route'.

Jamie.

--