Autofill Form based on Static Table Dlookup

[charlie442]

Hi

I have been playing around with dlookups over the weekend, but I am unable to figure this out. I have a form for entering new cases into my database (call it "Frm_New_Cases") whose control source is Tbl_Cases. One of the fields in this table is "Reference".

Now when I fill in the Reference field on Frm_New_Cases I would like the "Company" field to update automatically on the form based on a dlookup to static table called Tbl_Companies. Tbl_Companies has the fields "REF_NO", "COMPANY_NAME" etc. While the names of the fields are slightly different I should still be ble to use the Dlookup right?

Please could someone advice me how to do this. I'm guessing it's really simple but I m not the sharpest when it comes to VBA. However this will save my manager and I plenty of time when entering new cases. I will obviously roll this out to other fields but if I can get this one right I'll figure the rest out

 

[John Big Booty]

In After Update event of your field Reference put some cod like the following;

Me.Company = [URL="http://www.techonthenet.com/access/functions/domain/dlookup.php"]DLookup[/URL]("COMPANY_NAME" , "Tbl_Companies" , "Reference = " & Me.Reference)

 

[boblarson]

But I am going to say - Don't store the name of the company. DISPLAY it but do NOT store it. Store the ID of the company in your tbl_Cases.

 

[John Big Booty]

Given that we are using DLookup to populate the Company field perhaps that field is unbound (perhaps not too, just playing devils advocate ), and given that field Reference has allowed us to collect the company name; would there be any need to store the CompanyID as well?

I'm not suggesting, however, that we are currently dealing with a fully normalised table structure.

 

[boblarson]

First, I was assuming a bound form to tbl_Cases.

Second, if so, it should just be a combo box where the name is displayed and the ID saved. And if so - then no need for a DLookup. But if using a DLookup I was instructing for the OP that they need to store the ID in the table and NOT the company name.

 

[John Big Booty]

Yes a combo would be a whole lot simpler, and would certainly be how I would approach the problem