New Prices automatically filled in without changing the old information

[Liv]

Hi,

I have a form with the following fields:

frmORDERS
Date
Invoice#
CompanyName
Category

It has a subform:

sfrmOrders
Invoice# (child and master link field)
VendorCode
Qty
Description
MeasureUnit
QtyPerUnit
UnitPrice
Total

The Description field is a combo box, once I select the Description all
other fields (but the Total since it is just a formula) are
automatically filled in (I used the Me! code for this).

It worked fine but now the Vendors have increased their price. I don't
want to just replace the old price with the new one since I'd like to
keep history of the orders placed and the total spend.

I tried the IIF with the Me! code but didn't work, I also thought about
having a new combo box cboPriceType where I could select the price (old
price = price1, new price = price2, etc to be ready for future price
increases) and once I select it I thought the sfrm would be the one
according to the price. But I just couldn't make it.

I have no knowledge in codes, SQL, etc. I did some DBs by reading
forums about MS Access. I would appreciate any help you can provide.
All I need is to be able to have the new price automatically filled in
the orders placed after June 14th. but keeping the old price in those
placed before that date.

Thx in advance )

 

[ManningFan]

My brain hurts so this is going to be sketchy, but create another table
with StartDate, EndDate and Price. Then you can link the price in by
date and keep a record of historical prices. I did this at my previous
job, but I can't remember exactly how off the top of my head.

Or you can create a table with PriceID and Price and just do a DLookup
on the form (i.e. iif ([Date] < June 14, PriceID=1, PriceID=2) and
store the ID in your sfrmOrders. This limits you to just 2 prices, but
there may be a way to expand on that idea.

 

[jahoobob via AccessMonster.com]

What are the tables behind the forms? You should have at a minimum a
Customers table, an Orders table, a Products tabel, and an Invoice table.
You should keep the current price in the Products table and add it to the
invoice table when an invoice is created. That is your historical price
record. See the Northwind db and look at Orders and Order Details tables and
the way they are tied together in the Customer Orders form.

 

[Liv]

jahoobob via AccessMonster.com ha escrito:

What are the tables behind the forms? You should have at a minimum a
Customers table, an Orders table, a Products tabel, and an Invoice table.
You should keep the current price in the Products table and add it to the
invoice table when an invoice is created. That is your historical price
record. See the Northwind db and look at Orders and Order Details tables and
the way they are tied together in the Customer Orders form.

Jahoobob,

I do have a tblProducts (QtyperUnit, Description, UnitPrice, etc),
tblVendors (CompanyName, etc), a tblOrders (Date, Buyer, Total, etc), a
tblOrdersDetails which I used for the sfrmOrders already described.

But I don't know how to make the DB to automatically fill in the new
unit price. I looked into the NW DB but the frmOrders only have one
unit price and I'd like the DB to take the unit price according to the
date.

Any other suggestion that may help me?

 

[Liv]

ManningFan ha escrito:

My brain hurts so this is going to be sketchy, but create another table
with StartDate, EndDate and Price. Then you can link the price in by
date and keep a record of historical prices. I did this at my previous
job, but I can't remember exactly how off the top of my head.

Or you can create a table with PriceID and Price and just do a DLookup
on the form (i.e. iif ([Date] < June 14, PriceID=1, PriceID=2) and
store the ID in your sfrmOrders. This limits you to just 2 prices, but
there may be a way to expand on that idea.

Hi,

I have a form with the following fields:

frmORDERS
Date
Invoice#
CompanyName
Category

It has a subform:

sfrmOrders
Invoice# (child and master link field)
VendorCode
Qty
Description
MeasureUnit
QtyPerUnit
UnitPrice
Total

The Description field is a combo box, once I select the Description all
other fields (but the Total since it is just a formula) are
automatically filled in (I used the Me! code for this).

It worked fine but now the Vendors have increased their price. I don't
want to just replace the old price with the new one since I'd like to
keep history of the orders placed and the total spend.

I tried the IIF with the Me! code but didn't work, I also thought about
having a new combo box cboPriceType where I could select the price (old
price = price1, new price = price2, etc to be ready for future price
increases) and once I select it I thought the sfrm would be the one
according to the price. But I just couldn't make it.

I have no knowledge in codes, SQL, etc. I did some DBs by reading
forums about MS Access. I would appreciate any help you can provide.
All I need is to be able to have the new price automatically filled in
the orders placed after June 14th. but keeping the old price in those
placed before that date.

Thx in advance )

ManningFan,

Thx! but since there are different products this limits a lot what I
need. Hope you feel better now and later on you can recall how you did
it in your previous job )

 

