Before Update (DCount)

  • Thread starter Thread starter Patti
  • Start date Start date
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!
 
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,

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