VBA looping


Joined
Aug 22, 2014
Messages
2
Reaction score
0
I'm new to Access and VBA want I'm doing is drawing 8 boxes on a report and would like to know if I can use looping to create the code below to shorten it up:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim X1 As Single, Y1 As Single
Dim X2 As Single, Y2 As Single
Dim Color As Long
'************************************
Dim page_start_left_1 As Integer
Dim page_start_top_1 As Integer
Dim page_end_left_1 As Integer
Dim page_end_top_1 As Integer
Dim page_end_Width_1 As Integer
Dim page_end_Height_1 As Integer
'****
Dim page_start_left_2 As Integer
Dim page_start_top_2 As Integer
Dim page_end_left_2 As Integer
Dim page_end_top_2 As Integer
Dim page_end_Width_2 As Integer
Dim page_end_Height_2 As Integer
'****
Dim page_start_left_3 As Integer
Dim page_start_top_3 As Integer
Dim page_end_left_3 As Integer
Dim page_end_top_3 As Integer
Dim page_end_Width_3 As Integer
Dim page_end_Height_3 As Integer
'****
Dim page_start_left_4 As Integer
Dim page_start_top_4 As Integer
Dim page_end_left_4 As Integer
Dim page_end_top_4 As Integer
Dim page_end_Width_4 As Integer
Dim page_end_Height_4 As Integer
'************************************
' Set to 1st question on page 1
page_start_left_1 = Me!Q1text.Left
page_start_top_1 = Me!Q1text.Top
' Set to last question on page 1
page_end_left_1 = Me!Q7text.Left
page_end_top_1 = Me!Q7text.Top
page_end_Width_1 = Me!Q7text.Width
page_end_Height_1 = Me!Q7text.Height
' Set to 1st question on page 2
page_start_left_2 = Me!Q8text.Left
page_start_top_2 = Me!Q8text.Top
' Set to last question on page 2
page_end_left_2 = Me!Q27text.Left
page_end_top_2 = Me!Q27text.Top
page_end_Width_2 = Me!Q27text.Width
page_end_Height_2 = Me!Q27text.Height
' Set to 1st question on page 3
page_start_left_3 = Me!Q28text.Left
page_start_top_3 = Me!Q8text.Top
' Set to last question on page 3
page_end_left_3 = Me!QBP3text.Left
page_end_top_3 = Me!QBP3text.Top
page_end_Width_3 = Me!QBP3text.Width
page_end_Height_3 = Me!QBP3text.Height
' Set to 1st question on page 4
page_start_left_4 = Me!QADHD1text.Left
page_start_top_4 = Me!QADHD1text.Top
' Set to last question on page 4
page_end_left_4 = Me!QCoOccur4text.Left
page_end_top_4 = Me!QCoOccur4text.Top
page_end_Width_4 = Me!QCoOccur4text.Width
page_end_Height_4 = Me!QCoOccur4text.Height
'************************************************* *******************
'1st Page left box
' X and Y coordinates for the top left corner of the box.
X1 = page_start_left_1 + 7000
Y1 = page_start_top_1 - 100
' X and Y coordinates for the bottom right corner of the box.
X2 = page_end_left_1 + page_end_Width_1 + 2900
Y2 = page_end_top_1 + page_end_Height_1
Me.DrawWidth = 3 ' Width of the line (in pixels).
Color = RGB(0, 0, 0) ' Use black line color.
' Draw the rectangle with the Line method.
Me.Line (X1, Y1)-(X2, Y2), Color, B
'1st Page right box
' X and Y coordinates for the top left corner of the box.
X1 = page_start_left_1 + 10130
Y1 = page_start_top_1 - 100
' X and Y coordinates for the bottom right corner of the box.
X2 = page_end_left_1 + page_end_Width_1 + 5460
Y2 = page_end_top_1 + page_end_Height_1
Me.DrawWidth = 3 ' Width of the line (in pixels).
Color = RGB(0, 0, 0) ' Use black line color.
' Draw the rectangle with the Line method.
Me.Line (X1, Y1)-(X2, Y2), Color, B

