How to populate a subform programmatically?

E

Eric

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
 
M

Marshall Barton

Eric said:
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".
Click to expand...


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
 
D

David C. Holley

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

Eric

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
 
E

Eric

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

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

Marshall Barton

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.
 
D

David C. Holley

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

Eric

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

Marshall Barton said:
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.
--
Marsh
MVP [MS Access]

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
Click to expand...
Click to expand...
 
Top