resizing section of subform on the fly - is it possible? 1

[bhoran]

I am working in Access 2000.

I have Bank reconciliation form, frmBnkMain and a subform frmBnkDet. frmBnkMain displays the total for a given cheque number, frmBnkDet displays each cheque record.

The forms are linked by cheque number. When I select a new record from frmBnkMain (the aggregate record) the detail records are displayed.

This works fins as a basis, however, the size of the subform display area seems to be fixed. I want to dynamically resize the subform (& form if necessary) so that all records are displayed and the total is displayed directly below it.

E.g. Currently I have the size set at around 9 rows from the top so the total is always 9 rows from the top even if only2 rows are display and the user has to scroll down if more than 9 rows exist.

I want the total to be in the 3rd row from the top if only 2 rows are returned for that cheque number.

I did a search and someone suggested checking out a previous thread but it no longer exists.

Any ideas?

 

[bhoran]

I am thinking I might be able to count the number of records to be returned and allocate a size to them, then adjust the Height property of the subform to approx 0.6cm per record to be returned.

If anyone can give me some guidance on this it would be great!

Cheers

 

[bhoran]

Ok I have sorted it myself. - FYI

I built an extra form

I now have 3 forms:
Main = UnmatchedChqUpdate
Subform1 = DuplicateCheqBal
Subform2 = DuplicateChqNumUnmatched

I used the same source for main as subform1 and linked the the main and subform1 by the cheque number so that when I cycled through the cheque number using the navagation onmain it would also cycle through the same records in Subform1.

By attaching the code to on current it performs as the new record becomes current.

I hope this is helpful to people.

Private Sub Form_Current()

Dim dblCount As Integer
Dim dblHeight As Long
Dim dblSecHeight As Long

dblCount = DCount("[txtChequeNo]", "DuplicateChqNumUnmatched", "[txtChequeNo] = Forms![UnmatchedChqUpdate]![DuplicateCheqBal].Form![ctrtxtChequeNo] &quot
dblHeight = dblCount * 310 + 410
dblSecHeight = dblCount * 310 + 250


Forms![UnmatchedChqUpdate]![DuplicateCheqBal]![DuplicateChqNumUnmatched].Height = dblHeight
Forms![UnmatchedChqUpdate]![DuplicateCheqBal].Form.Section(0).Height = dblSecHeight

Forms![UnmatchedChqUpdate]![DuplicateCheqBal].Form.InsideHeight = dblSecHeight + 1100

End Sub