subform is blank when recordsource changed programmatically

[bob bob via AccessMonster.com]

I have a subform that consists of a continuous form that gets its data from
one of two queries. I want to change the recordsource between queries when
a button on the main form is clicked.

My onclick code is

Private Sub button_Click()
If Me!button.Caption = "query1" Then
Me!button.Caption = "query2"
Forms![main]![sub].Form.RecordSource = "query2"
Else
Me!button.Caption = "query2"
Forms![main]![sub].Form.RecordSource = "query1"
End If
Forms![main]![sub].Form.Requery
End Sub

The problem is that the subform shows no records after the code is run,
even though each query works fine if I manually enter it as the
recordsource before opening the form. In fact, if I open the subform on its
own, and run the same code using a button on the subform, it works fine.
It's only when I make the change through a main form onclick event that the
subform goes blank.

I've tried every combination of refresh, repaint, and requery that I can
think of, but the subform stays empty. Any ideas? Do I need to close and
re-open the main form to make this work?

 

[DebbieG]

Try changing
Forms![main]![sub].Form.RecordSource = "query2"
to
Me!NameOfSubform.Form.RecordSource = "query2"

I don't think you need the requery.


|I have a subform that consists of a continuous form that gets its data from
| one of two queries. I want to change the recordsource between queries when
| a button on the main form is clicked.
|
| My onclick code is
|
| Private Sub button_Click()
| If Me!button.Caption = "query1" Then
| Me!button.Caption = "query2"
| Forms![main]![sub].Form.RecordSource = "query2"
| Else
| Me!button.Caption = "query2"
| Forms![main]![sub].Form.RecordSource = "query1"
| End If
| Forms![main]![sub].Form.Requery
| End Sub
|
| The problem is that the subform shows no records after the code is run,
| even though each query works fine if I manually enter it as the
| recordsource before opening the form. In fact, if I open the subform on its
| own, and run the same code using a button on the subform, it works fine.
| It's only when I make the change through a main form onclick event that the
| subform goes blank.
|
| I've tried every combination of refresh, repaint, and requery that I can
| think of, but the subform stays empty. Any ideas? Do I need to close and
| re-open the main form to make this work?
|
| --
|

 

[david epsom dot com dot au]

think of, but the subform stays empty. Any ideas? Do I need to close

re-open the main form to make this work?

No, it will work if you keep trying :~)

Do not use filters on this subform. On subforms, filters
behave unpredictably at times.

Check the master field/child field properties of the
subform control. You will have trouble if these don't
match the new query, or if the field type changes. You
may have to make sure these are blank, and filter your
subform by altering the recordsource of the subform.
Since you are doing that anyway, that shouldn't be too
much trouble.

(david)

I have a subform that consists of a continuous form that gets its data from
one of two queries. I want to change the recordsource between queries
when
a button on the main form is clicked.

My onclick code is

Private Sub button_Click()
If Me!button.Caption = "query1" Then
Me!button.Caption = "query2"
Forms![main]![sub].Form.RecordSource = "query2"
Else
Me!button.Caption = "query2"
Forms![main]![sub].Form.RecordSource = "query1"
End If
Forms![main]![sub].Form.Requery
End Sub

The problem is that the subform shows no records after the code is run,
even though each query works fine if I manually enter it as the
recordsource before opening the form. In fact, if I open the subform on
its
own, and run the same code using a button on the subform, it works fine.
It's only when I make the change through a main form onclick event that
the
subform goes blank.

I've tried every combination of refresh, repaint, and requery that I can
think of, but the subform stays empty. Any ideas? Do I need to close and
re-open the main form to make this work?

 

[bob bob via AccessMonster.com]

I don't have any filters and the master/child fields are blank. It seems
more to be a matter of the form needing to be reloaded or something. As I
said, I've tried requery, refresh, and repaint in various locations in the
code, referring to both the subform and the main form at different times.

 

[david epsom dot com dot au]

I use both multiple subforms (in one subform control), and
multiple recordsources (on one subform). Typically, I reset
the subform recordsource either to change the source table
(in a poorly normalised database), or to filter the recordset.

I am not having any trouble doing so in A97-A2K3, and some
of the forms date back to Access 2.0.

You should not normally need to refresh or requery a subform
or subform control after changing the recordsource of a
subform: changing the recordsource will force the subform
to requery.

The problems that you report tend to imply a problem along
the lines I have indicated. In fact, I've never had the kind
of problems you report EXCEPT when it was associated with
filters or master/child properties. My next suggestion is
that you add debug code to report the master/child/filter
properties.

(david)

 

[bob bob via AccessMonster.com]

The master/child fields are blank. However, I've notice that sometimes,
when I interrupt the code to change it, the fields have set themselves to a
variable (examdate) that exists in both the main form and the query that
populates the subform. I'm not sure where this is happening, and it
doesn't happen every time.

 

[bob bob via AccessMonster.com]

Okay, you're right. I have added a trap and the damn thing is setting my
child and master fields, even though I have them blank. How can I keep
that from happening?

 

[bob bob via AccessMonster.com]

I've fixed the problem. There was no need for the main form to use the
date table as its object source. Once I eliminated the objectsource field
data on the main form, it stopped creating the master/child link I didn't
want.

 

[david epsom dot com dot au]

I read your previous post, but at that point it didn't
look like I could suggest anything more helpful than
what you could see for yourself :~)

So I'm relieved as well as glad that you've reported
the solution.

(david)