Subform Form.RecordSource inconsistent?

[Steve Miles]

I've got a form with a combo box and begin/end dates. When any of the
three are changed I set a subform's recordsource so the records
returned are filtered based on the three fields. The syntax I'm using
is:

Me.{subform-name}.Form.RecordSource = "Select * from tbl..."

....including the three fields in the SQL Where clause. This method
works well, but mysteriously stops returning any rows once in a while.
I can hard-code the SQL to return all rows and sometimes it works,
sometimes it doesn't. I don't think I've got any SQL syntax errors
since it works most of the time. I've closed out of Access and tried
both A2k and A2k3, both of which failed to return any rows, then just
as mysteriously began working properly.

Any clues what might be going on?

[david epsom dot com dot au]

When you change the form recordsource, you should also reset
the master/child fields to "". I think current versions of
Access will helpfully set these fields under some conditions.

Another thing that might be happening is Filtering. Subform
filters will helpfully snap in and out of play when you least
expect it. Don't try to filter subforms: make sure all the filters
are blank.

(david)
"Steve Miles" <el***@hotmail.com> wrote in message
news:b8**************************@posting.google.c om...

I've got a form with a combo box and begin/end dates. When any of the
three are changed I set a subform's recordsource so the records
returned are filtered based on the three fields. The syntax I'm using
is:

Me.{subform-name}.Form.RecordSource = "Select * from tbl..."

...including the three fields in the SQL Where clause. This method
works well, but mysteriously stops returning any rows once in a while.
I can hard-code the SQL to return all rows and sometimes it works,
sometimes it doesn't. I don't think I've got any SQL syntax errors
since it works most of the time. I've closed out of Access and tried
both A2k and A2k3, both of which failed to return any rows, then just
as mysteriously began working properly.

Any clues what might be going on?