Solved

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

Posted on 2014-04-22
11
2,959 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

912 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now