Set MySet = MyDef.OpenRecordset(MyDef, DB_OPEN_DYNASET)

[Rich]

This proc used to work fine in A97 but just doesn't work in 2010 any help on converting to the correct proc would be much appreciated

Function CheckNum() As String
Dim db As Database, ssMaxCheck As Recordset, MyDef As QueryDef, MySet As DAO.Recordset
Set db = CurrentDb
Set MyDef = db.QueryDefs("ChNumbQry")
MyDef![Ent] = MyNumb
Set MySet = MyDef.OpenRecordset(MyDef, DB_OPEN_DYNASET)


Set ssMaxCheck = MySet
If Not IsNull(ssMaxCheck.Fields(0).Value) Then
CheckNum = CStr(ssMaxCheck("MaxOfChNo") + 1)
Else:
Dim CurDB As Database, BA As Recordset, SQLStmt As String
Set CurDB = DBEngine.Workspaces(0).Databases(0)
SQLStmt = "SELECT AccTypes.StChNum, AccTypes.AccountTypeID FROM AccTypes;"
Set BA = CurDB.OpenRecordset(SQLStmt, DB_OPEN_DYNASET)
If Not IsNull(BA.Fields(0).Value) And BA![StChNum] > 0 Then
CheckNum = CStr(BA![StChNum])
Else
CheckNum = "00000100"
End If
BA.Close
CurDB.Close
End If
End Function

 

[boblarson]

Rich:

See if this works for you. There are a few things in there that I put in as slightly different because, for example, this line isn't needed as you can still use the variable db.

Set CurDB = DBEngine.Workspaces(0).[URL="http://www.access-programmers.co.uk/forums/showthread.php?t=211526#"][COLOR=darkgreen]Databases[/COLOR][/URL](0)

But make sure that at least all of the declared recordset objects are explicit with DAO otherwise you can run into problems due to possible ADO references. If ADO isn't checked, it would then be okay without the DAO. in the declaration but I suggest including it always just in case.

I think the main thing is just the difference between the DB_OPEN_DYNASET and the newer dbOpenDynaset option.

But here you go, see if this works for you.

Function CheckNum() As String
    Dim db As DAO.Database, ssMaxCheck As DAO.Recordset, MyDef As DAO.QueryDef, MySet As DAO.Recordset
    Set db = CurrentDb
    Set MyDef = db.QueryDefs("ChNumbQry")
    MyDef![Ent] = MyNumb
    Set MySet = MyDef.OpenRecordset(MyDef, dbOpenDynaset)

    Set ssMaxCheck = MySet
    If Not IsNull(ssMaxCheck.Fields(0).Value) Then
        CheckNum = CStr(ssMaxCheck("MaxOfChNo") + 1)
    Else
        Dim BA As DAO.Recordset, SQLStmt As String
        
        SQLStmt = "SELECT AccTypes.StChNum, AccTypes.AccountTypeID FROM AccTypes;"
        Set BA = db.OpenRecordset(SQLStmt, dbOpenDynaset)
        If Not IsNull(BA.Fields(0).Value) And BA![StChNum] > 0 Then
            CheckNum = CStr(BA![StChNum])
        Else
            CheckNum = "00000100"
        End If
        Set db = Nothing
   
   BA.Close
    MySet.Close
    
    Set BA = Nothing
    Set MySet = Nothing
    End If
End Function