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


Pass value from FORM into parameter query

Posted on 2004-11-05
Medium Priority
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

ID: 12507757
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

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

Author Comment

ID: 12510009
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?

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 39

Expert Comment

ID: 12512896
[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

GRayL earned 375 total points
ID: 12514243
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

stevbe earned 375 total points
ID: 12516828
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.




Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…
Suggested Courses

834 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