Creating the Forms


While you could create all the forms needed for this application from scratch by selecting Design View in the Access Form Wizard and setting all the form properties manually, there is an easier way. I’ll describe how to use my Design Schemes add-in’s wizards and builders to eliminate a lot of the repetitive work involved in creating forms with a uniform and professional appearance.

Using the Design Schemes Add-in

First, copy the Design Schemes Add-in’s library database (Design Schemes.mda) to your AddIns folder (usually C:\Documents and Settings\Administrator\Application Data\Microsoft\AddIns). Open the Toy Workshop database (or any other database), select Tools|Addins to open the Add-ins menu, and then select the Add-In Manager selection. The Add-In Manager dialog opens, as shown in Figure 2.2.

click to expand
Figure 2.2

There should be a Design Schemes entry; if not (perhaps because it was placed in a nonstandard location), click the Add New button and browse for the file. Once it appears in the list of available add-ins, select it and click Install; an appears next to the Design Schemes selection to indicate that it is installed. Close the dialog to complete the installation.

You won’t see any new entries on the Add-ins menu because this add-in contains only wizards and builders, no menu add-ins. To create a form with the Design Schemes add-in, click the New button with Forms selected in the object bar, and select Custom Form Wizard from the list of selections (as shown in Figure 2.3).

click to expand
Figure 2.3

Don’t select a data source on this screen; this is done on the next screen in this wizard, where you can choose the type of form to create. This screen is shown in Figure 2.4.

click to expand
Figure 2.4

Creating a Standard Main Form

We’ll start with creating a standard form to use as a main form, with subforms. The Customers form is of this type, so I selected tblCustomers in the Custom Form Wizard and accepted the default Standard Form selection, because this table needs a Single Form–type form for entering and editing data. The next screen (shown in Figure 2.5) offers a choice of four color schemes, which use standard colors that will display correctly even on low-resolution monitors.

click to expand
Figure 2.5

If you want to select one of the four standard color schemes, click its option button and then click the Finish button. The new form will be created, with the specified record source table or query, and opened in Design view. To create a custom color scheme, click the Create your own color schemes or control formats option in the Color Scheme Type option group, and then click the Next button. The next screen has a number of controls for setting every detail of color, font, and other characteristics of the form and controls on the form, as shown in Figure 2.6

click to expand
Figure 2.6

The command buttons in the upper right of the screen are used to change the color of various parts of the form and form controls. Click one of these buttons to open a standard color palette (similar to the one opened from the Build button of a color property), and select the color to use; the miniform on the lower left of the screen will reflect your choice. You can also open the color palette by clicking on a portion of the miniform.

Another standard dialog lets you select the font, weight, and color for controls on the form.

The option group in the middle of the form lets you select the alignment for labels, and the bottom option group gives you a choice of special effect for textboxes. These choices will also be displayed in the miniform.

Important

Although Access lets you select the Raised special effect for labels or textboxes, I don’t advise doing this. Araised textbox or label looks exactly like a command button, and users will probably try to click it and expect something to happen.

Figure 2.7 shows the custom color scheme page of the Design Schemes Wizard, after all selections have been made.

click to expand
Figure 2.7

The font, weight, color, and special effect selections for textboxes and labels will be applied to all the controls on the form. Select the choices you want for the Detail section of the form; the relatively few controls in the header and footer can be changed manually if needed.

After clicking the Finish button, the new form is created, with the colors and special effects you have selected. Figure 2.8 shows a new form with a custom color scheme. The form has some code already entered into its attached module; this code will be discussed in the next section.

click to expand
Figure 2.8

There are two field list shortcuts: Use the F8 function key to open the field list, and double-click its title bar to select all the fields in the list.

Adding Controls to a Form

Open the field list and drag all the fields to the form, as shown in Figure 2.9.

click to expand
Figure 2.9

The textboxes and labels all have the custom properties you selected; the single label in the header can be changed to white for better contrast, and edited to replace the underscore with “Customer.”

The new form requires several refinements (in no particular order):

  • Make the AutoNumber CustomerID field locked, with a different color background to tell the users that it can’t be edited. This is done with a builder that is part of the Design Schemes add-in. To invoke the builder, right-click the CustomerID field and select Build from its context menu; the Lock Wizard dialog appears (shown in Figure 2.10), where you can select the Locked option. The textbox can also be made smaller, since the ID won’t be very long, and left-aligned, to make it match the other controls.


    Figure 2.10

  • Move controls around to save space on the form. Delete the Contact Last Name label, move the ContactLastName textbox up next to the ContactFirstName textbox, and edit that control’s label to read Contact Name (First/Last). Similarly, delete the State/Province and Postal Code labels, move the PostalCode and StateOrProvince textboxes up and to the right of the City textbox, and edit the City label to read City/State/Zip. Because customer addresses are all in the United States, the PostalCode and StateOrProvince textboxes can be made smaller too.

  • Make the Company Name, Contact Title, Department, Notes, and Web site textboxes wider.

  • Move the Contact Title textbox and label up under the name controls.

  • Move the Notes and Web site controls up under the City controls

  • Move the Close button to either the middle or the right side of the footer section (I prefer the right side, because sometimes there are other buttons in this section).

  • Enter Customers into the form’s Caption property.

The redesigned form is shown in Figure 2.11.

click to expand
Figure 2.11

Applying a Naming Convention

