这个VBA代码有什么问题？ [英] What is wrong with this VBA code?

问题描述

我从

下载了TimeLines.zip（ http://www.mvps.org/access/reports/rpt0018.htm ）我想我发现了一个

的错误，但我似乎无法修复它。当你打开它时，转到

表并输入一个新事件并将它的开始日期设置为like12 / 01/1999

和结束日期为12 / 1999分之31。然后转到报告并找到新的

输入事件。如果它在我的计算机上执行的操作，你会发现

由于某种原因，条形码位于错误的位置。我猜测

它与detail_Format部分中的数学有关。

报告非常适合我的需要，但是如果你在

后期的一半时间线上放一个事件，它似乎无法正确显示在

图表。任何想法。

谢谢

Shawn Yates

I downloaded TimeLines.zip from
(http://www.mvps.org/access/reports/rpt0018.htm) and I think I found a
bug in it but I can''t seem to fix it. When you open this go to the
table and enter a new event and put it''s start date at like12/01/1999
and the end date at 12/31/1999. Then go to the report and find your new
entered event. If it does what it does on my computer you will notice
that for some reason the bar is in the wrong place. I''m guessing that
it has something to do with the math in the detail_Format section. The
report is perfect for what I need but if you put in an event in the
latter half of the timeline it doesn''t seem to show correctly on the
chart. Any Ideas.
Thanks
Shawn Yates

推荐答案

哎呀我忘了在这里发布VBA代码就是全部：


选项比较数据库

选项明确


私人mdat最早作为日期

私人mdat最新日期

私人mintDayDiff作为整数

-------- -------------------------------------------------- --------------------------------------------

Private Sub Detail_Format（取消为整数，FormatCount为整数）

Dim intStartDayDiff为整数

Dim intDayDiff为整数

Dim sngFactor As Single


On Error Resume Next


Me.ScaleMode = 1''Twips

sngFactor = Me.boxMaxDays。宽度/ mintDayDiff


如果不是IsNull（Me.StartDate）而不是IsNull（Me.EndDate）那么

Me.boxGrowForDate.Visible = True

Me.lblTotalDays.Visible = True

intStartDayDiff = Abs（DateDiff（" d"，Me.StartDate，mdatEarliest））

intDayDiff = Abs（DateDiff（" d"，Me.EndDate，Me.StartDate））


如果intStartDayDiff = 0则intStartDayDiff = 1

使用Me.boxGrowForDate

.Left = Me.boxMaxDays.Left +（intStartDayDiff * sngFactor）

.Width = intDayDiff * sngFactor

结束

Me.lblTotalDays.Left = Me.boxGrowForDate.Left

Me.lblTotalDays.Caption = intDayDiff& "日（））

Else''

Me.boxGrowForDate.Visible = False

Me.lblTotalDays.Visible = False

结束如果

结束次级

------------------------- -------------------------------------------------- ------------------------------

Private Sub Report_Open（取消为整数）

Dim db作为数据库

Dim rs作为记录集

设置db = CurrentDb

设置rs = db.OpenRecordset（" SELECT最小（[开始日期]）AS MinOfStartDate

" _

&" FROM Projects"，dbOpenSnapshot）

如果rs.RecordCount 0然后

mdatEarliest = rs！MinOfStartDate

结束如果

设置rs = db.OpenRecordset（" SELECT Max（IIf（IsDate（[结束

日期]），CDate（[结束日期]），空））_

&AS MaxOfEndDate FROM Projects"，dbOpenSnapshot）

如果rs.RecordCount 0那么

mdatLatest = rs！MaxOfEndDate

结束如果


mi ntDayDiff = DateDiff（" d"，mdatEarliest，mdatLatest）


Me.txtMinStartDate.Caption =格式（mdatEarliest，" mm / dd / yyyy"）

Me.txtMaxEndDate.Caption =格式（mdatLatest，" mm / dd / yyyy"）

设置rs =无什么

设置db = Nothing

End Sub

Oops I forgot to post the VBA code here is all of it:

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

经典bug，左边的位置设置在宽度之前。


所以

.Left = Me.boxMaxDays.Left +（intStartDayDiff * sngFactor）

.Width = intDayDiff * sngFactor


应< br $>
.Width = intDayDiff * sngFactor

.Left = Me.boxMaxDays.Left +（intStartDayDiff * sngFactor）

-
< br $>
Terry Kreft

" Shawn Yates" < sy **** @ cc.usu.eduwrote in message

news：11 ********************* @ p79g2000cwp。 googlegro ups.com ...

Classic bug, the left position is set before the width.

So
.Left = Me.boxMaxDays.Left + (intStartDayDiff * sngFactor)
.Width = intDayDiff * sngFactor

