Changing the Height of A list Box

[Shane]

I want to change the height of a list box depending on
how many records it has but I can get it to work on the
OnLoad property of the form. It keeps disapearing when I
open the pop up form.

Here's my code:

Private Sub Form_Load()
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim sql As String
Dim MyCount As Long
Dim MyM, MyH As Double

'MyM is the Height that needs to be increased with each
additional record
MyM = 0.147

sql = "SQL"

Set db = CurrentDb()

Set rec = db.OpenRecordset(sql)
MyCount = rec.RecordCount

MyH = MyCount * MyM

Me.lstInactive.Height = MyH

db.Close
Set rec = Nothing
End Sub

All th calculation are right but the height is not
changing.

 

[Dirk Goldgar]

I want to change the height of a list box depending on
how many records it has but I can get it to work on the
OnLoad property of the form. It keeps disapearing when I
open the pop up form.

Here's my code:

Private Sub Form_Load()
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim sql As String
Dim MyCount As Long
Dim MyM, MyH As Double

'MyM is the Height that needs to be increased with each
additional record
MyM = 0.147

sql = "SQL"

Set db = CurrentDb()

Set rec = db.OpenRecordset(sql)
MyCount = rec.RecordCount

MyH = MyCount * MyM

Me.lstInactive.Height = MyH

db.Close
Set rec = Nothing
End Sub

All th calculation are right but the height is not
changing.

I have four observations:

1. Except for recordsets opened on local tables, rather than on queries,
a recordset's RecordCount property is not accurate until you have
navigated to the end of the recordset.

2. Heights and widths specified in VBA must be expressed in "twips"
(1/1440 of an inch), not in inches or centimeters. Therefore, if MyM is
a value in inches, you must multiply it by 1440 before assigning it to
the Height property.

3. Your declaration,

Dim MyM, MyH As Double

is actually declaring MyM as Variant, not Double. In VB, you need to
specify the type for each variable, even when declaring multiple
variables on one line.

4. Assuming that "SQL" is just the rowsource query for the list box, you
don't need to open a recordset on it -- the list box has a .ListCount
property that tells how many rows it contains. So all your code might
be replaced by:

Dim MyM As Double

MyM = 0.147

With Me.lstInactive
.Height = MyM * 1440 * .ListCount
End With