VALIDATION RULE

[angie]

i have a form with the following fields of interest:
document id = invoice or credit note and
amount = positive number if document id equals to "invoice" and negative
number if document id equals to "credit note".
how can i block the amount field so that i am prohibited from inserting the
wrong number according to the document id value?
should i use the validation rule or could i automate the form to convert the
nmber inserted by the user either to positive or negative?

pls give me a hint!!!

 

[Allen Browne]

You could use Abs() in the AfterUpdate event procedure of the text box to
ensure it has the right sign. You would want to call the code in the
AfterUpdate of [document id] also.

Private Sub Amount_AfterUpdate()
If Me.[document id] = "invoice" Then
Me.Amount = - Abs(Me.Amount)
Else
Me.Amount = Abs(Me.Amount)
End If
End Sub
Private Sub document_id_AfterUpdate()
Call Amount_AfterUpdate
End Sub

A better solution might be to create a little lookup table of the document
types. Use fields:
DocumentTypeID Number
DocumentType Text
Enter 2 records like this:
DocumentTypeID DocumentType
-1 Invoice
1 Credit Note
Now use a DocumentTypeID field in your main table.

Now the user can just select the document type, and enter the amount without
having to worry about the sign. Since we used -1 and 1, you can create a
query, and enter the field:
EffectiveAmount: [DocumentTypeID] * [Amount]
which give you negative values for invoices and positive values for credit
notes.