It’s a good idea to name database objects (tables, queries, forms, reports, macros, and modules) with a distinctive three- or four-letter tag (prefix), to identify the object type when selecting objects from drop-down lists, or working in VBA code. Controls on forms and reports should also have distinctive tags to indicate the control type, and to prevent circular reference errors resulting from controls having the same name as their bound fields. For the full process of applying the Leszynski Naming Convention (LNC) to all objects in an application created without using a naming convention, see Chapter 10, Moving Old Data into the New Database.

For this chapter, we have the much simpler task of using the LNC right from the start. For naming the six main database objects, the tags listed in the table below are used. (See Chapter 10 for a more extensive table with more specialized tags for subvarieties of database objects).

Object

Tag

Form

frm

Form (dialog)

fdlg

Form (menu)

fmnu

Form (subform)

fsub

Macro

mcr

Module

bas

Query (any type)

qry

Report

rpt

Table

tbl

For this chapter, only a few form tags are needed. Standard forms will be given the tag frm, and subforms fsub.

Thus, the newly created form is saved as frmCustomers. This is a good time to give all the controls names with appropriate prefixes to indicate their control types, and this can be done easily using a builder that is part of the LNC Rename add-in.

First, install the LNC Rename add-in (if you don’t already have it installed). This add-in is available as LNC Rename Add-in.zip (for Access 2000 and up) from www.wrox.com. After decompressing the zip file, copy the LNC Rename.mda file to the Add-ins folder (usually C:\WINDOWS\Application Data\ Microsoft\AddIns), and install it from the Add-in Manager in any database, as shown in Figure 2.12.

click to expand
Figure 2.12

The LNC Rename add-in contains several menu add-ins, for use in renaming objects in an existing database. However, for use in a newly created database, only the builders in the LNC Rename add-in are needed. One builder (LNC Rename All Controls) renames all the controls on the currently open form; another (LNC Rename Current Control) renames the currently selected control on a form or report. To rename all the controls on frmCustomers with LNC tags, click the Detail bar (it turns black), and open its properties sheet.

Important

The F4 function key is a shortcut for opening the properties sheet. Unfortunately, it is not a toggle key, so you still have to click the Close button to close the properties sheet.

The properties sheet’s title bar should say Section: Detail. If it doesn’t, click the Detail section bar again, making sure that it is highlighted. Click the small Build button to the right of the Name property to open the Choose Builder dialog. Select the LNC Rename All Controls selection, and click OK to start the builder. (You may see other builders in this dialog, depending on which add-ins you have installed.)

First, you will be asking if you want to save the original control name to the control’s Tag property. For a newly created form, there is no particular need to do this, so just click No in this dialog. Next, you will be presented with a series of dialogs (one for each control on the form) showing you the original control name and the proposed new control name with the appropriate LNC tag. Figure 2.13 shows one of these dialogs, for a bound textbox, which has the same name as the field to which it is bound (as is the case when you drag a control to a form from the field list).


Figure 2.13

This is a good example of why you need to rename controls with tags. If you left the control with the same name as its field, you could get reference errors when using the field name or control name in code or queries. Click Yes to accept the name change, and proceed through all the controls this way. Generally, you can accept all the proposed name changes; the only exceptions are textboxes with calculated expressions, or labels with very large captions. In those cases, click No and enter an appropriate control name manually. When you have processed all the controls on the form, you will get an “All Controls Renamed!” success message.

If you later add more controls to the form, you can rename them individually by clicking the Build button next to the control’s Name property in its properties sheet. In this case, select the LNC Rename Current Control builder from the Choose Builder dialog, and accept (or, rarely, manually edit) the proposed new control name. Since this builder is the default choice, generally renaming a newly created control involves only clicking the build button and pressing Enter twice.

If you add a number of new controls, you can rerun the LNC Rename All Controls builder for the form and it will rename only the controls that need renaming.

In the next section, I’ll discuss using code behind forms to make forms part of a well-designed application.

Code behind Forms

VBA code is the connective tissue of an application. Without VBA code, users have to open each form or report separately from the database window, and return to the database window on closing it. An application has a main menu (perhaps one created by my Menu Manager add-in, which will be discussed in Chapter 6, Printing Data with Reports) with comboboxes, command buttons, and other controls powered by VBA event procedures that allow users to conveniently select which form or report to open. Similarly, VBA code on a command button or the form’s Close event returns the user to the main menu when a form is closed. In addition to selecting a report to print from the main menu, an application might also include command buttons on forms to open a report filtered for the current form record, or to open another form filtered by data on the first form.

Chapter 7, Writing VBA Code in Modules, covers VBA code in more detail, but I will discuss event procedures for forms and controls to some extent in this chapter. VBA code on various event procedures makes it easy and intuitive to work with the data in the database, while performing various business-related tasks. When you create a new form using my Design Schemes add-in, you get a head start on writing code behind forms, with several event procedures already created for the new form. For a new Standard form, you get the event procedures listed below, with explanation under each procedure.

 Option Compare Database Option Explicit Private Sub cboSelect_AfterUpdate() ‘Written by Helen Feddema 10-17-2003 ‘Last modified 10-17-2003 On Error GoTo ErrorHandler    Dim strSearch As String    ‘For text IDs    strSearch = "[______ID] = " & Chr$(34) & Me![cboSelect] & Chr$(34)    ‘For numeric IDs    strSearch = "[______ID] = " & Me![cboSelect]    ‘Find the record that matches the control.    Me.RecordsetClone.FindFirst strSearch    Me.Bookmark = Me.RecordsetClone.Bookmark ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description    Resume ErrorHandlerExit End Sub 

