On Print or On Format?

[jw5107]

Below is example of what I am working with.
Where is the best place to put this code on a report? OnPrint, OnFormat, OnActivate, etc....???
I very well may have the code laid out wrong. Right now its all in the Detail Print Event, and I tried using the Call function to run all the other subs...

Any suggestions?
Thanks in advance!!
jw

Option Compare Database

Dim rstAlloc As New ADODB.Recordset
Dim rstPooledGTWYs As New ADODB.Recordset
Dim rstmodGTWYs As New ADODB.Recordset
Dim rstNewAllocations As New ADODB.Recordset
Dim rstDEallocations As New ADODB.Recordset
Dim ctrl As Control
Dim i As Integer
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

rstAlloc.Open "AllocationStatusMain", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rstAlloc.EOF Then
Exit Sub
End If

For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
With rstAlloc
.MoveFirst
For i = 1 To .RecordCount
If ctrl.Caption = !Allocated Then
ctrl.ForeColor = 16711680
ctrl.FontBold = True
End If
.MoveNext
Next i
End With
End If
Next

For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
With rstAlloc
.MoveFirst
For i = 1 To .RecordCount
If ctrl.Caption = !OverAllocated Then
ctrl.BackColor = 12058623
ctrl.FontBold = True
End If
.MoveNext
Next i
End With
End If
Next

For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
With rstAlloc
.MoveFirst
For i = 1 To .RecordCount
If ctrl.Caption = !OnBackOrder Then
ctrl.BackColor = 8434687
ctrl.FontBold = True
End If
.MoveNext
Next i
End With
End If
Next

For Each ctrl In Me.Controls
If TypeOf ctrl Is TextBox Then
With rstAlloc
.MoveFirst
For i = 1 To .RecordCount
If ctrl.Name = !Allocated Then
ctrl.Value = !Allocation
End If
.MoveNext
Next i
End With
End If
Next


For Each ctrl In Me.Controls
If TypeOf ctrl Is TextBox Then
With rstAlloc
.MoveFirst
For i = 1 To .RecordCount
If ctrl.Name = !OverAllocated Then
ctrl.Value = !Allocation
End If
.MoveNext
Next i
End With
End If
Next


For Each ctrl In Me.Controls
If TypeOf ctrl Is TextBox Then
With rstAlloc
.MoveFirst
For i = 1 To .RecordCount
If ctrl.Name = !OnBackOrder Then
ctrl.Value = !Allocation
End If
.MoveNext
Next i
End With
End If
Next
Call PooledGtwy
End Sub
Sub ModGtwys()
rstmodGTWYs.Open "qryModelGTWYs", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rstmodGTWYs.EOF Then
Exit Sub
End If
Call NewAlloc
For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
With rstmodGTWYs
.MoveFirst
For i = 1 To .RecordCount
If ctrl.Caption = !GTWY Then
ctrl.BorderColor = 255
End If
.MoveNext
Next i
End With
End If
Next
Call NewAlloc


End Sub
Sub NewAlloc()

rstNewAllocations.Open "qryNewAllocations", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic


If rstNewAllocations.EOF Then
Exit Sub
End If
Call DEalloc
For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
With rstNewAllocations
.MoveFirst
For i = 1 To .RecordCount
If ctrl.Caption = !GTWY Then
ctrl.BackColor = 16645064
ctrl.FontBold = True
End If
.MoveNext
Next i
End With
End If
Next
Call DEalloc

End Sub
Sub DEalloc()
rstDEallocations.Open "qryDEallocations", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rstDEallocations.EOF Then
Exit Sub
End If
For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
With rstDEallocations
.MoveFirst
For i = 1 To .RecordCount
If ctrl.Caption = !GTWY Then
ctrl.ForeColor = 255
End If
.MoveNext
Next i
End With
End If
Next

End Sub
Sub PooledGtwy()
rstPooledGTWYs.Open "WWRPooledGTWYs", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rstPooledGTWYs.EOF Then
Exit Sub
End If
Call ModGtwys
For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
With rstPooledGTWYs
.MoveFirst
For i = 1 To .RecordCount
If ctrl.Caption = !GTWY Then
ctrl.BackColor = 11983506
End If
.MoveNext
Next i
End With
End If
Next
Call ModGtwys

End Sub

 

[BoxHead]

jw,

I'm not clear on why you have to roll through so many recordsets rather than binding the report (and subreports) to queries, but I'll assume I'm missing something there.

Take a look at Select Case to maybe streamline some of the formatting into a single roll through the controls collection.

Something like:

Select Case ctl.Caption
Case  !Allocated 
    ctrl.ForeColor = 16711680
    ctrl.FontBold = True
Case  !OverAllocated 
    ctrl.ForeColor = 12058623
    ctrl.FontBold = True
Case  !OnBackOrder 
    ctrl.ForeColor = 8434687
    ctrl.FontBold = True
[i]etc...[/i]
End Select

HTH





John

Use what you have,
Learn what you can,
Create what you need.