'************************************************* *******************
'2st Page left box
' X and Y coordinates for the top left corner of the box.
X1 = page_start_left_2 + 7000
Y1 = page_start_top_2 - 100
' X and Y coordinates for the bottom right corner of the box.
X2 = page_end_left_2 + page_end_Width_2 + 2900
Y2 = page_end_top_2 + page_end_Height_2
Me.DrawWidth = 3 ' Width of the line (in pixels).
Color = RGB(0, 0, 0) ' Use black line color.
' Draw the rectangle with the Line method.
Me.Line (X1, Y1)-(X2, Y2), Color, B
'2st Page right box
' X and Y coordinates for the top left corner of the box.
X1 = page_start_left_2 + 10130
Y1 = page_start_top_2 - 100
' X and Y coordinates for the bottom right corner of the box.
X2 = page_end_left_2 + page_end_Width_2 + 5460
Y2 = page_end_top_2 + page_end_Height_2
Me.DrawWidth = 3 ' Width of the line (in pixels).
Color = RGB(0, 0, 0) ' Use black line color.
' Draw the rectangle with the Line method.
Me.Line (X1, Y1)-(X2, Y2), Color, B
'************************************************* *******************
'3rd Page left box
' X and Y coordinates for the top left corner of the box.
X1 = page_start_left_3 + 7000
Y1 = page_start_top_3 - 100
' X and Y coordinates for the bottom right corner of the box.
X2 = page_end_left_3 + page_end_Width_3 + 2900
Y2 = page_end_top_3 + page_end_Height_3
Me.DrawWidth = 3 ' Width of the line (in pixels).
Color = RGB(0, 0, 0) ' Use black line color.
' Draw the rectangle with the Line method.
Me.Line (X1, Y1)-(X2, Y2), Color, B
'3rd Page right box
' X and Y coordinates for the top left corner of the box.
X1 = page_start_left_3 + 10130
Y1 = page_start_top_3 - 100
' X and Y coordinates for the bottom right corner of the box.
X2 = page_end_left_3 + page_end_Width_3 + 5460
Y2 = page_end_top_3 + page_end_Height_3
Me.DrawWidth = 3 ' Width of the line (in pixels).
Color = RGB(0, 0, 0) ' Use black line color.
' Draw the rectangle with the Line method.
Me.Line (X1, Y1)-(X2, Y2), Color, B

'************************************************* *******************
'4th Page left box
' X and Y coordinates for the top left corner of the box.
X1 = page_start_left_4 + 7000
Y1 = page_start_top_4 - 100
' X and Y coordinates for the bottom right corner of the box.
X2 = page_end_left_4 + page_end_Width_4 + 2900
Y2 = page_end_top_4 + page_end_Height_4
Me.DrawWidth = 3 ' Width of the line (in pixels).
Color = RGB(0, 0, 0) ' Use black line color.
' Draw the rectangle with the Line method.
Me.Line (X1, Y1)-(X2, Y2), Color, B
'4th Page right box
' X and Y coordinates for the top left corner of the box.
X1 = page_start_left_4 + 10130
Y1 = page_start_top_4 - 100
' X and Y coordinates for the bottom right corner of the box.
X2 = page_end_left_4 + page_end_Width_4 + 5460
Y2 = page_end_top_4 + page_end_Height_4
Me.DrawWidth = 3 ' Width of the line (in pixels).
Color = RGB(0, 0, 0) ' Use black line color.
' Draw the rectangle with the Line method.
Me.Line (X1, Y1)-(X2, Y2), Color, B
End Sub
 
Ad

Advertisements

Joined
Aug 22, 2014
Messages
2
Reaction score
0
Nevermind I found the answer:

Dim page_start_left(8) As Integer
Dim Q(8) as string
Q(1) = "Q1text"
Q(2) = "Q7text"
...
For I = 1 to 8
page_start_left(I).Left = me.controls(Q(I)).Left
...
next I
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top