Code Doesn't Work in 2007 3

[perrymans]

I have the following code in 2003, but it doesn't work in 2007. I don't have a 2007 install to test with.

The crix is the updating of the value in Tbl_Members.DuesYearID and refreshing the MainPage.ubSubform. My user is telling me it is not updating.

Is there a difference for this in 2007?

Thanks. Sean.

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim rs As Object
Dim frm As Form
Dim lngMemberID As Long
Dim lngDuesYearID As Long
Dim strSQL As String
Dim strDuesMax As String
Dim lngDuesMax As Long
Dim strDuesNow As String

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

        Select Case Me.cboTransactionTypeID
            Case 1
                lngMemberID = Me.cboMemberID
                strDuesMax = DLookup("[DuesYearSimple]", "SelQ_MemberDuesYear", "[MemberID] = " & lngMemberID)
                If strDuesMax > Me.cboDuesYearID.Column(2) Then
                    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
                    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
                    Exit Sub
                End If
                    lngDuesYearID = Me.cboDuesYearID
                    
                    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
                    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
                    
                    If Not IsNull(DMax("[DuesYearID]", "SelQ_TransactionDuesYear", "[MemberID] = " & lngMemberID)) Then
                        lngDuesMax = DMax("[DuesYearID]", "SelQ_TransactionDuesYear", "[MemberID] = " & lngMemberID)
                        strSQL = "UPDATE Tbl_Members SET DuesYearID = " & lngDuesMax & " WHERE (((MemberID)=" & lngMemberID & "));"
                
                        DoCmd.SetWarnings False
                        DoCmd.RunSQL strSQL
                        DoCmd.SetWarnings True
                        
                        Set frm = Forms![Frm_MainPage]![ubSubForm].Form
                        Forms![Frm_MainPage]![ubSubForm].Form.Requery
                        With frm.RecordsetClone
                            .FindFirst "[MemberID] = " & Str(Nz(lngMemberID, 0))
                            If Not .NoMatch Then frm.Bookmark = .Bookmark
                        End With
                    Else
                        strSQL = "UPDATE Tbl_Members SET DuesYearID = '' WHERE (((MemberID)=" & lngMemberID & "));"
                
                        DoCmd.SetWarnings False
                        DoCmd.RunSQL strSQL
                        DoCmd.SetWarnings True
                        
                        Set frm = Forms![Frm_MainPage]![ubSubForm].Form
                        Forms![Frm_MainPage]![ubSubForm].Form.Requery
                        With frm.RecordsetClone
                            .FindFirst "[MemberID] = " & Str(Nz(lngMemberID, 0))
                            If Not .NoMatch Then frm.Bookmark = .Bookmark
                        End With

                    End If
            Case Else
                DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
                DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        End Select

Exit_Command7_Click:
    Exit Sub

Err_Command7_Click:
    MsgBox Err.Description
    Resume Exit_Command7_Click
    
End Sub

 

[TheAceMan1]

How are ya perrymans . . .

No error messages or breaking of code?

Make sure the user has the [blue]Microsoft DAO 3.6 Object Library[/blue] set and

[blue]change
   Dim rs As Object
to 
   [b]Dim rs As DAO.Recordset[/b][/blue]

[blue]DoMenuItem[/blue] is a legacy method of DoCmd for for backward compataility. Use the [blue]RunCommand[/blue] methods instead:

[blue]change
   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
to
   [b]docmd.RunCommand acCmdSaveRecord[/b]


change
   DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
to
   [b]DoCmd.RunCommand acCmdSelectRecord[/b]


change
   DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
to
   [b]DoCmd.RunCommand acCmdDeleteRecord[/b][/blue]

In your Dim variables add an additional: [blue]Dim db As DAO.Database[/blue]. Then where you run SQL

[blue]change
   DoCmd.SetWarnings False
   DoCmd.RunSQL strSQL
   DoCmd.SetWarnings True
to
   [b]db.Execute SQL, dbFailOnError[/b]

You don't need SetWarnings![/blue]

Hope I snagged it! I have a few errands to run and will finish parsing the code later this evening.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[perrymans]

That is fantastic info! This is what I get for reusing the same code over and over and not looking for the latest ways to do something.

I also just downloaded and installed a trial Office 2007 and will get to test it for any other specfic errors.

Thanks! Sean.

 

[TheAceMan1]

perrymans . . .

I forgot one thing. At the end of your Dim statements, copy/paste the following line:

[blue]   Set db = CurrentDb[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[perrymans]

Would I update to dao.recordset in the following after_update as well?

    Dim rs As Object

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

Thanks. Sean.

 

[TheAceMan1]

perrymans . . .

Absolutely! ... Its better programming practice ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[missinglinq]

And remember, Sean, if you just downloaded and installed 2007, you have to declare the folder that the db resides in as a "trusted" location before any code will run.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

Roger that [blue]missinglinq![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]