How do I get a form field to auto populate from a lookup table field

[jimfrmla]

I am new at access programming. I want to get a field on a form to auto populate from a field in a lookup table from another field on the form based on the value that is selected in the original lookup form. Example

sample data in lookup table.

table name Packing Method
10 | pack in crate
11 | pack in box
12 | pack in plastic bag

There is a lookup table/combo box on my for where I can select "10". Once 10 is selectected, I want the text in the detail field "pack in crate" to auto populate another field on my form. I tried variations of =dlookup but I couldn't get this to work....I'm sure it's something simple.....HELP!!!

 

[Spyder1000]

You need to use Dlookup

Let's say your data entry form is named "Frm_DataEntry". It has field "EnterID" and "EnterPackingMethod".

Say your look up sable is called "Tbl_PackingMethod" and contains the fields "ID" and "PackingMethod".

[EnterPackingMethod] =Dlookup("[PackingMethod]", "Tbl_PackingMethod", "[ID] = Froms![Frm_DataEntry]![EnterID]")

 

[PHV]

In the AfterUpdate event procedure of the combo:
Me![relevant control name] = Me![combo name].Column(1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jimfrmla]

I tried severa this and several variations of this over the past fee days and still couldn't get it to work. Maybe I don't have enough details. Could somebody give me step by step instructions. Thanks

 

[randy700]

You have twice stated that it doesn't work but have yet to tell us what is happening. Do you get incorrect results? No results? An error message? What?


Randy

 

[rossco6]

I'm trying to do the same thing, and am also new to Access.

In my case, I have a table with ProdID and UnitPrice fields. In my form, Form1 (original, eh?) I have a combo box (combo2) which displays the values from the ProdID field, I'm trying to populate the text box (text4) field in the form with the price, depending on which prod is chosen from the combo box.

I have tried the suggestions above but always get an error saying that "Macro Text4...... cannot be found". I guess I must be entering the info in the wrong place or something?

Any help would be greatly appreciated and I apologise for asking, which I'm sure will be a straightforward question!

Thanks

 

[thehorse]

rossco,

Look at thread702-953231. It may help you.

thehorse

 

[jimfrmla]

I finally got this to work but only because I deleted the field that was to be auto pulated and re-inserted it again after using this statement. Me![relevant control name] = Me![combo name].Column(1) . It only works if you delete the subject field and re-inserted after putting in this VB statement.