How to populate a subform programmatically?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eric - 24 Sep 2005 12:21 GMT

Hi,

I have a main and subform. On the main form, I create a complex (depending
on my input after pressing a button) sql query.
Now I want to populate my subform with the result of the query. Also, when I
push mu button again (and the query is redefined), the subform should be
"refreshed".

I tried several things, but did not get it to work...

Any idea's? Thanks in advance.

Eric

Reply to this Message

Marshall Barton - 24 Sep 2005 14:50 GMT

>I have a main and subform. On the main form, I create a complex (depending
>on my input after pressing a button) sql query.
>Now I want to populate my subform with the result of the query. Also, when I
>push mu button again (and the query is redefined), the subform should be
>"refreshed".

Make sure the SQL statement does what you want by using
Debug.Print and a breakpoint to stop the code. Then Copy
the SQL to the query design window's SQL view and opening
the query. Note, switch the form back to design view before
making any code changes.

Once the query does the right thing, the code to assign it
to the subform is:
Me.subformcontrol.Form.RecordSource = strSQL

Signature

Marsh
MVP [MS Access]

Reply to this Message

Eric - 24 Sep 2005 18:51 GMT

Hi,

the query gives the right result (checked).
When I try your suggestion, following error appears: the expression points
to an object which doesn't exist or is closed

> >I have a main and subform. On the main form, I create a complex (depending
> >on my input after pressing a button) sql query.
[quoted text clipped - 11 lines]
> to the subform is:
> Me.subformcontrol.Form.RecordSource = strSQL

Reply to this Message

Marshall Barton - 24 Sep 2005 20:14 GMT

That sounds like you used the wrong name for the subform
control. Note that the control's Name property might be
different from the name of the form object it is displaying
(specified in the contol's SourceObject property).

The only other thing I can think of is that you are really
doing this to a sepatate form, not a real subform.

Signature

Marsh
MVP [MS Access]

>the query gives the right result (checked).
>When I try your suggestion, following error appears: the expression points
[quoted text clipped - 15 lines]
>> to the subform is:
>> Me.subformcontrol.Form.RecordSource = strSQL

Reply to this Message

Eric - 25 Sep 2005 13:46 GMT

I removed my subform and re-inserted it. Now it works!
The difference: on the subform control (name SubDelivery), the recordsource
was empty. Now it says SubDelivery.
The recordsource for the form SubDelivery is empty and is set
programmatically (SubDelivery.Form.RecordSource = strSQL is okay).

I don't understand it, but now it works! Thanks for the input.

Eric

> That sounds like you used the wrong name for the subform
> control. Note that the control's Name property might be
[quoted text clipped - 22 lines]
> >> to the subform is:
> >> Me.subformcontrol.Form.RecordSource = strSQL

Reply to this Message

David C. Holley - 24 Sep 2005 22:14 GMT

Me.subformcontrol.Form.RecordSource = strSQL
Me.[insert name of the subform control].Form.RecordSource = strSQL

> Hi,
>
[quoted text clipped - 17 lines]
>>to the subform is:
>> Me.subformcontrol.Form.RecordSource = strSQL

Reply to this Message

David C. Holley - 24 Sep 2005 15:31 GMT

Did you try setting the subform's recordSet to the SQL Statement for the
query via code?

> Hi,
>
[quoted text clipped - 9 lines]
>
> Eric

Reply to this Message

Eric - 24 Sep 2005 18:53 GMT

yes, with Me.mySubForm.Form.RecordSource = strSQL

but the object is closed or doesn't exists (?)

> Did you try setting the subform's recordSet to the SQL Statement for the
> query via code?
[quoted text clipped - 12 lines]
> >
> > Eric

Reply to this Message