change code for Access 2010 Form

Posted on 2012-08-13
Last Modified: 2012-08-13
Hello Experts.  I need to change below listed code line:

stLinkCriteria = "[P-LOAN-NO]=" & Me![LOAN-NO]

Please see explanation: [P-LOAN-NO] is name of the field in the table;
ME ![Loan-No] name of the field in the form which was used in reference.

Issue: we change Data Type for the field [P-Loan-No] from Numeric to Text.
Form Field Loan-No also contains text value.

We need to add either double or single quotes to reference text value (vs numeric as it was before)

See code below

Private Sub cmdVacancy_Click()
On Error GoTo Err_cmdVacancy_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmVACANCY"
    stLinkCriteria = "[P-LOAN-NO]=" & Me![LOAN-NO]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit Sub

    MsgBox Err.Description
    Resume Exit_cmdVacancy_Click
End Sub
Question by:maximyshka
    LVL 65

    Accepted Solution

    >we change Data Type for the field [P-Loan-No] from Numeric to Text.
    In that case, you'll need to add single-quotes around the passed value:

    stLinkCriteria = "[P-LOAN-NO]='" & Me![LOAN-NO] & "'"

    Author Closing Comment

    Thanks a lot!!!
    LVL 65

    Expert Comment

    by:Jim Horn
    When you're concatening a text/string, use single quotes inside the double quotes.

    Thanks for the grade, good luck with your project.  -Jim
    LVL 61

    Expert Comment

    As an aside, if possible you should adopt a standard naming convention which avoids spaces and special characters like "-" (depending on the nature of the project, I realize that this is not always easy or feasible).

    These are the naming standard that many of the regulars here use:

    Doing so will save you trouble in the long run.  One of the questionners here has been having a lot of headaches recently because of special characters in names.
    LVL 65

    Expert Comment

    by:Jim Horn
    Amen Sister.  Everybody sing...
    LVL 61

    Expert Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    759 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

    11 Experts available now in Live!

    Get 1:1 Help Now