All the procedures I create have a standard error handler that pops up a message with the error number and description, and then exits the procedure. If needed, a Select Case statement under the ErrorHandler label can be set up, to process specific error numbers appropriately. I use this technique when using GetObject to create an instance of Word, Excel, or Outlook in later chapters in this book.

The comments under the procedure name will have your name and the date the form is created.

Occasionally, when working with add-ins, you will get the error message “Error No. 462: The remote server machine does not exist or is unavailable.” This means that the Automation client has lost contact with the Automation server. When this happens, close the database and reopen it to get a fresh connection.

The cboSelect_AfterUpdate() event procedure synchronizes the form record with the record selected from the cboSelect combobox. I use a record selector combobox on all standard forms, to make it easy to go to the correct record. I use a string variable (strSearch) to synchronize the form’s RecordsetClone with the selected record. The key field (used in searching) might be a numeric field or a text field, and if it is a text field, it needs to be wrapped in quotes when creating the search string. Thus, the standard event procedure created for a Standard form by the Design Schemes add-in has two alternatives for setting the strSearch variable; for a text ID, the combobox value is wrapped in double quotes, using the Chr$(34) function, and for numeric IDs the combobox value is used alone.

Depending on the nature of the key field for the form’s record source table, delete one of these alternatives—in this case, delete the text ID alternative, since CustomerID is an AutoNumber (numeric) field. Next, replace the underscore in the search string definition with the name of the key field (CustomerID). The edited strSearch line is listed below:

   strSearch = “[CustomerID] = “ & Me![cboSelect]

The next procedure is a standard Close button procedure (when creating an application for users who prefer to close forms by clicking on the Close button, place this code in the form’s Close event procedure instead of, or in addition to, the cmdClose button’s Click event procedure).

 Private Sub cmdClose_Click() ‘Written by Helen Feddema 10-17-2003 ‘Last modified 10-17-2003 On Error GoTo ErrorHandler    Dim prj As Object    Set prj = Application.CurrentProject    If prj.AllForms("fmnuMain").IsLoaded Then       Forms![fmnuMain].Visible = True    Else       DoCmd.OpenForm "fmnuMain"    End If ErrorHandlerExit:    DoCmd.Close acForm, Me.Name    Exit Sub ErrorHandler:    If Err.Number = 2467 Then       Resume ErrorHandlerExit    Else       MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description       Resume ErrorHandlerExit    End If End Sub 

This procedure uses the IsLoaded property of a form accessed through the AllForms collection, which replaces the old IsLoaded function provided with the Northwind sample database (prior to Access 2000, you had to use that function). Using this method of checking whether a form is loaded avoids the need to import the IsLoaded function into a database.

The assumption here is that the application will have a main menu, called fmnuMain, which should be opened (or reopened) when the form is closed. Some forms shouldn’t return to the main menu (for example, a form opened from another form may need to return to the form that opened it); in those cases, you can replace fmnuMain with the appropriate form name, or remove the portion of the procedure that opens a form and just close the form with the DoCmd.Close line.

The form’s Current procedure sets the cboSelect combobox’s value to Null; this keeps it from showing the wrong data if the user navigates to another record using the navigation bar.

 Private Sub Form_Current() ‘Written by Helen Feddema 10-17-2003 ‘Last modified 10-17-2003 On Error GoTo ErrorHandler    Me![cboSelect] = Null ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description    Resume ErrorHandlerExit End Sub 

The Load event procedure has only one line of code, to size the form to fit. This ensures that the form won’t open to the wrong size in case it was previously resized. There is no error handler here (only On Error Resume Next) because sometimes running the acCmdSizeToFitForm command causes an error. The On Error Resume Next statement exits the procedure without an error message in case the form can’t be resized (this will happen, for example, if you are stepping through code in the module).

 Private Sub Form_Load() ‘Written by Helen Feddema 10-17-2003 ‘Last modified 10-17-2003 On Error Resume Next    DoCmd.RunCommand acCmdSizeToFitForm End Sub 

The cboSelect combobox starts out with a row source of tblCustomers (the same as the form itself), with Column 1 as the bound field and Column 2 as the displayed field. The aim is to have the form’s key field in column 1 (usually made invisible by setting the column width to zero) and one or more columns of information useful for selecting a record. The default selections are fine for the Customers form—we just need to see the company name in the drop-down list. However, it would be easier to select a company if the names were in alphabetical order, so we’ll need to do a little editing of the combobox’s row source.

To edit the row source, open cboSelect’s properties sheet and click the Build button next to the Row Source property, then click the Yes button on the dialog asking if you want to create a query based on the table. Drag the CustomerID and CompanyName fields to the query grid, and set an Ascending sort on the CompanyName field, as shown in Figure 2.14.

click to expand
Figure 2.14

You can get a preview of what will display in the combobox’s drop-down list by switching to datasheet view from the query designer.

Close the query designer, and click Yes to accept the changes you have made. While in the combobox’s properties sheet, you can adjust the size of the combobox itself or its drop-down list as desired (I made the combobox 2 inches wide, and set the list width to Auto). You can also set the number of rows to display in the drop-down list (I set this property to 16).

To see how the record selector combobox works, save the form, and switch to Form view. Enter a few customers (to save time, I have created a few dummy customers). Drop down the combobox, and select a customer, as shown in Figure 2.15; that customer’s record is displayed in the form.

