setting RecordSource vs. setting RecordSource and Filter

[T Ray Humphrey]

I have a subform on an Access XP form that is loaded dynamically based on a
selection in a TreeView. One level of node in the TreeView loads an Account
into the subform, the other loads a Contract (these are different forms
showing data from different tables).

I am currently setting the RecordSource property when I need to switch
levels. I then typically set the Filter property of the form in the subform
so that I can hide unwanted record in the underlying table.

My problem is that I am getting two flurries of events, the first when I set
the RecordSource, and the next when I set the Filter. As these forms
themselves have a few levels of subforms, there is way too much going on to
be firing these events twice.

As I was searching for answers to this question in this forum, it occurred
to me that I might better use something like "SELECT * FROM Contracts WHERE
...." for my RecordSource and forgo setting the Filter. Is this a recommended
practice? It seems as if it would prevent the second set of events firing.
Are there any disadvantages to this? Is there a better way?

Thanks in advance,
ray humphrey

 

[T Ray Humphrey]

I realized my mistake as soon as I tried this. I meant to say SourceObject
below instead of RecordSource. SourceObject is a property of the subform and
is set to a form name, not a table or query. I was confusing it with
RecordSource, the property of a form, which is a table or query.

But my basic question still stands. When I set SourceObject to a form name,
it loads the form without any filter, firing a series of events. If I then
set the RecordSource or Filter property of the form within the subform, it
fires off another series of events.

Can I prevent this? It would be nice if I could set the filter on the form
object before I hook it to the subform's SourceObject, but I can't find a way
to make that work.

Thanks again,
ray humphrey

 

[Marshall Barton]

Because there are several problems with the Filter property,
I always recommend using only the RecordSource property.

