Microsoft Access Form Tips

3 Top Techniques for Rapid Forms and Reports

Make your lines straight on Reports or Forms

How to refresh the combo list in a subform by activating the main form

How to change the Tab Order on a Form

Automatically setting left to right top to bottom tab using auto order

Tell Access where to tab to when you press tab whilst in the last field of the current record

Stop a control from receiving the focus when tabbing through a form

Create inaccessible fields on a form

Using a self-lookup combo box

Hiding the Scroll bars and Navigation buttons in your Forms

Open a Combo Box automatically

How can I remove the Menu Bar from my Form

How to Obtain Values from the Previous or Next Record

How can I prevent Users from adding records to my forms?

How to make a form close automatically after a set amount of time

3 Top Techniques for Rapid Forms and Reports
Don't you find it annoying how long it takes aligning controls to get forms and reports looking tidy? Well, the good news is there are several tips you can follow that will speed the process. I am assuming you are using Access 97.

Aligning controls with menu	Highlight the controls you want to align and use Format, Align and then choose left, right, top or bottom.
Create Alignment toolbar	Even better than above, create a "SpeedyForms" toolbar, that has 4 buttons covering the 4 alignment options mentioned above. Enable this toolbar when you are looking to tidy your forms and reports. It really does save you a lot of time.
Keyboard shortcuts for moving Controls	Its often much easier to align the Controls when you use the keyboard to "nudge" the object into place. Highlight the Control, hold down the Ctrl key and then use the navagation arrows to move in the required direction.

Make your lines straight on Reports or Forms
Select VIEW and TOOLBOX. Then click the line tool in the toolbox and drag to create the line. Using the CTRL key with the arrow keys will move the line and using the SHIFT key with the arrow keys will change the length or angle of the line.

How to refresh the combo list in a subform by activating the main form
You must use the Requery method within a statement that refers to the control on your sub-form from the main form.

If you place the code module in the main form use the following syntax:

Me![Sub-form Name].Form![Control Name].Requery

Or this can be shortened to:

[Sub-form Name].Form![Control Name].Requery

If the code is in the sub-form's code module then the syntax can be shortened even further to:

Me![Control Name].Requery

Which can be shortened again to:

[Control Name].Requery

How to change the Tab Order on a Form
Open the form in Design View. Then select VIEW, TAB ORDER and then select the SECTION that you want to change. Next click the selector for the control you want to move. (You can use click and drag to select more than one control at a time.)

Click the selector again and drag the control to the required location in the list. Now click OK.

You need to go to Form View to check the tab order. In design view the tab order will always be in the order that you created the controls.

Automatically setting left to right top to bottom tab using auto order
As above, first select VIEW, TAB ORDER and then select the SECTION that you want to change. Then simply click AUTO ORDER and then OK

Tell Access where to tab to when you press tab whilst in the last field of the current record
Open the form in Design View. Open the form's property sheet and amend the CYCLE property to one of the following:
· All Records - the focus moves to the first field in the next record.
· Current Record - the focus moves back to the first field in the current record.
· Current Page - the focus moves back to the first field in the current page.

Stop a control from receiving the focus when tabbing through a form
Set the TabStop property to No

Create inaccessible fields on a form
Sometimes you may wish to make a field in a form look like a normal field but it is completely inaccessible to the user.

To do this set the ENABLED property to No and the LOCKED property to Yes. Your field will not grey out but the user will not be able to do anything with it.

Using a self-lookup combo box
If you want to speed up data entry in fields such as 'Town' or 'County' on a 'Customers' form (for example) you can have users input information through a combo box rather than a text box.

You want the combo to get its information from the customer towns already typed in. To do this you should fill the combo using a query or SQL statement. To make sure the query that extracts the information only picks out each town once, set the query's 'Unique Values' property to 'Yes'.

Also, to make sure that no records which don't contain a town are returned, set the Town criteria to 'Is Not Null.'

And finally, set the sort order for the query so that the towns are listed in alphabetical order. The combo will facilitate quick entry of town data as any town previously entered with a previous record can be quickly added to a new record.

However if you add a new town it won't show up in the combo list straight away. To update the combo as soon as you enter a new town, you need to run a small amount of code in response to the 'After Update' event. Create an event procedure for the 'After Update event' and enter the following code:

Me.Refresh

This will refresh the data in the form and update the combo's data.

Hiding the Scroll bars and Navigation buttons in your Forms
Open the forms properties by selecting VIEW and PROPERTIES. Then for the ScrollBars property enter Neither and for the NavigationButtons property enter No.

It is just as easy to hide RecordSelector buttons, and the Minimise, Maximise, and Restore buttons in the right hand corner of the form. Simply set the RecordSelector, MinButton, and MaxButton properties to No respectively.

To do this for a subform you need to open up the subform in Design view and open up the properties window from there. You cannot hide scroll bars or navigation buttons for a datasheet.

Open a Combo Box automatically
This is particularly helpful if your users need to enter large amounts of data. It causes combo boxes to open when they get the focus by using the tab keys.
There are two ways to do this. Firstly we shall look at the Dropdown method

1. Add a combo box and a text box control to the form

2. Set the combo box's OnGotFocus property to the follwoing event procedure:

Private Sub Employees_GotFocus()
Me!ComboBoxName.Dropdown
End Sub

3. Open your form in Form View and use the TAB key to make sure it works.

You can also use a SendKeys Macro

1. Create the macro and call it OpenComboBox. The syntax for the macro is as follows:

Action

SendKeys

ActionsArguments

Keystrokes: %{DOWN}
Wait:Yes

2. Now open your form in Design View and add the macro to the OnEnter property of your combo box.

3. Open the form in Form view and test your TAB key.


How can I remove the Menu Bar from my Form
In Microsoft Access 2.0, 7.0 or 97 enter =1 in the MenuBar property of the form.

In Access version 1.x enter =1 in the OnMenu property of the form.

How to Obtain Values from the Previous or Next Record
You can use this DLookup() expression if you have a table with a field ID of numeric data type, in sequential order, not missing any numbers.

To look up the value in the previous record of a Form, put this code in the text box's ControlSource property:

=DLookUp("[Field]","Table","[ID]=Forms![Form1]![ID]-1)

The following code looks up the value in the next record:

=DLookUp("[Field]","Table","[ID]=Forms![Form1]![ID]+1)

You can use a similar piece of code to look up values in the previous or next record in a Report. Input the code in the ControlSource property of the text box:

For the previous record, use

=DLookUp("[Field]","Table","[ID]=Reports![Report1]![ID]-1)

For the next record, use

=DLookUp("[Field]","Table","[ID]=Reports![Report1]![ID]+1)

When the current record is the first record, using the "-1" will return a null value as will using "+1" on the last record. You can also amend this code to say produce the the fifth previous record by entering "-5" in place of "-1".

How can I prevent Users from adding records to my forms?
If you can't get hold of better behaved users then you will need to use the Default Editing property of the Form.

Firstly open your form in Design View and display the property sheet (VIEW, PROPERTIES). In the Default Editing property set it to Can't Add Records. Save the Form and go back to Form View to make sure it works.

How to make a form close automatically after a set amount of time
First of all you will need to create a macro to execute the Close action on the form.
Now go to the Design View of the Form and open the Properties sheet.

In the TimerInterval property enter the length of time you want the form open for. This should be in milliseconds, so for instance if you want the form open for 8 seconds enter 8000, for an minute enter 60000.

You now need to attach your macro (to execute the Close action) to the OnTimer event property of the form. You may need to go back to the TimerInterval property to adjust your timing a little to get it just right.

Click the link if you need an Access database repair. Fast service, no obligation, free quote!