Before Update (2003)

Topic

New Reply

I have the following snippet of code for a Before Update form field…

Private Sub Invoice_Num_BeforeUpdate(Cancel As Integer)
If DCount(“*”, “Invoices”, “Invoice_ID ” & Me.Invoice_ID & _
” AND Invoice_Num = ” & Chr(34) & Me.Invoice_Num & Chr(34)) > 0 Then
Cancel = (MsgBox(“This invoice number has already been used. Continue anyway?”, _
vbQuestion + vbYesNo + vbDefaultButton2) = vbNo)
End If
End Sub

The code works great, except for twhen the user clicks “No”. I get the following error:

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

For example, you may have changed a validation rule without verifying whether the existing data matches the new validation rule. Click undo on teh Edit menu to restore the previous value, or enter a new value that meets the validation rule for the field.

Currently the end users just clicks “OK” to continue and they change the Invoice Number. Is it possible to clean this up any? Thanks in advance!

Reply | Quote

Replies

Do you actually have a validation rule for Invoice_Num or for the record as a whole? And if so, what does it say?

Reply | Quote

Actually, I don’t have a validation rule in the table design. But, I do have the field set as required and also to disallow zero length.

Reply | Quote

Hmm, that doesn’t explain the error message. Does it help if you add the line

Me.Invoice_Num.Undo

above End If ?

Reply | Quote

Hi Hans,

Thanks for the suggestion. I gave it a shot, but no luck. Same behavior.

Reply | Quote

Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote