resizing subforms using code - mine not working.

[bhoran]

I had previously posted this in the forms forum but to no avail.

I have the following code:

Private Sub ctrtxtChequeNo_GotFocus()

Dim dblCount As Double
Dim dblHeight As Integer

dblCount = DCount("[txtChequeNo]", "DuplicateChqNumUnmatched", "[txtChequeNo] = ctrtxtChequeNo&quot
dblHeight = dblCount * 0.8

'Me.Text11 = dblHeight
Forms![DuplicateCheqBal]![DuplicateChqNumUnmatched].InsideHeight = dblHeight
End Sub

What I am trying to do is change the height of the entire subform based on the number of rows to be returned.

I have been looking in the Access help but without too much success, it appears you can only manipulate the height of sections of a form but I thought maybe the forms height is the same as the insideheight.

Anyway my code attempt does not work.

Any help appreciated.

N.b. Text11 is just totest the value is being returned.

 

[bhoran]

I have also used

Private Sub ctrtxtChequeNo_GotFocus()

Dim dblCount As Integer
Dim dblHeight As Integer

dblCount = DCount("[txtChequeNo]", "DuplicateChqNumUnmatched", "[txtChequeNo] = ctrtxtChequeNo&quot
dblHeight = (dblCount * 150) + 400

Me.Text11 = dblHeight
Forms![DuplicateCheqBal]![DuplicateChqNumUnmatched].Height = dblHeight

End Sub

With limited success. The subform seems to be resizing but it upsets the space between record in the detail section and the report footer does not move indicating it may be sizing the section rather than the overall report.

Cheers

 

[bhoran]

Which event?????

Ok I have it sorted by setting up another new form and placing both forms inside then using the following code:

Private Sub ctrtxtChequeNo_GotFocus()

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

dblCount = DCount("[txtChequeNo]", "DuplicateChqNumUnmatched", "[txtChequeNo] = ctrtxtChequeNo&quot
dblHeight = dblCount * 310 + 410
dblSecHeight = dblCount * 310 + 250

'Me.Text11 = dblHeight
Forms![UnmatchedChqUpdate]![DuplicateCheqBal]![DuplicateChqNumUnmatched].Height = dblHeight
Me.Section(acDetail).Height = dblSecHeight
'Debug.Print dblSecHeight
Me.InsideHeight = dblSecHeight + 1100

End Sub

But I don't know which event to attach it to. I have onGot Focus of a control on one of the subreports but the control actually has to be highlighted. I it to happen when ever someon views a new record. Any idea which event to attach this code to?

 

[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