Results 1 to 5 of 5
  1. #1
    D'Anconia is offline Novice
    Windows Vista Access 2007
    Join Date
    Feb 2014
    Location
    Rancho Cucamonga, CA
    Posts
    13

    Error 3211 - table in use by another

    Hello, All,

    I’ve been tearing my hair out over this one. I have 2 forms, fForm1 and fForm2. At the OnCurrent event of fForm1, I create a little table called tCurrentId, which captures the current record’s Id number, which will be used to filter the records I want to appear in fForm2. Works fine going into fForm2, but when I click the “Back” button I created to return to fForm1, it’s giving me the dreaded Error 3211, “Table in use by other”. In fForm2’s click event for the Back button, I’ve tried all if these:

    DoCmd.Close acTable, "tCurrentID"
    Trying to unlock by simply closing the table – error occurs in fForm1’s OnCurrent event.
    DoCmd.Close acQuery, "qSelectCurrGrades"
    Trying to unlock by closing the record source for fForm2, which uses tCurrentId table – error occurs again in fForm1
    DoCmd.Close acForm, "fForm2"
    Trying to unlock by closing the form, which I thought would release the table – Again, error in fForm1
    Call CurrentDb.TableDefs.Delete("tCurrentId")
    Trying to unlock by deleting the table, which triggers the 3211 error on this line
    Me.RecordSource = ""
    Trying another way to unlock by closing the record source. This line takes me back to the Load event of the current form, which then blows up because there’s no record source(!)



    I’ve seen many threads on the web, but have not seen much that is useful. In one case it was suggested that a recordset was opened somewhere, but not closed. I’m certainly not doing that in my code, so, if that’s being done, it’s behind the scenes. I would appreciate it if someone could please tell me how to unlock this table. Please give me examples of syntax, being as specific or explicit as you can.

    Thank you so much!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Post the code that you are using in the Current Event.

    Post the code that is filtering the second form.

  3. #3
    D'Anconia is offline Novice
    Windows Vista Access 2007
    Join Date
    Feb 2014
    Location
    Rancho Cucamonga, CA
    Posts
    13
    Here’s the requested code. In my original post, I changed object names for simplicity. These are a little more complex, but I again tried to conform with my original post. I hope it is clear.

    The record source for fForm1 is qvSelectedSelections. It contains the Id field SelectionId, which is the control source for the text box [Forms]![fForm1].[txtSelId].

    fForm1 Current Event:
    Private Sub Form_Current()
    Call InitializeCurrentForm
    - enables/disables a couple of buttons depending on certain condition (not relevant)
    DoCmd.OpenQuery ("qGetCurrentId") - Puts the current Id number in the tCurrentId table. Query shown below.
    End Sub

    qGetCurrentId:
    SELECT qvSelectedSels4AudWk.SelectionId INTO tCurrSelId
    FROM qvSelectedSels4AudWk
    WHERE (((qvSelectedSels4AudWk.SelectionId)=[Forms]![fAuditWorkSelection].[txtSelId]));


    fForm1’s Click event for the button to open fForm2:
    Private Sub OpenFormfForm2()
    DoCmd.RunMacro ("mSelectCurrGrds4AudWk")
    - filters the record source for fForm2. Query shown below.
    DoCmd.RunMacro ("mOpenFrom2") - Opens fForm2
    End Sub

    qSelectCurrGrds4AudWk:
    SELECT DISTINCT AW.*
    FROM qvSelectedGrds4AudWk AS AW LEFT JOIN
    tCurrSelId AS CS ON AW.SelectionId = CS.SelectionId
    WHERE AW.SelectionId=CS.SelectionId
    ORDER BY AW.CriterionNum;


    fForm2’s Click event
    to return to fForm1 – obviously, not working – needs new code:
    Private Sub btnSel_Click()
    DoCmd.Close acQuery, "qSelectCurrGrds4AudWk"
    DoCmd.Close acForm, "fGrade"
    DoCmd.Close acTable, "tCurrSelID", acSaveYes
    Call CurrentDb.TableDefs.Delete("tCurrSelId")
    'Me.RecordSource = ""
    DoCmd.RunMacro ("mOpenForm1")
    End Sub


    I really don't understand why this should be so hard. Thanks for any help anyone can give!

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    For starters...

    I am going to guess that qGetCurrentId is an action query. You could bring the SQL directly into your form's VBA module or you can run the object by name.

    Instead of
    DoCmd.OpenQuery ("qGetCurrentId")

    use
    CurrentDB.Execute "qGetCurrentId"

    or
    CurrentDB.Execute "UPDATE tblName SET .... etc"


  5. #5
    D'Anconia is offline Novice
    Windows Vista Access 2007
    Join Date
    Feb 2014
    Location
    Rancho Cucamonga, CA
    Posts
    13
    Ok, I'm afraid this isn't working. I'm going to try a different route in a new post. Thanks for your time!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 20
    Last Post: 04-11-2014, 07:07 AM
  2. Could not lock Table error
    By Lorlai in forum Access
    Replies: 5
    Last Post: 04-02-2013, 12:43 PM
  3. Error 3211
    By Phred in forum Programming
    Replies: 1
    Last Post: 01-25-2012, 10:29 PM
  4. Error 3211 Object cannot be locked
    By TheShabz in forum Forms
    Replies: 9
    Last Post: 08-23-2011, 12:05 PM
  5. Replies: 0
    Last Post: 11-30-2010, 12:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums