How to refer to mainform from query in subform?

[Guest]

Hi,
I've a form with a subform. The subform is based on a query that has to
refer to a TextBox in the mainform. That could be done with:

select * from aTable
where aField = Forms!myMainform!myTextBox

But in that case the subform is not re-usable in another form. To achieve
that I would like to use such as:

select * from aTable
where aField = Me.Parent!myTextBox

But that does'nt work. What should be the correct expression?

Thanks for advice
Henk

 

[Roger Carlson]

Unfortunately, SQL (even as the recordsource) does not know what "Me" is.
I'd say you have two options: 1) code the recordsource in the OnOpen (or
OnLoad) event of the subform, or 2) use a function that returns the current
parent form and use that in the SQL.

But before I pursue this further, why are you doing this? Surely this is
handles by the Link Parent/Child properties of the subform control.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

I've a list of items with comment. The comment is valid during a certain
period:
MyTable(item, startingdate, endingdate, comment)
Now I want to list the items and comments that are valid in a user-defined
period (date1, date2). "Valid" means now startingdate or ending date falling
in (date1, date2). Date1 and date2 are in mainform.

The query looks like:
Select * from MyTable
Where (startingdate > date1 and startingdate < date 2) OR (endingdate >
date1 and eindingdate < date 2)

I don't see how to achieve this with this with Link Parent/Child properties.

Your second suggestion seems to me the most attractive. Is there a property
to get the Current Form?

 

[Roger Carlson]

Actually, it would be better to set the recordsource of the subform from the
main form. In the OnCurrent event of your mainform, try something like
this:

Me.Subform.Form.RecordSource = "select * from aTable where aField = " &
Me.myTextBox

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L