Subform loading extremely slowly unless I switch to design view and back

[mattscott]

I currently have a subform which is based on a query where a unique account number (input on the outer form) is used to bring up a number of rows of data relating to that account number.

Normally this loads very slowly, row by row, sometimes multiple times over and if I scroll at all it makes it reload too. The strange thing is that this is all solved perfectly by going into design view then straight back into form view again, which makes everything load nearly instantly for the rest of the time I have the form open.

Does anyone know the difference between what is happening before and after I do that and why it does that? Ideally I'd like to have it so that it loads quickly from startup, but I can't for the life of me work out why a change like this makes a difference.

(Running Access 2002)

 

[bob fitz]

Normally this loads very slowly, row by row, sometimes multiple times over and if I scroll at all it makes it reload too

Perhaps you are requerying the subform in the code of an event in one of the forms.

Try this:
Remove the query from the Record Source property of the subform. Then use some code to set it in the subforms OnLoad event.
Something like:
Me.RecordSource = "NameOfQuery"

 

[mattscott]

Perhaps you are requerying the subform in the code of an event in one of the forms.

Try this:
Remove the query from the Record Source property of the subform. Then use some code to set it in the subforms OnLoad event.
Something like:
Me.RecordSource = "NameOfQuery"

Just tried this but it brings exactly the same result, thanks anyhow.

The 'Go' button that's meant to be pressed once you've entered the account number is set to requery the subform so we can check accounts one after the other. I don't know if requerying is is the best way to do it?

Either way, why could the same code (simply to requery the subform) give different results before and after being in design view?

TBH I don't have the greatest amount of access knowledge myself, and I'm working with something that I've been told was built by someone who was learning as they went along, so I don't know if it'll have been constructed in the optimal way in the first place.

 

[bob fitz]

Can you post a copy of the db with a few dummy records to illustrate the problem.

 

[mattscott]

Afraid not, this is for my job so I wouldn't be allowed to, but on top of that it's based on links to a large number of different tables across the company network so recreating in any sense would be very difficult.

I know that's not very helpful

 

[bob fitz]

Either way, why could the same code (simply to requery the subform) give different results before and after being in design view?

I'm clutching at straws, but can you show us the code.

 

[MarkK]

Another test, perhaps, is take the query text that runs the subform and copy it into a new query. Check the performance of that new query. Does it suck?

I'm not sure that putting a form into design view closes its data connections, so maybe doing that just gives the time required for the query to complete?

#GraspsAtStraw

 

[mattscott]

I'm requerying with:
Forms![outer_form].[subform1].Requery
Forms![outer_form].[subform2].Requery
Forms![outer_form].[subform3].Requery

Only subforms 1 and 2 go noticably slowly, but then there's only one record being pulled for subform3 as opposed to an average of about 30 for subform1 and 10 for subform2.

Just to clarify lagbolt, once I've switch the views, it will be lightning fast for as long as I have the form open, and I'm free to requery and check as many different accounts as I want.

I've tried running the queries themselves separately and they seem to be running as fast as I could hope. It looks like it's just them being displayed on the form that's slow. When it's being slow, even scrolling one line down will then make the whole subform load again line by line, but it's smooth as can be after doing the switch.