Dlookup or not dlookup

  • Thread starter Thread starter Bruce Ritchie
  • Start date Start date
B

Bruce Ritchie

I am a new user to access........so please spell
everything out to me in english and the slower the better.
I have the standard products,order and orderdetail
tables.As the price of products will change over time I
am finding that all my previous product price totals in
the orderdetails table are changing as well.Not good.I
want to be able to change the price of my products but
not affect any previous orders.
It has been suggested to me that the dlookup function is
my best option storing the price on orderdetails form
which will not be affected by any price changes.Is this
correct?If so how the hell do I do it.I understand about
the expr and domain but have no idea what the criteria
would be.
Please help.
 
Bruce Ritchie said:
I am a new user to access........so please spell
everything out to me in english and the slower the better.
I have the standard products,order and orderdetail
tables.As the price of products will change over time I
am finding that all my previous product price totals in
the orderdetails table are changing as well.Not good.I
want to be able to change the price of my products but
not affect any previous orders.
It has been suggested to me that the dlookup function is
my best option storing the price on orderdetails form
which will not be affected by any price changes.Is this
correct?If so how the hell do I do it.I understand about
the expr and domain but have no idea what the criteria
would be.
Please help.
Click to expand...
That would be one way and it could be argued that it is the "correct" way.
You would maintain a table of prices for each item as well as the date
range they were good for.
Your dLookup would get the date from the invoice, look up the values based
on date and ItemID and produce the correct results.
This could also be done with a query and not need the lookup.
I've never done that.
Instead I plug most everything into the table that holds the line item
information.
In some "after" event:
Me!LineItemCost = me!Currentcost.
etc.
I do the quantity as well (mostly out of habit from the slow old days.)

In some cases the description might also have to be inserted since it might
change or the itemID might be reused over time. In at least one app I have
the vendor changes frequently and the Item ID remains the same. (BECAUSE
that's the way the customer wants it.)
 
You do not have to go too slow. The concept you need is that because you
need to maintain history, you need to store either the elements of a
calculation, or the result. I suggest the elements because you can use them
for further requirements later. So now you want to figure how to do that.
There are 2 basic ways, pulling data and pushing it. Both work. I find it
easier (and faster) to push it. If you pull data, you will use a DLookup or,
even better, a recordset to retrieve your data after choosing the product.
Something like (air code):

Sub cboProduct_AfterUpdate()
Dim retValue As Variant
retValue = DLookup("Price", "tblProducts", "ProductID =" & Me.cboProductID)
Me.txtPrice = retValue
End Sub

Now if you push the data, you include the Price column in the cboProduct
combo box and push the data after it is selected:

Sub cboProduct_AfterUpdate()
Me.txtPrice = Me.cboProduct.Column(2) ' 3rd column
End Sub

A bit easier, don't you think?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top