I have a list box that I populate through a temporary table (t_tblTeacher). This is done from the following command in a module:
Public Sub CopyTeachers()
Dim strTableName As Variant
Dim strTemp As String
strTemp = "t_tblTeacher"
strTableName = "tblTeacher"
DoCmd.DeleteObject acTable, strTemp
DoCmd.CopyObject , strTemp, acTable, strTableName
I use this to create the temporary table prior to opening the form containing the list box. No problem. However, once the form is open, this command, if run, notes that the temporary table is locked, so cannot be deleted and thus ###error###. Understand the error (it makes sense), but I would still like to be able to reload the temporary table from the original using a simple "clear" button and a requery.
Any easy solution? Should I try to disconnect and reconnect the list box record source?
That or you can delete everything from the table then fill it again with an INSERT query. What I don't understand, though, is why you need a temporary table as data source for the list. Can't you simply use a SELECT query on the original table?
I am using temporary tables because in fact I have three list boxes. One list box contains teachers, and one courses. As a course is selected for a teacher, they are put in the third list box, and deleted from the teachers/course boxes. The ultimate in error checking: it is impossible to put two teachers with one course. The application is for choosing teachers for invigilation of exams. The list boxes function through the database and queries, not listbox commands (additem does not work in Access 2003).
I suppose I could to a delete, insert combination for courses and teachers. Thanks for the ideas.
There is another solution: go through a dummy form, close the original form, recopy the temporary tables (as objects), and then reopen the form and close the dummy. This can be done on the form open event (in the dummy form), and doesn't seem to be problematic (seems to work fine for me). Probably vastly faster than any SQL operation to delete and refill temporary tables. Anyone know of any potential pitfalls?
BTW, it would be nice when people come to the forum seeking solutions to problems if they inform the forum of any solutions they come upon. Its all about sharing...