Autofill field based on other field

[gwunta1907]

Ive spent the last hour and a half trying to sort this out. I know this is a dead simple thing to do but it just doesn't seem to be working for me.

I have a form based on a table (tbl_MMT) that contains a combo box amongst other things. The combo box looks up values from another table (tbl_shipping_charges) and is bound to the shipping charge ID. I then have a number of other fields on the same form that need to be auto filled with information from the table tbl_shipping_charges based on the shipping charge ID that is selected (the user doesn't see the shipping charge ID, rather a charge code they are used to seeing).

I tried creating a query that simply selects one field from the tbl_shipping_charges table based on the coed that the user selects, and it works fine, the query gives me a single value result (as opposed to a range of values). I then transferred the SQL from the correctly running query to the control source for the corresponding field in the form but don't get any result - it displays the "Name?" value in the field instead.

Ive tried writing plain SQL as the control source for the field but that doesn't work either.

I know that as the fields are calculated, there is no real need to have them on the form, but the powers that be require them on the form so I have to be able to display them. They also need to be saved into the same table (tbl_MMT) as the other fields because of various business rules.

Any help on this would really be appreciated. This sort of thing Im sure is used commonly in a variety of applications. Ive tried the search function on here but the results are far too broad.

Thanks in advance

 

[gwunta1907]

I suppose I should also add that if I paste in the access generated SQL as the control source, it gets truncated for some reason (possibly because of a rule in access to do with multiple results from a single query).

The query I pasted into the control source is as follows:

SELECT tbl_Shipping_Charges.PFO FROM tbl_Shipping_Charges WHERE (((tbl_Shipping_Charges.Item_ID)=[Forms]![frm_MMT]![ItemNum]));

But it gets truncated into

[SELECT tbl_Shipping_Charges].[[PFO FROM tbl_Shipping_Charges WHERE ([tbl_Shipping_Charges]

The results from the query are such that only one value can be returned from it

 

[gwunta1907]

Update: Tried the DLookup function with the following syntax in one of the fields:

DLookup("PFO","tbl_Shipping_Charges","Item_ID = " & Me!ItemNum)

However still no luck....

 

[gwunta1907]

John, could you offer any help on this?

 

[Kiwiman]

Howzit

Put your dlookup in the afterupdate event procedure of your code selection. The dllokup syntax looks fine, you just need to assign it to another control

me.yourotherfield = DLookup("PFO","tbl_Shipping_Charges","Item_ID = " & Me.ItemNum)

 

[gwunta1907]

Thankyou very much kind sir!! Kiwiman, your solution worked perfect. I was putting the DLookup on the Lostfocus event of the control I wanted to be as the target of the lookup. Thankyou again