Pass value from FORM into parameter query

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).

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
A query does not recognize Me.  Use Forms!FormName in the query.
TomBock2004Author Commented:
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?

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

[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.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.



It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.