[Guest]

Hi, Liv. It sounds like what you want to do is to be able to keep the
"current" prices in the tblProducts table, and copy the "as of today" prices
from the Products table into the OrderDetails. This is almost exactly what
the Northwinds DB does. Let me explain.

Look in the Orders Subform in NW, and open it in the design view. You may
need to expand the form to see the elements, but find the ProductID field and
display the Properties page. (There's a Properties button in the toolbar, or
right-click and choose Properties.)

Find the AfterUpdate event, and click on the three-dots button to display
the Event Procedure associated with it. Here it is:. I'll add some step by
step comments.

----------------------------------------------------------
Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String
' We need to look up only the attributes, such as Price, ShippingWeight, or
other
' such details that are applicable to the specific ProductID that we're
going to select
' in the ProductID ComboBox. We have to have someplace to store it.

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID
' The strFilter will contain the text "ProductID=" plus the actual ProductID
number.

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

' Here in the Orders Subform, we're going to go out to the Products table
' and find the UnitPrice in the Products table that matches the ProductID
' we selected in the ComboBox. Then put that UnitPrice from the Products
' table in the UnitPrice field of the Orders Subform, which will populate
' the UnitPrice field of the OrderDetails table.
'
' So, "Me.UnitPrice = " means "put into this field on this, the Me, form"
' the data from DLookup (Fieldname, Tablename, Criteria)

' We could add some other fields from the Products table if we wanted to,
' such as a ShippingWeight or UnitCost to go with the UnitPrice, but I got
the
' impression that you only wanted the Price. It might look something like
this:

' Me!UnitCost = DLookup("UnitCost", "Products", strFilter)
' Me!ShippingWeight = DLookup("ShippingWeight", "Products", strFilter)
' The strFilter and the Products table would be the same; only the fieldname
' changes.


Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub
--------------------------------------------------------

Clear as mud, right? Play around with the Northwinds DB, add some orders
and line items. Then go back to the Products form and change the prices.
Satisfy yourself that once you've entered an order, the price of the line
item doesn't change when the price in the Products table is changed.

Best of luck!

 

[Liv]

Ken Mitchell (NOSPAM) ha escrito:

Hi, Liv. It sounds like what you want to do is to be able to keep the
"current" prices in the tblProducts table, and copy the "as of today" prices
from the Products table into the OrderDetails. This is almost exactly what
the Northwinds DB does. Let me explain.

Look in the Orders Subform in NW, and open it in the design view. You may
need to expand the form to see the elements, but find the ProductID field and
display the Properties page. (There's a Properties button in the toolbar, or
right-click and choose Properties.)

Find the AfterUpdate event, and click on the three-dots button to display
the Event Procedure associated with it. Here it is:. I'll add some step by
step comments.

----------------------------------------------------------
Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String
' We need to look up only the attributes, such as Price, ShippingWeight, or
other
' such details that are applicable to the specific ProductID that we're
going to select
' in the ProductID ComboBox. We have to have someplace to store it.

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID
' The strFilter will contain the text "ProductID=" plus the actual ProductID
number.

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

' Here in the Orders Subform, we're going to go out to the Products table
' and find the UnitPrice in the Products table that matches the ProductID
' we selected in the ComboBox. Then put that UnitPrice from the Products
' table in the UnitPrice field of the Orders Subform, which will populate
' the UnitPrice field of the OrderDetails table.
'
' So, "Me.UnitPrice = " means "put into this field on this, the Me, form"
' the data from DLookup (Fieldname, Tablename, Criteria)

' We could add some other fields from the Products table if we wanted to,
' such as a ShippingWeight or UnitCost to go with the UnitPrice, but I got
the
' impression that you only wanted the Price. It might look something like
this:

' Me!UnitCost = DLookup("UnitCost", "Products", strFilter)
' Me!ShippingWeight = DLookup("ShippingWeight", "Products", strFilter)
' The strFilter and the Products table would be the same; only the fieldname
' changes.


Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub
--------------------------------------------------------

Clear as mud, right? Play around with the Northwinds DB, add some orders
and line items. Then go back to the Products form and change the prices.
Satisfy yourself that once you've entered an order, the price of the line
item doesn't change when the price in the Products table is changed.

Best of luck!

Thanks Ken!

