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
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