Problem Creating an AfterUpdate() Event

  • Thread starter Thread starter Marcia
  • Start date Start date
M

Marcia

I am trying to design a simple inventory database for work. Among my
tables are:

tblCrafts:
fldCraftID (PK)
fldCraftName
fldCurrentSalePrice

tblInventoryTransactions:
fldInvTransactionID (PK)
fldInvCraftID
fldSalePrice
fldQuantity

My problem lies in creating a "static" sale price AT THE TIME I enter
the transaction. My reason for doing this is to assign the current
sale price from tblCrafts to the fldSalePrice in my Inventory table
(when the transaction takes place) that will NOT change if the
fldCurrentSalePrice in my Crafts table is adjusted later.

Based on several postings that I have read from a Google search of the
newsgroups on this subject, I think that I'm supposed to assign the
static value via an "AfterUpdate() Event Procedure." It isn't
working, however -- I'm just getting "$0.00" in the fldSalePrice
field.

My problem may be just technical in nature because I'm really not sure
how the AfterUpdate() event is supposed to work, and I'm very
unskilled at programming; I suspect that I'm not properly referring to
the field names. Here's what I did:

My subform is based on a query that contains the fldSalePrice and
fldQuantity from the Inventory table. Although the tblCrafts table
that contains the current price is linked in the query design, the
fldCurrentSalePrice is NOT in a column... I just wanted to refer to
the field.

In the Design view for my subform, I opened the properties for
fldSalePrice, and entered the following for the AfterUpdate() Event
Procedure:

Private Sub fldSalePrice_AfterUpdate()
Me.fldSalePrice = tblCrafts.CurrentSalePrice * Me.fldQuantity
End Sub

I would really appreciate it if someone could tell me how this is
really supposed to work!

Thanks!!
Jessi
 
Jessi-

The most common way to do this is to use a Combo box to set fldInvCraftID
that uses a Row Source from tblCrafts. You can include all three columns in
the row source - make fldCraftID the bound column (and hide it if you like
by giving it a zero display width), display fldCraftName, and put
fldCurrentSalePrice in the third column, also with a display width of 0. In
the AfterUpdate event of the combo box, copy the price from the combo box to
fldSalePrice in tblInventoryTransactions:

Me.fldSalePrice = Me.cmbCraftID.Column(2)

... assuming that you name the combo box "cmbCraftID." Note that the
relative column number starts at zero, so Column(2) is the third column.

I also recommend that you *not* calculate and store quantity times price in
fldSalePrice. In my opinion, fldSalePrice should reflect the unit price at
the time you placed the item in inventory. You can easily calculate the
extended price in an expression in the control source of an additional text
box or in the underlying query.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Thanks! This worked brilliantly!

Jessi

P.S. I asked my previously employer to purchase your "Office XP"
book, but I did not know that you also wrote one just for Access.
I'll have to look that up.

Thanks, again.
 
Back
Top