Need to change the Recordsource of an open form from another form using VBA

I have a fairly complex interface screen (form) that is comprised of
several subforms that perform different, but related activities. I am
designing a search/filter form that should be able to change the data
displayed in one or more of the other forms, but I cannot find the way
to change the Recordsource property of the second form from the first.


In a VBA Sub procedure triggered by the Click event of a button on the
search form, I have these bits of code.

'**************************
'MsgBoxes used purely for my information while coding...
MsgBox Parent!
RecordListings1Panel.Form.DataFieldsPanel.Form.RecordSource

Parent!RecordListings1Panel.Form.DataFieldsPanel.Form.Record Source =
"Table004_Tasks"

MsgBox Parent!
RecordListings1Panel.Form.DataFieldsPanel.Form.RecordSource

'**************************
The first MsgBox correctly displays the name of the current
Recordsource (a query) for the form.
The assignment doesn't seem to work, as the displayed records do not
change on the form.
The second MsgBox doesn't display.

I feel sure that I am missing something terribly obvious, but, try as
I might, I can't figure out what it might be.

Thanks for your help.

Re: Need to change the Recordsource of an open form from another form using VBA

Several things could be happening here.

The first step will be to temporarily remove any error handling, so you get
to know what's going on. Comment out any error handler in this procedure
(and others if it is called from somewhere else), e.g.:
'On Error ...
Also make sure you have Error Trapping set to:
Break on Unhandled Errors
under:
Tools | Options | General
from the code window.

Chances are, you will now be able to see what's going on. It could be as
simple as that the form is dirty with a record that cannot be saved, so the
attempt to set the RecordSource was not successful. Or it could be more
involved, e.g. you may have triggered an endless loop where the Current
event of that form makes another change to something that re-triggers it
Current event.

You may also need to be aware that when you change the RecordSource of a
subform, Access reassigns the LinkMasterFields/LinkChildFields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David Haskins" wrote in message
news:1192816597.823879.112290@e34g2000pro.googlegroups.com.. .
>I have a fairly complex interface screen (form) that is comprised of
> several subforms that perform different, but related activities. I am
> designing a search/filter form that should be able to change the data
> displayed in one or more of the other forms, but I cannot find the way
> to change the Recordsource property of the second form from the first.
>
>
> In a VBA Sub procedure triggered by the Click event of a button on the
> search form, I have these bits of code.
>
> '**************************
> 'MsgBoxes used purely for my information while coding...
> MsgBox Parent!
> RecordListings1Panel.Form.DataFieldsPanel.Form.RecordSource
>
> Parent!RecordListings1Panel.Form.DataFieldsPanel.Form.Record Source =
> "Table004_Tasks"
>
> MsgBox Parent!
> RecordListings1Panel.Form.DataFieldsPanel.Form.RecordSource
>
> '**************************
> The first MsgBox correctly displays the name of the current
> Recordsource (a query) for the form.
> The assignment doesn't seem to work, as the displayed records do not
> change on the form.
> The second MsgBox doesn't display.
>
> I feel sure that I am missing something terribly obvious, but, try as
> I might, I can't figure out what it might be.
>
> Thanks for your help.

Re: Need to change the Recordsource of an open form from another form using VBA

Good tips, I'll try them. Thanks a lot!

-David



On Oct 19, 9:42 pm, "Allen Browne" wrote:
> Several things could be happening here.
>
> The first step will be to temporarily remove any error handling, so you get
> to know what's going on. Comment out any error handler in this procedure
> (and others if it is called from somewhere else), e.g.:
> 'On Error ...
> Also make sure you have Error Trapping set to:
> Break on Unhandled Errors
> under:
> Tools | Options | General
> from the code window.
>
> Chances are, you will now be able to see what's going on. It could be as
> simple as that the form is dirty with a record that cannot be saved, so the
> attempt to set the RecordSource was not successful. Or it could be more
> involved, e.g. you may have triggered an endless loop where the Current
> event of that form makes another change to something that re-triggers it
> Current event.
>
> You may also need to be aware that when you change the RecordSource of a
> subform, Access reassigns the LinkMasterFields/LinkChildFields.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "David Haskins" wrote in message
>
> news:1192816597.823879.112290@e34g2000pro.googlegroups.com.. .
>
>
>
> >I have a fairly complex interface screen (form) that is comprised of
> > several subforms that perform different, but related activities. I am
> > designing a search/filter form that should be able to change the data
> > displayed in one or more of the other forms, but I cannot find the way
> > to change the Recordsource property of the second form from the first.
>
> > In a VBA Sub procedure triggered by the Click event of a button on the
> > search form, I have these bits of code.
>
> > '**************************
> > 'MsgBoxes used purely for my information while coding...
> > MsgBox Parent!
> > RecordListings1Panel.Form.DataFieldsPanel.Form.RecordSource
>
> > Parent!RecordListings1Panel.Form.DataFieldsPanel.Form.Record Source =
> > "Table004_Tasks"
>
> > MsgBox Parent!
> > RecordListings1Panel.Form.DataFieldsPanel.Form.RecordSource
>
> > '**************************
> > The first MsgBox correctly displays the name of the current
> > Recordsource (a query) for the form.
> > The assignment doesn't seem to work, as the displayed records do not
> > change on the form.
> > The second MsgBox doesn't display.
>
> > I feel sure that I am missing something terribly obvious, but, try as
> > I might, I can't figure out what it might be.
>
> > Thanks for your help.- Hide quoted text -
>
> - Show quoted text -