Access Cookbook - Ken Getz [46]
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.MoveLast
For small recordsets, this will be fast but also unnecessary, because the RecordCount property will already be accurate. For large recordsets, this might take a few seconds to calculate and will make opening your form seem slower.
See Also
For more information on using DAO in Access databases, see How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.
2.7. Size a Form's Controls to Match the Form's Size
Problem
Windows users have become accustomed to resizing forms on their screens. A professional-looking application will proportionally resize the controls on a form when you stretch or shrink that form. You'd like to be able to resize your forms while the application is running and have the controls on the form react appropriately. For example, the Database Explorer window's list box expands when you expand the window. How can you do this on your own forms?
Solution
Because Access can notify your application when the user resizes a form, you can attach code to the Resize form event and react to the change in size. Access also triggers this event when it first draws the form, so you can place your controls correctly then, too. Base your calculations on the form's InsideWidth and InsideHeight properties.
Load and run the form frmExpando in 02-07.MDB. Resize the form and watch the size of the large text box. Also notice the positions of the two command buttons. Figure 2-13 shows the form in design mode, and Figure 2-14 shows the form sized to different proportions. Though it's perfectly reasonable to change the size of all the controls, this form does not. It uses three different techniques:
Figure 2-13. frmExpando in design mode
Do nothing
The label above the text box doesn't change at all as you resize the form.
Change position only
The two command buttons move with the right edge of the form, but they don't change size.
Change size
The large text box changes its size to match the size of the form.
Figure 2-14. frmExpando at runtime, with different proportions
The code that does the work in this case is specific to the particular form. Follow the steps below to create a form similar to frmExpando. Once you've gone through these steps, you should be able to expand on the concepts (pun intended) and create your own self-sizing forms.
Create a new form and create controls and properties as shown in Table 2-5.
Table 2-5. Controls and their properties for frmExpando
Control type
Property
Value
Label
Name
lblSample
Left
0.1 in
Top
0.0833 in
Width
1.7917 in
Height
0.1667 in
Caption
Enter some text
Text box
Name
txtEntry
Left
0.1 in
Top
0.3333 in
Width
1.8 in
Height
0.8333 in
Command button (OK)
Name
cmdOK
Caption
&OK
Left
2 in.
Top
0.3333 in
Width
0.6979 in
Height
0.25 in
Command button (Cancel)
Name
cmdCancel
Caption
&Cancel
Left
2 in.
Top
0.6667 in
Width
0.6979 in
Height
0.25 in
Place the following code in the form's Resize event procedure. You can copy this code from frmExpando's.
Private Sub Form_Resize( )
Dim intHeight As Integer
Dim intWidth As Integer
Dim ctl As Control
Static fInHere As Integer
Const acbcMinHeight = 2000
Const acbcMinWidth = 4000
' Optimize a bit. If you're already executing the code in here,
' just get out. This can happen if you're in here because of an
' auto-resize (if you try and size the form too small).
If fInHere Then GoTo ExitHere
fInHere = True
On Error GoTo HandleErr
' Get the current screen coordinates.
intHeight = Me.InsideHeight
intWidth = Me.InsideWidth
' Make sure the width and height aren't too small. If they are,
' resize the form accordingly. This could force Access to call
' this sub again, so use fInHere to avoid that extra overhead.
If intWidth