[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-04-22
11
Medium Priority
?
3,325 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 28

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 46

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 46

Accepted Solution

by:
aikimark earned 2000 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 28

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

872 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