Problems loading a listbox rowsource from code

[Corey]

I am having some real trouble getting a listbox rowsource to load
properly. The listbox is actually on a subform. My main form has
several command buttons that change the MASTER_SUB_FORM object source
to the specific sub form needed by the end user. When designing the
sub form i used the querybuilder to create an SQL statment to define
the listbox values.

****
RowSource SQL Statement
****

SELECT [CAN_LAST] & ", " & [CAN_FIRST] AS Name,
CANDIDATES.INTERVIEW_TIME AS [Scheduled At] FROM CANDIDATES WHERE
(((CANDIDATES.EVENT)=FORMS!SELECT_INTERVIEW!SELECT _INTERVIEW_EVENT))
GROUP BY [CAN_LAST] & ", " & [CAN_FIRST], CANDIDATES.INTERVIEW_TIME
ORDER BY CANDIDATES.INTERVIEW_TIME;
As you can see, I use an object(combobox) as criteria
(Forms!SELECT_INTERVIEW!SELECT_INTERVIEW_EVENT)

When i only have the subform open this works fine. However, when i try
and load the subform from the main form I get a query prompt that says
Forms!SELECT_INTERVIEW!SELECT_INTERVIEW_EVENT.

What would cause this? Is there a way around this? I would really,
greatly appreciate any advise from you pro's

[Larry Linson]

The Form displayed in a Subform control is neither "Open" nor is it in the
Forms! collection. You must refer to it as the .Form property of the Subform
control.

Larry Linson
Microsoft Access MVP

"Corey" <co**********@yahoo.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

I am having some real trouble getting a listbox rowsource to load
properly. The listbox is actually on a subform. My main form has
several command buttons that change the MASTER_SUB_FORM object source
to the specific sub form needed by the end user. When designing the
sub form i used the querybuilder to create an SQL statment to define
the listbox values.

****
RowSource SQL Statement
****

SELECT [CAN_LAST] & ", " & [CAN_FIRST] AS Name,
CANDIDATES.INTERVIEW_TIME AS [Scheduled At] FROM CANDIDATES WHERE
(((CANDIDATES.EVENT)=FORMS!SELECT_INTERVIEW!SELECT _INTERVIEW_EVENT))
GROUP BY [CAN_LAST] & ", " & [CAN_FIRST], CANDIDATES.INTERVIEW_TIME
ORDER BY CANDIDATES.INTERVIEW_TIME;
As you can see, I use an object(combobox) as criteria
(Forms!SELECT_INTERVIEW!SELECT_INTERVIEW_EVENT)

When i only have the subform open this works fine. However, when i try
and load the subform from the main form I get a query prompt that says
Forms!SELECT_INTERVIEW!SELECT_INTERVIEW_EVENT.

What would cause this? Is there a way around this? I would really,
greatly appreciate any advise from you pro's

[Corey]

Larry,

Im sorry but im totally drawing a blank. Can you help me a little
here? By refering to it as the .form property what exactly would i be
doing. Thanks for your help!

[pietlinden]

REad this article... it explains it all.

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

[Corey]

So i changed up my criteria a little and the form at least loads now
without the query prompt. But now the listbox doesnt load.
Appearntly, I am still missing something, any ideas? Should I be
creating the rowsource from code or can i still use the query builder
to build the SQL statement from the list box properties box?