click to expand
Figure 2.15

Creating and Embedding Datasheet and Single Form Subforms

As mentioned earlier in this chapter, the frmCustomers form needs several subforms. Therefore, the next step is to create these subforms based on tblCustomerPhones, tblCustomerEMails, and tblShippingAddresses. The first two tables have only a few fields, so they are most suitable for displaying in datasheet subforms; tblShippingAddresses has too many fields for datasheet display to be practical, but not enough that a Single Form–type subform is really required, so it could be either a Single Form– or Continuous Forms–type form.

To create a Datasheet form to use as a Customer Phones datasheet subform, select the Custom Form Wizard in the New Form dialog. On the first wizard screen, select the Datasheet form type and tblCustomerPhones as the record source, as shown in Figure 2.16.

click to expand
Figure 2.16

There isn’t a choice of standard or custom color schemes for datasheet forms because these choices aren’t relevant for datasheets, which use the color and font selections made in the Datasheet tab of the Access Options dialog.

Click the Finish button to create the new datasheet form. Drag the PhoneNumber and PhoneDescription fields to the form (CustomerID isn’t needed on the form). The same text will no doubt be entered into the PhoneDescription field for different records (say, “Work” or “Home”), so to save data entry time and avoid typos, the PhoneDescription field should be changed into a combobox. To make the change, right-click the PhoneDescription textbox, select Change To from its context menu, and then Combo Box from the available choices, as shown in Figure 2.17.

click to expand
Figure 2.17

Now we can run the LNC Rename All Controls builder from the Name property of the form’s Detail section, to rename the controls according to the LNC.

It’s best to do any control type changing before running the builder, so you won’t have to change the controls’ names after changing their type.

There is no need to set a subform’s caption (it won’t be displayed anywhere) or to resize the controls in Design view, but you do need to remove the colons from labels, since labels are used for column headings in the datasheet. Edit the labels to remove the colons, add spaces, and make any other changes you need. I just put a space between Phone and Number in lblPhoneNumber, and changed the lblPhone_Description caption to just Description. Save the form as fsubCustomerPhones (fsub is the LNC tag for forms intended for use as subforms).

The cboPhoneDescription combobox now needs a row source; it doesn’t need a separate lookup table (though it could use one, if you prefer); instead, the row source consists of all the entries that have been made into this field already (alphabetized and stripped of duplicates). To create this row source, select tblCustomerPhones for the Row Source property in the combobox’s properties sheet, and click the Build button next to it to open the query designer. Drag the PhoneDescription field to the query grid, and sort it Ascending. Open the query’s properties sheet, and click on the upper pane of the query designer so that the properties sheet’s title bar says Query Properties, and set the Unique Values property to Yes. It’s also a good idea to enter Is Not Null as the criterion for the Phone Description field, to eliminate blank descriptions from the drop-down list. Once some descriptions have been entered, for future entries, you will have a choice of all the descriptions that have been entered so far.

The final touch for datasheet subforms is to adjust column sizes as needed; this is done in Datasheet view, so switch to that view to see how the columns need to be adjusted. Figure 2.18 shows the fsubCustomerPhones form in datasheet view. The column sizes are OK, so there’s no need to change them (they can always be adjusted later on, if need be, after placing the subform on frmCustomers). If you do need to adjust column sizes, just stretch a column out or in using the standard double-headed resizing arrows, then save the form again.


Figure 2.18

tblCustomerEMails also needs a datasheet subform. I won’t go through the steps for creating this subform, which is even simpler than fsubCustomerPhones, since it only needs a single field (CustomerEMail). Since email addresses can be long, stretch out the txtEMail control in datasheet view to make it wider (see Figure 2.19).

click to expand
Figure 2.19

The third subform needed for frmCustomers is based on tblShippingAddresses. Since there probably won’t be more than two or three shipping addresses per customer, I’ll make it a Continuous Forms–type subform, choosing the Continuous form type in the Custom Form Wizard, with tblShippingAddresses as its record source. On the next screen (for consistency), select the Create your own color scheme or control formats option; you’ll see the color scheme created for frmCustomers. Click Finish to create the subform using this color scheme.

Drag the AddressIdentifier and all the address fields to the subform, and rearrange the Ship to City, State/Province, and Postal Code fields to place them on one line, as we did for the customer address fields on frmCustomers, and make any desired changes to label captions. Figure 2.20 shows the finished subform in Design view.

click to expand
Figure 2.20

The final step is to place the subforms on frmCustomers. I like to use a tab control when a lot of data needs to be displayed on a form, because it lets you divide up the data into groups of related controls, each group displayed on a different page. You can divide up fields in a single table into groups to display on different pages, or you can embed subforms on different pages of the tab control. The interface is familiar, and the look is clean and uncluttered.

To place a tab control on frmCustomers:

  1. Drag all the controls on the form way over to the right.

  2. Place a tab control on the form.

  3. Stretch the tab control out until it is large enough to accommodate all the moved-over controls.

    To select a tab control for purposes of moving or resizing, click the top portion of the tab control, to the right of the tabs.

Click on the first page of the tab control, open the properties sheet, and enter Customer Address as its caption; enter Shipping Addresses as the caption of the next page. We’ll need a third page for the customer phone numbers and emails, so right-click the tab control and select Insert page. Enter Customer Phones and Emails as the caption for the new page.

