Before Update (DCount)

P

Patti

The following is producing the error "data type mismatch in criteria
expression. " I'm not spotting the problem.
If it matters, the SSN is input with a mask, and the dashes are stored in
the table. Anyone?

Private Sub txt_tbl_Personal_Data_SSN_BeforeUpdate(Cancel As Integer)

If DCount("[SSN]", "tbl_ Personal Data", "[SSN] = " & Me.[txt_tbl_Personal
Data_SSN]) > 0 Then
MsgBox "Record already exists"
Cancel = True
End If

Thanks!
 
P

Patti

It turned out to be a problem with quotes. Should have been:

Private Sub txt_tbl_Personal_Data_SSN_BeforeUpdate(Cancel As Integer)

If DCount("[SSN]", "tbl_ Personal Data", "[SSN] = '" & Me.[txt_tbl_ Personal
Data_SSN] & "'") > 0 Then
MsgBox "Record already exists
Cancel = True
End If

** So it's working, but once I clear my MsgBox message, the Access supplied
error "the value in the field violates the validation rule" message kicks
in. Can I get rid of that? I find it odd to get that error now that I
added the BeforeUpdate sub & don't need it anymore!

Thanks,

Patti
 
F

Frederick Wilson

Patti,

Is there a particular reason you used Dcount instead of DLOOKUP?

In an earlier post from John Vison, he suggested this when I was having the
same problem. Add this to your BeforeUpdate event

me.txt_tbl_ PersonalData_SSN.undo
Patti said:
It turned out to be a problem with quotes. Should have been:

Private Sub txt_tbl_Personal_Data_SSN_BeforeUpdate(Cancel As Integer)

If DCount("[SSN]", "tbl_ Personal Data", "[SSN] = '" & Me.[txt_tbl_ Personal
Data_SSN] & "'") > 0 Then
MsgBox "Record already exists
Cancel = True
End If

** So it's working, but once I clear my MsgBox message, the Access supplied
error "the value in the field violates the validation rule" message kicks
in. Can I get rid of that? I find it odd to get that error now that I
added the BeforeUpdate sub & don't need it anymore!

Thanks,

Patti


Patti said:
The following is producing the error "data type mismatch in criteria
expression. " I'm not spotting the problem.
If it matters, the SSN is input with a mask, and the dashes are stored in
the table. Anyone?

Private Sub txt_tbl_Personal_Data_SSN_BeforeUpdate(Cancel As Integer)

If DCount("[SSN]", "tbl_ Personal Data", "[SSN] = " & Me.[txt_tbl_Personal
Data_SSN]) > 0 Then
MsgBox "Record already exists"
Cancel = True
End If

Thanks!
Click to expand...
Click to expand...
 
P

Patti

Frederick,

I used DCount because I found it to be the popular answer when I Googled
"prevent duplicates". I'll check in to DLookup if that'll fix the problem.

Thanks,

Patti


Frederick Wilson said:
Patti,

Is there a particular reason you used Dcount instead of DLOOKUP?

In an earlier post from John Vison, he suggested this when I was having the
same problem. Add this to your BeforeUpdate event

me.txt_tbl_ PersonalData_SSN.undo
Patti said:
It turned out to be a problem with quotes. Should have been:

Private Sub txt_tbl_Personal_Data_SSN_BeforeUpdate(Cancel As Integer)

If DCount("[SSN]", "tbl_ Personal Data", "[SSN] = '" & Me.[txt_tbl_ Personal
Data_SSN] & "'") > 0 Then
MsgBox "Record already exists
Cancel = True
End If

** So it's working, but once I clear my MsgBox message, the Access supplied
error "the value in the field violates the validation rule" message kicks
in. Can I get rid of that? I find it odd to get that error now that I
added the BeforeUpdate sub & don't need it anymore!

Thanks,

Patti


Patti said:
The following is producing the error "data type mismatch in criteria
expression. " I'm not spotting the problem.
If it matters, the SSN is input with a mask, and the dashes are stored in
the table. Anyone?

Private Sub txt_tbl_Personal_Data_SSN_BeforeUpdate(Cancel As Integer)

If DCount("[SSN]", "tbl_ Personal Data", "[SSN] = " & Me.[txt_tbl_Personal
Data_SSN]) > 0 Then
MsgBox "Record already exists"
Cancel = True
End If

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
P

Patti

Okay, I tried that, & it doesn't stop the Access supplied error. Also, even
though I added Me.[txt_tbl_Candidate Data_SSN].Undo after the "Cancel =
True", it is not clearing the text box. Maybe this is the cause?




Patti said:
Frederick,

I used DCount because I found it to be the popular answer when I Googled
"prevent duplicates". I'll check in to DLookup if that'll fix the problem.

Thanks,

Patti


Frederick Wilson said:
Patti,

Is there a particular reason you used Dcount instead of DLOOKUP?

In an earlier post from John Vison, he suggested this when I was having the
same problem. Add this to your BeforeUpdate event

me.txt_tbl_ PersonalData_SSN.undo
Patti said:
It turned out to be a problem with quotes. Should have been:

Private Sub txt_tbl_Personal_Data_SSN_BeforeUpdate(Cancel As Integer)

If DCount("[SSN]", "tbl_ Personal Data", "[SSN] = '" & Me.[txt_tbl_ Personal
Data_SSN] & "'") > 0 Then
MsgBox "Record already exists
Cancel = True
End If

** So it's working, but once I clear my MsgBox message, the Access supplied
error "the value in the field violates the validation rule" message kicks
in. Can I get rid of that? I find it odd to get that error now
Click to expand...
Click to expand...
that
I
added the BeforeUpdate sub & don't need it anymore!

Thanks,

Patti


The following is producing the error "data type mismatch in criteria
expression. " I'm not spotting the problem.
If it matters, the SSN is input with a mask, and the dashes are
Click to expand...
stored
in
the table. Anyone?

Private Sub txt_tbl_Personal_Data_SSN_BeforeUpdate(Cancel As Integer)

If DCount("[SSN]", "tbl_ Personal Data", "[SSN] = " & Me.[txt_tbl_Personal
Data_SSN]) > 0 Then
MsgBox "Record already exists"
Cancel = True
End If

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
J

John Vinson

The following is producing the error "data type mismatch in criteria
expression. " I'm not spotting the problem.
If it matters, the SSN is input with a mask, and the dashes are stored in
the table. Anyone?

Private Sub txt_tbl_Personal_Data_SSN_BeforeUpdate(Cancel As Integer)

If DCount("[SSN]", "tbl_ Personal Data", "[SSN] = " & Me.[txt_tbl_Personal
Data_SSN]) > 0 Then
MsgBox "Record already exists"
Cancel = True
End If

Thanks!
Click to expand...

If SSN is (as it must be, from your description) a Text field you need
the syntactically required quotemarks:

If DCount("[SSN]", "tbl_ Personal Data", "[SSN] = '" &
Me.[txt_tbl_Personal Data_SSN] & "'") > 0 Then

Actually DCount() is going to be slower than a simple DLookUp, which
will stop when it finds the first match:

If Not IsNull(DLookUp("[SSN]", "tbl_ Personal Data", "[SSN] = '" &
Me.[txt_tbl_Personal Data_SSN] & "'")) Then
 

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

Check for duplicate before update 9
BeforeUpdate Problem 16
Error Message with Duplicate Values 10
Replace Access error message with more descriptive message. 4
BeforeUpdate event 3
SSN Input Mask 1
Autofill 3
Forms 3

Top