referring to a subform control

[wingforward]

I have a main form, frmMemberships referencing a subform fsubSearch. The container for the subform is named subSearch. On fsubSearch is a control, txtOrgIDSearch. txtOrgIDSearch is an unbound combo box that users can select a Organization from.

I am trying to reference the control in the query source for frmMemberships, but I keep getting an error. The syntax is [Me].[subSearch].[Form].[txtOrgIDSearch].

The query is supposed to filter the main form to the Organization chosen. It works when the combo box is on the main form directly (and change the query to [Me].[txtOrgIDSearch]), but not when I put it on the subform.

I've double checked the names of the subform control and the text box control, but they are correct.

What am I missing?

Thanks from a first time poster.

 

[pbaldy]

Welcome to the site. I'm a little confused about where you have this. "Me" is only valid in VBA code, so could not be in a query criteria. You'd need the full reference.

Forms!frmMemberships!subSearch.Form!txtOrgIDSearch

 

[wingforward]

Well that explains it! It works with the full reference.

Would you mind explaining why VBA can't be used in a query? Are queries only SQL? What's the difference between VBA and VB?

Thanks again.

 

[pbaldy]

The only way VBA can be used in an Access query is to call a public function (either in the criteria or as a calculated field). You can't refer to a VBA variable from a query, and as you've found out "Me" can't be used. Since Me in VBA refers to the object containing the code, it would have no relevance in a query (ie, what object is it supposed to refer to?).

 

[ajetrumpet]

Paul,

can't you refer to a public variable when using a query? ie - setting it? something like:

SELECT function(), etc...

function()

mypublicvariable = myvalue

function = mypublicvariable

 

[boblarson]

But that is calling a FUNCTION it isn't using VBA. Note that you can't call a SUB from a query - only a function.

 

[pbaldy]

Sure, but you're still calling the function from the query, not the variable itself, which was my point (my apparently poorly worded point). Calling a function that returns the variable value is the way around not being able to refer to the variable directly.

 

[ajetrumpet]

Calling a function that returns the variable value is the way around not being able to refer to the variable directly.

it's a way to get the job done Paul. That's what I'm the best at, remember? It doesn't always have to be "perfect."

At least...there's not always time for that.

 

[Galaxiom]

In A2007 there is also the TempVars collection. The TempVar can be set up in VBA or by a macro and then used by expressions in queries and macros throughout the project.
http://msdn.microsoft.com/en-us/library/bb257080.aspxhttp://msdn.microsoft.com/en-us/library/bb257083.aspx