Calculated field not updating all the time

[HelenaT]

Hi - I've attached a dummy version of part of my database.
The idea is that I have several different types of logs (gas, convenience and bus) that I am entering into this database. Each type of card has it's own cost, and that cost can change over time.

I ultimately want to store FakeMRN, date of distribution, number of cards, and cost in the table tblGasBusConvCardLog.

For the control txtCardCostValue, I am using DLookup to find the card cost (based on distribution date and card type). The Cost should be number of cards * card cost. This updates some times, but not always. I can't seem to figure out why.

If I change the card type on an entry, the card cost updates. The Cost doesn't.
I think this may have something to do with the field being bound to the cost field. There are other changes I want to make to this form, but I will start with this question.

Thanks for any help!
Helena

 

[theDBguy]

Hi. Sorry, I can't download your file right now but just wanted to say the typical approach is to have a cost table where you record the cost of items/products with an effective date. You can then use this table in a query to get the actual cost of an item at the time of the transaction.

 

[arnelgp]

see query qryCardEntryCost.

 

[HelenaT]

Sorry arenlgp, but I don't see that the Cost field is updating. I see the change to the query, which I understand.
The cost field still doesn't update when you change the Card Type on the form.

 

[arnelgp]

try this again.

 

[June7]

Code for CardType combobox does not save cost.

Don't use Current event for saving cost. This puts record in edit mode as soon as form opens.

Saving calculated data is usually not necessary, especially such a simple calc. Do calc when needed in query or textbox. Save unit cost if you must because unit pricing can change over time. Use form BeforeUpdate event to set field value. Options:

1. save unit price in effect at that time, not the calculated quantity x price - problem with this approach is since the price is changed in products table there is no history of pricing info, no supporting documentation in db for the saved price

2. create a new price record in products table (or have Products and ProductPricing tables), set old price record as 'inactive' to exclude from combobox list, just save ID and retrieve price in queries that join tables

3. same setup as 2. but use code to find appropriate price based on effective date and order date - gets complicated

txtCardCostValue code does not execute because it is a calculated field, no user input. But I presume you already discovered that.

I have never used Change event. This code will execute after each key stroke.

Why save client ID and client Name? Why two client name fields?

I would not use DLookup. I would have a multi-column combobx that uses table as RowSource, not a value list. Then code can reference column of combobox for CardValue.

All those Refresh and Requery statements should not be needed.

 

[HelenaT]

Thanks for the input. The reason I am saving the cost is that other parts of this database will store costs directly, and I want to use the same table design for pulling all the data together.

Eventually there will be other data - taxi, housing, lab and medical. All of these will be entered as cost, not as a log of quantity and a price at a given time. Is this bad design?

I have the CardCost table already set up with dates to store when they are valid, and the query for txtCardCostValue looks at the date range to select the right one.

arnelgp - Thanks! This is now working as I want it to. Can you explain why the change in the formula to Nz(Me.txtCardCostValue.Value,0) * Val(Me.Number_of_Cards.txt & "") works? - specifically the Val() function part and the concatenating of the "". Thanks!!!

 

[arnelgp]

you will get error when Me.Number_of_Cards is blank and you multiply it with txtCardCostValue.

anyNumber x Null = error
(Null & "") = ""
Val(Null & "") = 0

 

[HelenaT]

Thanks for the input.

I will change to save the CardValue, not the calculated price.
I am having some trouble understanding how I would do this

June7 stated this as one way to save the current CardValue.
1. save unit price in effect at that time, not the calculated quantity x price - problem with this approach is since the price is changed in products table there is no history of pricing info, no supporting documentation in db for the saved price

I don't understand this statement - the tblCardCost has a start and end date, and the query (qryCardEntryCost) that is going to the txtCardCostValue looks at the date range to select the proper CardValue. Can you explain what I am not doing?

June7 also stated
"I would not use DLookup. I would have a multi-column combobx that uses table as RowSource, not a value list. Then code can reference column of combobox for CardValue."

The DLookup is using a query as the rowsource - the query selects the value from the tblCardCost based on the values entered in CardType and DistributionDate. I don't understand what table I would use as the source.
Can you explain further?

Pat Hartman - thanks for the info. I will check all names, etc. Much of this is test - not a final version by any stretch of the imagination, so this appreciated.

Thanks again for your help.