Using AfterUpdate to populate text box from combo value?

[NikToo]

Hello,

First post, new here.

I'm doing a course on Access and I'm trying to populate a text box based on the value I select in a combo box.

Basically, in the combo box I want to select a drug (for pets, it's for a veterinary clinic...) and in the text box next to it I want the cost to pop up. The list of drugs with their costs are listed in a separate table, with the drug name in column 1 (counting from 0) and the cost in column 3. I then have a continous form so I'd like to be able to keep adding drugs and their costs and then sum up the drug costs in a text box with the total.

Any help is much appreciated.

 

[adaniele]

niktoo,
is there any reason for having a list of drugs in a table and another table with a list a drugs + costs? couldnt be in the same table?

The combo and the text box will be in the same form than the form where you want to add the drugs and costs?

anyway try this,
For the forst combo where you select a drug you could do the following:

1- in the property window, data tab use the row source type, and select table/query. Then in the row source field, write the select statement

for filling the text box you can add in the after update of the combo a dlookup, and use as a condition the value selected in the combo.

ex:
Combo:
Row Source type: TABLE/QUERY
row source: select <drug field name> from <tablename>

in the after_update:

me.<textboxname>=DLookup("[costfieldname]", "table", "drugname='" & Me.<comboname> & "'")

i hope this hlp.

 

[davidbodhi]

NikToo

I've just done basically the same thing.

Have your combo box based on a query that pulls the drug name, it's unique ID (autonumber, probly) and the cost.
Bind the combo to the ID column (remembering that Access, in this instance, numbers the 1st column zero, not one).
Make the ID column and cost columns 0" wide, so only the drug name shows.
In the AfterUpdate function of the combo box, put code like this:
costtextboxname = comboname.Column(2)

This assumes that the cost is the 3rd column of the query your combo uses. If it isn't, change the 2 to 0 or 1, whichever.
costtextboxname is the name of the control you want the cost to show up in.
comboname is the name of your combo box.

Hope that's clear enough.

Works great for me and when I return to an old record, the cost still displays without having to refresh or anything.

As to summing the costs, create a control in the form FOOTER, and use DSUM. Check the help files for syntax. Your use sounds pretty straightforward.

However, if you're storing transactions, in order to keep DSUM from totalling all the transactions together, you may want to create a parent form with a Total control (text box) bound to a table that records transactions giving each a unique number.

Have a table of drugs and costs, another of transactions and another of items purchased.

Your parent form will store values in the transactions table. In a subform (the form we started talking about), you'll select drugs from the table of drugs (using their unique number), and store the selected items in the table of items purchased.

The unique transaction number will also go in the table of items purchased which will store something like: drug 5, transaction 25, drug 8, transaction 25, drug 12, transaction 25, drug 5, transaction 26, drug 15, transaction 26, etc.

This way, you enter drug names and costs in your table.
You record transactions' totals, etc, using the parent form and it's table.
You store the records detailing what was purchased each time in the purchased items table, which also records in which transaction each one was purchased by storing the unique transaction number.

Hope *this* makes SOME sense. lol