424,294 Members | 1,898 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,294 IT Pros & Developers. It's quick & easy.

How to Store / Save values Retrieved from DLookup

P: 51
Hello Friends.........
In the Data Entry Form..... I am using following fields by using Combo Box
Company Name.......... Combo Box .. and get Company ID by using DLookup
Region Name ............ Combo Box .. and get RegionID by using Dlookup
Area Name................ Combo Box ... and get AreaID by using Dlookup...
This is working OK....

I want to store the values I got from Dlookup........ plz some one guide me how to do it.....

Thanks
Irshad Ahmed
1 Week Ago #1
Share this Question
Share on Google+
7 Replies


Seth Schrock
Expert 2.5K+
P: 2,920
What you should do is have the combo boxes bound to the company ID, etc fields. Then you don't have to use DLookup() at all (it shouldn't be used when other options are available, as it is resource intensive). In the row source of the combo box, you would want a SELECT SQL statement that gives two fields: the company ID and the company name. Make sure the Bound To property is set to 1. You may need to change the columns property to 2, and the Column Widths property to 0";1.5" (this is inches, so if your installation isn't English you will need to change it to the proper metric).
1 Week Ago #2

P: 51
Dear Mr Seth Schrock...

kindly guide me what will be the complete SQL statement. and will this save the required information. ?
irshad
1 Week Ago #3

Seth Schrock
Expert 2.5K+
P: 2,920
Without knowing your table structures, I can't give the exact SQL. However I can give you a sample and you just have to make the name changes.
Expand|Select|Wrap|Line Numbers
  1. SELECT [Company ID], [Company Name]
  2. FROM [Company Table Name]
Make sure that your field names (Company ID and Company Name) and the company table name match your setup.
1 Week Ago #4

twinnyfo
Expert Mod 2.5K+
P: 2,894
Irsmalik,

You’ve been around our forum for a short bit, so you should know that we need more information from your description. Exactly what is it that you are asking for.

Your question may have a very simple solution (if I understand it correctly—in which case Seth’s first response would be correct, and require no VBA whatsoever). However, I’m not sure this is what you are trying to do.

Please give us a more detailed description of what it is you want to do.
1 Week Ago #5

P: 51
Hello Seniors
I have already made my Data Entry Form. Now I am want to modify it. Plz see the attachment of my Data Entry Form. I am using 2 tables.

Table 1 tblMain Containing required information like (CompanyID, CompanyName, RegionID, RegionName, AreaID , Area name and Division Name
Table 2 tblEntry Saving above mentioned info and Sales Staff Activities like FAS, FM, HPM etc

On Data Entry Form (plz see the attach picture). I select CompanyName via Combo Box and its related CompanyID will be displayed on Txt1 Field by using DLookup.

Company Name is being saved in Entry Table as it has Control Source of Combo Box.
CompanyID …. Control Source is DLookup formula… so it is not saving.

How can I save CompanyID in my Entry Table.
If there is an other way to Retrieve + Save these information… plz guide me.

I know these information are already saved in the Main Table. But I want to save these info in the Entry Table too.

Thanks
Irsmalik
Attached Images
File Type: jpg Data Entry Form.jpg (60.4 KB, 6 views)
1 Week Ago #6

Seth Schrock
Expert 2.5K+
P: 2,920
Sounds like your data isn't normalized at all. See Database Normalization and Table Structures. Until you have it normalized, you will have a lot of problems. You should have a table that contains all your companies, another table that contains all your regions, and another that contains all your divisions. You then link to your table with a primary key/foreign key relationship and your current problem becomes very easy.
1 Week Ago #7

twinnyfo
Expert Mod 2.5K+
P: 2,894
Yes, there should be no need to use DLookup for your Combo Boxes, unless there is something we are missing.
6 Days Ago #8

Post your reply

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