how do i check for duplicate entry in a table from a form text box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

greg higgy - 29 Sep 2005 05:54 GMT

I have a table named 'invoices' with a field named 'invoice#'. I am creating
a form which a person fills out different fields. The form is named
'FInvoices'. One text box is named 'Invoicebox'. What I am needing to do is
when a number is entered, and when the focus to that text box is lost, it
checks the 'invoices' table for a duplicate number (in the Invoice# field),
if there is, an error message will pop up, allowing the cashier to correct
the error. This checking needs to be done before the cashier is allowed to
continue.

This is my first real project I am attempting and my skill level would be
about "Intermediate". I have looked at several "dlookup" examples but could
not get any of them to work in my example.

Any help would be great. Thanks in advance.

greg

Reply to this Message

ozgirl - 29 Sep 2005 08:13 GMT

Hi Greg,

this may help

place it on the before update event on your invoice number field on your form

Dim InvID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

InvID = Me.InvoiceNumber.Value
stLinkCriteria = "[txtInvoiceNumber]=" & "'" & InvID & "'"

'Check Details table for duplicate Invoic Number

If DCount("InvoiceNumber", "your table name", stLinkCriteria) > 0 Then
'Undo duplicate entry
'Me.Undo

'Message box warning of duplication

MsgBox "Warning Invoice Number " _
& InvID & " has already been entered.", vbInformation , "Duplicate
Information"

End If

Set rsc = Nothing

regards

Lee-Anne

>I have a table named 'invoices' with a field named 'invoice#'. I am creating
>a form which a person fills out different fields. The form is named
[quoted text clipped - 12 lines]
>
>greg

Reply to this Message