I have inherited an Access 97 application. As table sizes have grown and users now access the application thru WAN, performance bottlenecks have appeared. After first splitting the database, I'm now trying to redesign a Search form which contains a subform that when first loaded currently retrieves all records in the table.
The subform's recordsource property is set to this query:
SELECT [lastname] & ", " & [firstname] AS Name, Students.ClassLevel, Students.Designator, Students.Semester, Students.Seabel, Students.Address1, Students.City, Students.State, Students.Zip, Students.ID
FROM Students
WHERE (((Students.Semester) Like [forms]![search]![semester] & "*") AND ((Students.City) Like [forms]![search]![text4] & "*") AND ((Students.State) Like [forms]![search]![text8] & "*") AND ((Students.Zip) Like [forms]![search]![text10] & "*") AND ((Students.ID) Like [forms]![search]![text6] & "*") AND ((Students.LastName) Like [forms]![search]![text0] & "*") AND ((Students.FirstName) Like [forms]![search]![text2] & "*"))
ORDER BY Students.LastName, Students.FirstName;
THe main form contains the above mentioned text boxes where the user can narrow down their search by populating the text boxes.
WHat might be the best way to initially load the form/subform so that no records are returned but then once the "Search" command button is pressed the above query criteria is then used.
DO i use the subform's Open event?
On Load event??
Or is it better to use code to dynamically change the record source of the subform when first opened then change it back once the "Search" command button is pressed.
Also I'd welcom any tips on better optimization of the above query.
Thanks so much..
The subform's recordsource property is set to this query:
SELECT [lastname] & ", " & [firstname] AS Name, Students.ClassLevel, Students.Designator, Students.Semester, Students.Seabel, Students.Address1, Students.City, Students.State, Students.Zip, Students.ID
FROM Students
WHERE (((Students.Semester) Like [forms]![search]![semester] & "*") AND ((Students.City) Like [forms]![search]![text4] & "*") AND ((Students.State) Like [forms]![search]![text8] & "*") AND ((Students.Zip) Like [forms]![search]![text10] & "*") AND ((Students.ID) Like [forms]![search]![text6] & "*") AND ((Students.LastName) Like [forms]![search]![text0] & "*") AND ((Students.FirstName) Like [forms]![search]![text2] & "*"))
ORDER BY Students.LastName, Students.FirstName;
THe main form contains the above mentioned text boxes where the user can narrow down their search by populating the text boxes.
WHat might be the best way to initially load the form/subform so that no records are returned but then once the "Search" command button is pressed the above query criteria is then used.
DO i use the subform's Open event?
On Load event??
Or is it better to use code to dynamically change the record source of the subform when first opened then change it back once the "Search" command button is pressed.
Also I'd welcom any tips on better optimization of the above query.
Thanks so much..
