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


change code for Access 2010 Form

Posted on 2012-08-13
Medium Priority
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
  • 3
  • 2
LVL 66

Accepted Solution

Jim Horn earned 2000 total points
ID: 38288198
>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

ID: 38288256
Thanks a lot!!!
LVL 66

Expert Comment

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

Thanks for the grade, good luck with your project.  -Jim
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 61

Expert Comment

ID: 38288432
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 66

Expert Comment

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

Expert Comment

ID: 38288464

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

873 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