changing a product price and keeping hisotical data

[Dan]

I have a inventory database with tables for items and item sales.
tblitems
itemsID
item
Iprice
active y/n

tblitemsales
itemsalesID
itemsID
Sprice
tax

I have a subform to enter the item sales with a combo box for ItemID which
pulls the item price from the items table. how do I get this to update in
the sales price? I have looked to the northwinds afterupdate code for
productID but do not understand how it works. I have limited VB knowledge.

Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub

 

[Steve McLeod]

This might be a lot simpler. You have a parent form bound to tblitems that
includes a subform control with a form bound to tblitemsales. These two
forms are syncronized using itemsID. So far no VBA code is needed.

Now you want Iprice from tblitems to be the default price for Sprice in
tblitemsales. In the onCurrent event for the parent form bound to tblitems
do this.

Me.subformControlName.Form.Sprice.defaultvalue = trim(str(Me!Iprice))

subformControlName is the name of the Control containing the subform. Not
the name of the subform.
Sprice is the name of the Control on the subform bound to Sprice in the
database. They should not be the same name. Get into the habit of changing
the default control name.

One line of VBA code in the main or parent form's onCurrent event should be
all you need.

 

[Dan]

I almost have the northwinds code working except the dlookup is only finding
the price for the 1st item, it doesn't matter which item I select I get the
same price.

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ItemsID = " & Me!ItemsID

' Look up product's unit price and assign it to UnitPrice control.
Me!Sprice = DLookup("Iprice", "tblitems", strFilter)

i assume Access isn't recognizing my ItemID

 

[Dan]

OK Steve, I think you way might be easier. however the parent form is not
bound to tblitems, it is actually bound to tblevents as I am tracking sales
based on an event. so of course your code for me!Iprice will not work. How
do i modify your code. the subform is actually bound to a qry which brings
eventID,itemsalesID, and itemsID together.

 

[Dan]

OK, I have solved the first problem and created a new one. the tblitemsales
is updated with the price with an afterupdate event on the combobox for
ItemID in the subform. this works fine if I use the combo box to select the
item, however I have another event on the mainform which autopopulates the
subform with items, which also works, but does not trigger the afterupdate
event on the subform. I have to manually reselect each item in the subform in
order to popluate the price. is there a way to trigger the afterupdate event
in the subform when the subform auto populates
??

thanks

 

[Dan]

because I require all my items in the subform I have used the following code
to populate the subform:
Private Sub Form_AfterInsert()
Dim db As Database
Dim LSQL As String
Dim LCntr As Integer

'Establish connection to current database
Set db = CurrentDb()

LCntr = 2

'Create SQL to insert item_numbers 1 to 36
Do Until LCntr > 36

LSQL = "insert into qrysales (EventID, ItemsID)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & EventID & "', " & LCntr & ")"

'Perform SQL
db.Execute LSQL

'Increment counter variable
LCntr = LCntr + 1
Loop

'Requery subform that new records are listing
frmsales_eventsubform.Requery
End Sub

so as soon as the event record saves the subform autopopulates with my items.