The Click event of tab controls doesn’t work in Access; to respond to clicks on different tabs of a tab control, use a Select Case statement on the control’s Change event instead.

After this, run the LNC Rename All Controls builder to rename all the new controls just added to the form.

When referring to tab controls, strictly speaking the term tab refers to the small protruding bit with the caption (Customer Address, and so forth), while the portion under the tab is the page. However, in ordinary usage often the page is called a tab.

Now we need to move the customer address controls to the Customer Address page of the tab control. The method you need to use here is quite unintuitive; you can’t just drag the controls to the page; if you do that they will still be located on the main form and will be visible on whatever page you select. Instead, you have to select all the controls for a page, and Shift-Delete them, then click the tab of the page where you want to insert the controls (square dots will appear at the corners and sides of the selected page), then insert the controls with Ctrl-V. To check that the controls are really on that page, click the tab of another page. The controls should disappear, and they should reappear when you click the Customer Addresses page’s tab again. You can now resize the frmCustomers form to eliminate the extra space on the right side. Figure 2.21 shows the form with customer address fields on the Customer Addresses page of the tab control.

click to expand
Figure 2.21

Tab controls (like command buttons) have a mid-gray back color, which can’t be changed. If you want a tab control to blend into the form background, you have to change the form’s background to match the tab control’s back color.

Save the form, and select the Shipping Addresses page of the tab control. The fsubShippingAddresses form belongs on this page, and there are two ways to place it there:

  • Drag it to the form from the database window.

  • Place a Subform/Subreport control on the form, and select fsubShippingAddresses as its source object.

The first method is quicker, so let’s do that. Resize the database window so it doesn’t overlap the form, and select the ShippingAddresses page of the tab control. Drag fsubShippingAddresses to the ShippingAddresses page of the tab control (the page turns black when the subform is over it, as shown in Figure 2.22).

click to expand
Figure 2.22

When a form is dragged to another form as a subform and there is a one-to-many relationship between the main form’s table and the subform’s table, the LinkMasterFields and LinkChildFields properties of the subform are automatically filled in with the linking field, as shown in Figure 2.23.

click to expand
Figure 2.23

The subform doesn’t need a label, because the tab caption describes it, so delete its label. I like subforms to blend in to the background, so I usually change the subform’s border style to Transparent, unless I want it to have a visible border. In that case, I change its special effect to Flat.

The third page of the tab control needs two subforms: fsubCustomerPhones and fsubCustomerEMails. Drag them to this page, placing them one above the other or side by side. They do need their labels, so don’t delete them; just edit the label captions as desired. Finally, run the LNC Rename All builder to rename the new subform controls and their labels.

Figures 2.24 through 2.26 show the three pages of frmCustomers, with the embedded subforms on the second and third pages.

click to expand
Figure 2.24

click to expand
Figure 2.25

click to expand
Figure 2.26

The datasheet columns need to be resized to avoid cutting off data; to do this, switch to Design view and open one of the datasheets in its own window by selecting View|Subform in New Window, switching to datasheet view in the subform, and resizing the columns. It may take some switching back and forth between the subform in its own window and the main form, to get the column sizes right. Close the subform after resizing its columns, so when you switch to Form view for the main form, you will see the new column sizes.

The Subform in New Window command is a little touchy, especially when the subform is very small. If this command is disabled when you try to open the subform, try clicking on the form background (outside of the subform), then click on the subform, or drag the mouse so the lasso just touches the subform. Now the command should work.

The Vendors form is similar to frmCustomers; it needs a standard main form with two subforms. It can be created in a similar manner, with a tab control displaying data from tblVendors on one page, and Vendor phone and ID data on the other page.

The Orders form also needs a subform, to display shipping address data, but in this case the subform is not the “many” side of a one-to-many relationship, as with Customer and Vendor phones and IDs. Instead, tblShippingAddresses is the “one” side of the relationship because one shipping address could be used for multiple orders. Shipping address selection can be done with a combobox that displays the shipping addresses for the selected customer. Once the shipping address has been selected, it is displayed in a subform.

The Orders form also needs a number of comboboxes for selecting data from other tables, in addition to textboxes for entering Vendor data. To start, select the Custom Form Wizard in the New Form dialog, and select the Standard form type with tblOrders as its data source. To maintain a consistent appearance, on the next screen, select the Create your own color scheme or control formats option to use the color scheme created for frmCustomers.

Drag all the fields from the field list to the form. Other than OrderID, all the fields whose names end with ID are foreign key fields, and most of them need comboboxes instead of textboxes, so the next step is to convert them to comboboxes, one by one, using the Change To|Combo Box selection on their context menu, as shown in Figure 2.27 for the ToyID field.

click to expand
Figure 2.27

The captions of the newly converted comboboxes’ attached labels should also be edited to remove ID.

The ShippingMethodID field, however, is best represented by an option group, which is not available as a Change To choice. So, remove its textbox, and place an option group on the form, and select ShippingMethodID as its field. Place five option buttons in the option group, with the default value for each one matching the ShippingMethodID for its label.

An option group bound to a field with an Integer value is only practical if the choices are fixed; otherwise, use a combobox or listbox for selecting a value.

After changing the control types, you can run the LNC Rename All builder to give all the controls the appropriate tags.

The ToyID, CustomerID and EmployeeID comboboxes need similar treatment: the tblToys, tblCustomers, and tblEmployees tables need to be selected (respectively) as their row sources, and a few appropriate fields must be selected for display in each combobox’s drop-down list.

