ms-access loading data question

i have 开发者_如何学运维a pretty complicated form. as you can see below:

alt text http://img9.imageshack.us/img9/2465/test2xk.jpg

however, i have only maybe 8mb of data total in the database.

the way it works is it populates the listbox all the way on the left with all the record names. when you scroll through the records it loads the appropriate data for each record

when i scroll through the listbox all the way on the left, for some reason it takes about 2 seconds to load each record into all the appropriate fields. there are no pictures loading or anything huge. the database resides locally. it's just loading maybe at most 100kb total for each record

can you tell me whether it's supposed to take this long to load?

could it be a problem with the query?

this is what is running each time i scroll through the items in the listbox

SELECT u.id,u.title,u.title,u.first,u.last FROM 
  (((tblusers u LEFT JOIN tbluserstudentteacher 
     ON u.id = tbluserstudentteacher.student_teacher_user_id) 
     LEFT JOIN tblUsersSubjects ON u.id = tblUsersSubjects.user_id) 
     LEFT JOIN tblUserAvailability ON u.id=tblUserAvailability.user_id) 
     LEFT JOIN chavrusas ON u.id=chavrusas.luser_id 
       WHERE 1=1 AND (u.gender) LIKE 'm*' 
       AND (chavrusas.luser_type)='shliach' 
       AND (chavrusas.ruser_type)='shliach' AND (u.last LIKE 'd*') 
 GROUP BY u.id, u.title, u.title, u.first, u.last 
 ORDER BY last;

I notice that you have a number of subforms, you may get better performance if you do not load all the subforms until they are needed: Does it degrade performance to use subforms in MS Access?

That SQL is bad because do you really want to group by those many fields? It seems like that SQL statement was thrown together and looked like it worked so you choose to keep it that way.

Do you use dlookups anywhere or any other calculated controls = even simpler ones tend to slow the loading/refreshing of a form...

After reformatting the SQL, it looks like you're doing some joins that aren't necessary, those being the join on tbluserstudentteacher, tblUsersSubjects and tblUserAvailability. You can simplify (and I'd strongly suspect improve the performance) considerably. Something like the following should work:

SELECT u.id, u.title, u.first, u.last
  FROM (tblusers u LEFT JOIN chavrusas c ON u.id = c.luser_id
  AND u.gender LIKE 'm*'
  AND u.last LIKE 'd*'
  AND c.luser_type = 'shliach'
  AND c.ruser_type = c.luser_type)
ORDER BY last;

The GROUP BY isn't appropriate or necessary either, as you have no aggregate functions that would result in grouping.

I'd suspect you didn't post your real SQL here, because of the unneeded GROUP BY and JOINed tables.