Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41

    Error 3211, Table in Use

    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.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,287
    Without seeing the code, it's hard to provide an answer. Did you try to requery the combo after answering "No"?
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41
    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
    rs.MoveLast
    rs.MoveFirst

    ' Find the record (The NutrID has no dupes).
    rs.FindFirst strFindCri

    ' 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.txtInput.SetFocus
    Me.cboSearch.Visible = False
    Me.cboSearch_Label.Visible = False
    Me.Box62.Visible = False

    ' Clean up
    rs.Close
    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
    Call Open_frmConsumption
    Else
    ‘ Without this, the table is not released
    DoCmd.OpenForm "frmDummy"
    GoTo Exit_cboSearch_AfterUpdate
    End If

    Exit_cboSearch_AfterUpdate:
    Exit Sub
    Err_cboSearch_AfterUpdate:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_cboSearch_AfterUpdate
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •