Subform only shows 1 record on requery

[pryordr]

I have a tabbed master form that has a subform on one tab that shows several fields of a list of projects we are working. There are several buttons at the top of the form that reset the data selection so as to show active projects, planning projects, and one to reset to all records. I am using VBA to set the form recordsource to something like "SELECT * FROM tblWOItems WHERE [phase] LIKE 'act*'", or just "SELECT * FROM tblWOItems" to reset to all records.

This used to work fine in Access 2003, but now in 2007, when I hit the button to show the active records, it shows no records, and when I hit the reset button it shows only one record. Funny thing is if I open the subform as a stand-alone form (and modify the VBA code to take out the reference to the master tabbed form frmactionitems), it works fine. There seems to be something about having it as a subform that is making it not work.

forms![frmactionitems]![frmtestlist].form.Recordset = "SELECT * FROM tblWOItems" does NOT work when form frmtestlist is a subform on the main form frmactionitems

forms![frmtestlist].Recordsource = "SELECT * from tblWOItems" DOES work when frmtestlist is a standalone form.

Any ideas? I played with the "Filter on empty master" property of the subform frame even though there are no master/child relationships but that didn't do anything.

Thanks,

 

[GKChesterton]

There's no Master-Child relationship; is there some other filter mechanism at work? Would the behaviors be explained by that? (There's one record that can be made to show ... is it always the same record? Can you set filtering from the main form that would reveal that record, and is this successful?)

Sometimes I've been stumped by persistent filtering that I can't locate. As a last resort, I run the database documentor with fairly exhaustive settings, then export it into .rtf format so I can run searches. But more often I am able to find the problem by poking around in all the properties.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[dhookom]

Why do you use .RecordSet in one instance and .RecordSource in the other. I would think you would always use .RecordSource.


Duane
Hook'D on Access
MS Access MVP

 

[pryordr]

I meant to type .Recordsource on both in my post, using the same property on each.

I have poked through the properties, thought I was on to something when I saw "Filter on empty master" set true, but changing it didn't make any difference.

The one record that shows up is always the first record in the table. I also had the thought that somehow it was coming out of continuous forms mode and going into single but the navigation buttons at the bottom show it as 1 of 1 record which would have been 1 of 65 if it really was in single.

I will look into the database documenter.

 

[GKChesterton]

I wonder what happens if you merely set the RecordSource to "tblWOItems" when you are showing all. Also wonder if the Requery method is needed. (I laugh though, because if you're like me in this situation you have Requery/Refresh/Repaint sprinkled generously about, with a DoEvents for good measure.)

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[pryordr]

I set the recordsource to "tblWOItems" and it then only shows one record, the first record again. I wasn't actually using a Requery anywhere, changing the recordsource always made if refresh the data before, but I tried throwing in a Requery to see what happens but it didn't help.

Experimenting a little, I found a work-around in using the filter property instead of changing the recordsource. I still don't know why changing the recordsource is not working, but I have something I can get working now.