How to auto populate a field after combo box selection

[mattmurdock]

I have a form called "New Item Form" that has a three combo boxes- Size, Price and Upcharge. Price is determined by the Size. I have a query for Size and Price (SizePrice). How do I get my form for when size is selected, it auto fills in the price? My dB is attached.


I'm kind of new to access so the most clear and direct directions (like step by step) would be greatly appreciated. Thank you in advance!

[pbaldy]

Does this help?

BaldyWeb - Autofill

[jwill]

mattmurdock...

I looked at your database and it needs to be what is called "normalized". This will help w/ the combo boxes as well.


1. In the NDP table and the Cansize(oz) field, only the ID number from the table Can_Size should go in there. If not, you have duplicating info. Like have the same phone number in two place in your db, it's unnecessary. I would remove the lookup feature from the Cansize(oz) field in NDP (or delete the column and re-create it).

2. If the price is always dependent on the can size, then these should be in the same table (move price column from the NDP table to the can_size table, rename the cansize(oz) column in NDP to something like cansizeID (because it can get confusing), and delete the price table being sure to populate the prices in the can_size table before you delete it). Here is how the can_size table should look:


Field Name Data Type
ID Autonumber
CanSize(oz) number
price number

3. Erase the one record you have in NDP (remember it if you need to) and create a relationship between ID (in the Can_size table) and cansizeID(in the NDP table)

4. Recreate your sizeprice query like this:

SELECT *
FROM NDP INNER JOIN can_size ON ndp.cansizeid = can_size.id;

5. Base your form on the sizeprice query

6. Go to properties on your Can Size combo box and make the

control source = cansize.ID
row source = SELECT * FROM can_size;
bound column = 1


7. Change your price combo box to a text box; set control source = price


This should cause your prices to auto populate. You may want to pick up a comprehensive Microsoft Access book, this will help a lot with not only Access feature but will help some w/ structure. Hope this helps.