Using Me.Parent in a query

[Historypaul]

It all worked just fine. Then I put it into a navigation control and now I'm fixing it...



I'm trying to code my form so that it works in or outside of the navigation form...

On a subform my combo box uses a query for its rowsource. The filter criteria for the query is the current value in a combobox on the parent form. Instead of addressing the parent form object longform ([Forms].[Parent].[control], can I just use me.parent in my query? Seems like it's not liking that so much.

What do you think would be the best way to solve this? Should I use an Iif function in my rowsource for the subform combobox to see if it's a sub or a sub-sub then use the other query?

[alcapps]

usually if you are in a subform and you want to refer back to the main form.
me.parent.controlname is enough.

if you are trying to change something in another subform on the main page..

then you use me.parent.subformControlName.form.controls(nameofc ontrol)

hope this helps

if it doesn't maybe you can post a db with that form in the website..

[Historypaul]

Your reply brings up an interesting question to me. Does Me.parent refer the the form one level above "Me" or does it refer to the master open form?

[alcapps]

one level up..

[Historypaul]

Thanks for the help, alcapps. It seems that the me.parent property can't be used in a sql statement. The easiest way ended up being to put an unbound text box in my sub form and use the OnCurrent event and some code to give that textbox the current value of the combobox on the parent form. (I also placed the code in the afterupdate event on my parent form combobox to update the subform textbox) so if someone changes the combobox or navigates to another record, my subform automatically updates.

I was very intimidated when I first cracked the book on Access a couple weeks ago, but I'm really starting to enjoy this!

[alcapps]

you can embed the values into a sql query then either add that to a row source.

i.e.
strSQL = "select * from table where IDfield = " & me.parent.txtbox

change whatever control's row source or record source and requery the control

if you want to embed a query..

try
db as DAO.database
qdf as DAO.querydef
strSQL as string

strSQL = "select * from YourTableName where IDfield = " me.parent.comboboxName

set db = currentdb()
set qdf = db.Querydefs("queryname")
qdf.sql = strSQL
qdf.close

now you can run the changed query..

db.execute "queryname", dbfailonerror
or
requery what ever control you have tied to the query.

hope this helps..

[Historypaul]

Okay, you're using a lot of stuff there that apparently, my Access book didn't cover. It certainly looks much more eloquent than the way I went about it... Effectively, you're writing the query in VBA and packaging it up in a string variable, then using that variable as the control source? Am I understanding that right?

Never thought about using a variable as a control source. There are about 2,000 different ways to do each task, isn't there?

[alcapps]

Yes that is right. Which means you have more control. And more control means you have less limitations.
And your right you can do things at least a dozen different ways.