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


jdallainAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
clarkscottConnect With a Mentor Commented:
The way your tables are designed, it looks like there can only be 1 nutrition (record) per medid record.
If this is NOT true and you can have multiple nutrition records per medid record, then put a medid field in the nutrition table and lose the nutimpid field from the med table.
In any case...
Create a query using the nutrition table and add the nutimpid field to a column.
In the criterial column, enter   = forms![YourForm].[nutimpid].
Use this query as the recordsource for your nutrition drop-down box (combo).
In the after update event of the med combo, type  me.YourNutCombo.requery
Scott C
 
0
 
jdallainAuthor Commented:
Thanks Scott! It works great! Is there any way to do that in a query as well?
0
 
clarkscottCommented:
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
 
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.