How to get values from another table based on drop down list.

[Zak14]

In my form's table (tblMain), I've got a lookup field (drop-down list) that lists the primary key field from a different table (tblDiff).

tblDiff includes 3 more fields.
In my form for tblMain, I want to include 3 more textboxes that get filled up with these 3 fields from tblDiff when the corresponding primary key is selected in the drop-down box.

I've tried looking it up but can't seem to find much that helps.

Thanksss

 

[pbaldy]

How about:

http://www.baldyweb.com/Autofill.htm

 

[Zak14]

How about:

http://www.baldyweb.com/Autofill.htm

Yep, thank you. It mostly works.
The 2nd and 3rd fields show just fine, but the 1st box doesn't because the corresponding column in the drop-down just displays "1". This must be because the field is a lookup field (to another table).
How can I workaround this?

 

[pbaldy]

Personally I haven't and wouldn't use a lookup field:

http://access.mvps.org/access/lookupfields.htm

If you stick with it, you may need to create a query for the combo row source that joins the tables together so you can get the text value.

 

[Zak14]

Personally I haven't and wouldn't use a lookup field:

http://access.mvps.org/access/lookupfields.htm

If you stick with it, you may need to create a query for the combo row source that joins the tables together so you can get the text value.

I need the field to be a lookup field, unless there's a different way to limit a field to a list from another table. Is there?

The query thing would be too complicated and unefficient I think because this form is a subform for another form.

 

[pbaldy]

A lookup table is great, I use them in most applications. The lookup field is what most of us recommend against. I either refer directly to the lookup table or use a query, either way hiding the key field.

 

[Zak14]

I'm sorry for my ignorance but what do you mean by directly referring to the lookup table?
And how do you use a query for this?
Is it also possible to conclude by getting a drop down on the form level?

 

[Uncle Gizmo]

Have a look at this thread:-

http://www.access-programmers.co.uk/forums/showthread.php?t=267631

 

[Zak14]

Thanks so much for this guys, I got what I was looking for.
I'm just gonna go change all my lookup fields to form level only!

 

[pbaldy]

No problem.