Solved

Drop down menu with corresponding values

Posted on 2008-10-07
3
176 Views
Last Modified: 2013-11-28
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


0
Comment
Question by:jdallain
  • 2
3 Comments
 
LVL 20

Accepted Solution

by:
clarkscott earned 500 total points
ID: 22663038
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
 

Author Comment

by:jdallain
ID: 22668761
Thanks Scott! It works great! Is there any way to do that in a query as well?
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 22672005
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now