Searching for Duplicates

[lost]

sorry for the basic question, but:

I have users entering a serial number on a form in a text box that is
bounded and called via a query. Prior to allowing that number to be
entered into the table, I need to make sure it's not a duplicate. I
can't use the primary key here, so I need some vb code to do the
search the table.

I tried Dlookup, but it seems to trigger as soon as I enter a serial
number for the second case, even if it is not the same as the first
case.

Here's the code I am using:

= DLookup("[name]", "Assignment Table", "forms![assignment
table]![txtserial] = forms![assignment table]![txtserial]")

Any help would be appreciated.
Thanks

[PC Datasheet]

Here are two suggestions ---

1. Go back to the table that your form is based on and open it in design view.
Select the [via a query] field and in the bottom section set indexes to No
Duplicates.

2. DLookup is the wrong function to use; try DCount in the BeforeUpdate event
of the textbox.
If DCount("[via a query]", "Assignment Table", _
"[via a query] = " & forms![assignment table]![via a query]) <> 0 Then
<<Your code to handle the duplicate>>
End If
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

<lo**@aol.com> wrote in message
news:97********************************@4ax.com...

sorry for the basic question, but:

I have users entering a serial number on a form in a text box that is
bounded and called via a query. Prior to allowing that number to be
entered into the table, I need to make sure it's not a duplicate. I
can't use the primary key here, so I need some vb code to do the
search the table.

I tried Dlookup, but it seems to trigger as soon as I enter a serial
number for the second case, even if it is not the same as the first
case.

Here's the code I am using:

= DLookup("[name]", "Assignment Table", "forms![assignment
table]![txtserial] = forms![assignment table]![txtserial]")

Any help would be appreciated.
Thanks