check for duplicates

[Ivor Williams]

I have a form which includes two fields - ProjNo and PartNo. If the
combination of the two fields (ProjNo&""&PartNo) is entered more than once,
I would like to be warned with a message box. I do not want to use these two
fields as primary key, because I may want to accept the 'double entry'. How
can I do this?

Ivor

 

[tina]

well, since you might want to allow the duplicate entry, you can't set a
unique index on the combination of fields (btw, to prevent duplicate entries
in all cases, you *could* set a unique index, which is not the same as a
primary key index). suggest you try using a DCount() function to check the
underlying table, as

If DCount(*, "table name", "ProjNo = " _
& Me!ProjNo & " And PartNo = " _
& Me!PartNo) > 0 Then
Msgbox "This project/part combination already " _
& "exists.", , "Warning"
End If

the above syntax in the DCount() assumes that both ProjNo and PartNo fields
in the table are Number data types. if they're Text data types, change the
syntax to

If DCount(*, "table name", "ProjNo = '" _
& Me!ProjNo & "' And PartNo = '" _
& Me!PartNo & "'") > 0 Then

hth

 

[Ivor Williams]

Both fields are text, so I used the following:

If DCount("*", "tblMaterial", "ProjNo = '"& Me!ProjNo & "' And PartNo =
'" Me!PartNo & "'") > 0 Then MsgBox "This part number has already been
ordered for this project.",,"Warning"

Note that I added quotes around the asterisk. When I run this code, I get a
syntax error, but can't figure out where it is.

Ivor

 

[tina]

remove the quote marks around the asterisk, unless you have a field in your
table with * as its' fieldname. also, i don't know if an If statement will
run without the End If, when it runs to more than one line. if you still
have problems, try using the specific layout that i posted.

hth

 

[Ivor Williams]

I've copied and pasted your code, subsituting "table name" with the name of
the table I'm searching. I've removed the quotes around the asterisk and put
all the code on one line, so End If should not be necessary. When I make
these adjustments, the code turns red and I get a dialog that says Compile
error: Expected :Expression, and the asterisk is hilighted. Not quite sure
what is wrong here.

Ivor

 

[tina]

then try replacing the asterisk with the number 1. if that doesn't work,
then just use the primary key fieldname, surrounded by quotes, for that
argument.

hth

 

[Ivor Williams]

Tina...

It works perfectly when I use the primary key! Thanks so much for all your
help.

Ivor

 

[tina]

you're welcome

Tina...

It works perfectly when I use the primary key! Thanks so much for all your
help.

Ivor