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

 

[DDonnachie]

Thanks Bob, tbh I think this code has been generated automatically, but I appreciate the pointers.

 

[Norie]

David

What code has been automatically generated and by what?

I've not seen any of the code you've posted that looks like it's generated by Access

 

[DDonnachie]

The code that was in list box event, I'm pretty sure it was generated using a wizard in Access. Select the record from the list box which is then displayed on the rest of the form, but like I said it has been a while since I looked at it.

 

[boblarson]

I'm pretty sure it was generated using a wizard in Access.

Which is a good lesson that the Wizard doesn't always (actually in a lot of cases) give the best and most efficient code. You can cut your teeth on the code that is generated but many times there are better ways to code it and also to be more efficient.