Determine if a record exists

L

lcox400w

I have a key field "DRNO". Its the first field on the form. When the user
exists the field, I want to check to see if the DRNO already exists in the
database so the user doesnt end up filling out the whole form only to find
out it already exists.

How do I test for the existance of DRNO when the user exits the field?

thanks
 
B

boblarson

In the control's BEFORE UPDATE event, use

If DCount("[DRNO]","YourTableNameHere") > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________
 
L

lcox400w

Thanks for taking the time to send me the below code. However, no matter
what number I put in the field (its a 9 digit number field), now it always
goes to the message box and says the number already exists, even if it
doesnt.

boblarson said:
In the control's BEFORE UPDATE event, use

If DCount("[DRNO]","YourTableNameHere") > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


lcox400w said:
I have a key field "DRNO". Its the first field on the form. When the user
exists the field, I want to check to see if the DRNO already exists in the
database so the user doesnt end up filling out the whole form only to find
out it already exists.

How do I test for the existance of DRNO when the user exits the field?

thanks
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Um, shouldn't there be a Where clause in that DCount statement?

If DCount("*","YourTableNameHere", "]DRNO] =" & Me.DRNO) > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


boblarson said:
In the control's BEFORE UPDATE event, use

If DCount("[DRNO]","YourTableNameHere") > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


lcox400w said:
I have a key field "DRNO". Its the first field on the form. When the
user
exists the field, I want to check to see if the DRNO already exists in
the
database so the user doesnt end up filling out the whole form only to
find
out it already exists.

How do I test for the existance of DRNO when the user exits the field?

thanks
Click to expand...
Click to expand...
 
B

boblarson

That would be because I goofed and didn't give you the complete code (sorry
about that):

If DCount("[DRNO]","YourTableNameHere", "[DRNO]=" & Me!DRNO) > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If

--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


lcox400w said:
Thanks for taking the time to send me the below code. However, no matter
what number I put in the field (its a 9 digit number field), now it always
goes to the message box and says the number already exists, even if it
doesnt.

boblarson said:
In the control's BEFORE UPDATE event, use

If DCount("[DRNO]","YourTableNameHere") > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


lcox400w said:
I have a key field "DRNO". Its the first field on the form. When the user
exists the field, I want to check to see if the DRNO already exists in the
database so the user doesnt end up filling out the whole form only to find
out it already exists.

How do I test for the existance of DRNO when the user exits the field?

thanks
Click to expand...
Click to expand...
Click to expand...
 
L

lcox400w

Well I appreciate you getting back to me so fast.

Now I get a data type mismatch error when I run the code. Here is what I
have...

If DCount("[DRNO]", "tblMain", "[DRNO]=" & Me!DRNO) > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If

boblarson said:
That would be because I goofed and didn't give you the complete code (sorry
about that):

If DCount("[DRNO]","YourTableNameHere", "[DRNO]=" & Me!DRNO) > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If

--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


lcox400w said:
Thanks for taking the time to send me the below code. However, no matter
what number I put in the field (its a 9 digit number field), now it always
goes to the message box and says the number already exists, even if it
doesnt.

boblarson said:
In the control's BEFORE UPDATE event, use

If DCount("[DRNO]","YourTableNameHere") > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


:

I have a key field "DRNO". Its the first field on the form. When the user
exists the field, I want to check to see if the DRNO already exists in the
database so the user doesnt end up filling out the whole form only to find
out it already exists.

How do I test for the existance of DRNO when the user exits the field?

thanks
Click to expand...
Click to expand...
Click to expand...
 
L

lcox400w

Douglas,

I tried this code and I get the same error, data type mismatch. The field
was created as a text field in the table data type, but i'm entering a 9
digit number in the field. I didnt think it would matter as long as i'm
compairng whats entered into the field to the database field to look for a
match?


Douglas J. Steele said:
Um, shouldn't there be a Where clause in that DCount statement?

If DCount("*","YourTableNameHere", "]DRNO] =" & Me.DRNO) > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


boblarson said:
In the control's BEFORE UPDATE event, use

If DCount("[DRNO]","YourTableNameHere") > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


lcox400w said:
I have a key field "DRNO". Its the first field on the form. When the
user
exists the field, I want to check to see if the DRNO already exists in
the
database so the user doesnt end up filling out the whole form only to
find
out it already exists.

How do I test for the existance of DRNO when the user exits the field?

thanks
Click to expand...
Click to expand...
Click to expand...
 
B

boblarson

It DOES matter what datatype it is. So, it would need to be:

If DCount("*","YourTableNameHere", "[DRNO] ='" & Me.DRNO & "'") > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If

With the extra single quote after the = sign and the & "'" added at the end.
You need quotes if it is a text datatype.
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


lcox400w said:
Douglas,

I tried this code and I get the same error, data type mismatch. The field
was created as a text field in the table data type, but i'm entering a 9
digit number in the field. I didnt think it would matter as long as i'm
compairng whats entered into the field to the database field to look for a
match?


Douglas J. Steele said:
Um, shouldn't there be a Where clause in that DCount statement?

If DCount("*","YourTableNameHere", "]DRNO] =" & Me.DRNO) > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


boblarson said:
In the control's BEFORE UPDATE event, use

If DCount("[DRNO]","YourTableNameHere") > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


:

I have a key field "DRNO". Its the first field on the form. When the
user
exists the field, I want to check to see if the DRNO already exists in
the
database so the user doesnt end up filling out the whole form only to
find
out it already exists.

How do I test for the existance of DRNO when the user exits the field?

thanks
Click to expand...
Click to expand...
Click to expand...
 
L

lcox400w

that was the trick. I never would of figured that out.

thank you

boblarson said:
It DOES matter what datatype it is. So, it would need to be:

If DCount("*","YourTableNameHere", "[DRNO] ='" & Me.DRNO & "'") > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If

With the extra single quote after the = sign and the & "'" added at the end.
You need quotes if it is a text datatype.
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


lcox400w said:
Douglas,

I tried this code and I get the same error, data type mismatch. The field
was created as a text field in the table data type, but i'm entering a 9
digit number in the field. I didnt think it would matter as long as i'm
compairng whats entered into the field to the database field to look for a
match?


Douglas J. Steele said:
Um, shouldn't there be a Where clause in that DCount statement?

If DCount("*","YourTableNameHere", "]DRNO] =" & Me.DRNO) > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In the control's BEFORE UPDATE event, use

If DCount("[DRNO]","YourTableNameHere") > 0 Then
MsgBox "This number already exists", vbExclamation, "Number Exists"
Cancel = True
End If
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


:

I have a key field "DRNO". Its the first field on the form. When the
user
exists the field, I want to check to see if the DRNO already exists in
the
database so the user doesnt end up filling out the whole form only to
find
out it already exists.

How do I test for the existance of DRNO when the user exits the field?

thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to see if a Section exists in Word Document, and if it does, delete it 2
Verifying data is in a field before exit or close 2
Msgbox YESNO trouble 6
Dynamic Naming of Form Button 3
find file exists 1
VBA - exporting serial letters to single documents 0
ASK Field Formatting 1
Copy previous record into form field 0

Top