Subform with ODBC connection very slow unless criteria already set when form loads

[mattscott]

Hi,

I have a form with a subform that queries a table via a Machine DSN through ODBC. This brings back multiple records based on a unique ID that is entered in a text box that is used as the criteria for the query. When I put in a ID, the records (between 5 and 100 usually) are brought back line by line, very slowly, and loaded down the list twice usually before it stops loading and I am able to do anything. If I scroll the subform at any point it reloads every record individually again too with each scrolled line.



This was frustrating but acceptable until I found out by accident that if I switch to design view and back to form view when an ID is already loaded, from that moment onwards everything loads entirely instantly, I can switch from ID to ID with no wait at all and scroll as normal. The best way I've found to make this occur for all users on startup is to set an ID as a default value in the text box, but I was wondering if anyone knew what might cause this problem, if there was a setting somewhere or a bit of code that could replicate this effect without having to load up an account as default?

Thanks

[mattscott]

SOLVED (after literally months of trying on and off!)
All I needed to do was change the RecordSet Type property to Snapshot instead of Dynaset...
Thanks anyhow.