Need ADO help.

[Dherrera]

Can someone help me convert the following code to ADO. Im moving my database to sql server.

'This procedure generates the Expense ID with the last two digits of the current year
'and increments each time.  The year counter does roll over at the end of the year.

Private Sub txtExpenseID_Enter()

    Dim rsa As Recordset
    Dim rsb As Recordset
    Dim DateNow
    Dim strMonth As String
    Dim strYear As String
    Dim temp As Integer

    Set rsta = CurrentDb()
    Set rstb = CurrentDb()
    Set rsa = rsta.OpenRecordset("tblCurrentYear1", dbOpenDynaset)
    Set rsb = rstb.OpenRecordset("tblCounter1", dbOpenDynaset)
    rsa.Edit
    rsb.Edit

    DateNow = Date
    strYear = Right(DatePart("yyyy", Now()), 2)
    strMonth = Right(DatePart("m", Now()), 2)
        If IsNull(txtExpenseID.Value) Then
            If DateNow > rsa![year] + 365 Then
                rsa![year] = rsa![year] + 365
                rsb![counter] = 0
                rsb.Update
                rsa.Update
            Else
        End If
     
        temp = DMax("counter", "tblCounter1") + IIf(CurrentUser() = vCurrUser, 0, 1)
        Me![txtExpenseID] = Format$(strMonth, "00") & strYear & Format$(temp, "000")
           
        rsb.Edit
        rsb![counter] = temp
        rsb.Update
            Else
        End If
    
    rsa.Close
    rsb.Close

    vCurrUser = CurrentUser()

End Sub

 

[PHV]

And what have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dherrera]

this is what i have so far but its not working.

Private Sub txtExpenseID_Enter()

    Dim rsa As ADODB.Recordset
    Dim rsb As ADODB.Recordset
    Dim DateNow As Date
    Dim strMonth As String
    Dim strYear As String
    Dim temp As Integer

    Set rsa = New ADODB.Recordset
    Set rsb = New ADODB.Recordset
    
    rsa.Open "tblCurrentYear", CurrentProject.Connection, adOpenStatic
    rsb.Open "tblCounter", CurrentProject.Connection, adOpenStatic

    DateNow = Date
    strYear = Right(DatePart("yyyy", Now()), 2)
    strMonth = Right(DatePart("m", Now()), 2)
        If IsNull(txtExpenseID.Value) Then
            If DateNow > rsa![Year] + 365 Then
                rsa![Year] = rsa![Year] + 365
                rsb![counter] = 0
                rsb.Update
                rsa.Update
            Else
        End If
     
        temp = DMax("counter", "tblCounter1") + IIf(CurrentUser() = vCurrUser, 0, 1)
        Me![txtExpenseID] = Format$(strMonth, "00") & strYear & Format$(temp, "000")
           
        'rsb.Edit
        rsb![counter] = temp
        rsb.Update
            Else
        End If
    
    rsa.Close
    rsb.Close

    vCurrUser = CurrentUser()

    Set rsa = Nothing
    Set rsb = Nothing
    
End Sub