Autofill field based on other field

[gwunta]

Ive spent the last 2 and a half hours 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.

Ive tried DLookup using the following syntax:

DLookup("POF", "tbl_Shipping_Charges", "Item_ID = " & Me![ItemNum])

where POF represents the field that contains the value I want to insert into the field on the form



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.

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

Thanks in advance

[R_Badger]

I would enclosed an example but im at work at the moment and thanks to ie6 i have lsot a lot of functionality!What you seem to be looking for is a join queryWhen you create a query from two tables you can join them using a foreign key (in this case your shipping charge id) using a join query you can show data from both tables.It helps if you have established the relationships using that tools->relationships but you dont have to.When you open up the query and it asks you for the tables you want select both of them, and add all the fields you wish to see from the pair.The should be a black line (relationship) between your shipping charge id on both tables, if there isnt drag the field some one field list at the top to the other. On the followingdialog you want to show all where the values are equal.Give that a try see if thats what you are after. I'll try to upload an example when I get home!