I've added a list box to my form that displays a table. This table is completely independent and gains it's data from an Append query. There is also a Delete query, which clears the table for a new set of data, and an Alter query which resets an AutoNumber column in the table (so that new data sets always start numbered at 1).
Here's the problem: Since I've added the listbox and set the RowSource to the table, whenever I run the Append query I receive the error: "The database engine could not lock table 'tblLineSheet' because it is already in use by another person or process." I've made sure to close the table, but to no avail I still get this error. Without the List Box I don't encounter this error.
This is not normal. Using a table as the RowSource property of a listbox or a combobox does not creates a dynamic recordset (i.e. you cannot update the table from the combo or the list), so the table should not be locked (see attached example)
How do you define the RowSource property of the control?
The macro associaded with the button opens a DDL query (qryResetAutoNumb) then tries to open a DML query (qryDeleteData) while the first one is still open. More stangely, the list seems to lock the table, for some reason I don't understand. Instead of associating a macro to the Click event of the command button cmndDeleteTable, use a VBA procedure, like this:
Private Sub cmndDeleteTable_Click()
Me.listboxLineSheet.RowSource = ""
CurrentDb.Execute "qryResetAutoNumb", dbFailOnError
CurrentDb.Execute "qryDeleteData", dbFailOnError
Me.listboxLineSheet.RowSource = "SELECT tblLineSheet.* FROM tblLineSheet;"