VB Script in Access

I have a script I am trying to use in Access 2007. I am using it in a form to automatically insert data from a field in one form, into another form. I use a similar script in a different form without any problems. When I try to use it in this new form, I get an error when I double click the field that reads "Type Mismatch" Can someone tell me what is causing the error?
Private Sub On_Hold_DblClick(Cancel As Integer)

On Error GoTo Err_On_Hold_DblClick

    Dim stDocName As String
    Dim stLinkCriteria As Integer
    Me!LastRecordNumber = Me.CurrentRecord

    stDocName = "FrmStatus"
    
    stLinkCriteria = "([MOLineKey1]='" & Me![MOLineKey] & "')"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_On_Hold_DblClick:
    Exit Sub

Err_On_Hold_DblClick:
    MsgBox Err.Description
    Resume Exit_On_Hold_DblClick
End Sub

Open in new window

LVL 1
CJSilverAsked:
Who is Participating?
 
mbizupCommented:
The above assumes that the datatype of MOLineKey1 is TEXT.  If MOLineKey1 is a text/integer field, then the criteria should still be defined as a string, but the sytax would be slightly different (no embedded quote delimiters for numeric data types):


On Error GoTo Err_On_Hold_DblClick

    Dim stDocName As String
    Dim stLinkCriteria As String
    Me!LastRecordNumber = Me.CurrentRecord

    stDocName = "FrmStatus"
   
' use this syntax for numeric MOLineKey1
    stLinkCriteria = "[MOLineKey1]=" & Me![MOLineKey]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_On_Hold_DblClick:
    Exit Sub

Err_On_Hold_DblClick:
    MsgBox Err.Description
    Resume Exit_On_Hold_DblClick
End Sub
0
 
NorieVBA ExpertCommented:
That code only seems to open a form.
0
 
mbizupCommented:
Your criteria is currently defined as integer, but you are assigning a string to it, resulting in a type mismatch.  It should be defined as string:


On Error GoTo Err_On_Hold_DblClick

    Dim stDocName As String
    Dim stLinkCriteria As String
    Me!LastRecordNumber = Me.CurrentRecord

    stDocName = "FrmStatus"
   
    stLinkCriteria = "[MOLineKey1]='" & Me![MOLineKey] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_On_Hold_DblClick:
    Exit Sub

Err_On_Hold_DblClick:
    MsgBox Err.Description
    Resume Exit_On_Hold_DblClick
End Sub
0
 
CJSilverAuthor Commented:
Thank you mbizup, that worked perfectly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.