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)

--
aw

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"aw" <(E-Mail Removed)> wrote in message
news:AED2419D-70A6-451F-8E11-(E-Mail Removed)...
> 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)
>
> --
> aw

"aw" <(E-Mail Removed)> wrote in message
news:AED2419D-70A6-451F-8E11-(E-Mail Removed)...
>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)
>
> --
> aw

On Thu, 13 May 2010 19:15:01 -0700, aw <(E-Mail Removed)> wrote:

>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)

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
--

John W. Vinson [MVP]

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."

"aw" <(E-Mail Removed)> wrote in message
news:AED2419D-70A6-451F-8E11-(E-Mail Removed)...
>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)
>
> --
> aw