Access report is not correct

Question

I am using the following code:
Option Compare Database
Option Explicit

Private Sub Reset_Background()  
    Dim lblCtrl As Control  
    For Each lblCtrl In Report  
        If lblCtrl.ControlType = acLabel Then  
            lblCtrl.BackColor = vbWhite  
        End If  
    Next  
End Sub  
  
Private Sub Pick5_Highlight(ByVal lngTag As Long)  
    'Purpose:       Tales the value of the specific  
    '               TextBox value - txtPick_01 through  
    '               txtPick_06 and highlights thw  
    '               corresponding label  
    'Parameters:    lngValue As Long - Value inside TextBox  
    'Returns:       Nothing  
      
    Dim lblCtrl As Control  
    For Each lblCtrl In Report  
        If lblCtrl.ControlType = acLabel Then  
            Debug.Print (CStr(lblCtrl.Tag))  
            If CLng(lblCtrl.Tag) = lngTag Then  
                lblCtrl.BackColor = vbYellow  
            End If  
        End If  
    Next  
End Sub  
  
  
Private Sub Detail_Paint()  
    Dim txtCtrl As Control, _  
        lngTag As Long, _  
        strLB As String  
          
    For Each txtCtrl In Report  
        Select Case txtCtrl.Name  
            Case "txtPick_01"  
                lngTag = CLng(txtCtrl.Value)  
                Call Pick5_Highlight(lngTag)  
                DoEvents  
            Case "txtPick_02"  
                lngTag = CLng(txtCtrl.Value)  
                Call Pick5_Highlight(lngTag)  
                DoEvents  
            Case "txtPick_03"  
                lngTag = CLng(txtCtrl.Value)  
                Call Pick5_Highlight(lngTag)  
                DoEvents  
            Case "txtPick_04"  
                lngTag = CLng(txtCtrl.Value)  
                Call Pick5_Highlight(lngTag)  
                DoEvents  
            Case "txtPick_05"  
                lngTag = CLng(txtCtrl.Value)  
                Call Pick5_Highlight(lngTag)  
                DoEvents  
            'Case "txtPick_06"  
               '    lngTag = CLng(txtCtrl.Value)  
               '    strLB = "lb_" & CStr(lngTag)  
        End Select  
    Next  
End Sub

This is the desired output:

This is the actual output:"

Why is this occurring?

Thank you,
MRM256

Accepted Answer

I found the answer.

Instead of using the Detail_Paint event. Use the Detail_Format. Which in hindsight makes sense, because we want the output to have a specific look. Programming languages in the past told the programmer the rules on how to use the language.

The code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim txtCtrl As Control, _
lngTag As Long, _
I As Long

    Call Reset_Background     
    For Each txtCtrl In Report
        Select Case txtCtrl.Name
            Case "txtPick_01"
                lngTag = CLng(txtCtrl.Value)
                arPick5(1) = lngTag
                Call Pick5_Highlight(lngTag)

            Case "txtPick_02"
                lngTag = CLng(txtCtrl.Value)
                arPick5(2) = lngTag
                Call Pick5_Highlight(lngTag)

            Case "txtPick_03"
                lngTag = CLng(txtCtrl.Value)
                arPick5(3) = lngTag
                Call Pick5_Highlight(lngTag)

            Case "txtPick_04"
                lngTag = CLng(txtCtrl.Value)
                arPick5(4) = lngTag
                Call Pick5_Highlight(lngTag)

            Case "txtPick_05"
                lngTag = CLng(txtCtrl.Value)
                arPick5(5) = lngTag
                Call Pick5_Highlight(lngTag)

            Case "txtPick_06"
                lngTag = CLng(txtCtrl.Value)
                lngPick1 = lngTag
                Call Pick1_Highlight(lngPick1)
        End Select
        DoEvents
    Next
End Sub

Thank you,
MRM256

Answer

Did you step through your code to see what's actually happening? It might be helpful if you could share a copy of your db for testing. Just a thought...

Answer

Yes, I did step through the code. It seems to be processing more than one record at a time.

As you can see the desired output is each section is suppose to represent one record. The actual output is showing two records per section, then three and so on.

I am not sure how the print output is processed. It should do only one record at a time.

Any thoughts?

MRM256

Share via

Access report is not correct

2 additional answers

Your answer