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

[David Haskins]

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.Rec ordSource

Parent!RecordListings1Panel.Form.DataFieldsPanel.F orm.RecordSource =
"Table004_Tasks"

MsgBox Parent!
RecordListings1Panel.Form.DataFieldsPanel.Form.Rec ordSource

'**************************
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.

[Allen Browne]

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" <da*******@gmail.comwrote in message
news:11**********************@e34g2000pro.googlegr oups.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.Rec ordSource

Parent!RecordListings1Panel.Form.DataFieldsPanel.F orm.RecordSource =
"Table004_Tasks"

MsgBox Parent!
RecordListings1Panel.Form.DataFieldsPanel.Form.Rec ordSource

'**************************
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.

[David Haskins]

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

-David

On Oct 19, 9:42 pm, "Allen Browne" <[e-mail]:

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" <[e-mail] in message

news:11**********************@e34g2000pro.googlegr oups.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.Rec ordSource

Parent!RecordListings1Panel.Form.DataFieldsPanel.F orm.RecordSource =
"Table004_Tasks"

MsgBox Parent!
RecordListings1Panel.Form.DataFieldsPanel.Form.Rec ordSource

'**************************
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 -