Work Days -"Object vairiable or With Block variable not set"

[carlp69]

In Access 2002, I have the following code to search a table and return a true/false result if the date 'dtedate' appears in the table.

It keeps returnig an error "Object vairiable or With Block variable not set"


Public Function IsAHoliday(ByVal dteDate As Date) As Boolean
Dim db As DAO.Database
Dim rsHoliday As DAO.Recordset
Set db = CurrentDb()
Set rsHoliday = db.OpenRecordset("SELECT holidayDate From tbl_PublicHolidays WHERE HolidayDate = #'" & dteDate & "'#;&quot

IsAHoliday = False
MsgBox (dteDate)
With rsHoliday
If .EOF Then
Exit Function
Else
IsAHoliday = True
End If
End With

rsHoliday.Close
Set rsHoliday = Nothing
End Function

Can anybody help!

 

[TonyJollans]

Hi carlp69,

What line are you getting the error on?

The only thing I can see which is obviously wrong is that you have extra single quotes around your date literal. It should be ..

... = #" & dteDate & "#;&quot

and not ..

... = #'" & dteDate & "'#;&quot

Enjoy,
Tony

 

[p27br]

I think the problem is in your SQL statement : try taking out the single quotes around the date :
("SELECT holidayDate From tbl_PublicHolidays WHERE HolidayDate = #" & dteDate & "#&quot

 

[MichaelRed]

dteDate = Date
? dteDate
10/9/03


#'" & dteDate & "'#;&quot ==> & dteDate & ";"





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[carlp69]

I have tried both with and without the single quotes.

The error occurs on the Set rsHoliday = db.OpenRecordset.... Line

 

[TonyJollans]

Hi carlp69,

Sorry, can't see anything else. It works (with the quotes removed) for me in 2000; over to someone with 2002.

Enjoy,
Tony

 

[SkipVought]

Hi,

Try converting dteDate to a string.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[carlp69]

Tried change dtedate to string, still no joy.

Any more ideas?

 

[MichaelRed]

Public Function IsAHoliday(ByVal dteDate As Date) As Boolean

    Dim db As DAO.Database
    Dim rsHoliday As DAO.Recordset
    Dim strSQL As String

    strSQL = "SELECT HoliDate "
    strSQL = strSQL & "From tblHolidays "
    strSQL = strSQL & "WHERE HoliDate = #" & dteDate & "#;"

    Set db = CurrentDb()
    Set rsHoliday = db.OpenRecordset(strSQL)
    
    IsAHoliday = False
    MsgBox (dteDate)

    With rsHoliday
        If (.EOF) Then
            Exit Function
        Else
            IsAHoliday = True
        End If
    End With
    
    rsHoliday.Close
    Set rsHoliday = Nothing
End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[carlp69]

This is strange!

I can get the code to work if I start from scratch, but does not work in my current project.

Is it something to do with the fact I am using an SQL server project rather than a Access .MDB database?

Carl

 

[TonyJollans]

Hi Carl,

I might be completely wrong with this but should you be using DAO definitions with SQL Server?

Enjoy,
Tony

 

[carlp69]

How else can i write the code to produce the same result?

 

[MichaelRed]

set up an SQL server ODBC connection in place of the DAO.Recordset.


or -use a LINK to the SQL Server Table.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[carlp69]

PROBLEM SOLVED
I have solved the problem with the following code :-

Public Function IsAHoliday(ByVal dteDate As String) As Boolean
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
IsAHoliday = False
Set cn = CurrentProject.Connection
sql = "SELECT HolidayDate FROM tbl_Holidays"
rs.Open sql, cn
rs.MoveFirst
Do While Not (rs.EOF)
If dteDate = rs!HolidayDate Then IsAHoliday = True
rs.MoveNext
Loop
rs.Close
cn.Close
End Function

 

[TonyJollans]

Hi Carl,

Well done! Thanks for coming back with that.

Enjoy,
Tony