Solved

Drop down menu with corresponding values

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

830 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