Recordset Problem

[access7]

Hello

This is my first time using a record set and I am not sure I have it set up correctly... I keep getting the following error 'Command text was not set for the command object"... I am hoping that someone may have experienced this error themselves and could point me in the direction of where I might be going wrong....
Code is as follows

' Procedure to get the diaryactionID ready to link it to the correspondence record
Function LLastDiaryRef() As Long
Dim cnnX As ADODB.Connection
Set cnnX = CurrentProject.Connection
Dim lsSQL As String
Dim recDiary As New ADODB.Recordset
recDiary.ActiveConnection = cnnX
On Error GoTo Oops

lsSQL = ""
lsSQL = lsSQL & " Select Top 1 DiaryActionID "
lsSQL = lsSQL & " from tbl_Diary "
lsSQL = lsSQL & " Where CompanyRef = '" & ICompanyRef() & "' "
lsSQL = lsSQL & " Order by DiaryActionID Desc "

Set recDiary = CurrentDb.OpenRecordset(lsSQL)

If recDiary.EOF Then
LLastDiaryRef = 0
Else
LLastDiaryRef = recDiary!DiaryActionID
End If


Exit Function

Oops:
MsgBox Err.Description

End Function

Thanks

 

[GregRun]

The problem here is that you're mixing DAO and ADO. Your object declarations are ADO and your code is DAO.

I use DAO but it doesn't really matter which one you choose. Using DAO:

Remove these lines:
Dim cnnX As ADODB.Connection
Set cnnX = CurrentProject.Connection
Dim lsSQL As String
Dim recDiary As New ADODB.Recordset
recDiary.ActiveConnection = cnnX

replace them with:
Dim recDiary As DAO.Recordset

And your code should work.

 

[GregRun]

Did this work for you?

 

[access7]

yes, thank you... I needed to use DAO