I can't find the Type Mismatch

[Lucantha]

I have a form that I placed a "link" to which is supposed to open one of three forms dependent on a value on the form. This is the sub:

Private Sub Label46_Click()
On Error GoTo Err_Label46_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim strFrmOpt As String
Dim strIDr As String


Select Case Me.Cl_Type
Case "Auto"
strFrmOpt = "Auto_Claim_frm" And strIDr = "Auto_adjID"
Case "GL"
strFrmOpt = "General_Liability_Claim_frm" And strIDr = "GL_adjID"
Case "WC"
strFrmOpt = "WC_Injury_Claim_frm" And strIDr = "WC_adjID"
End Select

stDocName = strFrmOpt

stLinkCriteria = strIDr & " ='" & Me![ClaimID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Label46_Click:
Exit Sub

Err_Label46_Click:
MsgBox Err.Description
Resume Exit_Label46_Click

End Sub

Problem is, on the form itself when I click the label a msg box appears that simply says "Type Mismatch", and I can't figure out why. All the values this sub is looking at are text values. Any suggestions?

 

[RuralGuy]

Make the following addition to your code and you will see what's wrong.

stLinkCriteria = strIDr & " ='" & Me![ClaimID] & "'"
[B][COLOR="Red"]MsgBox "[" & stLinkCriteria & "]"[/COLOR][/B]
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Lucantha]

Hmm... I added it, but nothing has changed.

 

[petehilljnr]

Take the error handling off so you can see where the error is occuring.

My guess is the error is occurring on the following lines:

Select Case Me.Cl_Type
Case "Auto"
strFrmOpt = "Auto_Claim_frm" And strIDr = "Auto_adjID"
Case "GL"
strFrmOpt = "General_Liability_Claim_frm" And strIDr = "GL_adjID"
Case "WC"
strFrmOpt = "WC_Injury_Claim_frm" And strIDr = "WC_adjID"
End Select

I'm not claiming to be an expert but I'd have written:

Select Case Me.Cl_Type
Case "Auto"
strFrmOpt = "Auto_Claim_frm"
strIDr = "Auto_adjID"
Case "GL"
strFrmOpt = "General_Liability_Claim_frm"
strIDr = "GL_adjID"
Case "WC"
strFrmOpt = "WC_Injury_Claim_frm"
strIDr = "WC_adjID"
End Select

The next thing I'd check is that in the forms "Auto_Claim_frm" etc that fields that you are matching (e.g. Auto_adhjID) is a String and not a Number because in your criteria string, you're putting a single quote around the value.


Hope this helps.

Regards,
Pete.

 

[unclejoe]

Hi Lucantha,

Look at code and comments.

First is the "strIDr" where you want to filter the form is the control/Column/Field, is it named correctly?
And is "strIDr" a Text or Number?

Private Sub Label46_Click()
On Error GoTo Err_Label46_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim strFrmOpt As String
Dim strIDr As String


Select Case Me.Cl_Type
Case "Auto"
strFrmOpt = "Auto_Claim_frm"
Case "GL"
strFrmOpt = "General_Liability_Claim_frm"
Case "WC"
strFrmOpt = "WC_Injury_Claim_frm"
End Select

stDocName = strFrmOpt
'if Me![ClaimID] is text than use this
'where strIDr is the control on that form

stLinkCriteria = "strIDr ='" & Me![ClaimID] & "'"

'if Me![ClaimID] is number than use this
'use without the quotes
'stLinkCriteria = "strIDr = " & Me![ClaimID]

'NOTE: if strIDr name is incorrect,
'find and correct it to the control name on that form
'or correct it to the name column/field on that table.

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Label46_Click:
Exit Sub

Err_Label46_Click:
MsgBox Err.Description
Resume Exit_Label46_Click

End Sub

I have a form that I placed a "link" to which is
Problem is, on the form itself when I click the label a msg box appears that simply says "Type Mismatch", and I can't figure out why. All the values this sub is looking at are text values. Any suggestions?

 

[RuralGuy]

Pete's solution will resolve the issue.

 

[Lucantha]

Pete,

I changed the Select Case statement to match yours:

Select Case Me.Cl_Type.Value
Case "Auto"
strFrmOpt = "Auto_Claim_frm"
strIDr = "Auto_adjID"
Case "GL"
strFrmOpt = "General_Liability_Claim_frm"
strIDr = "GL_adjID"
Case "WC"
strFrmOpt = "WC_Injury_Claim_frm"
strIDr = "WC_adjID"
End Select

It works.... sorta. Now in the form, when I click my "View Details" label it does open the form and take me to that record, but only for the first record. Any other record on that form also takes me to that first one, which is as if only the first case is executing. (To give you an idea of what the form looks like, it's a search style form:

Form Header.
--------------
Cl_Type.Label IncID.Label InternalID.Label ClaimID.Label Fieldetc.Label
txtCl_type txtIncID txtInternalID txtClaimID txtEtc

Form Details.
--------------
Cl_Type.txt IncID.txt InternalID.txt ClaimID.txt Fieldetc.txt
ViewDetails.label

The txtblahblah controls are unbound text boxes used to filter the records. As you can see I want the user to be able to click the "View Details" label to take them to that record, but since there are different forms for each type of claim I have to run that Select Case to make sure it goes to the right form and changes the fieldname appropriately. The [auto_AdjID], [GL_AdjID], and [WC_AdjID] Fields are fed into a query as "ClaimID" via a Union Query, and then that query adds an extra field "Location" and "City" from the master record joined by the IncID, and that's the record source for the form. Perhaps there's a conflict there?

Also, all the values referred to are text (alphanumeric) values such as "123AB". So the strIDr variable as far as I know should be a string. I check the original table and the queries that feed into this form and it's text all the way.

Hah, I'm starting to get the impression I'm making this more complicated than it needs to be. What do you think?

 

[Lucantha]

Okay, I just noticed something interesting, but I may need a little help to automate this.

As I said, it works now but only shows a detail form for the first record. However, I noticed that if I click on the ClaimID text box in a different record, the link does execute properly.

Is there a way I can tell the link to set the focus of the code to apply to the ClaimID text box in the same record?