Solved

VB Script in Access

Posted on 2012-03-15
4
256 Views
Last Modified: 2012-03-19
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

0
Comment
Question by:CJSilver
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 37727231
That code only seems to open a form.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37727465
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
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37727475
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
 
LVL 1

Author Closing Comment

by:CJSilver
ID: 37738583
Thank you mbizup, that worked perfectly!
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

635 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