Duplicated Invoice number checking when leave the field

  • Thread starter Thread starter aw
  • Start date Start date
A

aw

I have a FORM to input NEW invoice information.

How can I write code for prompt user of duplicate invoice number being
inputted (under the form) when user leave this field IMMEDIATELY

(rather than my existing one do this when all information already input and
prompt you at the time of saving)
 
Private Sub InvoiceID_BeforeUpate(Cancel As Integer)
Dim strWhere As String
Dim strMsg As String
Dim varResult As Variant

With Me.[InvoiceID]
If Me.NewRecord And Not IsNull(.Value)
strWhere = "[InvoiceID] = " & .Value
varResult = DLookup("InvoiceID", "tblInvoice", strWhere)
If Not IsNull(varResult) Then
Cancel = True
strMsg = "That invoice number already exists." & vbCrLf & _
"Enter a different number, or press "<Esc> to undo."
MsgBox strMsg, vbExclamation, "Duplicate"
End If
End If
End With
End Sub
 
aw said:
I have a FORM to input NEW invoice information.

How can I write code for prompt user of duplicate invoice number being
inputted (under the form) when user leave this field IMMEDIATELY

(rather than my existing one do this when all information already input
and
prompt you at the time of saving)
Click to expand...
 
I have a FORM to input NEW invoice information.

How can I write code for prompt user of duplicate invoice number being
inputted (under the form) when user leave this field IMMEDIATELY

(rather than my existing one do this when all information already input and
prompt you at the time of saving)
Click to expand...

If the user is manually entering the invoice number into txtInvoiceNo, you can
use the BeforeUpdate event of txtInvoiceNo (not the event of the Form):

Private Sub txtInvoiceNo_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("InvoiceNo", "Invoices", _
"[InvoiceNo] = '" & Me!txtInvoiceNo & "'") Then
MsgBox "This invoice number has been used"
Cancel = True
<any other appropriate actions>
End If
End Sub
 
aw,
Several ways to do that. One would be...
Your InvoiceNo should be a unique value key field.
In your table design, make it...
Indexed - No Duplicates
Use the AfterUpdate event of InvoiceID to
Refresh
the form, and trigger an Access "Duplicate Value" error.

Or... use the InvoiceID BeforeUpdate event to do a DLookup of the just
entered value, to see if it matches any previous values. If so,
post a custom message to the user that the InvoiceID is a Dupe, and ...
Cancel = True
InvoiceID.Undo
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Back
Top