Solved

Ms Access VBA Type MisMatch Error 13.  Can't resolve it.

Posted on 2014-04-22
11
3,094 Views
Last Modified: 2014-04-23
I have a listbox on a form, I want to use the number in field 4 to open another form and open the record that has that unique number.

The number in Field 4 is an AutoNumber - Long Integer. It is simple a unique serial number for each record so I can keep them all unique.

My goal is to dblClick a line in the list box and then have that record open in the form that allows me to fully manage the record.

I get an Error 13 Type Mismatch on this line
stLinkCriteria = "[Service_Tag_No] = " '" & [List78]

Open in new window


All MsgBoxes provide the correct info
The RowSource in List78 is a SQL statement with Binding on Column 4

Here is my failing code:
Private Sub List78_DblClick(Cancel As Integer)
    Dim stDocName As String
    Dim stLinkCriteria As Integer
    stDocName = "Call_Ticket"
    MsgBox "We have arrived"
    MsgBox stDocName
    MsgBox Me![List78]
    MsgBox [List78]
    stLinkCriteria = "[Service_Tag_No] = " '" & [List78]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
End Sub

Open in new window


I think the problem is that I am not referencing the field on the next form properly. As I understand stLinkCriteria, it should open the record where Field = Data.

The field on the next form is a text box and it is called Service_Tag_No
Its ControlSource is Service_Tag_Number_Auto

Thanks for any help you can provide.
0
Comment
Question by:jasgot
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40016792
If Service_Tag_No is text:
stLinkCriteria = "[Service_Tag_No] = '" & [List78] & "'"

Open in new window

If Service_Tag_No is a number:
stLinkCriteria = "[Service_Tag_No] = " & [List78]

Open in new window

0
 
LVL 75
ID: 40016806
"The number in Field 4 is an AutoNumber - Long Integer"

Try this:

stLinkCriteria = "[Service_Tag_No] = " & CLng(Me.List78)

mx
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 40017113
Field 4, I'm assuming, is the fourth column in your listbox.
Your ListBox value may not be this 4th column value.

Put a break in your code and view the value of "me.list78".
It's probably not the value you are expecting.

So, you must change your criteria to apply the 4th column value.

Scott C
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 20

Expert Comment

by:clarkscott
ID: 40017114
Field 4, I'm assuming, is the fourth column in your listbox.
Your ListBox value may not be this 4th column value.

Put a break in your code and view the value of "me.list78".
It's probably not the value you are expecting.

So, you must change your criteria to apply the 4th column value.

Scott C
0
 

Author Comment

by:jasgot
ID: 40017353
Sage:
Both return Type Mismatch
Column 4 is correct, I use the MsgBoxes above to confirm.

Savant:
It returns a Type Mismatch
Capture.JPG
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40017394
Please try this:
stLinkCriteria = "[Service_Tag_No] = " & List78.Column(3)

Open in new window

0
 

Author Comment

by:jasgot
ID: 40017401
Ace:
Type MisMatch
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40017426
I missed this earlier.  You need to change the variable to a string or variant:
Dim stLinkCriteria As String

Open in new window

0
 

Author Comment

by:jasgot
ID: 40017469
Ace:
That made worked. Now I need to start on the next issue!  Thanks!
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40017480
Ouch! How did I miss that?!?
0
 

Author Comment

by:jasgot
ID: 40017506
Sorry :(  I'm starting a new question on the same project if you want to take another stab at it!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question