The database engine could not lock table 'xxxxx' because it is alreadyin use by another person or process

I have a button on a form that tries to open a report.  This is the error message I get.  They are both using the same Table.

How can I open my report?

thanks
galneweinhawAsked:
Who is Participating?
 
dannywarehamConnect With a Mentor Commented:
You tend to get this error if you are linking a query or table to a form, and then using a control to append or delete data from the table.

I've also seen it happen with Make-Table queries too.

From what I gather, its caused by the database trying to save data to the table, which is in a read-only due to the form pulling the information from it.

In this instance, you cannot write to the table until the query has run, but you can't run the query whilst the table is being editted - you end in a catch22 situation.

What is the code/SQL that you're using to update your table?
If you can post that, it may be something as simple as setting ALLOW ADDITIONS/EDITs etc status again.
0
 
Krys_WilsonCommented:
Have you tried basing the report and the form from a query?

Chris
0
 
galneweinhawAuthor Commented:
The Report is based on a query, the form is based on the tables as it edits data....
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Krys_WilsonCommented:
A form can still be based on a query and be updateable as long as its based on either 1 table or tables that are hooked together and one of the tables is a subform.

Still strange that you would be getting this error.  I just tested this in a testing database and I can have a form based on the table and the report based on the table and it still works correctly.

Try creating a new basic report based on the table and see if that works.  Use one of the wizards to create another one.

Chris
0
 
stevbeCommented:
I would commit any changes before opening the report ...

Private Sub cmdPreview_Click()
    'save any uncommitted changes
    If Me.Dirty = True Then Me.Dirty = False

    'open report in preview mode
    DoCmd.OpenReport ReportName:="rptMyReport", View:=acPreview

End Sub

Steve
0
 
galneweinhawAuthor Commented:
Thanks for the help, here is all the code for the form including the code that opens the report.....

I tried not allowing additions/deletions etc. and tried the Me.Dirty stuff but still get the error.

----------------------------------------------------------------------------------------
Option Compare Database

Private Sub btnCancel_Click()
On Error GoTo Err_btnCancel_Click

    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 'Undo new record
    DoCmd.Close

Exit_btnCancel_Click:
    Exit Sub

Err_btnCancel_Click:
    If Err.Number = 2046 Then  'Error 2046 nothing to undo... don't care.
        Resume Next
    End If
    MsgBox Err.Description & " " & Err.Number
    Resume Exit_btnCancel_Click

End Sub


Private Sub Form_Current()

    'If this is a new record insert PlateSeriesID
    If (IsNull(Me.PlateSeriesID)) Then
            Me!PlateSeriesID = DMax("PlateSeriesID", "SpinCoatingSeries") + 1
            Me![Spin Date].SetFocus
    End If

End Sub


Private Sub btn_Save_Click()
On Error GoTo Err_btn_Save_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.Close

Exit_btn_Save_Click:
    Exit Sub
   
Err_btn_Save_Click:
    Select Case Err
    Case 3314       'Missing required info field
        Call RequiredFieldMissing(Err.Description, True)
        Resume Exit_btn_Save_Click
       
    Case Else
        MsgBox Err.Description
        Resume Exit_btn_Save_Click
        Resume
    End Select
   
End Sub


Private Sub Number_of_Plates_AfterUpdate()
On Error GoTo Err_Plates_AfterUpdate

    Dim I As Integer 'counter dummy
    Dim ThisSeries As Integer
    ThisSeries = Me!PlateSeriesID.Value
    Dim NewNumber As Integer
    NewNumber = Me![Number Of Plates].Value
    Dim sqlSeriesTable As String
    sqlSeriesTable = "PlateDetails WHERE PlateSeriesID = " & ThisSeries

    'Open the Spin Plate Notes table (filtered for PlateSeriesID) for editing
    Dim Rs As New ADODB.Recordset
    'Set Rs = CurrentDb.OpenRecordset(sqlSeriesTable, dbOpenDynaset)
    Rs.Open sqlSeriesTable, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    Dim NumberOfRecords As Integer
    NumberOfRecords = 0
   
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
   
    If (Rs.EOF = True) Then   'There is no plate data yet (no records)
        Rs.AddNew             'Then add one?
        Rs("PlateSeriesID") = ThisSeries
        Rs("PlateID") = 1
        Rs.Update
    End If
   
    Rs.MoveFirst
    Do While (Rs.EOF = False) 'Count how many records there are
        NumberOfRecords = NumberOfRecords + 1
        Rs.MoveNext
    Loop
   
    If (NewNumber < NumberOfRecords) Then
        Rs.MoveLast
        For I = (NumberOfRecords - NewNumber) To 1 Step -1
            Rs.Delete
            Rs.Update
            Rs.MovePrevious
        Next I
    End If

    If (NewNumber > NumberOfRecords) Then
        For I = (NewNumber - NumberOfRecords) To 1 Step -1
            Rs.AddNew
            Rs("PlateSeriesID") = ThisSeries
            Rs("PlateID") = DMax("[PlateID]", "PlateDetails", "[PlateSeriesID]=" & ThisSeries) + 1
            Rs.Update
        Next I
    End If
   
    Me![Plate Spin Notes].Requery
   
