Negative values when credit note created

[CW]

Our job control system is generating invoices which we then pass to our
accounting system.
I've got this working OK, except that occasionally we need to create credit
notes, on which obviously the values should be negatives, both stored in the
table as such when created and displayed as such on the form that is used to
produce the hard copy credit note.
I have an InvoiceTypes table, with values SI for Sales Invoice and SC for
Sales Credit.
If the user selects InvoiceType SC when he/she starts creating the form, I
want all the values entered upon it and stored in the InvoiceLines table("for
example Line1Value", "Line2Value", "TotalValue") to be prefixed with a minus
sign.
Looking forward to your suggestions - many thanks
CW

 

[UpRider]

Code the after_update event for each textbox, e.g.

if Line1Value > 0 then
Line1Value = Line1Value * -1
endif

HTH, UpRider

 

[CW]

Thanks - but how does it know to do that only when it's a credit note that's
being created?
If I simply add that code to each textbox and nothing more than that, won't
it create negatives on invoices as well?
Thanks
CW

 

[John W. Vinson/MVP]

Our job control system is generating invoices which we then pass to our
accounting system.
I've got this working OK, except that occasionally we need to create credit
notes, on which obviously the values should be negatives, both stored in the
table as such when created and displayed as such on the form that is used to
produce the hard copy credit note.
I have an InvoiceTypes table, with values SI for Sales Invoice and SC for
Sales Credit.
If the user selects InvoiceType SC when he/she starts creating the form, I
want all the values entered upon it and stored in the InvoiceLines table("for
example Line1Value", "Line2Value", "TotalValue") to be prefixed with a minus
sign.
Looking forward to your suggestions - many thanks
CW

I'd use the Form's BeforeUpdate event to check the value of
InvoiceType, and set each field to its own negative if appropriate:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me!InvoiceType = "SC" Then
Me!Line1Value = - Abs(Me!Line1Value)
<etc>
End If

I'm taking the absolute value of Line1Value just in case the user
manually enters a negative (which just negating would undo).

If your InvoiceLines table has one *FIELD* for each line, your table
design is very suspect. It should have one *RECORD* for each line - in
which case the code will be differnt and may require running an Update
query instead.

 

[CW]

John -
Ah yes that looks good, thanks a lot.
Re your point about the number of fields in each line, there are several as
follows including more than one "currency" field -
InvNo
Text
NominalCode
TaxCode
TaxRate (a macro sets this according to the entered TaxCode)
TaxValue (calculated)
LineNetValue
LineGrossValue (calculated)

So I guess I need to extend the code that you kindly suggested, to apply
also to the TaxValue and GrossValue fields? Or is this where an Update query
might come into play - and if so, could you please guide me through that?
Very many thanks
CW

 

[John W. Vinson/MVP]

So I guess I need to extend the code that you kindly suggested, to apply
also to the TaxValue and GrossValue fields? Or is this where an Update query
might come into play - and if so, could you please guide me through that?

On the contrary, these two fields should simply NOT EXIST in your
table, either as positive or negative - if they mean what I'm guessing
they mean!

Storing derived data such as this in a table accomplishes three
things: bloats your database; makes your application more complicated;
and most importantly, risks data corruption. If one of the underlying
fields changes you will now have a value that is WRONG with no obvious
signal that it is wrong. Just calculate the tax value (say) on demand
as needed.