Subform Sizing MS Access 10 -- doesn't work moving from one Parent record to another

[rmcgaffic]

thread702-1731627

This thread is in reference to an earlier posting which described a mehtod of subform sizing in MS Access 10.

I think I have identified a problem with the code suggested there but am not sure what to do about it.

A form frmPerson has 4 subforms: frmPersonAddressSub, frmPersonPhoneSub, frmPersonEmailSub, and frmPersonWebSub.

Each of these subforms can display a different number of records and are dynamically repositioned on the parent form.

So the suggested code does its job for a single parent record. but when the parent record is changed, it behaves strangely.

Here is an example:
My first parent record has 5 addresses, 2 phones, 3 emails, and 3 web addresses all of which display properly.

My second parent record has 1 address, 0 phone, 0 email, and 0 web address all of which display property.

When I click on the navigation button to return to my first record, here is the result
5 address records, 1 phone, 1 email, 1 web address.

Of course, the record count and dislay should not have changed from its original display.

To summarize:
First record 5, 2, 3, 3 records
Second record 1, 0, 0, 0 records
First record 5, 1, 1, 1 records

Here is my code.

Note the msgbox is returning the numbers above.
Any ideas of why this statement

intRecs = subformctl.Form.RecordsetClone.RecordCount


won't return the correct number of records?


Public Sub GrowSub(subformctl As Access.SubForm)
'Adapted from TEK-TIPS.com thread 702-1731627 Subform Sizing MS Access 10
Dim intRecs As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Const oneRecordHt = 0.2298
Const HeaderHt = 0.2298
intRecs = subformctl.Form.RecordsetClone.RecordCount

MsgBox intRecs

subformctl.height = InchesToTwips(oneRecordHt) * intRecs + InchesToTwips(HeaderHt)

End Sub

Public Function InchesToTwips(inches As Double) As Long
InchesToTwips = CInt(inches * 1440)
End Function

Public Sub MoveSubForms()
Const spaceBetween = 0.05
Me.frmPersonPhoneSub.Top = Me.frmPersonAddressSub.Top + Me.frmPersonAddressSub.height + InchesToTwips(spaceBetween)
Me.frmPersonEmailSub.Top = Me.frmPersonPhoneSub.Top + Me.frmPersonPhoneSub.height + InchesToTwips(spaceBetween)
Me.frmPersonWebSub.Top = Me.frmPersonEmailSub.Top + Me.frmPersonEmailSub.height + InchesToTwips(spaceBetween)
End Sub

Private Sub Form_Current()
GrowSub Me.frmPersonAddressSub
GrowSub Me.frmPersonPhoneSub
GrowSub Me.frmPersonEmailSub
GrowSub Me.frmPersonWebSub
MoveSubForms
End Sub

 

[rmcgaffic]

I found the issue:

The original code uses a command button to resize the subforms.

My code uses a OnCurrent record event to resize the subforms.

When I place a command button on my form, and place the same code behind it, and click it after moving to a new parent record, everything is fine.

So I guess my question can be restated:

How do I get the on current event to behave as if I were pushing a command button.

 

[rmcgaffic]

Solution:

Note that the original code uses a command button to move the subforms.

If you want the OnCurrent event to refresh the forms, I found it necessary to modify the code as follows:

subformctl.Form.RecordsetClone.MoveLast
intRecs = subformctl.Form.RecordsetClone.RecordCount

Additionally, because some parent records have no child records, it is necessary to add some error trapping code or test first that the record count is not equal to zero.

On Error GoTo Error_GrowSub

Then insert the following code

Exit_GrowSub:
Exit Sub
Error_GrowSub:
Resume Exit_GrowSub