ACCESS FEED: [MS_AccessPros] Re: Multi columns report - remove a line when the report reaches the EOF

Jumat, 08 Februari 2013

[MS_AccessPros] Re: Multi columns report - remove a line when the report reaches the EOF

Phucon

Wow! I never would have guessed this to be so hard. Maybe it has to do with a line being a graphic and not a field. Anyway, here is the solution.

You have to use the .Line method instead of putting a line in design view. And it has to be in the Detail_Print event, not the Detail_Format event.

I change the style of the line from dashed to invisible if the counter is the same as the number of records. I commented the code as thoroughly as I thought necessary.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim bolEnd As Boolean
Dim X1 As Single
Dim X2 As Single
Dim Y1 As Single
Dim Y2 As Single
Dim Color As Variant

'Increment the counter for each row.
lngCounter = lngCounter + 1

'Set the Y coordinant by adding the top of the lowest field in the detail
'and adding the height to it.
Y1 = (Me.CustomerID.Top + Me.CustomerID.Height) / 1440
Y2 = (Me.CustomerID.Top + Me.CustomerID.Height) / 1440

'Set the starting point for the line.
X1 = 0

'Set the width of the line
X2 = Me.Width / 1440

'Specify unit of measurement as inches.
Me.ScaleMode = 5

'You have to use DrawWidth of 1 or the DrawStyle is ignored.
Me.DrawWidth = 1

If lngCounter = lngRecCount Then
'Last row so make line style invisible
Me.DrawStyle = 5
Else
'Make Style a dashed line
Me.DrawStyle = 1
End If

'Color is black
Color = RGB(0, 0, 0)

'Draw line.
Me.Line (X1, Y1)-(X2, Y2), Color

End Sub

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com

--- In MS_Access_Professionals@yahoogroups.com, "saigonf7q5" wrote:
>
> I have a multi columns report sample that looks like below.
>
> Name: XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
> City: XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
> State: XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
> Region: XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
> District: XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
> ----------------------------------------------------------
> Name: XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
> City: XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
> State: XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
> Region: XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
> District: XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
> ----------------------------------------------------------
> ******End of Report ******
>
> And have been trying to remove the last 'dashed line' when the report or record set reaches the end of file. Below is the code which I tried. It's not working. What's right way to do it?
>
> Thanks
> Phucon
>
> Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
>
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
> Dim i As Integer
>
> If Me.Left < 1.5 * 1440 Then
> Me.NextRecord = False
> For i = 1 To 5
> Me("txt" & i).Visible = False
> Me("lbl" & i).Visible = True
> Next i
> Else
> For i = 1 To 5
> Me("txt" & i).Visible = True
> Me("lbl" & i).Visible = False
> Next i
> End If
>
> Set db = CurrentDb
> Set rs = db.OpenRecordset(strRS)
>
> With rs
> If .EOF Then
> Me.lineNextRow.Visible = False
> End If
> End With
>
> End Sub
>