Using VBA with Combo Boxes

[gaber85]

Hello, I am still new to the access world and having some trouble so I
thought I'd post my problem and see if someone can help me. I created a
printer cartridge database and would like to have the price of the cartridge
automatically calculated based on the cartridge I choose as well as the
quantity. I have a table named tblCartridges that contains all of the
different printer cartridge numbers along with the unit price of each. I
also have a table named tblTransactions. The form that is based off of the
tblTransactions is where the I can select the cartridge from a combo box, put
in the qty and have to manually enter the price. Do I need to create a
relationship between tblCartridges and tblTransaction in order to write a
piece of VBA code that will allow the unit price to be calculated based on
the cartridge and quantity I enter? If I do need to create a realation ship
how would I go about it? Also how would you write the VBA code that would
look at the combo box field and see what cartridge I chose, look at the
quantity that I entered and calculate if for you in the unit price field? Any
help is much appreciated. Thanks

 

[Ken Snell \(MVP\)]

See this article on The ACCESS Web; it should give you a good starting
point. See the second option.
http://www.mvps.org/access/forms/frm0058.htm

 

[JonWayn]

First off, it is not real good database practice to stored calculated data in
tables because the factors that go into those calculations may change
throughout the life of the database, and would only necessitate finding those
records and updating them with the recalculated data. Furthermore, you can
always use queries to display the calculated result, whatever it is, whenever
you open that query.

If, on the other hand, you just wanted to see the calculated value in a
form, but not store it, then:

Let's say you have a textbox named txtUnitPrice, a label named
lblTotalPrice, and your combobox is named cboInkNum. Then in the AfterUpdate
event of cboInkNum, place the following code:

lblTotalPrice.Caption = cboInkNum * cboUnitPrice


Or

If lblTotalPrice was another textbox instead, enter:

lblTotalPrice = cboInkNum * txtUnitPrice


'Good luck

 

[Ken Snell \(MVP\)]

First off, it is not real good database practice to stored calculated data
in
tables because the factors that go into those calculations may change

Completely depends upon the need for the storing of the data. For example,
it is highly recommended to store the price of an item that was ordered,
even though the price is stored elsewhere in the database, so that you can
have an accurate history of prices after you've made updates to prices --
and there are many other similar situations for calculated results.
Additionally, sometimes it's helpful to store intermediate calculations that
involve aggregated data, as this can speed up reports that are run from
those data.

But, I agree with you if one is just wanting to store a result because one
can store a result.

 

[JonWayn]

I was aware that there were some exceptions to the general rule, and I meant
to make mention of it, but left it out - forgot.

But, good point to note.

 

[gaber85 via AccessMonster.com]

Thanks a bunch for your help, this is all good information that you have
given and I appreciate it. Thanks

 

[gaber85 via AccessMonster.com]

I wanted to thank you also with your and Ken's information I am making good
progress on my issue and just about have it done.

I was aware that there were some exceptions to the general rule, and I meant
to make mention of it, but left it out - forgot.

But, good point to note.
[quoted text clipped - 11 lines]

But, I agree with you if one is just wanting to store a result because one
can store a result.