Module Does Not change properties (1 Viewer)

[trdummy]

Good afternoon,

I posted a new thread a few days ago and I only received one response which did not help. I continued trying to research a few of my issues and hit another bump in the road. I believe or it would appear there are no issues with the code any longer but the main purpose of this Module is to adjust the size of 2 things, the report footer and the label within the report footer. The code is below. Thanks for any help in advance.

Option Compare Database
Option Explicit
Sub MsgBox_Yes_No()
    Dim Response As Integer
    Dim TotalLastPage As Integer
    Dim AmountSub As Integer
    Dim TotalRecords As Integer
    Const MaxPerPage = 34
    
    DoCmd.OpenReport "SF153", acViewReport 'Opens Report in order to acquire proper information
    TotalRecords = [Reports]![SF153]![CounterDuh]
    Response = MsgBox(prompt:="Are you sure you want to continue?" & Chr(10) & Chr(13) & Chr(10) & Chr(13) & "Continuing will adjust the form to the appropriate size.", Buttons:=vbYesNo)
     ' if user selects yes, i would like to macro to continue running
    If Response = vbYes Then
        TotalLastPage = TotalRecords
        AmountSub = TotalRecords
        Do While TotalLastPage > 34 'Loop created to get remainder of records on last page
        TotalLastPage = AmountSub - MaxPerPage
        
        Loop
        If TotalLastPage = 34 Then 'IfThenElse Statement used to assign height to both Report footer and label.
            Reports!SF153!NFE.Height = 0
            Reports!SF153!OMGFooter.Height = 0
        ElseIf TotalLastPage = 33 Then
            Reports!SF153!NFE.Height = 454
            Reports!SF153!OMGFooter.Height = 454
        ElseIf TotalLastPage = 32 Then
            Reports!SF153!NFE.Height = 688
            Reports!SF153!OMGFooter.Height = 688
        ElseIf TotalLastPage = 31 Then
            Reports!SF153!NFE.Height = 922
            Reports!SF153!OMGFooter.Height = 922
        ElseIf TotalLastPage = 30 Then
            Reports!SF153!NFE.Height = 1156
            Reports!SF153!OMGFooter.Height = 1156
        ElseIf TotalLastPage = 29 Then
            Reports!SF153!NFE.Height = 1390
            Reports!SF153!OMGFooter.Height = 1390
        ElseIf TotalLastPage = 28 Then
            Reports!SF153!NFE.Height = 1624
            Reports!SF153!OMGFooter.Height = 1624
        ElseIf TotalLastPage = 27 Then
            Reports!SF153!NFE.Height = 1858
            Reports!SF153!OMGFooter.Height = 1858
        ElseIf TotalLastPage = 26 Then
            Reports!SF153!NFE.Height = 2092
            Reports!SF153!OMGFooter.Height = 2092
        ElseIf TotalLastPage = 25 Then
            Reports!SF153!NFE.Height = 2326
            Reports!SF153!OMGFooter.Height = 2326
        ElseIf TotalLastPage = 24 Then
            Reports!SF153!NFE.Height = 2560
            Reports!SF153!OMGFooter.Height = 2560
        ElseIf TotalLastPage = 23 Then
            Reports!SF153!NFE.Height = 2794
            Reports!SF153!OMGFooter.Height = 2794
        ElseIf TotalLastPage = 22 Then
            Reports!SF153!NFE.Height = 3028
            Reports!SF153!OMGFooter.Height = 3028
        ElseIf TotalLastPage = 21 Then
            Reports!SF153!NFE.Height = 3262
            Reports!SF153!OMGFooter.Height = 3262
        ElseIf TotalLastPage = 20 Then
            Reports!SF153!NFE.Height = 3496
            Reports!SF153!OMGFooter.Height = 3496
        ElseIf TotalLastPage = 19 Then
            Reports!SF153!NFE.Height = 3730
            Reports!SF153!OMGFooter.Height = 3730
        ElseIf TotalLastPage = 18 Then
            Reports!SF153!NFE.Height = 3964
            Reports!SF153!OMGFooter.Height = 3964
        ElseIf TotalLastPage = 17 Then
            Reports!SF153!NFE.Height = 4198
            Reports!SF153!OMGFooter.Height = 4198
        ElseIf TotalLastPage = 16 Then
            Reports!SF153!NFE.Height = 4432
            Reports!SF153!OMGFooter.Height = 4432
        ElseIf TotalLastPage = 15 Then
            Reports!SF153!NFE.Height = 4666
            Reports!SF153!OMGFooter.Height = 4666
        ElseIf TotalLastPage = 14 Then
            Reports!SF153!NFE.Height = 4900
            Reports!SF153!OMGFooter.Height = 4900
        ElseIf TotalLastPage = 13 Then
            Reports!SF153!NFE.Height = 5134
            Reports!SF153!OMGFooter.Height = 5134
        ElseIf TotalLastPage = 12 Then
            Reports!SF153!NFE.Height = 5368
            Reports!SF153!OMGFooter.Height = 5368
        ElseIf TotalLastPage = 11 Then
            Reports!SF153!NFE.Height = 5602
            Reports!SF153!OMGFooter.Height = 5602
        ElseIf TotalLastPage = 10 Then
            Reports!SF153!NFE.Height = 5836
            Reports!SF153!OMGFooter.Height = 5836
        ElseIf TotalLastPage = 9 Then
            Reports!SF153!NFE.Height = 6070
            Reports!SF153!OMGFooter.Height = 6070
        ElseIf TotalLastPage = 8 Then
            Reports!SF153!NFE.Height = 6304
            Reports!SF153!OMGFooter.Height = 6304
        ElseIf TotalLastPage = 7 Then
            Reports!SF153!NFE.Height = 6538
            Reports!SF153!OMGFooter.Height = 6538
        ElseIf TotalLastPage = 6 Then
            Reports!SF153!NFE.Height = 6772
            Reports!SF153!OMGFooter.Height = 6772
        ElseIf TotalLastPage = 5 Then
            Reports!SF153!NFE.Height = 7006
            Reports!SF153!OMGFooter.Height = 7006
        ElseIf TotalLastPage = 4 Then
            Reports!SF153!NFE.Height = 7240
            Reports!SF153!OMGFooter.Height = 7240
        ElseIf TotalLastPage = 3 Then
            Reports!SF153!NFE.Height = 7474
            Reports!SF153!OMGFooter.Height = 7474
        ElseIf TotalLastPage = 2 Then
            Reports!SF153!NFE.Height = 7708
            Reports!SF153!OMGFooter.Height = 7708
        ElseIf TotalLastPage = 1 Then
            Reports!SF153!NFE.Height = 7942
            Reports!SF153!OMGFooter.Height = 7942
        End If
    Else
         ' if the user selects no, I would like the macro to stop and the user be returned to the main form
    End If
End Sub

 

[spikepl]

and the problem is...?

Update: aaargh... that was hidden in the title, apparently.

 

[boblarson]

In order to change footer height and/or a label in the footer, this code has to be run from the OnFormat event of the footer section.