Check to see if record already exists

[zipaway]

So I have a button that opens up an append query, the append query adds the text from a textbox to the Projects table under the Project_Number field.

How could I write an if statement to check if the text in the textbox is already in the Projects table so I could write a msg box saying the record already exists.

[rpeare]

You really do not want to check for duplicates based on a text field

for instance

John Smith
Jon smith
joh smit

would all appear as unique values even though they may be the same person

but to answer your question you could do a dcount

if dcount("*", "[TableName]", "[DescriptionField] = '" & [forms]![formname]![fieldname] & "'") >0 then
    debug.print "OMG SOMETHING EXISTS"
else
    debug.print "OMG NOTHING EXISTS"
endif

[zipaway]

You really do not want to check for duplicates based on a text field

for instance

John Smith
Jon smith
joh smit

would all appear as unique values even though they may be the same person

but to answer your question you could do a dcount

if dcount("*", "[TableName]", "[DescriptionField] = '" & [forms]![formname]![fieldname] & "'") >0 then
    debug.print "OMG SOMETHING EXISTS"
else
    debug.print "OMG NOTHING EXISTS"
endif

The field is only a number, so this method would be safe.

I tried the code above but it is giving me an error on the first line "Datatype mismatch in criteria expression"

If DCount("*", "[Projects]", "[Project_Number] = '" & [Forms]![NewProjectNumber]![ProjectNumber] & "'") > 0 Then

Do I have to convert the text to an integer?

[rpeare]

no just remove the ' marks from the equation

if dcount("*", "[TableName]", "[DescriptionField] = " & [forms]![formname]![fieldname]) >0 then

your OP said it was a text value, you don't need the ' marks for numerics

[zipaway]

no just remove the ' marks from the equation

if dcount("*", "[TableName]", "[DescriptionField] = " & [forms]![formname]![fieldname]) >0 then

your OP said it was a text value, you don't need the ' marks for numerics

Thank you so much, this worked.