Solved

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

Posted on 2014-04-22
11
3,045 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

840 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