Pass value from FORM into parameter query

Posted on 2004-11-05
Last Modified: 2008-01-09
I have a parameter query that prompts me to enter a billet code (a "billet code" is the equivalent to a customer id).

I the the billet code exists, the correct record is brought up.   So far so good...

Below is the SQL code for parameter query:

SELECT tbl_BilletCodes.BilletCode, tbl_BilletCodes.Division, tbl_BilletCodes.Prefix, tbl_BilletCodes.Extension FROM tbl_BilletCodes WHERE (((tbl_BilletCodes.BilletCode)=[Me].[BilletCode]));

In a form, I have the four fields (BilletCode, Division, Prefix, and Division).  In this form I don't allow any changes to the fields (unless I "double-click" BilletCode).   However, a double-click event brings up another form (data source of this subform is the SQL code of the parameter query) where I then can update the record.   So far so good...

Here's the problem now ... when I double-click on BilletCode, I get the parameter dialog box into which I must enter the billet code.   Instead, I just want to automatically pass the value of the double-clicked BilletCode and bring up the record w/o any additional input.  That's not happening though....

Does anyone know how I can pass the value of the currently selected (double-clicked) billet code into the parameter query so that I get the proper single record in the form (I don't want to see all other records).

Question by:TomBock2004
    LVL 11

    Expert Comment

    You might be better off prompting the user for the billet code, then store it locally for use while form is open:

    Option Compare Database
    Option Explicit

    Dim sCrit As String

    Private Sub Command0_Click()
        Dim sCrit As String
        sCrit = InputBox("enter billet code", "Criteria")
        SELECT tbl_BilletCodes.BilletCode, tbl_BilletCodes.Division, tbl_BilletCodes.Prefix, tbl_BilletCodes.Extension FROM tbl_BilletCodes WHERE (((tbl_BilletCodes.BilletCode)=sCrit));

    End Sub
    LVL 44

    Expert Comment

    A query does not recognize Me.  Use Forms!FormName in the query.

    Author Comment

    Hello GRayL:

    Thanks... I tried this approach but something doesn't seem to work out.   I changed the SQL as follows:

    SELECT tbl_BilletCodes.BilletCode, tbl_BilletCodes.Division, tbl_BilletCodes.Prefix, tbl_BilletCodes.Extension FROM tbl_BilletCodes WHERE (((tbl_BilletCodes.BilletCode)=[Forms]![tblCorrespondenceActionOfficer Subform]![BilletCode]));

    Now, when double-clicking on the Billetcode, I still get the parameter window that "asks" for input.

    Did I do anything wrong?

    LVL 39

    Expert Comment

    [Forms]![tblCorrespondenceActionOfficer Subform]![BilletCode]

    you have to start of the form chain and use the name of the subform control ...


    Change MyForm to the name of the main form and MySubFormControl to the name of the subform control that contains your BilletCode data.

    LVL 44

    Accepted Solution

    If you are running the query from the same form containing the BilletCode Me! is defined.
    However, as stevbe pointed out, calling the BilletCode from a subform requires different treatment.  Change your query to:

    SELECT tbl_BilletCodes.BilletCode, tbl_BilletCodes.Division, tbl_BilletCodes.Prefix, tbl_BilletCodes.Extension FROM tbl_BilletCodes WHERE (((tbl_BilletCodes.BilletCode)=
    Me![tblCorrespondenceActionOfficer Subform].Form![BilletCode]));

    [tblCorrespondenceActionOfficer Subform] is a poor name for a subform.  You should try and prefix all form controls with an abbreviation which tells you the type of control:

    tbx - textbox
    cbo - combobox
    lbx - list box
    sfm - subform

    Thus, I would rename [tblCorrespondenceActionOfficer Subform] to
    sfmcorrespondenceActionOfficer.  Note now that you do not have any "strange" characters or spaces in the name you no longer need square brackets to delimit the name.

    You still have not told us the name of the form on which the subform is a control.

    LVL 39

    Assisted Solution

    we all have our own naming conventions ... some are a bit more alike that others but the importamnt thing is that by using nameing conventions it makes it easier to read and understand code. I prefix the name of the acualy subform object "sfrm" but prefix the subform control name with "child" so that when I am typing reference paths I do not confuse myself about which type of an object I am using.




    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    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…

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now