Form Control Notes

Forms opening restored (not maximised)

If there is a current form open that is maximised then the next open will be maximised, so open form1, maximise, when switching, open form2 before closing form1

Note that if you are hiding form1, this still seems to count as a close of form1 for the pueposes of opening form2 maximise, so open form2 then hide form1


Combo Box:

Access visible selection (where bound column is different): cmb.text

Access specific column: cmb.column(n) where n >= 0


Set defaultselection prorammatically:

cmb.defaultvalue = chr(34) & "xxx" & chr(34)



Leaving Echo Off

You can significantly speed up some code by turning Echo off. In a macro, Echo is automatically turned on again when the macro ends, but this is not the case in VBA code. This means that the machine is left in an apparently non-responsive state if you forget to turn Echo back on again. If your code generates an error while Echo is off, the error message is suppressed, and you will not know why your program has stopped.


In any procedure that turns echo off, include Echo True in the exit recovery section:

    Sub MySub
On Error Goto Err_MySub
' Some code here.
DoCmd.Echo False
' Rest of your code here.

'Turns Echo on again HERE: works even after an error.
DoCmd.Echo True
Exit Sub

Debug.Print Err.Number & ": " & Err.Description
Resume Exit_MySub
End Sub


While developing, use an AutoKeys macro that turns Echo on.

- Macro - New

- Click Keys Button and code +^E or for name similar (key encoding for SHIFT-CTRL-E), save macro as AUTOKEY



There are 1440 Twips per Inch. If you are using metric, there are approximately 566.9 Twips per Centimeter

Us the Move method of the form.  


Dim lngHeight As Long, lngWidth As Long

lngHeight = 1440 * 5

lngWidth = 1440 * 7

Me.Move 0, 0, lngWidth, lngHeight

This will move the upper, left corner of the form to the top, left of the

Access window then size the form width to 7" and form height to 5". The

value is in Twips. There are 1440 Twips per Inch. If you are using metric,

there are approximately 566.9 Twips per Centimeter. If you want the form to

fully fill the Access window, you could maximize it. However, if you

maximize one form, all forms will be maximized until you issue a Restore.

You could also size the form to fill the Access window without maximizing

it. To do this, you need API calls to get the size of the Access window and

allow for the toolbars. An example of doing this is in the link below. It

gets the information to put a picture in the Access window, so you don't

need all of the code in the example, just enough to get the window size.

If your form is the only open, visible window inside Access, you can also

scale it to the Access window by using the Window menu Tile command.

RunCommand acCmdTileHorizontally


Wayne Morgan

MS Access MVP

"Will" <> wrote in message




    Call frmAccessionManage.set_form



DoCmd.OpenForm "Form2", , , , acFormAdd

DoCmd.Close acForm, "frmMain", acSaveYes




Question:  In Access 2003/XP/2000/97, how do I set the background color of a command button?

Answer:  Unfortunately, you can't change the back color of a command button. As an alternative, you could create a label and have it behave as a button.

To do this, create a label. Right-click on the label and view its properties.

Set the Back Color property of the label to the color that you want. Then set the Special Effect property to Raised.

Then code it's Mouse Down event to:

[LabelName].SpecialEffect = 2

Code the Mouse Up event:

[LabelName].SpecialEffect = 1

Code the OnClick event as if it was a command button.

Center the label text horizontally using the text alignment tool button or setting the Text Align property to Center.

If you want to align text vertically, Press Shift + Enter then type in the label text.

It will look and act like a command button.


Disable all controls on a subform

You are right this is really too simple to ask.

Simply add the third control to your navigation subform. This can be
for example blank option group without label which looks like frame and
call SetFocus method for this control before disabling buttons.

Example of working code:

Private Sub btn1_Click()


Me.btn1.Enabled = False

End Sub

Private Sub btn2_Click()


Me.btn2.Enabled = False

End Sub

The form contains 3 controls: 2 buttons (btn1, btn2) placed in the
frame of option group (Frame2). Click on the button disables it. All
two buttons may be disabled without getting error message.

The problems with dummy control are the folowing:

1) not all controls can actually receive focus (e.g. Label, Image etc)

2) the control has to be visible to receive focus.

Option group frame control does fit these needs or you should use any custom control.



Subforms are not in the forms collection!