Exit_Plates_AfterUpdate:
    Rs.Close
    Set Rs = Nothing
    Exit Sub

Err_Plates_AfterUpdate:
    Select Case Err
    Case 3314       'Missing required info field
        MsgBox "The required Spin Coating Series data must be entered before plates can be added."
        Me![Number Of Plates] = Empty
        Call RequiredFieldMissing(Err.Description, True)
        Resume Exit_Plates_AfterUpdate
    Case Else
        MsgBox Err.Description & " " & Err.Number
        Resume Exit_Plates_AfterUpdate
    End Select
   
End Sub


Private Sub Number_of_Plates_BeforeUpdate(Cancel As Integer)

    If (Me![Number Of Plates] < Me![Number Of Plates].OldValue) Then
        Dim Response As Integer
        Response = MsgBox("There is currently data for " & Me![Number Of Plates].OldValue & " plates. Do you want the extra plates deleted?", vbYesNo + vbExclamation)
        If (Response = vbNo) Then
            Cancel = True
            Me!PlateSeriesID.Undo
        End If
    End If
   
    If (Me![Number Of Plates] = Me![Number Of Plates].OldValue) Then
        Cancel = True
        Me!PlateSeriesID.Undo
    End If
       
End Sub


Private Sub PlateSeriesID_BeforeUpdate(Cancel As Integer)
   
    If (Not IsNull(DLookup("[PlateSeriesID]", "SpinCoatingSeries", "[PlateSeriesID] =" & Me!PlateSeriesID))) Then
        MsgBox "Plate Series " & Me!PlateSeriesID & " already exists.", vbOKOnly + vbExclamation
        Cancel = True
        Me!PlateSeriesID.Undo
    End If

End Sub
Private Sub BakeDetails_Click()
On Error GoTo Err_BakeDetails_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stArgs As String
   
    stArgs = Me![PlateSeriesID] & "|" & Me![Plate Spin Notes].Form![PlateID]
    stDocName = "Plate Bake Details"
   
    stLinkCriteria = "[PlateSeriesID]=" & Me![PlateSeriesID] & " AND [PlateID]=" & Me![Plate Spin Notes].Form![PlateID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , stArgs

Exit_BakeDetails_Click:
    Exit Sub

Err_BakeDetails_Click:
    MsgBox Err.Description
    Resume Exit_BakeDetails_Click
   
End Sub
Private Sub AddNew_Click()
On Error GoTo Err_AddNew_Click
    DoCmd.GoToRecord , , acNewRec

Exit_AddNew_Click:
    Exit Sub

Err_AddNew_Click:
    MsgBox Err.Description
    Resume Exit_AddNew_Click
   
End Sub


Private Sub Report_Click()
On Error GoTo Err_Report_Click

    Dim stDocName As String
    stDocName = "Spin Coating Data Form"
   
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
   
    MsgBox "almost there"
    DoCmd.OpenReport stDocName, acPreview   '<------ DANGER! DANGER!  :)

Exit_Report_Click:
    Exit Sub

Err_Report_Click:
    MsgBox Err.Description
    Resume Exit_Report_Click
   
End Sub
0
 
galneweinhawAuthor Commented:
I tried to close the form and open the report in a seperate sub.... didn't work, same problem:

Sub PrintSpinCoatingReport(SpinSeriesID As Integer)

    Dim stDocName As String
   
    stDocName = "Spin Series And Plate Details"
    DoCmd.Close acForm, stDocName, acSaveYes
       
    stDocName = "Spin Coating Data Form"
       
    MsgBox "almost there"
    DoCmd.OpenReport stDocName, acViewPreview, "PlateSeriesID = SpinSeriesID"   ' <------- Error happens here
               
End Sub
0
 
dannywarehamCommented:
Where are the values "PlateSeriesID" and "SpinSeriesID" coming from?

The syntax on your OpenReport statement looks incorrect.
0
 
galneweinhawAuthor Commented:
The code for the button to open the report:

Private Sub Report_Click()
On Error GoTo Err_Report_Click

    Call PrintSpinCoatingReport(Me!PlateSeriesID)   ' send ID of current record being viewed

Exit_Report_Click:
    Exit Sub

PlateSeriesID is an integer value.  It's also a field in the report...... but that's all kind of irrelevent cus the error and problem of opening the report doesn't care about the WHERE condition.
0
 
dannywarehamCommented:
In your code:

DoCmd.OpenReport stDocName, acViewPreview, "PlateSeriesID = SpinSeriesID"

is the   "PlateSeriesID = SpinSeriesID"    meant to be a filter or condition?

It looks like a condition to me.
In which case, try changing to:

DoCmd.OpenReport stDocName, acViewPreview, , "PlateSeriesID = SpinSeriesID"  

(Note the extra comma)
0
 
galneweinhawAuthor Commented:
Thanks,

but unfortunately this is not related to my problem of not being able to open my report and recieving the error message in the question title...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.