If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Access > Error 3211, Table in Use

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
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.
Reply With Quote
  #2 (permalink)  
Join Date: Mar 2009
Posts: 5,199
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!
Reply With Quote
  #3 (permalink)  
Registered User
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

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

Exit Sub
MsgBox Err.Number & " " & Err.Description
Resume Exit_cboSearch_AfterUpdate
End Sub
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On