As an example, I’ll go through the process for cboToyID. Unlike the record selector combobox in the form’s header, in this case it is a good idea to display data in the combobox after selection, to let the user know what the selection is. After selecting tblToys as the Row Source value for cboToyID, click the build button next to this property to open the query builder. In this case, we want to see the toy name and vendor name. Since tblToys only has VendorID, the vendor name can be obtained through the link to tblVendors.

To add VendorName to the query grid, add tblVendors to the query grid using the Add Table button in the query toolbar. You will see the one-to-many link between the two tables. Add VendorName to the query datasheet, and arrange the fields as shown in Figure 2.28. We need ToyID to save to the ToyID field in tblOrders, but ToyName should be displayed in the combobox after a selection is made.

click to expand
Figure 2.28

After closing the query designer, set the combobox’s properties as follows (as shown in Figure 2.29), to show the three descriptive fields in the drop-down list, display ToyName in the combobox, and save ToyID to the ToyID field (to see all these properties, you need to be on the All tab of the properties sheet).

click to expand
Figure 2.29

cboCustomerID needs only the CustomerName and CustomerID fields. cboEmployeeID needs a calculated field, to display employee names last name first. While this expression could be created directly in the combobox’s row source, this calculated field will be used elsewhere in the database, so it will save time to create a simple select query based on tblEmployees, with two calculated name expressions: LastNameFirst and FirstNameFirst. The simplest way to do this is to select tblEmployees in the database window, click the Object Selector in the toolbar, and select Query and then Design View, as shown in Figure 2.30.

click to expand
Figure 2.30

Double-click the asterisk at the top of the field list to place it on the query datasheet (the asterisk represents all the fields in the query). Then, create the two calculated fields using the expressions listed below, and save the query as qryEmployees.

LastNameFirst: [LastName] & “, “ & [FirstName] & IIf([MiddleName],” “ & [MiddleName],””) FirstNameFirst: [FirstName] & “ “ & IIf([MiddleName],[MiddleName] & “ “,””) & [LastName]

Create a SQL statement based on qryEmployees as the row source of the cboEmployeeID combobox, and use LastNameFirst and EmployeeID for the drop-down list; the SQL statement is shown in the Query Builder in Figure 2.31.

click to expand
Figure 2.31

I made some of the textboxes narrower, and moved some of them side-by-side, to save space on the form.

The final touch is to provide a way to display the selected shipping address. This can be done in two ways: a subform that is refreshed after making a selection from cboShippingAddressID, or a calculated ShippingAddress field that could be displayed directly in the combobox, after selection. Because we need to display several lines of data, a subform would be better. First, cboShippingAddressID needs a special query as its row source. Because the shipping address might be either the customer’s main address (in tblCustomers) or one of that customer’s shipping addresses (in tblShippingAddresses), address data from these two tables needs to be combined into one data source, using a union query. More details on creating the quniCustomerAddresses union query will be given in the next chapter. For now, we’ll just open the query builder from the Row Source property of the cboShippingAddressID combobox, select quniCustomerAddresses from the Show Table dialog, and add the ShipAddressID, CustomerID, and AddressIdentifier fields to the query grid.

Only the addresses for the selected customer should be available in this combobox, so the next step is to create a criterion for the CustomerID field that matches the CustomerID selected in the cboCustomerID combobox on the same form. The most accurate way to do this is to use the Expression Builder to select the combobox. Using the Expression Builder guarantees that the syntax is correct. To open the Expression Builder, right-click the Criteria row of the CustomerID field, and select Build (or click the Build button on the query toolbar). In the Expression Builder, open the Forms folder and the Loaded Forms folder under it, and select frmOrders. Select cboCustomerID in the list of controls on this form, and click the Paste button to paste it into the expression box, as shown in Figure 2.32.

click to expand
Figure 2.32

Click OK to place the expression in the Criteria row of the CustomerID field. The finished row source SQL statement is shown in Figure 2.33.

click to expand
Figure 2.33

The combobox can’t be filtered by CustomerID unless a customer has been selected, so cboShipAddressID should be disabled initially, and be enabled only after a customer has been selected. The code to enable cboShipAddressID needs to run off the form’s Current event as well as cboCustomerID’s AfterUpdate event, so that as users navigate from one record to another. cboShipAddressID will be enabled or disabled for each record, depending on whether a customer has or has not been selected. I created a private Sub procedure to do the enabling or disabling, and called it from the other event procedures as needed, to avoid having to write the same code in several procedures. The relevant procedures from frmOrders’ code module are listed below.

 Option Compare Database Option Explicit Private Sub EnableShippingAddress() On Error GoTo ErrorHandler        Dim lngCustomerID As Long        lngCustomerID = Nz(Me![cboCustomerID])    If lngCustomerID = 0 Then       Me![cboShipAddressID].Enabled = False    Else       Me![cboShipAddressID].Enabled = True       Me![cboShipAddressID].Requery    End If     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub Private Sub cboCustomerID_AfterUpdate()    Call EnableShippingAddress End Sub Private Sub Form_Current()    Call EnableShippingAddress End Sub 

The cboSelect record selector combobox on frmOrders needs to display data from several other tables to allow users to select the order record they want to look at. Figure 2.34 shows the SQL statement row source for this combobox.

click to expand
Figure 2.34

