rs.Close give '438' error

[Guest]

On rs.Close I receive an error 'Object doesn't support this property or method'

I have 2 Functions in the module, On the first function rs1.Close works,
however on the second function rs2.Close gives an error. I am not sure as to
why. I am using rs2 to find out the first business day of the current
reporting period. Could that error appear if something else like a report or
a query is using the returned variable from rs2?

Thanks

 

[TC]

Post the line of code that gets the error, and the Dim statement of
each variable that is used in that statement.

TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Also if there is better way to combine code, any suggestions are welcome:
*********
Public Function CBDPeriod1()

Dim rsDays
Dim DayDate
Dim CDay
Dim StartDate
Dim EndDate
Dim PMonth

Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])=Month(Date())")

PMonth = Month(Date) - 1
DayDate = rsDays!Date
CDay = Date

If CDay = DayDate Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date ASC")
End If

StartDate = rsDays!Date
Debug.Print StartDate

If Month(StartDate) = PMonth Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date DESC")
Else:
rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date()) ORDER BY Date DESC")
End If

EndDate = rsDays!Date
CBDPeriod1 = StartDate

rsDays.Close ' this where the error comes up

End Function
***************

 

[Guest]

WHERE Month([Date])= Month(date())-1 ORDER BY Date DESC")
Else:
rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM

There is no Set statement in this part of the code. This could be causing
the problem.

Also if there is better way to combine code, any suggestions are welcome:
*********
Public Function CBDPeriod1()

Dim rsDays
Dim DayDate
Dim CDay
Dim StartDate
Dim EndDate
Dim PMonth

Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])=Month(Date())")

PMonth = Month(Date) - 1
DayDate = rsDays!Date
CDay = Date

If CDay = DayDate Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date ASC")
End If

StartDate = rsDays!Date
Debug.Print StartDate

If Month(StartDate) = PMonth Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date DESC")
Else:
rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date()) ORDER BY Date DESC")
End If

EndDate = rsDays!Date
CBDPeriod1 = StartDate

rsDays.Close ' this where the error comes up

End Function
***************
--
If at first you don''''t succeed, destroy all evidence that you tried.

Post the line of code that gets the error, and the Dim statement of
each variable that is used in that statement.

TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Thanks, that worked.
--
If at first you don''''t succeed, destroy all evidence that you tried.

WHERE Month([Date])= Month(date())-1 ORDER BY Date DESC")
Else:
rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM

There is no Set statement in this part of the code. This could be causing
the problem.

Also if there is better way to combine code, any suggestions are welcome:
*********
Public Function CBDPeriod1()

Dim rsDays
Dim DayDate
Dim CDay
Dim StartDate
Dim EndDate
Dim PMonth

Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])=Month(Date())")

PMonth = Month(Date) - 1
DayDate = rsDays!Date
CDay = Date

If CDay = DayDate Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date ASC")
End If

StartDate = rsDays!Date
Debug.Print StartDate

If Month(StartDate) = PMonth Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date DESC")
Else:
rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date()) ORDER BY Date DESC")
End If

EndDate = rsDays!Date
CBDPeriod1 = StartDate

rsDays.Close ' this where the error comes up

End Function
***************
--
If at first you don''''t succeed, destroy all evidence that you tried.

Post the line of code that gets the error, and the Dim statement of
each variable that is used in that statement.

TC (MVP Access)
http://tc2.atspace.com

 

[Duncan Bachen]

Also if there is better way to combine code, any suggestions are welcome:
*********
Public Function CBDPeriod1()

Dim rsDays
Dim DayDate
Dim CDay
Dim StartDate
Dim EndDate
Dim PMonth

Let me jump in here real fast and mention to you that you should be
defining the kinds of variables these are. By dimming them this way,
they are all variants. This puts some unnecessary overhead into your
system as Access needs to covert between datatypes. It also causes
problems for debugging by making it harder to trace code, and ultimately
find out why you aren't getting the results you expect.

Dim rsDays as DAO.Recordset
Dim DayDate as Date
Dim CDay as Date
Dim StartDate as Date
Dim EndDate as Date
Dim PMonth as Integer

 

[Guest]

Thank you. Originally I try to define the datatypes for variables (cause if I
have to look at this code several month down the road, it will be easier to
understand), what I met with, is sometimes when I define the data type as
Date, I come up with empty value for variable, I am not sure as to why. I
will try your suggestion and see how it will affect this code.

Thanks again.

 

[Duncan Bachen]

Thank you. Originally I try to define the datatypes for variables (cause if I
have to look at this code several month down the road, it will be easier to
understand), what I met with, is sometimes when I define the data type as
Date, I come up with empty value for variable, I am not sure as to why. I
will try your suggestion and see how it will affect this code.

Thanks again.

You come up with an empty value if you don't assign something to it.

Defined as a Date, it can hold only a Date value. If you try to assign a
non-date value to it, it will generate an error. The value won't be
empty. If you assign nothing to it, it will still be 12:00:00 AM.

Example:
Public Sub Test()
Dim dtmMyDate As Date
Debug.Print IsNull(dtmMyDate)
Debug.Print dtmMyDate
End Sub