Agee Software, Inc.

Access Tutorial for Beginners

Microsoft Access is a popular database because it is easy to use. Wizards guide you through many tasks and let you become productive in a very short time. Eventually you will want to do something that the wizard won't do. Congratulations! you have just started the jump from User to Developer. This tutorial is designed to help you make that jump. We will step through creating a form without using the form wizard.

Contact Form

First we must create a new database. Start Access and select File, New, highlight the New Database icon and click OK. Name the database Contact. Next we will create a table. On the Tables tab, click New, Design View, OK. For our example, we will create a Contacts application that contains information you would normally keep in an address book. Type the following under Field Name:
To make sure your table can be read by other applications, we avoid using spaces in field names. Change the ContactID Data Type to AutoNumber. This tells Access to automatically generate a unique long integer value for each record that makes an excellent primary key for this table. Click the Primary Key icon on the toolbar. This primary key guarantees we get one and only record per ContactID.

Click on FirstName and set its Field Size to 15. Click LastName and set its Field Size to 20. Set Street to 40, City to 20, State to 2, Zip to 10, WorkPhone to 14 and HomePhone to 10. Describe each field in the Description column. Documenting the database as you develop it not only makes it easier to maintain, but adds features that make it more user friendly. Save your table design as tblContact when you close the design view.

Open the table and enter a record with your information. Enter another record of test data. Note that Access provides the ContactID. Don't put parenthesis or dashes in the phone numbers. When you finish the second record, close the table.

Next we will start the form. Select the Forms tab in the database window. Click New, Design View, and choose tblContact as the table or query where the object's data comes from. This makes tblContact the RecordSource for the form. Click OK to create the form. To add data fields to the form, select View, Field List. A list of the fields in tblContact appears. You can drag the fields onto the form one at a time or hold the shift key down to select several fields. Position the fields onto the form. The object on the left is a Label and the object on the right is a TextBox. Select the State textbox and adjust it to a more narrow width by dragging the small square handle on the right border. Widen the Street textbox by selecting it, holding the shift key down and pressing the right arrow. Shift-Left makes the text box more narrow. Select all the labels by moving to a point below the HomePhone label, click and hold the left mouse button and move the upper left corner of the select box to a point above the ContactID label. This selects all the labels. Left align the labels by selecting Format, Align, Left. After aligning the labels, click one of the textboxes. Hold the shift key and click each textbox to select all of them. Selct Format, Align, Left to left align the text boxes. Select all the text boxes again and select Format, Vertical Spacing, Make Equal to evenly space the textboxes. Position the right edge of the form and the bottom of the form to frame the fields evenly. Close the form and save it as frmContact.

Open the form and you should see the first record you entered. If you change the value in a field and move to another field, the change remains. To move to another record, use the the navigation buttons at the bottom of the form. Like the controls of a VCR, the far left button moves to the first record, the left button moves to the previous record, the right button moves the next record. The next button moves to the last record and the button with the * asterisk moves beyond the last record to append a new blank record. Click the >* button and add a new record. Moving the record pointer to another record or closing the form saves the record and your changes. If you don't want to save the record, press the Esc key on the keyboard before moving to another record or closing the form. To delete a record, click the vertical Record Selector bar at the left of the form and press the Del key on your keyboard. Click Yes to confirm that you want to delete the record. Practice adding, changing and deleting records until you are comfortable with this process.

Let's make our form a little more user friendly. Select the Forms tab on the database window, select frmContact and click Design to enter the design mode. If you don't see the Form properties, right click on the blue area of the form header and select Properties. On the Properties sheet, select the Format tab and enter Contact in the Caption property. Since the user cannot update the ContactID, let's lock it. Select the ContactID textbox and the properties sheet changes to show the ContactID properties. On the Format tab, change the SpecialEffect property from Sunken to Etched. On the Data tab, change Enabled to No and Locked to Yes. On the Other tab, change the name from ContactID to txtContactID. It is a good habit to name textboxes with the prefix of txt to prevent confusing field names and textbox names. Select each of the other textboxes one by one and give the Name property a suffix of txt. Save and close the form and test your changes. Note that the form caption says Contact instead of frmContact and cursor focus is on FirstName instead of ContactID.

Our form works fine with only a few records, but if we have a dozen or more contacts, selecting the record we want will be cumbersome. Let's add a combo box at the top of the form to select our contacts by name. Enter the developer mode for the form and add a form header by selecting View, Form Header/Footer from the menu. Select the ComboBox from the ToolBox, move the cursor to the form header and click to create the combo box. Cancel the wizard. Remember we're roughing it. Change the combobox label caption to Contact. Change combobox name property to cboContactID. On the combobox data tab, set the RowSource property to qryCboContactID. We'll create it later. On the format tab, set the ColumnCount property to 2 and the ColumnWidths property to 0;1.5 to set the two columns to 0 and 1.5 inches wide respectively. Click on the form header and set its BackColor property by clicking the ... at the right and choosing a color from the color picker form. This helps the user understand the different areas of the form; choose a record in the header and edit it in the detail area. Save and close the form.

When we test the form, we see there are no values in the Contact combobox because we still need to create the RowSource query. Select the Queries tab of the database window. Click New, Design View, select tblContact, click Add and click Close. In the query designer, double click ContactID to make it the first field of the query. In the field, enter:
    FullName:FirstName & ' ' & LastName
To sort the names by LastName and FirstName, add LastName in field 3 and FirstName in field 4. Set the sort order of field 3 and 4 to Ascending and uncheck the Show checkboxes. We don't need to see the fields to sort on them. Save the query as qryCboContactID and close the query design view. Open the query to test it. The first field should be ContactID and the second field should be FullName with the contact's first and last name. The names should be sorted by last name. Close the query and test the form again. This time the combobox shows names but the form isn't synchronized to show the record you selected.

To synchronize the form to the combobox, we need to create a new query for the form RecordSource. This query should contain all the fields in tblContact. Create the query by selecting the Queries tab, New, Design View, tblContact, Add and Close as you did before. Double click each of the nine fields to add it to the query. Under the ContactID field, enter the following Criteria:
Save the query as qryContact and close it. Test it. If the form is open and you have a value in the Contact combobox, you should see one record in qryContact. If you get more than one record, make sure you have the ContactID criteria set to Forms!frmContact!cboContactID. If you see no records, verify that the combobox is named cboContactID and your form is named frmContact. Verify the BoundColumn property of the combobox is 1 and the first column of qryCboContactID is ContactID. When the query shows the correct record, we are ready to write one line of VBA code. Open the form in the design mode and select cboContactID. On the properties sheet, select the Event tab and click the AfterUpdate property line. Pull the combobox down, select [Event Procedure] and click the ... on the right. In the Form_frmContact:Class Module between the Private Sub cboContactID_AfterUpdate() and End Sub lines, enter
Close the module window. Select the form properties sheet by clicking the blue area at the top of the form. Change the RecordSource from tblContact to qryContact, save the form and test it. The form should show the second record when you select it from the combobox. If it doesn't, verify you changed the form RecordSource to qryContact. Troubleshoot the combobox by pressing Ctrl-G to see the debug window. In the debug window, type
? Forms!frmContact!cboContactID
When you change the combobox, the number returned should change. Verify you typed Me.requery in the cboContactID AfterUpdate event module.