For purposes of debugging while working on the shipping address controls, I placed a textbox on the form to display the CustomerID from the second column of cboCustomerID. I used the Lock Wizard builder to make the textbox invisible and bright yellow (not a contradiction—you can see the yellow in Design view even when the control is invisible). Temporarily, I made the control visible so I could see it in Form view; after everything is working, it can be made invisible again. The yellow color tells me that this control should be made invisible before finalizing the application.

The subform that displays the selected shipping address will only display one address, so it is a Single Form–type subform created using the Subform selection in the Custom Form Wizard. The record source is the union query quniCustomerAddresses. When this subform is dragged to frmOrders, the Link_ChildFields and LinkMasterFields properties aren’t filled in automatically. The subform needs to be linked on both CustomerID and ShipAddressID; enter the following string in both properties to link the subform to the main form appropriately:

CustomerID;ShipAddressID

Figure 2.35 shows frmOrders, with the Selected Shipping Address subform, with several yellow controls that will be made invisible later on.

click to expand
Figure 2.35

The subform’s record source is a union query, so it is read-only. I gave it a light blue background (my convention for locked controls), to let users know the address can’t be edited, and I also made the subform’s border visible.

One more finishing touch is needed for frmOrders; when a toy is selected from cboToyID, its price needs to be picked up from tblToys and written to the ToyPrice field. Technically, this is a violation of normalization (the same data is stored in two different tables), but it is needed for business purposes because prices change, and you need to store the actual price charged when the order was made, even if that toy’s price changes later on. To make this easy, I added the SellPrice field from tblToys to the combobox’s row source SQL statement. The AfterUpdate event procedure of cboToyID (which follows) picks up the toy price from the fourth column of the combobox (column numbers are zero-based in code), writes it to the ToyPrice field in tblOrders, and then re-queries the txtToyPrice control.

 Private Sub cboToyID_AfterUpdate() On Error GoTo ErrorHandler        Dim curToyPrice As Currency        curToyPrice = Nz(Me![cboToyID].Column(3))    Me![ToyPrice] = curToyPrice    Me![txtToyPrice].Requery     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub 

The remaining controls on frmOrders are filled in manually; the date fields have input masks to ensure correct date entry.

tblMaterials needs a standard form with no subforms. You can create it using the Standard form type in the Custom Form Wizard, in a manner similar to frmCustomers, except without the tab control and subforms.

tblEmployees also needs a standard form, created in a similar manner, but this form has a few special features:

  • EmployeeID is not an AutoNumber field, but a numeric ID that is filled in manually for existing employees who already have IDs, and created for new employees by adding 1 to the highest existing EmployeeID value. I made a totals query that returns the highest EmployeeID so far entered, and a subform that displays this value, to place on frmEmployees (it is set invisible). (See Chapter 4, Sorting and Filtering Data with Queries, for more information on totals queries.) The procedure that creates a new EmployeeID is listed below. It is called from the form’s BeforeInsert event, so the new ID is created when the user starts to enter data into a new employee record.

    At the same time, the code creates a new record in tblEmployeesConfidential. This is required because of the one-to-one relationship between tblEmployees and tblEmployeesConfidential. (See Chapter 7, Writing VBA Code in Modules, for more information on writing to tables using DAO recordsets.)

     Private Sub NewEmployeeID() On Error GoTo ErrorHandler        Dim strEmployeeID As String    Dim lngEmployeeID As Long    Dim dbs As DAO.database    Dim rst As DAO.Recordset        Me![subMaxEmployeeID].Form.Requery    lngEmployeeID = Me![subMaxEmployeeID].Form![txtNumericID] + 1    strEmployeeID = Format(lngEmployeeID, "00000")    Me![EmployeeID] = strEmployeeID    Me![txtEmployeeID].Requery        ‘Add a corresponding record to tblEmployeesConfidential.    Set dbs = CurrentDb    Set rst = dbs.OpenRecordset("tblEmployeesConfidential", dbOpenTable)    rst.AddNew    rst![EmployeeID] = strEmployeeID    rst.Update    rst.Close     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub 

The Department textbox should be changed to a combobox. It could have a lookup table of department names for its row source, or a SQL statement that displays all departments that have been entered. We’ll use a SQL statement, done like the Description field on the phone number subforms. (The Title field could also be treated in a similar manner.)

The last special feature of this form is a combobox that selects another record in the same table. cboSupervisorID has tblEmployees as its row source, with the LastNameFirst and Employee ID fields, and also the Department field, with a criterion of Management; it is used to select the employee’s supervisor.

Figure 2.36 shows the finished frmEmployees.

click to expand
Figure 2.36

One more form with a subform needs to be created, for the tblMailingList and tblMailingListCompanies tables. There is a one-to-many relationship between these tables, but you would most likely want to view the records in tblMailingList in the main form, even though it is the “many” side of the relationship. Therefore, I made a standard form based on tblMailingList, and a subform linked on CompanyID for tblMailingListCompanies. The mailing list company is selected from a combobox, much like selecting a shipping address on frmOrders, and the address data for the selected company is displayed in a subform. I won’t discuss creating this form and its subform in detail because it is virtually identical to the process of creating the shipping address selector combobox and the shipping address subform on frmOrders. The frmMailingList form is shown in Figure 2.37, on a record with a company selected.

click to expand
Figure 2.37

Creating Continuous Forms

