Using Access 2010 to open Access 2000 db

[DDonnachie]

Lo Folks

It's been a while since I looked at this database (a few years). The database was written a while ago using Access version 2003, obviously now we've had to upgrade to 2010 and I thought we had managed it with very few problems. But this one has me a little stuck.

Getting a run-time error "Runtime error -2147352567 (80020009): Update or CancelUpdate without AddNew or Edit" on a form which you click on a list box to select a record. This code has worked for years (so I believed, just been told by a user that it's always been flaky ..... they've only been using it for about 4 years), but obviously something about it is upsetting Access 2010. Hoping some-one can tell me what changed even better if you can tell me how to get this working again.
Since discovered that sometimes you can change the selected record 3 or 4 times before this gives an error. The same form is used to add in new records and the new records are going in fine.

I have already posted this in another forum (http://www.utteraccess.com/forum/Written-V2003-Error-20-t1973610.html)
I know it isn't the references

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim CurRcrds As Recordset
    Dim AllowInsert As Integer
    Dim Ans
    
    If Me.NewRecord Then
        If IsDate([CST_Date]) And ([CST_Name] <> 0) And ([CST_Phase] <> 0) Then
'            AllowInsert = ChkDuplicateHours([CST_Date], [CST_Name], [CST_Phase])
            AllowInsert = 0
        Else
            AllowInsert = 1
        End If
        [CST_Maker] = "App:" & Access_LogApp & " // PC:" & Access_LogPC
    End If
'**************Error here****************************************************
    [CST_Modified] = Now()
    [CST_Modder] = "App:" & Access_LogApp & " // PC:" & Access_LogPC
'**************Error here****************************************************
    If AllowInsert = 0 Then
        Set CurRcrds = Me.Recordset
        If IsDate([CST_Date]) Then
            CST_DayFor = CST_Date
            If Weekday([CST_Date]) <> vbSunday Then
                MsgBox "Date is not valid, This date must be a Sunday", vbCritical, "Date not valid"
                Cancel = True
                CST_Date.SetFocus
            Else
                Ans = MsgBox("Do you want to save this Record ? ", vbYesNo, "Save Weekly Hours Record")
                If Ans = vbNo Then
                    Cancel = True
                    Ans = MsgBox("Do you want to undo changes to this Record ? ", vbYesNo, _
                      "Undo changes to Weekly Hours Record")
                    If Ans = vbYes Then
                        BtnCstUndo_Click
                    End If
                Else
                    If Not Me.NewRecord Then
                        If CurRcrds!CST_Name <> CST_Name Then
                            SaveChanges "Eng_Costing", "CST_Name", "Modified {" & GetField("Eng_Employee", _
                            "Emp_ID", CurRcrds!CST_Name, "Emp_Surname") & ", " & GetField("Eng_Employee", "Emp_ID", _
                            CurRcrds!CST_Name, "Emp_Forename") & "} - {" & CST_Name.Column(1, CST_Name.ListIndex) _
                            & "}", ID, CurRcrds!CST_Name, CST_Name
                        End If
                        If CurRcrds!CST_Phase <> CST_Phase Then
                            SaveChanges "Eng_Costing", "CST_Phase", "Modified {" & GetField("Eng_Phase", _
                            "Phs_Auto", CurRcrds!CST_Phase, "Phs_MainPrj") & " / " & GetField("Eng_Phase", "Phs_Auto", _
                            CurRcrds!CST_Phase, "Phs_ID") & "} - {" & CST_Phase.Column(1, CST_Phase.ListIndex) _
                            & "}", ID, CurRcrds!CST_Phase, CST_Phase.Column(1, CST_Phase.ListIndex)
                        End If
                        If CurRcrds!CST_Grade <> CST_Grade Then
                            SaveChanges "Eng_Costing", "CST_Grade", "Modified", ID, CurRcrds!CST_Grade, CST_Grade
                        End If
                        If CurRcrds!CST_Date <> CST_Date Then
                            SaveChanges "Eng_Costing", "CST_Date", "Modified", ID, CurRcrds!CST_Date, CST_Date
                        End If
                        If CurRcrds!CST_Norm_H <> CST_Norm_H Then
                            SaveChanges "Eng_Costing", "CST_Norm_H", "Modified", ID, CurRcrds!CST_Norm_H, CST_Norm_H
                        End If
                        If CurRcrds!CST_OT_H <> CST_OT_H Then
                            SaveChanges "Eng_Costing", "CST_OT_H", "Modified", ID, CurRcrds!CST_OT_H, CST_OT_H
                        End If
                        If CurRcrds!CST_OTX_H <> CST_OTX_H Then
                            SaveChanges "Eng_Costing", "CST_OTX_H", "Modified", ID, CurRcrds!CST_OTX_H, CST_OTX_H
                        End If
                        If CurRcrds!CST_EventID <> CST_EventID Then
                            SaveChanges "Eng_Costing", "CST_EventID", "Modified", ID, CurRcrds!CST_EventID, CST_EventID
                        End If
                        If CurRcrds!CST_DayFor <> CST_DayFor Then
                            SaveChanges "Eng_Costing", "CST_DayFor", "Modified", ID, CurRcrds!CST_DayFor, CST_DayFor
                        End If
                        If CurRcrds!CST_OverHead <> CST_OverHead Then
                            SaveChanges "Eng_Costing", "CST_OverHead", "Modified", ID, CurRcrds!CST_OverHead, CST_OverHead
                        End If
                        If CurRcrds!CST_Ref_1 <> CST_Ref_1 Then
                            SaveChanges "Eng_Costing", "CST_Ref_1", "Modified", ID, CurRcrds!CST_Ref_1, CST_Ref_1
                        End If
                        If CurRcrds!CST_Ref_2 <> CST_Ref_2 Then
                            SaveChanges "Eng_Costing", "CST_Ref_2", "Modified", ID, CurRcrds!CST_Ref_2, CST_Ref_2
                        End If
                        If CurRcrds!CST_Ref_3 <> CST_Ref_3 Then
                            SaveChanges "Eng_Costing", "CST_Ref_3", "Modified", ID, CurRcrds!CST_Ref_3, CST_Ref_3
                        End If
                        If CurRcrds!CST_Comment <> CST_Comment Then
                            SaveChanges "Eng_Costing", "CST_Comment", "Modified", ID, CurRcrds!CST_Comment, CST_Comment
                        End If
                        If CurRcrds!CST_Group <> CST_Group Then
                            SaveChanges "Eng_Costing", "CST_Group", "Modified", ID, CurRcrds!CST_Group, CST_Group
                        End If
                    End If
                End If
            End If
        End If
    Else
        MsgBox "This Employee already has a record for this date and Project/Phase. Record ID #" & AllowInsert, _
          vbCritical, "Duplicate Record"
        Cancel = True
    End If
    If Cancel <> True Then
        TglBtnFilter.Value = 0  'turn off filter
        TglBtnFilter_Click
    End If
End Sub

Any help you can give or suggestions you might have will be appreciated.

Thanks
David

 

[boblarson]

You're going to need to provide the code for the procedure "SaveChanges" which is being used in there.

 

[DDonnachie]

Thanks Bob,

It's really just a procedure which creates an Audit trail.

Sub SaveChanges(Ch_Tbl As String, Ch_Fld As String, Ch_Dtl As String, Ch_ID As String, Ch_Old As String, Ch_New As String)
    Dim SQL_Str As String
'    Dim AdmRcrds As Recordset
    SQL_Str = "INSERT INTO ADM_Trail ([TableName],[FieldName],[Details],[Who],[When],[UniqueID],[OldValue],[NewValue])"
    SQL_Str = SQL_Str & " VALUES("
    SQL_Str = SQL_Str & "'" & Ch_Tbl & "', '" & Ch_Fld & "', '" & Ch_Dtl & "'"
    SQL_Str = SQL_Str & ", 'App:" & Access_LogApp & " / PPC:" & Access_LogPC & "'"
    SQL_Str = SQL_Str & ",#" & Format(Now, "MM/dd/yy HH:mm") & "#, " & Ch_ID & ", '" & Ch_Old & "', '" & Ch_New & "');"
    Debug.Print "Save Audit - " & SQL_Str
    DoCmd.RunSQL (SQL_Str)
End Sub

 

[boblarson]

This is a bound form? I think you may be having a problem because you are setting the recordset object to the form's recordset instead of using RecordsetClone. Either that, or you might try using:

    Me![CST_Modified] = Now()
    Me![CST_Modder] = "App:" & Access_LogApp & " // PC:" & Access_LogPC

to make sure that Access isn't confused. You should use the ME keyword when referring to objects/fields on the form because sometimes Access gets messed up if you don't explicitly tell it where to look.

 

[DDonnachie]

Appreciate the help Bob, unfortunately the same error occurs in the same place.
I can't put my finger on it but there is definitely something weird happening, sometimes I can choose 3 different entries before the error and other times I can get past 10. It's like something is taking it out of edit mode.

 

[Norie]

David

What exactly are you doing when the error occurs?

Are you just selecting an item in the listbox?

If that's all you are doing I can't quite see how the BeforeUpdate event is being triggered?

Is there anything else happening when you select an item?

Is the listbox itself triggering any code via one of it's events?

 

[DDonnachie]

Norrie

the list box has an after_update event to select records.

Private Sub List26_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Nz(Me![List26], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Not sure why it's not an on_click event. Still just to make it a little more confusing when I put a break in this code I get no errors, stopped trying at twenty different record selections.

 

[Norie]

David

Do you know what the code is meant to do?

If you do I suppose you could try replacing it, though if you did that you might never find out what the problem was.

 

[DDonnachie]

I really should know as I wrote it, but it was a while a go and this is the first I found out about anything not working on it.

I'll change it to an on_click and see what that does for me.

Edited
======

Ok I don't see how me changing the event which executes this would solve the issue, but it has.

Thanks for helping Bob and thanks for pointing me at the answer Norrie

 

[boblarson]

By the way it really should be:

Set rs = Me.RecordsetClone

and not

Set rs = Me.Recordset.Clone

Also, you should always check

If rs.NoMatch Then
   Msgbox "No match"
Else
   Me.Bookmark = rs.Bookmark
End If

Because if there is no match it won't necessarily be obvious, other than it doesn't move.

And, normally the After Update event of the control is used to ensure that it has the correct value to reference, but sometimes the click event is sufficient, but I rarely use the click event.

Also, if ID is not text then you just need:

    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.RecordsetClone

    rs.FindFirst "[ID] = " & Me.List26

    If rs.NoMatch Then
          MsgBox "No Match"
    Else
          Me.Bookmark = rs.Bookmark
    End If