My database is on a PC with a single user. The project that I am working on has only one database.
I have a combobox (cbo) on FormOne that is filled from a table that is created by a search. When I select an item from the cbo, it goes to the cbo_AfterUpdate() and asks if I want to add it to a diary.
If I click “yes”, FormOne opens FormTwo passing the information in the cbo selection using the OpenArgs property. FormTwo closes FormOne. After FormTwo has completed its tasks it re-opens FormOne and closes itself. This is NOT the problem.
This is the problem. If I click “no”, it exits the procedure. However, when I run the Search routine again, I get a 3211 error “Table in use ….” The only way I have found to get around this error is to close FormOne then open FormThree which has only one purpose. It asks if I want to re-open FormOne. This closing and reopening allows me to do another search from FormOne .
Isn’t there another way to get around this error??
BTW, please don’t tell me to create my data for the cbo using a Callback function. There is very little in the Help file but I have books for Access 97, Access 2000 and The Access Cookbook which basically have the same explanations with different examples. I worked for hours trying to understand that and got so confused that I watched Thursday night football instead.
I don't think a requery would help, but I might try it. I've been trying to figure this out for several days (amongst other problems) and I might have tried it. The table that is the source for the cbo is only used once then is deleted and re-created on the next search. Here is the code from the AfterUpdate event.
Private Sub cboSearch_AfterUpdate()
On Error GoTo Err_cboSearch_AfterUpdate
Dim rs As DAO.Recordset
Dim lngSrch_NutrID As Long
Dim strFindCri As String
' ** This proc is invoked when an item is selected from cboSearch
' ** It finds the item and displays it on the upper portion of the form.
' Move the NutrID of the selected record into a variable
lngSrch_NutrID = Me.cboSearch.Column(0)
' Create a string to hold the search criteria, e.g. "NutrID = 1234"
strFindCri = "NutrID = " & lngSrch_NutrID
' Set the recordset variable to a recordsetclone of the form
' ** This is necessary because the FindFirst method cannot be used with a form's recordset
Set rs = Me.RecordsetClone
' Make sure all records have been read into the recordset
' Find the record (The NutrID has no dupes).
' Move the ‘record pointer’ on the form to the bookmark of the recordsetclone
Me.Bookmark = rs.Bookmark
' Move the focus from cboSearch to txtInput then hide the cbo, its label and the rectangle around it.
Me.cboSearch.Visible = False
Me.cboSearch_Label.Visible = False
Me.Box62.Visible = False
' Clean up
Set rs = Nothing
' Ask if the record should be used in the diary
If MsgBox("Do you want to add this item to the diary?", vbYesNo) = vbYes Then
‘ Without this, the table is not released
MsgBox Err.Number & " " & Err.Description