type mismatch notice

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is a repost from another section (wrong section).

I have a txtbox on a form that the user will place an 8 place number. In the
table it is listed as Number/Long Integer. However, I continue to get a type
mismatch error notice. Below is the coding that I am using.

Private Sub ClaimNumber_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler
Dim strWhere As Long

With Me.ClaimNumber
If .Value = .OldValue Then

Else
strWhere = "[ClaimNumber]=""" & .Value & """"
If Not IsNull(DLookup("ClaimNumber", "tblSurveyResponses",
strWhere)) Then
MsgBox "The Claim Number is a duplicate and will need to
be changed. " & _
"The computer may also follow with a warning
message. If so, select the OK " & _
"button and proceed to change the Claim Number. You
will continue to get " & _
"this message until the duplication is corrected.
You will also not be able " & _
"to proceed until the duplication is corrected.",
vbOKOnly, "DUPLICATE CLAIM NUMBER"

Cancel = True

End If
End If
End With

exit_ClaimNumber_BeforeUpdate:
Exit Sub

ErrorHandler:
MsgBox Err.Description
Resume exit_ClaimNumber_BeforeUpdate

End Sub

Can anyone see where I am going wrong? Thanks in advance for the assistance.
*** John
 
John,

I believe your error lies in your declaration of strWhere
Dim strWhere As Long
Click to expand...

It should be declared as a String to enter text values into it. That should
clear up the problem ( I hope ). If not, write back and I'll take another
look.

- Nick
 
Nick, thanks. The datatype is actually a number and the field size is long
integer. As mentioned, all numbers are filling this field.
*** John
 
John,

Your strWhere does need to be declared as a String, but I believe that the
main problem lies in your concatenation of your SQL search term:
strWhere = "[ClaimNumber]=""" & .Value & """"
Click to expand...

The triple quotes encloses your .Value in quotation marks, so the program is
trying to search for a text value, not a number. Change it to

strWhere = "[ClaimNumber] = " & .Value

And you should be fine. I have had problems like that before and many times
they were just caused by enclosing number values in quotes.

I think that should clear it up... and if I'm on a completely different page
than you feel free to tell me, because I'm by no means an expert... just had
a lot of problems like that lately that I've had to clear up.

Hope that helps a little more,

Nick
 
Nick, I changed it but I still get the type mismatch notice. You were right
about the quotes. I didn't even think about those. But now I wonder what is
wrong. The datatype is Number and FieldSize is Long Integer. I wonder if I
would be better served changing this field to text even tho it is all numbers?
Any other thoughts on this dilemma?
*** John


Nick via AccessMonster.com said:
John,

Your strWhere does need to be declared as a String, but I believe that the
main problem lies in your concatenation of your SQL search term:
strWhere = "[ClaimNumber]=""" & .Value & """"
Click to expand...

The triple quotes encloses your .Value in quotation marks, so the program is
trying to search for a text value, not a number. Change it to

strWhere = "[ClaimNumber] = " & .Value

And you should be fine. I have had problems like that before and many times
they were just caused by enclosing number values in quotes.

I think that should clear it up... and if I'm on a completely different page
than you feel free to tell me, because I'm by no means an expert... just had
a lot of problems like that lately that I've had to clear up.

Hope that helps a little more,

Nick
Click to expand...
 
PMFJI,

John,

The reason you are getting the "Type Mismatch" error is due to the way this
line of code is written:

If .Value = .OldValue Then
Click to expand...

This will work if you are *editing* a previously entered number. If you
entering a new record, .OldValue is equal to NULL. So you are comparing a
number to NULL which is impossible. You could use:
If .Value = NZ(.OldValue,0) Then
Click to expand...

but, again, this would only catch duplicates if you are *editing* a
"ClaimNumber".

One other thing I noticed: the *field* is named "ClaimNumber" and the
*control* is named "ClaimNumber". This is confusing (for Access also). It
would be better to name the control something like "txtClaimNumber" or
"tbClaimNumber".

Here is your code (modified) that should work.
NOTE: I used "txtClaimNumber" as the name of the control box on the form.


'***begin code ***
Private Sub txtClaimNumber_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler
Dim strWhere As String

' no number entered when editing
If IsNull(txtClaimNumber) Then
Cancel = True
'undoes editing or cancels new record
Me.Undo
Exit Sub
End If
With Me.txtClaimNumber
strWhere = " [ClaimNumber] = " & .Value

If .Value = DLookup("ClaimNumber", "tblSurveyResponses", strWhere)
Then
MsgBox "The Claim Number is a duplicate and will need to be
changed. " & _
"The computer may also follow with a warning message. If
so, select the OK " & _
"button and proceed to change the Claim Number. You will
continue to get " & _
"this message until the duplication is corrected. You
will also not be able " & _
"to proceed until the duplication is corrected.",
vbOKOnly, "DUPLICATE CLAIM NUMBER"

Cancel = True

End If

End With

exit_txtClaimNumber_BeforeUpdate:
Exit Sub

ErrorHandler:
MsgBox Err.Description
Resume exit_txtClaimNumber_BeforeUpdate
End Sub

"*** end code ***


BTW, in your first post, you said: "....the user will place an 8 place
number...."

Will there *always* be an 8 digit number? This means the lowest Claim number
would have to be 10,000,000 and increase from there. Since the field type is
numeric, you can't enter 00001000 because you cannot save leading zeros. but
you could format it to "display" as 8 digits.

Just wondering....Anyway, HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


JohnE said:
Nick, I changed it but I still get the type mismatch notice. You were right
about the quotes. I didn't even think about those. But now I wonder what is
wrong. The datatype is Number and FieldSize is Long Integer. I wonder if I
would be better served changing this field to text even tho it is all numbers?
Any other thoughts on this dilemma?
*** John


Nick via AccessMonster.com said:
John,

Your strWhere does need to be declared as a String, but I believe that the
main problem lies in your concatenation of your SQL search term:
strWhere = "[ClaimNumber]=""" & .Value & """"
Click to expand...

The triple quotes encloses your .Value in quotation marks, so the program is
trying to search for a text value, not a number. Change it to

strWhere = "[ClaimNumber] = " & .Value

And you should be fine. I have had problems like that before and many times
they were just caused by enclosing number values in quotes.

I think that should clear it up... and if I'm on a completely different page
than you feel free to tell me, because I'm by no means an expert... just had
a lot of problems like that lately that I've had to clear up.

Hope that helps a little more,

Nick
Click to expand...
Click to expand...
 
Back
Top