SourceObject and RecordSource

[T Ray Humphrey]

This is related to another problem I posted about regarding setting
RecordSource programmatically, but it deserved a new thread.

I have a dynamic form hierarchy where a choice made in the Main form
determines which form to load in a subform object. At runtime, I need to set
two properties:
subform.SourceObject to load the form
subform.Form.RecordSource to filter the records.

When SourceObject is set, Access loads the form and generates a series of
events including Load and Current. Then when RecordSource is set, Access
generates another series of events including Current.

Is there a way to get only one Current event to run?

In the form, I have set the designtime RecordSource to "", but Access still
calls my Current event when I set subform.SourceObject. This generates an
error because I reference a field (Me!fldname) that is not found.

Must I check in Current to see if RecordSource is set before I process
anything?

Is there a better way to do this?

Thanks,
Ray

 

[Dale Fye]

Why do you need to set the RecordSource at runtime? I frequently do what you
are doing with regard to changing the SourceObject for the subform, but
rarely do I need to change the recordsource property of the subform.

Generally, the recordsource of the subform is:

1. a table, if I have Master/child relationships built between the form
and subform

or

2. a query that references one or more controls on the main form

Dale

 

[Klatuu]

I'm with Dale on this. I don't see a need, although there could be one you
haven't stated, to change the record source. But if it is necessary, can you
not use the form's Load event to set the record source? That way, by the
time it gets to the Current event, you already have the record source in
place.

 

[T Ray Humphrey]

This is an Access f/e and SQL Server b/e application that I'm optimizing. The
main reason I'm setting RecordSource at runtime is to optimize record access.
I've read in several places that setting RecordSource to only pull the record
needed at that time is a good performance optimization.

I also have a situation where a subform at one level needs to show some
records in a table and another subform needs to show different records in the
same table. These records are all linked by a common ID, but a second status
field is used to determine which subform displays the records. To be clearer,
one subform shows "Active" records, the other shows "History" records. They
all belong to the same parent ID, but I don't want them mixed on one subform.

Dave, I haven't tried what you suggest about setting RecordSource in the
Load event, but won't setting RecordSource itself call Current? So if I
change SourceObject in a sequence of events that will eventually fire
Current, and in another of those events (Load), I set RecordSource (which
calls Current), won't that still call Current twice? As I said, I haven't
tried, and I will, but I wonder if you know that the first one will not occur
(because we are in Load) or that the second one will not occur (Access will
be smarter in this instance).

Thanks for your idea. I'll post the results.
Ray

 

[Klatuu]

I don't think it would fire the Current event more than once, but in any
case, you are making this much harder than it needs to be.
It would be much better if you used one subform with a query as the record
source that filtered on field that designates Active or InActive records.
Then when the user changes the choice, requery the subform's recordset. I
really don't believe what you are trying to do would be any faster. I
actually believe, in reality, it would be slower.

 

[T Ray Humphrey]

Others on this forum have advised to load forms dynamically by changing
RecordSource. I guess the proof is in the pudding.

If you're right, you'll save me a lot of time. I'm in the middle of upsizing
and have run across a few kinks I've been working out. If I don't have to
change all my forms to dynamic loads, it will lighten my load a bit.

Thanks again for your insights,
Ray

 

[Klatuu]

There are times when that is appropriate. For example, I had a case where I
had to load 8 lst boxes each of which had row sources that were large tables.
When I first created the form, it took over 2 minutes to load. To fix
this, I loaded all the list boxes without row sources. For visual effect, I
set the height of the list boxes to 0. Then I put a command button over each
box. When the user selected the list to use, it then loaded the list box row
source and set the height so the use could see it. It made a huge
difference. But, with just one subform to load, it don't think it will be a
problem.

I currently am working on an app that uses SQL Server 2005 as the back end.
The main form has a tab control with 6 tabs. Three of the tabs have
subforms. The largest table has 154,2227 records. The next down has almot
66,000 records.
The smallest has about 40. The filtering is cascading. so that the first
subform on tab1 has the 40 records, the subform on tab 2 currently has 414
records and is filtered based on the current row on subform 1, so each time
you move to a different record, subform 2 requeries. Subform 3 on tab 3 is
the table wit 66,000 records and is filtered on the current row in subform 2.
The performance is pretty snappy. So, that should give you an idea of the
possiblities.

 

[T Ray Humphrey]

Wow! Are you doing that with static RecordSource and Link Master / Link Child
properties? I had read about so many problems with these that I was afraid I
was going to have to abandon them (haven't had any master/child problems in
the Access-only app, but was afraid I'd be facing lots of rewrites in this
upsize).

My b/e is also SQL 2005, but I don't have nearly that many records. I am
nested up to 4 subforms deep in a couple of places, but that usually filters
down to 10 to 15 records, with a max of probably 150.

Very helpful you are.
Ray

There are times when that is appropriate. For example, I had a case where I
had to load 8 lst boxes each of which had row sources that were large tables.
When I first created the form, it took over 2 minutes to load. To fix
this, I loaded all the list boxes without row sources. For visual effect, I
set the height of the list boxes to 0. Then I put a command button over each
box. When the user selected the list to use, it then loaded the list box row
source and set the height so the use could see it. It made a huge
difference. But, with just one subform to load, it don't think it will be a
problem.

I currently am working on an app that uses SQL Server 2005 as the back end.
The main form has a tab control with 6 tabs. Three of the tabs have
subforms. The largest table has 154,2227 records. The next down has almot
66,000 records.
The smallest has about 40. The filtering is cascading. so that the first
subform on tab1 has the 40 records, the subform on tab 2 currently has 414
records and is filtered based on the current row on subform 1, so each time
you move to a different record, subform 2 requeries. Subform 3 on tab 3 is
the table wit 66,000 records and is filtered on the current row in subform 2.
The performance is pretty snappy. So, that should give you an idea of the
possiblities.

 

[Klatuu]

You shouldn't have any problems.
One thing I am in the process of doing now is converting all my Access
queries into SQL Views. I have read, but have not verified, that if you use
linked views rather than Access queries you get better performance.

All my subforms use Link Master/Child fields.

The app is a project tracking and cost system. We have Initiatives which
have projects which have tasks. Then tasks have costs associated with them.
The way it is laid out is that the first tab shows Initiatives. When you
move to the second tab, it shows the projects for the selected initiative.
The third tab shows the tasks for the project. On tab 1, if you double click
an initiative, it brings up a detail form for the Initiative. It has a
subform that shows the projects. You can click on a project and it opens a
project detail form with a subform showing the tasks. If you click on a
task, it shows the task with a subform that shows the resource usage records
for the task (this is the big table).

All this works and performs nicely.