Updating control based on another table

[Monovian]

I have a form based on two tables. Table A Holds advert data and Table B holds the advert types and their fee. The form is for the entry of new adverts and records data in Table A. When I enter the advert type code (from Table B) in one control I would like the fee (also from Table B) to be shown in another control and saved in the adverts table. How do I achieve that please?

 

[Kiwiman]

Howzit

In the after update event procedure of your advert type control you want to assign the fee using a dlookup.

Something like the following:

If the advert type code is a number

me.yourfeectrl = dlookup("[YourFeeField]","tableA","[AdvertTypeCodeField]=" & me.yourtypecode)

if your advert type code is text

If the advert type code is a number

me.yourfeectrl = dlookup("[YourFeeField]","tableA","[AdvertTypeCodeField]='" & me.yourtypecode & "'")

 

[missinglinq]

Ignore this line

If the advert type code is a number

the second time it appears in Kiwiman's post!

The second code example is correct for advert type code when it is Text.

 

[Kiwiman]

Howzit

Thanks linq - didn't spot that

 

[missinglinq]

Copy & Paste is a double-edged sword!