"Enter parameter value" on access query issue

Question

I'm trying to run an access SELECT query through a button on a subform. I need to use one of the fields in the subform as criteria for the query. I currently have this:

SELECT id, issue, complaint, action, reviewer, ticket
FROM tblEscalations
WHERE asin = Forms![form_name]![subform_name].[id];

Every time I run the query, I get the pop up asking for a parameter value for WHERE asin = Forms![form_name]![subform_name].[id];. Can you please tell me what's wrong with this?

I've only been doing Access for like a month and a half so the the answer might be very simple.

HansUp HansUp · Accepted Answer · 2014-04-15T00:57:45

When you refer to a control which is contained on a subform, you must reference it via the name of the subform control rather than the name of the form which is contained in the subform control.

And the subform control name can be different than the name of the form which it contains.

Since Forms![form_name]![subform_name].[id] isn't working, subform_name may be the name of the form. You need to substitute the name of the subform control:

Forms!form_name!YourSubformControl!id

When you have that name correct, running this statement in the Immediate window will tell you "SubForm":

? TypeName(Forms!form_name!YourSubformControl!id)

Here is an Immediate window session on my system with a form named frmParent which includes a subform control named Child2. And that control contains a form named fsubColortable which in turn contains a text box named ID:

? TypeName(Forms!frmParent!Child2)
SubForm
? Forms!frmParent!Child2.Form.Name
fsubColortable
? Forms!frmParent!Child2!ID
 1

If you're not familiar with the Immediate window, press Ctrl+g. Then type this in the window and press Enter

? Forms.Count

That should be enough to get you started. :-)

"Enter parameter value" on access query issue

1 Answers