I'm using Access 2010 and trying to change the recordsource of a subform by
pressing a button on the parent form which contains the following code:

With Me!sfPeakTypes
.LinkMasterFields = ""
.LinkChildFields = ""
.Form.RecordSource = "SELECT * FROM txPeakTypes WHERE PropNum = 98"
End With

The correct results flash on the screen in the subform, but then clear,
leaving a blank subform.
Any ideas out there?

--
Bob Darlington
Brisbane

Bob Darlington wrote:


Shooting in the dark, but ... Right after the subform goes
blank, use the Immediate Window to check the
LinkMaster/Child properties to see if they were set to an
auto link field:
?Forms!yourform.subformcontrol.LinkMasterFields
?Forms!yourform.subformcontrol.LinkChildFields
If those come back blank, forget I posted this.

But if those display some field name, then try to find the
equivalent of the A2003 Tools - Options - Tables/Queries -
AutoIndex On Import/Create and clear it.

--
Marsh

"Marshall Barton" <marshbarton (AT) wowway (DOT) com> wrote

Thanks Marsh.
That was the problem, but clearing the auto index didn't work.
But you put me on the right track. I put the Link lines after (instead of
before) resetting the recordsource and it now works fine.

With Me!sfPeakTypes
.Form.RecordSource = "SELECT * FROM txPeakTypes WHERE PropNum = 98"
.LinkMasterFields = ""
.LinkChildFields = ""
End With

Thanks again.
--
Bob Darlington
Brisbane

Bob Darlington wrote:
Did you close and reopen Access after clearing the AutoIndex
option? I haven't seen this issue in years since I made it
a habit for all new databases so I'm pretty sure it should
work.

I think it's a good idea regardless of your current problem,
if for no other reason than having a field indexed just
because its name matches up with something in that list.

--
Marsh

"Marshall Barton" <marshbarton (AT) wowway (DOT) com> wrote

Marsh,
No. I closed the application but not Access.
But as you suggested, I've cleared the AutoIndex now and all seems well.


--
Bob Darlington
Brisbane