Make a continuous form set its height automatically

Question

I am trying to make a continuous form set its height to equal the height of the Header + height of Footer + height of the Detail. This is to avoid having a form with only two or three records have a large amount of blank space. Conversely I want to have a large number of records appear on a form with 10 records per page and arrow Down/Up to see the rest of the records. These are the procedures I set up:

Public Function NumRecs() As Integer
    NumRecs = DCount("*", "tblDisclosure", "Not IsNull(FormSentOff) and IsNull(DBSInvoice))
End Function

Public Function FrmHt() As Integer
    Dim FrmDet As Integer
    Dim FrmHdr As Integer
    Dim FrmFtr As Integer

    FrmDet = Form.Section(0).Height
    FrmHdr = Form.Section(1).Height
    FrmFtr = Form.Section(2).Height

    If FrmHt > (11 * FrmDet + FrmHdr + FrmFtr) Then
        FrmHt = 11 * FrmDet + FrmHdr + FrmFtr
    Else
        FrmHt = (NumRecs * FrmDet) + FrmHdr + FrmFtr + FrmDet
    End If
End Function

Private Sub Form_Load()
    Me.Move Left:=8000, Top:=1000, Width:=9240, Height:=FrmHt
End Sub

It ignores the If…Then… Else statement and creates a form showing all records in one window which is unmanageable.

It works for a form of less than 11 records (obviously) but I find that I have to add in the height of an extra record otherwise it puts the last record on a second page. Viz:

FrmHt = (NumRecs * FrmDet) + FrmHdr + FrmFtr + FrmDet

I can live with this but I would really like to resolve the problem of the If…Then…Else statement being ignored.

I have changed the type casting to Variant and Double but this made no difference.

Renaud Bompuis Renaud Bompuis · Accepted Answer · 2014-07-31T02:23:40

There seems to be an issue your code: you use FrmHt in your If statement, but it has no value at that point, it will always be 0.

I think you probably want your statement to be something like:

If NumRecs > 11 Then
    FrmHt = 11 * FrmDet + FrmHdr + FrmFtr
Else
    FrmHt = (NumRecs * FrmDet) + FrmHdr + FrmFtr + FrmDet
End If

The other issue of the last record being on the next page may be because you're too tight in displaying the 10 records on screen and during the conversion from twips to pixels rounding errors may make the total height a tiny bit less than what would be necessary for 10 records.
Try adding a few twips to your FrmHT result to see if this works.

Make a continuous form set its height automatically

1 Answers