To avoid the events from being triggered twice, try removing
the record source from the form's design after you are done
making design changes. This may require you to use the
syntax Me!fieldname instead of Me.fieldname in your code
(note that fieldname is the name of any field in the record
source table/query, the syntax for referring to control
names isn't affected).

Then, after setting the SourceObject property, you should
get minimal event activity. So the events will be deferred
until you set the RecordSource property.

 

[david epsom dot com dot au]

Also, step through some of those 'flurries' if you are still
getting multiple events after removing the filters. Careful
coding can make a big difference: It's easy to get multiple
requeries, but it is also easy to get rid of them.

(david)

 

[T Ray Humphrey]

So what you are saying is to unbind the form from any RecordSource? Then when
Access loads the form, it won't bind to any data. The form will only bind
when I set the RecordSource. Hmmm. Sounds like a great idea.

My top-level form has a subform, and a sub-subform, etc. Could I leave my
subforms bound and then just hook up the RecordSource of my top-level form
when I know it? In other words, what will Access do with my Link Master and
Link Child fields if the master form is not bound? Will the children stay
unbound until the master has a value? That would be great because then I
could leave most forms statically bound and only dynamically bind those that
truly need it.

Thanks for your help,
Ray

Because there are several problems with the Filter property,
I always recommend using only the RecordSource property.

To avoid the events from being triggered twice, try removing
the record source from the form's design after you are done
making design changes. This may require you to use the
syntax Me!fieldname instead of Me.fieldname in your code
(note that fieldname is the name of any field in the record
source table/query, the syntax for referring to control
names isn't affected).

Then, after setting the SourceObject property, you should
get minimal event activity. So the events will be deferred
until you set the RecordSource property.
--
Marsh
MVP [MS Access]

I realized my mistake as soon as I tried this. I meant to say SourceObject
below instead of RecordSource. SourceObject is a property of the subform and
is set to a form name, not a table or query. I was confusing it with
RecordSource, the property of a form, which is a table or query.

But my basic question still stands. When I set SourceObject to a form name,
it loads the form without any filter, firing a series of events. If I then
set the RecordSource or Filter property of the form within the subform, it
fires off another series of events.

Can I prevent this? It would be nice if I could set the filter on the form
object before I hook it to the subform's SourceObject, but I can't find a way
to make that work.

 

[Marshall Barton]

Hmmm, I don't remember ever trying that, but I would expect
the subforms to display nothing since the link master
property will link to a nonexistent value. Give it a try as
see what happens.

 

[david epsom dot com dot au]

In A97, when I designed my forms (I know, I'm sorry), the subforms
loaded before the main form.

So all the subforms loaded, and queried, and returned no records,
before the main form.

Then the main form loaded. And the subforms re-queried, and returned
no records.

Then the main form moved to the correct record. And the subforms
re-queried, and displayed the correct data.

Unless this has been changed, it is a good idea to leave your
subform controls empty until required, and your subforms unbound
until required.

Changing your subforms and your subform recordsources, and your
form recordsource, gives you another point where your master/child
fields can and do fail.

I don't use master/child fields, or filters. I rewrite the record
source for the subforms.

This still causes problems with recordsetclones. Reset the record
source twice, and avoid using recordsetclones unless required.

Because of the extra work involved, we still always start with
bound subforms and master/child fields. I come along later if
some one actually uses the form and complains about the speed.

(david)

So what you are saying is to unbind the form from any RecordSource? Then
when
Access loads the form, it won't bind to any data. The form will only bind
when I set the RecordSource. Hmmm. Sounds like a great idea.

My top-level form has a subform, and a sub-subform, etc. Could I leave my
subforms bound and then just hook up the RecordSource of my top-level form
when I know it? In other words, what will Access do with my Link Master
and
Link Child fields if the master form is not bound? Will the children stay
unbound until the master has a value? That would be great because then I
could leave most forms statically bound and only dynamically bind those
that
truly need it.

Thanks for your help,
Ray

Because there are several problems with the Filter property,
I always recommend using only the RecordSource property.

To avoid the events from being triggered twice, try removing
the record source from the form's design after you are done
making design changes. This may require you to use the
syntax Me!fieldname instead of Me.fieldname in your code
(note that fieldname is the name of any field in the record
source table/query, the syntax for referring to control
names isn't affected).

Then, after setting the SourceObject property, you should
get minimal event activity. So the events will be deferred
until you set the RecordSource property.
--
Marsh
MVP [MS Access]

I realized my mistake as soon as I tried this. I meant to say
SourceObject
below instead of RecordSource. SourceObject is a property of the subform
and
is set to a form name, not a table or query. I was confusing it with
RecordSource, the property of a form, which is a table or query.

But my basic question still stands. When I set SourceObject to a form
name,
it loads the form without any filter, firing a series of events. If I
then
set the RecordSource or Filter property of the form within the subform,
it
fires off another series of events.

Can I prevent this? It would be nice if I could set the filter on the
form
object before I hook it to the subform's SourceObject, but I can't find
a way
to make that work.


:
I have a subform on an Access XP form that is loaded dynamically based
on a
selection in a TreeView. One level of node in the TreeView loads an
Account
into the subform, the other loads a Contract (these are different
forms
showing data from different tables).

I am currently setting the RecordSource property when I need to switch
levels. I then typically set the Filter property of the form in the
subform
so that I can hide unwanted record in the underlying table.

My problem is that I am getting two flurries of events, the first when
I set
the RecordSource, and the next when I set the Filter. As these forms
themselves have a few levels of subforms, there is way too much going
on to
be firing these events twice.

As I was searching for answers to this question in this forum, it
occurred
to me that I might better use something like "SELECT * FROM Contracts
WHERE
..." for my RecordSource and forgo setting the Filter. Is this a
recommended
practice? It seems as if it would prevent the second set of events
firing.
Are there any disadvantages to this? Is there a better way?

 

[T Ray Humphrey]

I'm pretty sure Access XP still loads subforms first. So I may have to hook
them all up manually. I'll have to test this out and see.

I think I will give up on filters, though. You're not the only one (by far)
who has mentioned having trouble with them.

I'm struggling with letting Access do all the work and being thoroughly
annoyed at all the calls to my event procedures. Performance is not an issue.
Yet. And there you have it. So when do you bite the bullet?

BTW, am I the only person that thinks this event model is whacked? I've
gotten Access to jump through many hoops over the years, and am generally
happy with the ease with which a small database application can be put
together. But I can't believe they don't have some delay mechanism, or some
way to filter the records on load. What a waste of CPU cycles.

Thank you, Marshall and David, for your insights.

In A97, when I designed my forms (I know, I'm sorry), the subforms
loaded before the main form.

So all the subforms loaded, and queried, and returned no records,
before the main form.

Then the main form loaded. And the subforms re-queried, and returned
no records.

Then the main form moved to the correct record. And the subforms
re-queried, and displayed the correct data.

Unless this has been changed, it is a good idea to leave your
subform controls empty until required, and your subforms unbound
until required.

Changing your subforms and your subform recordsources, and your
form recordsource, gives you another point where your master/child
fields can and do fail.

I don't use master/child fields, or filters. I rewrite the record
source for the subforms.

This still causes problems with recordsetclones. Reset the record
source twice, and avoid using recordsetclones unless required.

Because of the extra work involved, we still always start with
bound subforms and master/child fields. I come along later if
some one actually uses the form and complains about the speed.

(david)

So what you are saying is to unbind the form from any RecordSource? Then
when
Access loads the form, it won't bind to any data. The form will only bind
when I set the RecordSource. Hmmm. Sounds like a great idea.

My top-level form has a subform, and a sub-subform, etc. Could I leave my
subforms bound and then just hook up the RecordSource of my top-level form
when I know it? In other words, what will Access do with my Link Master
and
Link Child fields if the master form is not bound? Will the children stay
unbound until the master has a value? That would be great because then I
could leave most forms statically bound and only dynamically bind those
that
truly need it.

Thanks for your help,
Ray

Because there are several problems with the Filter property,
I always recommend using only the RecordSource property.

To avoid the events from being triggered twice, try removing
the record source from the form's design after you are done
making design changes. This may require you to use the
syntax Me!fieldname instead of Me.fieldname in your code
(note that fieldname is the name of any field in the record
source table/query, the syntax for referring to control
names isn't affected).

Then, after setting the SourceObject property, you should
get minimal event activity. So the events will be deferred
until you set the RecordSource property.
--
Marsh
MVP [MS Access]


T Ray Humphrey wrote:
I realized my mistake as soon as I tried this. I meant to say
SourceObject
below instead of RecordSource. SourceObject is a property of the subform
and
is set to a form name, not a table or query. I was confusing it with
RecordSource, the property of a form, which is a table or query.

But my basic question still stands. When I set SourceObject to a form
name,
it loads the form without any filter, firing a series of events. If I
then
set the RecordSource or Filter property of the form within the subform,
it
fires off another series of events.

Can I prevent this? It would be nice if I could set the filter on the
form
object before I hook it to the subform's SourceObject, but I can't find
a way
to make that work.


:
I have a subform on an Access XP form that is loaded dynamically based
on a
selection in a TreeView. One level of node in the TreeView loads an
Account
into the subform, the other loads a Contract (these are different
forms
showing data from different tables).

I am currently setting the RecordSource property when I need to switch
levels. I then typically set the Filter property of the form in the
subform
so that I can hide unwanted record in the underlying table.

My problem is that I am getting two flurries of events, the first when
I set
the RecordSource, and the next when I set the Filter. As these forms
themselves have a few levels of subforms, there is way too much going
on to
be firing these events twice.

As I was searching for answers to this question in this forum, it
occurred
to me that I might better use something like "SELECT * FROM Contracts
WHERE
..." for my RecordSource and forgo setting the Filter. Is this a
recommended
practice? It seems as if it would prevent the second set of events
firing.
Are there any disadvantages to this? Is there a better way?