Determine if a record exists

  • Thread starter Thread starter lcox400w
  • Start date Start date
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
 
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

__________________________________
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top