I have a database (held in an Access .MDB file) that records staff members, and any absence they have e.g. holiday, sickness, training course, the start and end dates, and hours of productive time lost.
I then have a dbgrid bound to an "master" ADO query that finds all staff meeting the selected criteria of date range, department, search string for name, summing up the hours of productive time lost.
I have another dbgrid bound to a "detail" ADO table containing the absence records.
The desired effect is that the detail dbgrid should only contain those records from the Absence table that match the row selected in the master record (both "master" Staff and "detail" Absence tables contain a common EmployeeID field).
Though I can achieve this using ADO Queries created on the fly, changing the query each time the user moves to a different master staff record, I was hoping to use the detail DBGrid as my main method of deleting, updating, and adding additional absence records, complete with in grid lookups; so user can select record types without having to remember the code for that type.
I would also like the changes in this detail grid to be reflected in the summaries in the master dbgrid.
I have achieved this using a detail ADOTable linked as MasterDetail to the Staff Query, but need to have filtered set to True, and control the onfilterevent in code; but as the database increases in size this is getting slower and slower.
Is there anything I can do to improve this performance, or will I be forced to have the detail dbgrid as purely read-only, and all Absence records entered through another form or panel?
