code error - what needs adjusting

[aussie_user]

Hi,

Last year I found a report somewhere on an access help site that would show a timeline. I copied the report into my Access 97 database and it worked fine. I tried exporting this report to my current Access 2000 database but it keeps coming up with an error. The error section that is highlighted is

Private Sub Report_Open(Cancel As Integer)
Dim db As Database

Does anyone have an idea of what I might need to change to get it to work properly. Below is the entire procedure

Option Compare Database
Option Explicit

Private mdatEarliest As Date
Private mdatLatest As Date
Private mintDayDiff As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intStartDayDiff As Integer
Dim intDayDiff As Integer
Dim sngFactor As Single

On Error Resume Next

Me.ScaleMode = 1 'Twips
sngFactor = Me.boxMaxDays.Width / mintDayDiff

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.boxGrowForDate.Visible = True
Me.lblTotalDays.Visible = True
intStartDayDiff = Abs(DateDiff("d", Me.StartDate, mdatEarliest))
intDayDiff = Abs(DateDiff("d", Me.EndDate, Me.StartDate))

If intStartDayDiff = 0 Then intStartDayDiff = 1
With Me.boxGrowForDate
.Left = Me.boxMaxDays.Left + (intStartDayDiff * sngFactor)
.Width = intDayDiff * sngFactor
End With
Me.lblTotalDays.Left = Me.boxGrowForDate.Left
Me.lblTotalDays.Caption = intDayDiff & " Day(s)"
Else '
Me.boxGrowForDate.Visible = False
Me.lblTotalDays.Visible = False
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Min([StartDate]) AS MinOfStartDate " _
& " FROM qryTimeline", dbOpenSnapshot)
If rs.RecordCount > 0 Then
mdatEarliest = rs!MinOfStartDate
End If
Set rs = db.OpenRecordset("SELECT Max(IIf(IsDate([EndDate]),CDate([EndDate]),Null)) " _
& "AS MaxOfEndDate FROM qryTimeline", dbOpenSnapshot)
If rs.RecordCount > 0 Then
mdatLatest = rs!MaxOfEndDate
End If

mintDayDiff = DateDiff("d", mdatEarliest, mdatLatest)

Me.txtMinStartDate.Caption = Format(mdatEarliest, "mm/dd/yyyy")
Me.txtMaxEndDate.Caption = Format(mdatLatest, "mm/dd/yyyy")
Set rs = Nothing
Set db = Nothing
End Sub



Thanks for any help. I have the feeling that it might not be possible for someone to help based on this information but figured it was worth a try.

 

[ghudson]

You might have to set your "References" to DAO 3.6. When you are viewing a Module, click the Tools menu >>> References >>> Browse for Microsoft DAO 3.6 >>> Select "Files of type: Executable Files (*.exe; *.dll)" (mine is located @ C:\Program Files\Common Files\Microsoft Shared\DAO).

Then explicitly dimension your code...
Dim db As DAO.Database
Dim rs As DAO.Recordset

HTH