form speed up

[ken]

Hi,
I'm trying to speed up the loading of my forms. One problem that I
have(probably) is that I have many subforms in the form that I'm trying
to speed up. I can't change that now, but I will redesign for a future
upgrade.
I heard having proper indexes may also speed up form loading. So I was
wondering, if 2 tables have a 1 to many relationship, should both
fields be indexed? I read somewhere that if a field has many records
with the same value an index won't speed things up...? So the primary
key of one table is obiously indexed, should the other field which
completes the relationship be indexed as well?

thanks

[Douglas J. Steele]

What do you mean by "the other field which completes the relationship"? Are
you talking about the foreign key in the child table?

Assuming you created actual relationships in Access, the necessary indexes
should have been created for you.

One "trick" is not to load the subforms when you load the form, and load
them afterwards. This can take the form of not having a recordsource defined
for the subforms and setting it programmatically afterwards, or not having
the ObjectSource defined for each of the subform placeholders on the form,
and setting them programmatically.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"ken" <ge****@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hi,
I'm trying to speed up the loading of my forms. One problem that I
have(probably) is that I have many subforms in the form that I'm trying
to speed up. I can't change that now, but I will redesign for a future
upgrade.
I heard having proper indexes may also speed up form loading. So I was
wondering, if 2 tables have a 1 to many relationship, should both
fields be indexed? I read somewhere that if a field has many records
with the same value an index won't speed things up...? So the primary
key of one table is obiously indexed, should the other field which
completes the relationship be indexed as well?

thanks

[ken]

Hi...you are correct about "the necessary indexes should have been
created for you. "
One thing that you mentioned about not loading the subforms was
interesting...I was thinking of locking and disabling them...but I
guess that won't stop them from loading...

So to stop them from loading one can on the main form onOpen event set
their source objects?

Also I found out what causes such a delay on my forms...its not really
the subforms, its a list box. This list box is not a very important
part of my forms so I was thinking of making it a pop-up...
this way it won't be loaded everytime one opens the input form...just
when the user needs to use the list

what do you think?

[Douglas J. Steele]

Yes, you can set the source object in the form's Open event.

Having a pop-up for your listbox isn't a bad idea, except if they have to
invoke it multiple times, you'll have the delay each time. You could put
code in the listbox's GotFocus event to check whether or not its RowSource
is set. If it isn't, set it, and you'll only incur the overhead once, when
they actually need to use the listbox.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"ken" <ge****@gmail.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

Hi...you are correct about "the necessary indexes should have been
created for you. "
One thing that you mentioned about not loading the subforms was
interesting...I was thinking of locking and disabling them...but I
guess that won't stop them from loading...

So to stop them from loading one can on the main form onOpen event set
their source objects?

Also I found out what causes such a delay on my forms...its not really
the subforms, its a list box. This list box is not a very important
part of my forms so I was thinking of making it a pop-up...
this way it won't be loaded everytime one opens the input form...just
when the user needs to use the list

what do you think?

[Larry Linson]

Probably the most useful speed-up I found for forms is to index all join
fields, and limit the number of records with a WHERE clause in the Record
Source... yep, you may need to set the RecordSource after the user chooses
the records they need to see. It is amazing how often that is only one if it
exists, or none if it does not.

This is even more effective in a client-server relationship, but is helpful
in multiuser. It probably makes little or no diference in a monolithic
database where the objects and the data have not been split.

Larry Linson
Microsoft Access MVP

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:I6********************@rogers.com...

Yes, you can set the source object in the form's Open event.

Having a pop-up for your listbox isn't a bad idea, except if they have to
invoke it multiple times, you'll have the delay each time. You could put
code in the listbox's GotFocus event to check whether or not its RowSource
is set. If it isn't, set it, and you'll only incur the overhead once, when
they actually need to use the listbox.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"ken" <ge****@gmail.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

Hi...you are correct about "the necessary indexes should have been
created for you. "
One thing that you mentioned about not loading the subforms was
interesting...I was thinking of locking and disabling them...but I
guess that won't stop them from loading...

So to stop them from loading one can on the main form onOpen event set
their source objects?

Also I found out what causes such a delay on my forms...its not really
the subforms, its a list box. This list box is not a very important
part of my forms so I was thinking of making it a pop-up...
this way it won't be loaded everytime one opens the input form...just
when the user needs to use the list

what do you think?

[ken]

Thanks Larry

[ken]

Thanks Larry and Doug

[Salad]

Douglas J. Steele wrote:

What do you mean by "the other field which completes the relationship"? Are
you talking about the foreign key in the child table?

Assuming you created actual relationships in Access, the necessary indexes
should have been created for you.

One "trick" is not to load the subforms when you load the form, and load
them afterwards. This can take the form of not having a recordsource defined
for the subforms and setting it programmatically afterwards,

Could you explain that please? If the recordsource is blank in the
subform, and all the fields hare a control source of something, won't
Access burp on that? Are you saying to set both recordsource and
control sources when the main form has loaded?

[Douglas J. Steele]

"Salad" <oi*@vinegar.com> wrote in message
news:QJ****************@newsread3.news.pas.earthli nk.net...

Douglas J. Steele wrote:

What do you mean by "the other field which completes the relationship"?
Are you talking about the foreign key in the child table?

Assuming you created actual relationships in Access, the necessary
indexes should have been created for you.

One "trick" is not to load the subforms when you load the form, and load
them afterwards. This can take the form of not having a recordsource
defined for the subforms and setting it programmatically afterwards,

Could you explain that please? If the recordsource is blank in the
subform, and all the fields hare a control source of something, won't
Access burp on that? Are you saying to set both recordsource and control
sources when the main form has loaded?

Yeah, you're right. Not having forms associated with the subform
"placeholders" is the appropriate method.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)