Dynamic Height Problem

[WhiskyLima]

Hey guys, I have a form with 2 sub forms in it. The first form is on the top and holds bookings and the second is under that and holds invoices. I have made it so that each time an invoice or a booking is added or removed, the code below runs to change the height of each sub form relative to the number of records in it, in order to keep the page looking concise and tide.

Now I should mention that this was previously working fine and suddenly does not. Now when I create a new booking or invoice, nothing happens to the height. If I delete one, that works and if I close and reopen the form, it corrects the height there too as the function is also called in the onload. It seems to be an issue with the record count as it is not finding the correct number of records.

Function dynamicHeight()

'Change the height and top position of each subform dynamically
'based on the number of records in each subform.

Dim numRec As Integer
Dim recHeight As Integer
Dim minHeight As Integer
Dim totalHeight As Integer

minHeight = 1077
recHeight = 566
'First resize the booking windows

numRec = [Forms]![SAEvent]![SABookings].Form.RecordsetClone.RecordCount
totalHeight = minHeight + (numRec * recHeight)
[Forms]![SAEvent]![SABookings].Height = totalHeight

'Then the invoices position and height depending on the size of the bookings subform

[Forms]![SAEvent]![SAInvoices].Top = [Forms]![SAEvent]![SABookings].Top + totalHeight + 400

numRec = [Forms]![SAEvent]![SAInvoices].Form.RecordsetClone.RecordCount
totalHeight = minHeight + (numRec * recHeight)
[Forms]![SAEvent]![SAInvoices].Height = totalHeight

End Function

Any help would be greatly appreciated

 

[WhiskyLima]

I have worked out my problem. It turns out that the height of the sub forms determines how many records can be seen that that determines the record count. so if the box can only fit one record visibly, it wont count any more than 1 despite the actual number of records there.

I have to either use a different way of counting the records or I have to change the height of the sub form to its maximum size momentarily before the count and then change the height to what it is supposed to be.

 

[sneuberg]

Maybe you need to move to the last record to get the correct count. Try substituting

numRec = [Forms]![SAEvent]![SABookings].Form.RecordsetClone.RecordCount

with this type of code

Dim rsBookings As DAO.Recordset
Set rsBookings = [Forms]![SAEvent]![SABookings].Form.RecordsetClone
rsBookings.MoveLast
numRec = rsBookings.RecordCount
rsBookings.Close

Of course the same type of thing for the invoices.

 

[WhiskyLima]

Maybe you need to move to the last record to get the correct count. Try substituting

numRec = [Forms]![SAEvent]![SABookings].Form.RecordsetClone.RecordCount

with this type of code

Dim rsBookings As DAO.Recordset
Set rsBookings = [Forms]![SAEvent]![SABookings].Form.RecordsetClone
rsBookings.MoveLast
numRec = rsBookings.RecordCount
rsBookings.Close

Of course the same type of thing for the invoices.

Yes that works great, I had originally just put an extra line in to make the height of the whole thing bigger than it needed to be before resizing back to the correct height but I don't like that messy way of doing things. This code works great thank you.