Hum, you are thus using sql server...right?
You seem to be short on details about the server side, and what you are
sending the pass through queries to?
Are you sure you form is based on a pass through query?
I would check is if you are using any sql via linked tables.
If you are using linked tables, then JET often gets in the way. I am talking
about ODBC linked tables to a server here, not tables linked to a back end.
Since, with a linked back end, ODBC, or even pass through queries are
impossible!! (odbc to a back end table is not possible). The problem is when
you have a query with more then one table, then JET has to do the join on
the local side, and does a very poor job. The solution is for any linked
tables that require a join is to create a view on the server side, and then
link to that view. When you do that, then access will perform very well. So,
if you have any tables that you join in sql, then try creating a view. You
can even get away using in-line sql, or the "where" clause to a form when
you use a view (and neither are pass through).
I would also try removing the sub-forms, and see if performance changes.
Again, while ms-access *can* do a ok job loading the sub-forms, you have to
be careful.
In addition, virtually all of my designs restrict the main form to one
record anyway (again, via ODBC to the sql server, a lot of records can be
transferred, and that is a bad design). So, when you open the form, you
should restrict the main for to the one record you need to work with, as it
does not make sense to load a form without some type of restriction on the
record that it loads. (why load a zillion invoices, when you only need to
work on one?). Hence, very few of my main edit forms have navigation buttons
on the bottom, as this just does not make sense. Ask the user for what
invoice, or whatever, and then load the form to that ONE record.
