asked on

Drop down menu with corresponding values

Hey Experts,

I just can't figure this out. I want to create a drop down menu for medications on a form and have the corresponding nutritional implication pop up. Here is what I have (it's abbreviated):

Table1: tblClient
Field1: ClientID (autonumber)
Field2: ClientName (text)
Field3: AID (number)

Table2: tblNA (Nutrition Assessment)
Field1: AID (autonumber)
Field2: AssessmentInfo (text)
Field3: MedID (number)

Table3: tblsubMed
Field1: MedID (autonumber)
Field2: MedName (text)
Field3: NutImpID (number)

Table4: tblNutImp (Nutrition Implications)
Field1: NutImpID (autonumber)
Field2: WeightCh (text)
Field3: AppCh (text)

I know how to setup a form with a subform, but I'd like to select a [MedName], maybe on a continous form, then the corresponing nutrition implications pop up. And do that over and over on each assessment depending on the number of meds that the child is on.

I appreciate any help more than you can imagine. Thanks.

James

ASKER CERTIFIED SOLUTION

clarkscott

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jdallain

ASKER

Thanks Scott! It works great! Is there any way to do that in a query as well?

clarkscott

dim sql as string
sql = "Select * from YourTable where YourField = " & yourvalue

(if YourField is text then you must put quotes around yourvalue)
sql = "Select * from YourTable where YourField = " & chr(34) & yourvalue & chr(34)

dim db as database
dim rst as recordset
set db = codedb
set rst = db.openrecordset(sql,dbopensnapshot) '--- if you plan to edit the record - then dbopendynaset.
if not rst.eof and not rst.bof then
rst.movefirst
do while not rst.eof
'---- rst!YourField1
'--- rst!yourfield2
rst.movenext
loop
end if
rst.close
db.close

scott c