Solved

Drop down menu with corresponding values

Posted on 2008-10-07
3
175 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

743 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

10 Experts available now in Live!

Get 1:1 Help Now