[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2014-04-22
11
Medium Priority
?
3,374 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

612 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