Datebase or object suddenly read only

[Gina Maylone]

Happy Friday!



My working database has suddenly gone read only. I reverted to a back up that is not read only and run through the process of posting payments. It doesn't happen on the monthly process only on the "roads" process. Is there anything in my code that would make this happen? It breaks at the loop highlighted below.

Private Sub RoadsCheckNum_AfterUpdate()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strsql As String
    Dim CN As String
    Dim BD As Variant
    Dim BL As Variant
    Dim CR As Currency
    Dim P As Currency
    Dim mbrid As Long
    Dim dp As Date
    Dim u As String
    Dim C As String
    
    '    Dim RC As Long
    'get values from sub form
    dp = Me.RoadsDatePaid
    u = Forms![memberpayments]![dbuser]
    CN = Forms![memberpayments]![RoadsPayments].Form![RoadsCheckNum]
    'debug.print u
    
    mbrid = Forms![memberpayments]![MemberID_PK]
    P = Me.Roadspayment
    BL = P  '<<-- payment amount
    'open recordset
    strsql = "SELECT roadsQueryforPayments.MemberID_FK, roadsQueryforPayments.DBAmount, roadsQueryforPayments.CRAmount,"
    strsql = strsql & " roadsQueryforPayments.dbdate, roadsQueryforPayments.AsmtType, roadsQueryforPayments.Details,"
    strsql = strsql & " roadsQueryforPayments.LotNumber, roadsQueryforPayments.EnteredBy, roadsQueryforPayments.crdate,"
    strsql = strsql & " roadsQueryforPayments.CheckNumber, roadsQueryforPayments.comments, [dbamount]-[cramount] AS baldue"
    strsql = strsql & " FROM roadsQueryforPayments"
    strsql = strsql & " WHERE roadsQueryforPayments.MemberID_FK = " & [Forms]![memberpayments]![MemberID_PK]
    'strsql = strsql & " And ([DBAmount] - [CRAmount]) > 0"
    
    strsql = strsql & " ORDER BY roadsQueryforPayments.dbdate;"
    Set rst = CurrentDb.OpenRecordset(strsql)
    'check for records in recordset
    If Not rst.BOF And Not rst.EOF Then
        rst.MoveLast
        rst.MoveFirst
        Do While Not rst.EOF

            'get values from each record in recordset
            BD = rst!baldue
            CR = rst!CRAmount

            'edit or add a payment to the record set
  
            If BL < BD Then   'less than
                rst.Edit
                rst!CRDATE = dp
                rst!CRAmount = BL + CR
                rst!CheckNumber = CN
                rst!EnteredBy = Forms![memberpayments]![dbuser]
                rst!Comments = dp & " - " & CN
                rst.Update
                BL = 0
            ElseIf BL > BD Then  'greater than
                rst.Edit
                rst!CRDATE = dp
                rst!CRAmount = BD + CR
                rst!CheckNumber = CN
                rst!EnteredBy = Forms![memberpayments]![dbuser]
                rst!Comments = dp & " - " & CN
                rst.Update
                BL = BL - BD
            ElseIf BL = BD Then  'equal to
                rst.Edit
                rst!CRDATE = dp
                rst!CRAmount = BD + CR
                rst!CheckNumber = CN
                rst!EnteredBy = Forms![memberpayments]![dbuser]
                rst!Comments = dp & " - " & CN
                rst.Update
                BL = 0
            End If
            rst.MoveNext
        Loop

        '---- extra left over after making payments ----
        'save remaining amt to additional principal in last records of record set
        If BL > 0 Then
            rst.MoveLast
            rst.Edit
            rst!CRAmount = rst!CRAmount + BL
            rst!Comments = dp & " - " & CN
            rst!EnteredBy = Forms![memberpayments]![dbuser]
            rst.Update
            BL = 0
        End If

        'add payment to payments table
        strsql = "INSERT INTO AsmtPayments ( MemberID_FK, [asmttype], PaymentAmount, PaymentDate, CheckNumber, enteredby )"
        strsql = strsql & " VALUES ( " & mbrid & ", '" & AsmtType & "'," & P & ", #" & dp & "#, '" & CN & "', '" & u & "');"

        
DoCmd.OpenQuery "UpdateCRDATEStoNull"
    DoCmd.OpenQuery "balfwd"
    DoCmd.OpenQuery "cleanupoverpayments"
    Forms![memberpayments]![AsmtPayments].Form.Requery
Forms![memberpayments]![AsmtPayments].Form.Refresh
    Me.Refresh
CurrentDb.Execute strsql, dbFailOnError
    End If

    rst.Close
    Set rst = Nothing
    
    
    Forms![memberpayments]![AsmtPayments].Form.Requery
    
    'MsgBox "End of process"
End Sub

Thanks in advance for any thoughts!!

[pbaldy]

Do you know if the If or ElseIf ran and successfully edited the recordset? It would be pretty odd if they worked and the bit in red didn't. Is roadsQueryforPayments editable if you open it directly?

[Gina Maylone]

Hi Paul! I checked the query which was not updateable because i had it set to distinct (no duplicate values) so I changed that and was able to enter a record. And now, no lock issues! Thank you!!!!

[pbaldy]

Glad you got it sorted!