Adding/subtracting from fields.

[jon jomaco]

I have a combo box where one customer can be assigned to one order.

The after update event is:

[CreditUsage] = [CreditUsage] + Forms![frmOrders]![OrderTotal]
Refresh

...and the before update event is:

Me.[CreditUsage] = Me.[CreditUsage] - Forms![frmOrders]![OrderTotal]
Refresh

The aim of this is to ensure that when the combo box is changed, so that a customer of a particular ID is not assigned to an order anymore, the credit usage of the customer in question is subtracted from (since the credit which is available to them is no longer used up by this order). After this the new value of the combo box will mean that a different customer is assigned to the order and therefore their credit usage will increase by the total amount of the order.

It seems pretty simple, but unfortunately i'm clearly doing something wrong as the following error message is displayed:

"The value in the field or record violates the validation rule for the record or field... "

There isn't any validation applied to this field so i don't understand what the problem is. Maybe someone can help me out. I apologise in advance if I have explained the problem poorly, i'm new to this sort of thing as you can probably judge by the nature of the question, so please ask if you need clarification.

Any information appreciated
Thanks, Jon.

 

[gemma-the-husky]

this sounds awkward

within the form, i am not sure that you will be able to change the value of the exposure on the customer related to the order in this way, and perhaps this is the problem


so which one is falling over?

the beforeupdate or the afterupdate event?

 

[jon jomaco]

hmm, there must be an easy way to do this sort of thing without getting errors. The error appears with the before update event.

Jon.

 

[gemma-the-husky]

1. make sure creditusage is numeric in the customer table, and there really is no validation rule - it might be value required or something like that

2. put the orderdtotal in an nz(,0) just in case. If there are no orders in the subform this will/may fail so you may need an onerror at this point anyway

3. are you sure the combo box is linked to the correct source

4. comment out the creditusage statement and see if the error still happens

5. you could put a breakpoint in the beforeupdate event and step through the code to see exactly what is happening

6. i am not sure whether the refresh is necessary

 

[neileg]

In any event, storing totals like this is a poor approach. Two users accessing the same record, a system crash or a user exiting without a proper closedown can all lead to invalid totals. It is better to store a series of transactions and sum these to calculate the current total.

 

[missinglinq]

The Before update event is:
Me.[CreditUsage] = Me.[CreditUsage] - Forms![frmOrders]![OrderTotal]

The after update event is:
[CreditUsage] = [CreditUsage] + Forms![frmOrders]![OrderTotal]

This makes no sense at all!

In the BeforeUpdate event [OrderTotal] is subtracted from [CreditUsage]

In the AfterUpdate event [OrderTotal] is added to [CreditUsage]

I think the OP has a major misconception as to what these events do.

 

[jon jomaco]

Sorry guys, I don't have a clue what I was thinking, perhaps I was a little tired, or at least I hope that's the explanation. Thanks for your advice, but what missinglinq said is true, after looking over it I realised that I was adding something then taking the same thing away effectively meaning nothing happens! (OriginalValue + 2 - 2 = OriginalValue anyone?)

Sorry for my poor explanation however I have now fixed the problem I was having using an update query and bit of perseverance.

If you're interested in what I was actually trying to find out. The idea was that if the ID of the record is changed but I still need to change the values of some of the fields of the original record then I can use the update query to, after the record has changed, change some part of the original record by altering the desired value in its table (as this cannot be done in the form as it is now focussed on another record of a different ID). If this makes no sense I apologise, I have almost confused myself (again!).

In order not to create a new topic for a small question I have another problem which I hope you can help me with.

In my main form I refer to a subform embedded within it, however I get the error that it cannot be found. Perhaps I am referring to it incorrectly.

The name of the subform is subfrmProducts and is referred to in the following section of code which is located in the form load procedure of the form in which it is embedded.

'find out if order is complete (done)
Text41 = DLookup("Done", "tblCustomerLink", "forms!frmOrders!OrderID")
If Text41 = True Then
Forms!subfrmProducts.AllowAdditions = False
Forms!subfrmProducts.AllowDeletions = False
Forms!subfrmProducts.AllowEdits = False
Me.[OrderDone].Visible = True
Else:
Forms!subfrmProducts.AllowAdditions = True
Forms!subfrmProducts.AllowDeletions = True
Forms!subfrmProducts.AllowEdits = True
Me.[OrderDone].Visible = False
End If

(basically if the order is completed (done) you shouldn't be able to edit the data in the subform)

Sorry for my earlier error! Any help appreciated.
Thanks,
Jon.