Getting a Value from what I think is a Subform

[azhian]

So, I'm fairly new to Access so there's still a lot of things I am trying to figure out. Hence, I may make some mistakes in explaining my situation so feel free to correct me.

I created a form that is user friendly name "HOME". In that form, I added navigation controls which include a form in each button (i am assuming these are subforms). These subforms are identical to the regular forms I built them off of (I first made the forms then embedded them into the "HOME" form as subforms).

One of my subforms is "Model Number Search" where I have the person type in a model number in a text box in the subform then they click a button which runs a macro to open a query in a new tab. The query is a union query that looks up records with with the following line as a criteria:
WHERE ((([Service Call Log].[Model Number #1])=[Forms]![Model Number Search].[Model Number]))

This however only works if the original form "Model Number Search" is open and has the value typed into that text box but I am trying to open it from the "HOME" form that also has the "Model Number Search" as a subform. I tried googling what to do and changed the criteria value "[Forms]![Model Number Search].[Model Number]))" to many different versions but to no avail. I was wondering if anyone has a idea of what I would have to do to get this to work.

Below is a picture of my "HOME" form if that helps.
easycaptures.com/fs/uploaded/776/0996518851.png

 

[JHB]

.. I tried googling what to do and changed the criteria value "[Forms]![Model Number Search].[Model Number]))" to many different versions but to no avail.

Have you also tried to put in the main form name?

"[Forms]![B][COLOR=Red][Home]![/COLOR][/B][Model Number Search].[Model Number]"

 

[azhian]

Have you also tried to put in the main form name?

"[Forms]![B][COLOR=Red][Home]![/COLOR][/B][Model Number Search].[Model Number]"

Yes, I have. That was the code I kept seeing and then I also saw variations that would include periods instead of the exclamation point. I just tried it again right now and I got the popup that asks me to type in the "parameter value". Is this feature I'm trying to do just not available?

 

[Frothingslosh]

For referring to controls on forms and subforms, here's a good link to have:

http://access.mvps.org/access/forms/frm0031.htm

The code JHB provided is how you would pull a value from a control on a subform. The fact that you're asked for a parameter value suggests something is spelled incorrectly or you're using the wrong name. I'd recommend checking the spelling of names of both the sub form control (NOT the form itself) and the model number control. Ten to one says one of those is not the same as you used in your query - probably the subform control.

 

[azhian]

For referring to controls on forms and subforms, here's a good link to have:

access.mvps.org/access/forms/frm0031.htm

The code JHB provided is how you would pull a value from a control on a subform. The fact that you're asked for a parameter value suggests something is spelled incorrectly or you're using the wrong name. I'd recommend checking the spelling of names of both the sub form control (NOT the form itself) and the model number control. Ten to one says one of those is not the same as you used in your query - probably the subform control.

It seems like this would most likely be the case. How do I check what the subfom control name is? Is it the "Name" from the property sheet when I click on the text box while in layout mode?

 

[Frothingslosh]

It seems like this would most likely be the case. How do I check what the subfom control name is? Is it the "Name" from the property sheet when I click on the text box while in layout mode?

I avoid layout view like the plague, so I couldn't tell you that. If you go into design view, right click the subform control itself (not inside the subform - it's easiest to click around the border at the top), and select 'properties', it'll be on the 'other' tab as 'Name'. That's what you want to use, and it's best if it's not identical to the name of the form itself. (I generally use sfmFormName for subforms.)

 

[azhian]

I avoid layout view like the plague, so I couldn't tell you that. If you go into design view, right click the subform control itself (not inside the subform - it's easiest to click around the border at the top), and select 'properties', it'll be on the 'other' tab as 'Name'. That's what you want to use, and it's best if it's not identical to the name of the form itself. (I generally use sfmFormName for subforms.)

I see. I'm guessing you don't like the layout view cuz you're a pro at this. I'm kind of new so it's a lot friendlier to me.

I took your suggestion and went into design view and selected the border. you can see it here in the picture (http://easycaptures.com/4451338221). In the "Other" tab in the properties, it said the "Name" was "NavigationSubform" so I changed my criteria to
WHERE ((([Service Call Log].[Model Number #1])=[Forms]![HOME]![NavigationSubform].[Model Number]))
but it still gave the same issue with the popup asking to enter the parameter value. Am I doing something wrong?

 

[Frothingslosh]

I see. I'm guessing you don't like the layout view cuz you're a pro at this. I'm kind of new so it's a lot friendlier to me.

I took your suggestion and went into design view and selected the border. you can see it here in the picture (http://easycaptures.com/4451338221). In the "Other" tab in the properties, it said the "Name" was "NavigationSubform" so I changed my criteria to
WHERE ((([Service Call Log].[Model Number #1])=[Forms]![HOME]![NavigationSubform].[Model Number]))
but it still gave the same issue with the popup asking to enter the parameter value. Am I doing something wrong?

It's partially because it doesn't seem to give me the level of control I like, and partially because I use A2003 at work, and it doesn't have layout view.

Did you check the name of the Model Number control? You would get that error both if the field name and the control name are identical, and if the control name is not what you've entered.

I would also recommend reading THIS and following its advice.

 

[azhian]

I got it. It was because the criteria I used was
WHERE ((([Service Call Log].[Model Number #1])=[Forms]![HOME]![NavigationSubform].[Model Number]))
but what it was supposed to be was
WHERE ((([Service Call Log].[Model Number #1])=[Forms]![HOME].[NavigationSubform].Form.[Model Number]))
That link you gave me really helped a lot. And thanks for the naming guidelines. I knew that there should've been a better way for me to name my tables and queries.