By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

Report back the value from a field in a table when I know another field in the table

MattFitzgerald
P: 30
I have a main menu ("Frm_Startup") which contains a drop down box for selecting a field "LE ID" from my customer table where the "LE ID" is the primary key and there is another field with the customers "Legal Entity Desc"

I have a button "Add new Order" which opens a new form where I can enter new orders.

In the form for new orders the Default value for the LE ID is the value selected in the main menu .

I wanted to pick up the customers "Legal Entity Name" from my Customer Table to populate the default value for the Customer Name field in my orders table using the LE ID enterd on the main menu.

In the properties of "Customer Name" for default value I tried the code below

Expand|Select|Wrap|Line Numbers
  1. = [Tbl_LE_List]![LEGAL ENTITY DESC] where [Tbl_LE_List]![LE ID] = Forms![Frm_Startup]![CMD LE] 
Please can some one help I am new to Access and think I have got a bit lost,
Aug 13 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,382
I have a main menu ("Frm_Startup") which contains a drop down box for selecting a field "LE ID" from my customer table where the "LE ID" is the primary key and there is another field with the customers "Legal Entity Desc"

I have a button "Add new Order" which opens a new form where I can enter new orders.

In the form for new orders the Default value for the LE ID is the value selected in the main menu .

I wanted to pick up the customers "Legal Entity Name" from my Customer Table to populate the default value for the Customer Name field in my orders table using the LE ID enterd on the main menu.

In the properties of "Customer Name" for default value I tried the code below

Expand|Select|Wrap|Line Numbers
  1. = [Tbl_LE_List]![LEGAL ENTITY DESC] where [Tbl_LE_List]![LE ID] = Forms![Frm_Startup]![CMD LE] 
Please can some one help I am new to Access and think I have got a bit lost,
You can't refer to items in a table that way.

You can, however, use the DLookup function.
Expand|Select|Wrap|Line Numbers
  1. DLookup("[LEGAL ENTITY DESC]", "[Tbl_LE_List]", "[LE ID] = Forms![Frm_Startup]![CMD LE]"
  2.  
It's not the most efficient solution but the other one is more complicated.
Aug 13 '07 #2

MattFitzgerald
P: 30
Thanks that answers my question - and worked just as I wanted it to.

With the more efficient method I would be very interested to know it if it only takes a short amount of your time.
Aug 16 '07 #3

Rabbit
Expert Mod 10K+
P: 12,382
Actually, I just realized it doesn't work unless [LEGAL ENTITY DESC] is also stored in your main menu.
Aug 16 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.