Continuous Forms–type forms are most suitable for entering and editing data when you only have a few fields to deal with and want to see several records at once. In the Toy Workshop database, tblCategories, tblShippingMethods, and tblEmployeesConfidential are good candidates for this type of form. To create a Continuous Forms–type form based on tblCategories, select the Custom Form Wizard in the New Form dialog, and select the Continuous form type with tblCategories as its data source. Since Continuous Forms–type forms do use colors and special effects, on the next screen, select the Create your own color scheme or control formats option to use the color scheme created for frmCustomers. Click Finish to create the form.

The new form has a standard Close button in the footer section, and a zero-height header section (Continuous Forms–type forms rarely need a record selector combobox). Drag the two fields from the field list to the form, and make a few modifications:

  • Make the CategoryID field left-aligned and narrower (it just displays an AutoNumber ID), and use the Lock Wizard to make it locked and give it a blue background. To use the Lock Wizard, right-click the CategoryID textbox, and select Build. Accept the default Locked option in the Lock Wizard dialog, as shown earlier in Figure 2.10.

  • Move the CategoryID textbox and label over to the left of the form, and the CategoryName textbox and label up and to the right of the CategoryID textbox.

  • Shrink the blank space over and under the controls so there is just a small amount.

  • (If desired) move the Close button over to the right side of the footer.

  • Enter Toy Categories in the form’s Caption property.

  • Run the LNC Rename All Controls builder to rename all controls with the appropriate LNC tags. This is done by invoking the builder from the Build button of the Name property of the form’s Detail section, as shown in Figure 2.38.

    click to expand
    Figure 2.38

Save the new form as frmCategories, and switch to Form view. You’ll see why there is no need for a record selector combobox. With only ten records, you can see all of them at once. For those rare cases where there are many records displayed on a Continuous Forms–type form, you can turn on the vertical scroll bar, or add a record selector combobox to the header, similar to the one on frmCustomers. Figure 2.39 shows the form in Form view.

click to expand
Figure 2.39

The Continuous Forms–type forms for tblShippingMethods and tblEmployeesConfidential can be created in exactly the same manner.

Creating a Form for a Many-to-Many Relationship

The last form variation needed for this application is a form used to enter data into a table that has a many-to-many relationship with another table. In the Toy Workshop database, there are two such relationships, tblToys—tblToyMaterials—tblMaterials, and tblMaterials—tblRepairMaterials—tblRepairs. I will demonstrate creating the Toys form, using an embedded datasheet subform that lets you select values from the other “many” table, tblMaterials, adding a record for each selection to the linking table, tblToyMaterials.

Start by creating a Datasheet form based on tblToyMaterials. Select the Custom Form Wizard in the New Form dialog, and select the Datasheet form type with tblCategories as its data source. Click Finish to create the new form (there are no color choices for datasheet forms).

This subform will be used to select materials, and it needs a combobox that shows you the material name, but stores the MaterialID to tblToyMaterials. Place the MaterialID field on the form, and change the textbox to a combobox. Select tblMaterials as the row source for the combobox, and select MaterialID as the bound field and MaterialName to display in the drop-down list. Select the Ascending sort for MaterialName, then drag the Unit, Quantity and Unit Cost fields to the form. Unit will appear as a combobox, because it is a lookup field. Finally, create a calculated Total Cost textbox with the following expression:

=[Quantity]*[UnitCost] 

Create a standard form based on tblToys. This form has two fields (CategoryID and VendorID) that are foreign keys. They need to be converted to comboboxes so the value can be selected from (respectively) tblCategories and tblVendors. This is done in a manner similar to that used for the foreign key fields on frmOrders.

Depending on whether chkPurchased is checked or not, the Toy Materials subform is invisible or visible (materials need to be selected only for manufactured toys, not purchased toys), and controls related to either purchased or manufactured toys (but not both) are enabled or disabled appropriately. This is done with a Sub procedure called by a line of code on the checkbox’s AfterUpdate event and the form’s Current event. This procedure follows.

 Private Sub EnablePurchased() On Error GoTo ErrorHandler        ‘Make subform visible only if toy is manufactured (not purchased),    ‘and enable or disable other controls as needed.    blnPurchased = Nz(Me![chkPurchased])    If blnPurchased = True Then       Me![subToyMaterials].Visible = False       Me![txtVendorPrice].Enabled = True       Me![txtVendorProductID].Enabled = True       Me![txtMaterialsCost].Enabled = False    Else       Me![subToyMaterials].Visible = True       Me![txtVendorPrice].Enabled = False       Me![txtVendorProductID].Enabled = False       Me![txtMaterialsCost].Enabled = True    End If        ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub 

Figure 2.40 shows the frmToys form on a record with a manufactured toy, with the Toy Materials subform visible.

There is an AfterUpdate event procedure on the cboMaterialID combobox on the subform that picks up the unit cost from tblMaterials and writes it to tblToyMaterials; and a calculated expression in txtTotalCost that multiplies unit cost by quantity, to get the total cost for that item. The Repairs form is done in a similar manner, except that the Select Materials subform is always visible.

The cost of materials used for manufacturing or repairing toys is calculated using totals queries, subforms, and event procedures; this technique will be discussed in detail in Chapter 4, Sorting and Filtering Data with Queries.

click to expand
Figure 2.40




Expert One-on-One(c) Microsoft Access Application Development
Expert One-on-One Microsoft Access Application Development
ISBN: 0764559044
EAN: 2147483647
Year: 2006
Pages: 124
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net