Error 3211 Table in Use ...

Question

My database is on a PC with a single user. The project that I am working on has only one database.  I am using Access 2007.

I have a combobox (cbo) on FormOne that is filled from a table that is created by a search using a proc in FormOne. 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.

Answer 1

I don't understand your comment about the rs.Close statement.  I opened (if that's the right word) it in the statement:  ' Set rs = Me.RecordsetClone"

No, you didn't open it. It was already open, and you instantiated another instance of it. The only way to open a recordset is to use the OpenRecordset method. It's the same as you don't close a database when you've instantiated the object as Set db = CurrentDb, but you would close it is you used Set db = ws.OpenDatabase("C:\Folder\File.accdb")

I don't understand why you're deleting tblSrch and recreating it each time. Why not just delete the rows from the table and append to it? (I also don't see how your routine guarantees that that tblSrch has a row that satisfies "NutrID = " & lngSrch_NutrID, but I'll take your word for it!)

See whether it makes a difference instantiating a database object in procedure  cmdSearch_Click(). By this, I mean add declaration Dim db As DAO.Database, instantiate it as Set db = CurrentDb and replace all the other occurrences of CurrentDb in your code with db. Make sure you have Set db = Nothing at the end. (I don't think it will, but you never know!)

Answer 2

The problem of no records is taken care of in a procedure which builds tblSrch.

I don't understand your comment about the rs.Close statement.  I opened (if that's the right word) it in the statement:  ' Set rs = Me.RecordsetClone"

The procedure to build tblSrch follows.

Private Sub cmdSearch_Click()

On Error GoTo Err_cmdSearch_Click

Dim rs As DAO.Recordset

Dim stSQL As String

Dim varData As Variant

Dim varInput As Variant

Dim strWhere As String

Dim intSpaces As Integer

Dim intWords As Integer

Dim raWords() As String

Dim I As Integer

On Error GoTo Err_cmdSearch_Click

' Delete tblSrch.  If tblSrch does not exist, an error will be created

' and taken care of by the error handler

CurrentDb.TableDefs.Delete "tblSrch"

' Make sure tblSrch is available to the project

Application.RefreshDatabaseWindow

' Copy what's in the text box named txtInput to varInput

varInput = Nz(Me.txtInput, Space(0))

' Case of no input

If varInput = Space(0) Then

MsgBox "You must input something!", vbOKOnly

GoTo Exit_cmdSearch_Click

End If

' The TrimAll function will remove leading and trailing spaces from varInput

' then check the spaces between words and delete any extra spaces.

varInput = TrimAll(ByVal varInput)

' How many spaces are left?

intSpaces = CountIn(varInput)

' There will be one more word than spaces

'Redimension the array to the number of words.  Note:  zero-based

ReDim raWords(0 To intSpaces)

' Put the words in an array (one word per array element).

' First handle the condition of only one word

If intSpaces = 0 Then

raWords(0) = varInput

Else

raWords = Split(varInput, " ")

End If

' Build the Where clause for the SQL

' First clear the variable

strWhere = Space(0)

' Make an element in the Where clause for each word.

' Note:  chr(34) is a quote and chr(42) is an asterisk.

For I = 0 To intSpaces

strWhere = strWhere & "(tblNutrition.DESC) Like " & _

Chr(34) & Chr(42) & raWords(I) & _

Chr(42) & Chr(34) & " AND "

Next I

' Take the " And " off the end of strWhere

strWhere = Left(strWhere, Len(strWhere) - 5)

' Add the final parenthesis to strWhere

strWhere = strWhere & "))"

' Preface strWhere

strWhere = "WHERE ((" & strWhere

' Records will be selected if Description field contains all of

' the words in the food description segment input by the user.

stSQL = "SELECT tblNutrition.NutrID, tblNutrition.DESC, tblNutrition.SRVG " & _

"INTO tblSrch FROM tblNutrition " & _

strWhere & _

" ORDER BY tblNutrition.NutrID;"

' Create tblSrch from stSQL

CurrentDb.Execute stSQL

' Make sure tblSrch is available to the project

Application.RefreshDatabaseWindow

' Open a recordset of tblSrch

Set rs = CurrentDb.OpenRecordset("tblSrch")

' See if there are records in the recordset (tblSrch)

If rs.BOF And rs.EOF Then

MsgBox "No records found.", vbOKOnly

rs.Close

Set rs = Nothing

Me!txtInput = Space(0)

GoTo Exit_cmdSearch_Click

End If

' Records were found so make cboSrch visible

Me.Box62.Visible = True

Me.cboSearch_Label.Visible = True

Me.cboSearch.Visible = True

' Cleanup

rs.Close

Set rs = Nothing

Me.txtInput = Space(0)

Me.txtInput.SetFocus

Exit_cmdSearch_Click:

Exit Sub

Err_cmdSearch_Click:

If Err.Number = 3265 Or Err.Number = 94 Then

Resume Next

Else

MsgBox Err.Number & " " & Err.Description

Resume Exit_cmdSearch_Click

End If

End Sub

Answer 3

Hmm. I don't see anything in there that should be locking the table. I do, however, see a couple of things that perhaps should be changed. Are you certain that there will always be at least one record with a value of lngSrch_NutrID? If not, you should probably wrap the Me.Bookmark = rs.Bookmark statement with a NoMatch:

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

    rs.FindFirst strFindCri

  ' Check whether a record was found

    If rs.NoMatch = False Then

      Me.Bookmark = rs.Bookmark

    End If

Also, you shouldn't have the rs.Close statement. The general rule is do not close what you didn't open.

Answer 4

Here is my code.

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

Answer 5

What's the actual code in the AfterUpdate event of the combo box?