Preparation
Data Sources
Before creating a form, careful thought has to be given to determine
what tables and/or queries will be needed for the form.
- For a form that maintains a master file, such as Customers,
Vendors, etc, usually a single table will suffice.
- Other more complicated forms, such as an Invoice form will require
data from more than one table, in which case a query is required.
Embedded Query
If only a single table is required, it is recommended to use the table
name when initially creating the form. After the form is created,
you can change the form to use a query that is embedded
within the form. The same thing can be achieved if
multiple tables are required for the form. Initially create
the form specifying a single table name. Then change the form
to use an embedded query and add the other necessary tables at that point.
Standard Look and Feel
To provide a consistent interface for the user, a definition should be
created for what standard colors, fonts and formats should
be used. Be conservative in using colors. There can be a tendency
to use too many colors. Colors should only be used to emphasize or draw attention.
Form Wizard
The quickest way to create a new form is to use the form wizard. This will
step you through most of the necessary options. Most likely,
you will modify the format and add controls and other options.
top of page
Form Properties
Every object within the form has properties which control how the object functions.
The properties that control the overall form usually need to be changed.
To view form properties...
- Click on the small rectangle box in the upper left corner of the form
- A property window should open labeled form
- Make sure the All tab is selected
Caption
The caption property is the name that will display on
the title bar when the form is executed. You can place a
label field within a form such as 'Customer Maintenance',
which can take up value real estate space or use the caption
property to label the form. If a user minimizes the form,
they will see the caption value and not the label field
within the form. We recommend the 'KISS' concept of
not showing anything more than is necessary for the user.
The less to read to the better.
Views
Edit Controls
Forms can be used to add, change, delete or only display data.
You can control what options are available using the properties:
Allow Edits, Allow Deletions, Allow Additions and
Data Entry. If Data Entry is set to Yes, the form
will open in Add mode and not show previous entries.
Records/Navigation Controls
Depending on the form usage, there are times when you may want to
turn off record selection and navigation properties. These consist of:
- Scroll Bars
- Record Selectors
- Navigation Buttons
- Min/Max Controls
The best way to understand these properties is change them one
at a time and view the resulting conditions when you open the form in form view.
Grid Size
top of page
Field Types
| Type |
Used For |
Key Feature(s) |
Additional Comments |
Label |
Title, Label or Information |
Controls nothing |
All fields do not need an associated label. Should only
be used to aid the user. Example: The fields City, State and Zip can have one label for all three fields. |
Text (It makes more sense to think of this as a 'Field' control.) |
Display/Maintain a data field that is bound or unbound.
A bound field is tied to a table. Unbound fields are usually used to display calculated values. |
Controls all aspects of data field: display format, editing, validating, etc. |
|
Toggle |
Yes/No options |
User can click or use space bar to select |
|
Radio Button (Option) |
Yes/No options or List |
User can click or use space bar to select |
|
Check |
Yes/No options or List |
User can click or use space bar to select |
|
Option Group |
Assign a single choice from multiple options to a single data field. |
User can click or use space bar to select |
|
Combo |
Drop down box to allow record selection from tables or queries |
Data can be sorted and/or filtered.
The trick to creating succesful combo boxes is understanding
the control properties.
The example in the next column assumes you want to look
up a ship via code to assign to a Invoice where the
combo box has two fields: ShipViaID and ShipDesc. ShipDesc
is sorted ascending. (Row Source)
You don't want to display the ShipViaID to the user, but
you need to 'bind' the Invoice field for ShipVia. (Bound
Column) |
- Row ource Type
- Row Source
- Column Count
- Column Heading
- In this example the user will only see one column, so set this to No.
- If the combo box displayed several columns, you would probably set this to Yes
- ColumnWidths
- Set to 0";1.5"
- The first column is set to 0, to hide the ShipViaID
- The 1.5" represents the width to use for ShipDesc
- Bound Column
- List Width
- Set to 1.5"
- Should
always be the sum of all Column Width
|
List |
Drop down box to allow selection from a hard coded list |
Program changes are required to add/delete items to list. |
List
values normally contain 2 pieces of information for
each item in the list.The first value is a numeric value.
The second value is a description. Example:
1, Choice 1
2, Choice 2
3, Choice 3
etc. |
Command Button |
Used to perform an operation. |
Can open a form or report. Update data, validate data, etc. |
Usually the On Click property is used
to call a macro or function to execute the command |
Image |
Display an image |
This is a static image for appearance only. |
|
Unbound Object Frame |
Display an unbound OLE object |
A spreadsheet could be displayed within the form |
Contents of object do not change with records. |
Bound Object Frame |
Store/Display Image from an image field in a table. |
|
|
Page Break |
Set page controls for a long vertical form. |
Allows user to use page down/up keys to navigate form. |
|
Tab Control |
Set tabs to select different views with in a single form.
Handy when there are too many fields to fit on the screen |
Helps to organize similar type of data and makes it easier
for the user to understand |
Don't confuse this with the tab key. |
Subform |
for a selected customer. |
Data in subform must have linkable fields. |
See below |
Line |
Display a line that is used to create a visible separator |
Can change the type, size, color |
|
Rectangle |
Create a box that is used to create a visible group of similar items. |
Usually select 'Format, Send to Back' to allow the viewing
of objects displayed within the rectangle. Can change type, size and color. |
|
top of page
Default, Tab, Locked, Enable, Visible
This section only applies to form objects where user entry could be performed.
| Property |
Used For |
Key Feature(s) |
Additional Comments |
Default value |
A default value to be used when adding a new record. |
Speeds entry. Ensures that a field contains a value and is not set to null. |
Enter =date() for today's date. |
Tab Stop |
To control if tab or shift tab should stop at field. |
Convenient means to skip infrequently used fields or defaulted/calculated fields. |
|
Locked |
Allow user to view data, but don't allow user to change data |
Colors/format can be controlled |
|
Enable |
Allow user to view data, but don't allow user to change data. |
Can't override the standard 'disabled Access' colors or format |
|
Visible |
Hide the display of a field |
Hide meaningless data from user. |
You can still calculate or reference a hidden field if
necessary with internal programming controls |
top of page
Save Form
As you make modifications, it is important to periodically save you work.
Access and Windows have been know to
'die' without providing any warnings. You can click
on File, Save or the easier method is to use the command Control S.
top of page
Save Record
To force the saving of a record using VB...
RunCommand acCmdSaveRecord
top of page
Size to Fit
When the form is in form view mode, you may notice the window size is either
too large or too small. The easiest way to fix this is click on
Window, Size to Fit. Make sure that in design
view, you have not left excess space around the sizes of the form.
top of page
Tab Order
A user will typically use the 'tab' key to navigate the form. During your
modification process, where fields may be deleted or added, the
tab order may not be correct. To fix or check the tab order, click on View, Tab Order
top of page
Sub Forms
There are situations, such as invoice entry form, where you will need a
subform with in a form. In this case, the subform would be used
to enter the line items for an invoice. Here are things to consider when using a subform.
- Create the main form first
- Create the subform as a separate form
- Insert the subform within the main form (just like creating a text
object. The short cut is to open the main form in design mode.
Drag the subform from the database forms window on to the main
form where you want it located.
- Click once on the subform to bring up the properties window for the subform controls.
- Make sure the name of the subform object is meaningful
- Linking
- In the Link Child Fields property, enter the field names
that will be used to 'link' the subform data to the main form data
- In the Link Master Fields property, enter the field
names that will be used to 'link' the main form data to the sub form data
- Normally the field names will be the same for both entries.
- It is important that the fields used for linking are set up
as 'indexed' in the table structures. This is not mandatory,
but will help to increase execution speed.
top of page
Input
Masks
| Mask Character |
Description |
Entry Required? |
Sample |
Required Data |
What can be entered |
| L |
Letter |
Yes |
LLL |
3 letters |
abc
ABC |
| ? |
Letter |
No |
??? |
Up to 3 letters |
abc
AB |
| 0 |
Digit 1-9; + and - signs not allowed |
Yes |
000 |
3 digits |
123 |
| 9 |
Digit 1-9; + and - signs not allowed |
No |
999 |
Up to 3 digits |
123
12 |
| # |
Digit 1-9 or space; + and - signs allowed |
No |
### |
Up to 3 digits or spaces |
123
12
1 3 |
| A |
Letter or Digit |
Yes |
AAA |
3 letters or digits |
ABC
123
A2B |
| a |
Letter or Digit |
No |
aaa |
Up to 3 letters or digits |
ABC
12
a2 |
| & |
Any character or space |
Yes |
&&& |
3 characters or space |
$%*
A$1
% $ |
| C |
Any character or space |
No |
CCC |
Up to 3 characters or spaces |
##
A$1
a c |
| < |
Converts all following characters to lowercase |
|
<LLL |
Enter ABC |
abc |
| > |
Converts all following characters to uppercase |
|
>??? |
Enter abc |
ABC |
| Password |
Displays asterisks as characters are typed. |
|
PASSWORD |
Enter abc |
*** |
top of page
SQL Logic to select recordset
To select a specific set of records...
Dim db as Database
Dim rset as Recordset
Dim SQLStmt as String
Set db=CurrentDB()
'Use the following if matching text fields
- ends with double quote, single qoute, double quote
SQLstmt = "SELECT * FROM tablename WHERE [fieldname] =' " & Me![fieldname2] & " ' "
'Use the following if matching numeric fields
SQLstmt = "SELECT * FROM tablename WHERE [fieldname] =" & Me![fieldname2]
Set rset = db.OpenRecordset (SQLStmt)
if not rset.eof then
'(logic goes here)
end if
rset.close
top of page
Allow Edits
To allow editing a field or not...
Allowedits = true or false
top of page
Bookmarks
Handy logic to use to find a record when using a combo box...
Create an event for afterupdate
Me.RecordsetClone.findfirst "[fieldname to match] = " & Me![comboboxfieldname]
Me.Bookmark = Me.RecordSetClone.bookmark
docmd.gotocontrol ([fieldname on form])
top of page |