I played around with the NW DB as suggested and confirmed that the
orders already placed wouldn't change their products unit price to
the new one.

Currently my after update code is as follows:

Private Sub DESCRIPTION_AfterUpdate()
Me!VENDORCODE = DESCRIPTION.Column(1)
Me!BRAND = DESCRIPTION.Column(2)
Me!MEASUREUNIT = DESCRIPTION.Column(3)
Me!QtyperUnit = DESCRIPTION.Column(4)
Me!UNITPRICE = DESCRIPTION.Column(6)
End Sub

And it works in the same way (or at least I think so), but I was
thinking about having both prices in the tblPRODUCTS to keep record of
the prices and not just keeping them in the orders. I am afraid that
someone by mistake changes it (say someone re-selects the product in an
order already placed it will automatically change the unit price to the
new one instead of keeping the old one).

So I thought to have price1 and price2 and have the unit price taken
according to the orders date (the prices increased on June 14th) and
have the DB prepared for further increases; I wouldn't mind adding
columns with the new prices but I don't want to be changing codes
everytime.

So, is it possible to automatically fill in the UNITPRICE field
according to the date? Instead of replacing the prices in the
tblPPRODUCTS?

 

[Guest]

Hi, Liv. Yes, you probably can - but I think it would be a nightmare to
administer. If you allow for the fact that prices will change, then you'd
need to allow them to change AGAIN - and then you'd need to keep historical
records on when every price changed. I'm not sure I'd want to tackle that
project.

How about this? You seem concerned that a user might accidentally re-select
a product and the program would overwrite the old prices with the new ones.
To prevent that, write an "if isnull()" condition, so that if there's already
data in a field, it won't overwrite the prices/descriptions. Somthing like
this...

If IsNull(Me.Brand) then
Me!BRAND = DESCRIPTION.Column(2)
End If
If IsNull(Me.UnitPrice) Then
Me!UNITPRICE = DESCRIPTION.Column(6)
End If


Your users would need to know that now, changing a product WOULD NOT
overwrite the old prices, so if they really wanted to change to a different
product, they'd need to either delete that product and re-add it or to
manually blank the affected fields.

But I think this would be more maintainable, in the long run, than trying to
keep track of what prices had been changed, and when.

This joke worked better in Access 95, but do you know what the "97" stood
for? There were 95 different ways to do anything. There aren't 2003 ways
to do things - but I guarantee there's at least five different ways to code
anything. Don't focus too much on one PROCESS, when what you're really trying
to achieve is one RESULT.

Best of luck!

 

[Liv]

Ken Mitchell (NOSPAM) ha escrito:

Hi, Liv. Yes, you probably can - but I think it would be a nightmare to
administer. If you allow for the fact that prices will change, then you'd
need to allow them to change AGAIN - and then you'd need to keep historical
records on when every price changed. I'm not sure I'd want to tackle that
project.

How about this? You seem concerned that a user might accidentally re-select
a product and the program would overwrite the old prices with the new ones.
To prevent that, write an "if isnull()" condition, so that if there's already
data in a field, it won't overwrite the prices/descriptions. Somthing like
this...

If IsNull(Me.Brand) then
Me!BRAND = DESCRIPTION.Column(2)
End If
If IsNull(Me.UnitPrice) Then
Me!UNITPRICE = DESCRIPTION.Column(6)
End If


Your users would need to know that now, changing a product WOULD NOT
overwrite the old prices, so if they really wanted to change to a different
product, they'd need to either delete that product and re-add it or to
manually blank the affected fields.

But I think this would be more maintainable, in the long run, than trying to
keep track of what prices had been changed, and when.

This joke worked better in Access 95, but do you know what the "97" stood
for? There were 95 different ways to do anything. There aren't 2003 ways
to do things - but I guarantee there's at least five different ways to code
anything. Don't focus too much on one PROCESS, when what you're really trying
to achieve is one RESULT.

Best of luck!

Ken,

Got a dumb question... where do I place that code? sorry but my Access
knowledge is very limited. I've placed it in combo Description after
update, onclik event, change event but none worked. Can you please let
me know?

 

[Guest]

The old-time computer teacher in me says "The only dumb question is the one
you didn't ask."

Put it in the OnUpdate event, right where your original code was. Just add
the "If...Then... End If" stuff right in your original code.

Drop me an e-mail to my G-mail address (click my name on this post, and
delete the part about "(NOSPAM)" and I'll mail you some example code.