VB Programers Unite!!!

Factual Questions
1

Ok, I don’t like doing this, but I need some help, and I can only hope the TM can lend a hand.

I’me developing a database for resource schedualing, and deconfliction. One of the reports they asked me to create for them is a time-line type of format. I found some code at http://www.mvps.org/access/ that gave a simple timelind report that I could modify to work for my report. It worked great while I had it in Access 97, but when they converted us all to 2000, it bombs out totally. I understand this is because they have taken the code that used to be a bit slapdash and made it more consistent to regular VB. The problem is that I don’t know VB, I can look at code, and modify it, and do some simple things, but I’ve never taken the time to learn VB. I’ve been able to do just fine with Macro’s and such. So, I’m going to paste the code here, if someone - anyone can tell me why it’s not working anymore, I would be eteranlly gratefull. If this paste doesn’t give enough info to show what I’m trying to do, I’d be happy to e-mail the DB to anyone that can give me some help. Also, maybe some explination as to why is doesn’t work anymore, so I can figure it out. Thanks in advance for any help you can give.

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([Exercise Start Date]) AS MinOfStartDate " _
& " FROM tblexercise”, dbOpenSnapshot)
If rs.RecordCount > 0 Then
mdatEarliest = rs!MinOfStartDate
End If
Set rs = db.OpenRecordset("SELECT Max(IIf(IsDate([Exercise End Date]),CDate([Exercise End Date]),Null)) " _
& “AS MaxOfEndDate FROM tblexercise”, 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

2

Is there any more data you can provide? Can you be a little more specific than “bombs out totally”? Is there aby particular error message that shows up?

I’d be willing to take a look at it if you send me a copy of the database and a little more info.

3

I suspect that the problem may be that Access 2000 uses ADO by default, rather than DAO (Access 97). Consequently, when you use recordset variables (and the like) in code, it’s going to get mucked up.
To see if this is the problem, open the code module. On the menu bar, click “Tools,” then “References…” You should see “Microsoft DAO 3.6 Object Library,” or something similar, checked. If it’s not checked, scroll down until you find it, select it, and increase it’s priority as much as you can. It should have priority over “Microsoft ActiveX Data Objects 2.1 Library.”
HTH
btw- the comp.databases.ms-access newsgroup can be a wonderful help.

4

I thought this was a gonna gimme a chance to pick on Java programmers for a change. But since its a fellow VB’er I also would be happy to lend a hand. You can email me the db at jamesdcarroll@hotmail.com and I’ll be more than happy to help you out.

5

aramis–

When we run the code as is, we get a “Compile error: User-defined type not found” Which is caused by what padabe was talking about, the ADO DAO problem. That I can fix. What’s wrong now is the box that’s supposed to grow and shrink depending on the leangth of the event, won’t grow or shrink. It also won’t display the Start and End dates like it should.

Again, thanks for any help you can give. I really need to sit down and learn VB someday, but the press of deadlines doens’t give me a lot of time to learn new skills.

6

I think part of your problem may be using the recordcount property. Recordcount only has a value if a record has been accessed, so I think it’s zero initially, and your “If rs.RecordCount > 0 Then” may never work. Try “If Not rs.EOF then”. That may help.