Should be
.Width = intDayDiff * sngFactor
.Left = Me.boxMaxDays.Left + (intStartDayDiff * sngFactor)
--

Terry Kreft
"Shawn Yates" <sy****@cc.usu.eduwrote in message
news:11*********************@p79g2000cwp.googlegro ups.com...

我从

下载了TimeLines.zip（ http://www.mvps.org/access/reports/rpt0018.htm ）我想我发现了

bug，但我似乎无法修复它。当你打开它时，转到

表并输入一个新事件并将它的开始日期设置为like12 / 01/1999

和结束日期为12 / 1999分之31。然后转到报告并找到新的

输入事件。如果它在我的计算机上执行的操作，你会发现

由于某种原因，条形码位于错误的位置。我猜测

它与detail_Format部分中的数学有关。

报告非常适合我的需要，但是如果你在

后期的一半时间线上放一个事件，它似乎无法正确显示在

图表。任何想法。

谢谢

Shawn Yates

I downloaded TimeLines.zip from
(http://www.mvps.org/access/reports/rpt0018.htm) and I think I found a
bug in it but I can''t seem to fix it. When you open this go to the
table and enter a new event and put it''s start date at like12/01/1999
and the end date at 12/31/1999. Then go to the report and find your new
entered event. If it does what it does on my computer you will notice
that for some reason the bar is in the wrong place. I''m guessing that
it has something to do with the math in the detail_Format section. The
report is perfect for what I need but if you put in an event in the
latter half of the timeline it doesn''t seem to show correctly on the
chart. Any Ideas.
Thanks
Shawn Yates

Hi Shawn，


作为一般规则，你应该避免使用''On Error Resume Next''。

任何事情都可能发生在你的代码中，你不会意识到什么

正在进行（它还使调试变得更容易，因为代码将停止

，其中发生错误）。


祝你好运/>

尼克


Shawn Yates写道：

Hi Shawn,

As a general rule you should avoid using ''On Error Resume Next''.
Anything could be happening in your code and you would not realise what
was going on (it also makes debugging easier as the code will stop
where the error occurs).

Good Luck

Nick

Shawn Yates wrote:

哎呀我忘记发布VBA这里的代码就是全部：


选项比较数据库

选项明确


私有mdatEarliest As Date

私人mdat最新日期

私人mintDayDiff为整数

-------------------- -------------------------------------------------- --------------------------------

Private Sub Detail_Format（取消为整数，FormatCount为整数）

迪m intStartDayDiff As Integer

Dim intDayDiff As Integer

Dim sngFactor As Single


On Error Resume Next


Me.ScaleMode = 1''Twips

sngFactor = Me.boxMaxDays.Width / mintDayDiff


如果不是IsNull（Me.StartDate） ）而不是IsNull（Me.EndDate）那么

Me.boxGrowForDate.Visible = True

Me.lblTotalDays.Visible = True

intStartDayDiff = Abs（DateDiff（" d"，Me.StartDate，mdatEarliest））

intDayDiff = Abs（DateDiff（" d"，Me.EndDate，Me.StartDate））


如果intStartDayDiff = 0则intStartDayDiff = 1

使用Me.boxGrowForDate

.Left = Me.boxMaxDays.Left +（intStartDayDiff * sngFactor）

.Width = intDayDiff * sngFactor

结束

Me.lblTotalDays.Left = Me.boxGrowForDate.Left

Me .lblTotalDays.Caption = intDayDiff& "日（））

Else''

Me.boxGrowForDate.Visible = False

Me.lblTotalDays.Visible = False

结束如果

结束次级

------------------------- -------------------------------------------------- ------------------------------

Private Sub Report_Open（取消为整数）

Dim db作为数据库

Dim rs作为记录集

设置db = CurrentDb

设置rs = db.OpenRecordset（" SELECT最小（[开始日期]）AS MinOfStartDate

" _

&" FROM Projects"，dbOpenSnapshot）

如果rs.RecordCount 0然后

mdatEarliest = rs！MinOfStartDate

结束如果

设置rs = db.OpenRecordset（" SELECT Max（IIf（IsDate（[结束

日期]），CDate（[结束日期]），空））_

&AS MaxOfEndDate FROM Projects"，dbOpenSnapshot）

如果rs.RecordCount 0那么

mda tLatest = rs！MaxOfEndDate

结束如果


mintDayDiff = DateDiff（" d"，mdatEarliest，mdatLatest）


Me.txtMinStartDate.Caption =格式（mdatEarliest，" mm / dd / yyyy"）

Me.txtMaxEndDate.Caption =格式（mdatLatest，" mm / dd / yyyy"）

设置rs = Nothing

设置db = Nothing

End Sub

Oops I forgot to post the VBA code here is all of it:

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

这篇关于这个VBA代码有什么问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！