Chapter 7. Creating a CheckOut Form
In the previous chapter, you created several forms using the design tools provided by Access. This is what Access does really well, and this is why it is such a popular platform for creating database solutions. It is really easy to create forms for your existing tables. The primary constraint, however, is that the standard forms can only access a single table. There are some ways around this. For example, you can use a query to join related tables and then the form can access all the fields in the query. This is really helpful, especially with a normalized database. You can also use subforms when a parent/child relationship exists between the tables.
As most of us have seen from some painful experiences, there are times when you need a form that just doesn't behave that way. The CheckOut form that you'll create in this chapter is a perfect example. It needs to do the following:
Find and display Customer data
Look up and display Item and InventoryItem information
Insert records into the Loan table
In this chapter as well as the next, you'll build some complex solutions that will require Visual Basic for Applications (VBA) code to connect the pieces together. The forms themselves still rely on the foundational concepts presented in the previous chapter.
The process of building a checkout form will be described in the following steps:
1. Building a reusable CustomerSearch form.
2. Creating the CheckOut form, invoking the CustomerSearch form, and displaying the customer details.
3. Looking up and displaying an InventoryItem record.
4. Adding an item to the CheckOut form and finalizing the checkout process.
Implementing a Customer Search Feature
The first step during the checkout process is to identify the customer that is borrowing the items. In many cases, the customer will have a library card with them, and their CustomerID can be entered from that. We'll also need to allow a customer to be searched for based on name, address, phone, and so on. The search form will be implemented as a modal dialog so it can be reused in other places.
Populating the Customer Table
The initial database that you created in Chapter 2 only has a few records in the Customer table. You'll need more records than that to test the search capability. To easily populate additional records, I have downloaded contact information for each of the members of the United States House of Representatives.
If you're starting each chapter with the online version of the previous chapter's Library database, then you already have these loaded. If not, you can download the Library_06.accdb file from www.apress.com, and use that as the starting point for this chapter's projects. If you want to keep using your own database, you can also download the Customer.xlsx file. Then copy and paste all the records (except the header) into your database. The Customer table should look like Figure 7-1.
Figure 7.1. Customer table populated with additional records
Creating the CustomerSearch Dialog Box
From the Create tab of the ribbon, click the More Forms button, and then click the Modal Dialog link, as shown in Figure 7-2.
Figure 7.2. Creating a modal dialog form
This creates a blank form that has OK and Cancel buttons on it. You'll add un-bound controls to the Form Header. The user will enter their search criteria in these fields. The Detail section will use standard data-bound fields that will display the records that match the specified criteria.
To display the Form Header section, right click on the form and click the Form Header/Footer link, as shown in Figure 7-3.
Figure 7.3. Displaying the Form Header section
The OK and Cancel buttons should be in the Form Footer. You can't drag controls from one section to another. Instead, use the cut and paste operations to move them to the footer.
ADDING THE DETAIL FIELDS
You'll start by adding the data-bound controls in the Detail section. In the previous chapter, this step was done for you, but you'll find it's fairly easy to do yourself. Follow these steps:
1. In the Property Sheet, select the Form object and the Data tab. Notice that the Record Source property is blank. There is a dropdown button that allows you to select an existing table or query. There is also a button with ellipses. This will launch the Query Designer that you used in Chapter 4. You can use this to create an ad-hoc query as the record source for this form. In this case, just select the Customer table from the dropdown list.
2. While you're here, set the Allow Additions and Allow Deletions properties to No. This form is for viewing only and should not be used for modifying records.
NOTE
ou can't set Allow Edits to No, because the user will need to edit the unbound controls that you will add later. Instead, you'll set the Locked property on the controls in the Detail section to prevent the user from modifying them.
3. From the Create tab of the ribbon, click the Add Existing Fields button. This will display a list of the available fields in the selected record source (the Customer table). You'll double-click a field in this list to add it to the form. Add the following fields:
CustomerID
LastName
FirstName
Address
PhoneNumber
4. To quickly format the fields on the form, select all of the controls, including labels. In the Arrange tab of the ribbon, click the Tabular button. This will be a Continuous Form and you'll want the labels in the Form Header, which is what the Tabular button does. From the Property Sheet, select the Form object and the Format tab. Change the Default View property to Continuous Form.
5. Drag the labels to the bottom of the Form Header, and drag the data-bound controls to the top of the Detail section. Shrink the Detail section so there is just enough room for one row of controls. You'll also need to resize the data-bound controls, based on data that will be contained in each. You can switch to the Layout View to see how the actual data will appear.
6. Change the Caption property of the CustomerID label to ID. Save the form and enter the name CustomerSearch when prompted. The initial layout should be similar to Figure 7-4.
Figure 7.4. Initial layout of the CustomerSearch form
You'll need to make the following few adjustments to the Form's properties:
In the Property Sheet, select the Form object.
In the Format tab, change the Record Selectors property to Yes. Also set the Scroll Bars property to Vertical Only.
Select all of the data-bound controls and from the Property Sheet, set the Locked property (on the Data tab) to Yes. This will prevent the user from modifying the data.
7. Save the form changes. Try out the form by selecting the Form View. The dialog box should look like Figure 7-5.
Figure 7.5. The initial implementation of the CustomerSearch dialog
ADDING THE SEARCH FIELDS
At this point, the form returns all of the records in the Customer table. You'll now add unbound controls to the Form Header that the user will use to enter the search criteria. Follow these steps:
1. To add a TextBox control to a form, click the Text Box button in the Design tab of the ribbon.
2. Draw a rectangle on the form where control should go. This will create a TextBox control and an associated Label control. You'll also need to change the name of the control. Because there are controls in the Detail section with similar names, prefix these controls with "txt," for example, txtCustomerID. You'll then edit the Caption property of the Label, which you can do directly on the form, jut edit the text that is displayed.
3. Add the following TextBox controls to the Form Header. Don't worry about the size or alignment; you'll take care of that with a layout control. The Form Header will look similar to Figure 7-6.
CustomerID
Phone
Email
LastName
FirstName
Postal
Figure 7.6. Initial Form Header layout with unbound controls
4. Now select all of these controls and the associated labels, being careful not to include the column heading labels for the Detail section.
5. In the Arrange tab of the ribbon, click the Stacked button. This will stack all of the controls vertically. Click the Insert Right button in the ribbon four times to create four more columns. Click the Select Layout button to select all of the controls and drag everything to the left.
Follow these steps to arrange the control in the Form Header layout control:
1. Drag the txtLastName control and its label just to the right of the txtCustomerID control and remove the empty row (where the txtLastName control used to be).
2. In the same way, drag the txtFirstName control and its label just to the right of the txtPhone control and remove the empty row.
3. Drag the txtPostal control and its label to the right of the txtEmail control, leaving an empty cell between them. Delete the empty row.
4. Select the txtEmail control and the empty cell next to it and click the Merge button.
5. Select all of the Label controls and in the Format tab of the Property Sheet, set the Text Align property to Right.
6. Select one of the column heading labels and click the Select Row button to select the rest of them. Position this row of labels so they are under the last unbound control.
7. Shrink the height of the Form Header to remove any unused space beneath the column headings.
8. Also shrink the width of the Form to remove any unused space.
Next, you'll need two command buttons. The first will execute the search and the second will clear the search criteria. Follow these steps:
1. In the Design tab of the ribbon, click the Button button and draw a rectangle in the Form Header. The Command Button Wizard will pop up, which allows you to select from some standard functions. Click the Cancel button, because you will be implementing this yourself.
2. Drag this control to the top-right corner cell of the layout grid. In the Format tab of the Property Sheet change the Caption property to Search and in the Other tab, change the Name property to Search.
3. In the same way, add a second button with the Caption and Name properties set to Clear. Drag this to the cell just below the Search button. The final layout should look like Figure 7-7.
Figure 7.7. The final layout of the CustomerSearch form
IMPLEMENTING THE SEARCH LOGIC
The search form is designed to display records from the Customer table. At this point, it is displaying all of the records. All you need to do now is apply a filter so the appropriate subset of records is returned. You'll do that using VBA code.
Select the Search button. In the Property Sheet, select the Event tab to list all of the events that you can write a handler for. For a command button, you normally add code to the OnClick event. In the dropdown for this event, select Event Procedure and then click the ellipses next to it. This will display the VBA editor. In the Search_Click method, enter the implementation shown in Listing 7-1.
Example 7.1. The OnClick Event
DoCmd.ApplyFilter "", _
"([CustomerID] = [Forms]![CustomerSearch]![txtCustomerID] " & _
"Or IsNull([Forms]![CustomerSearch]![txtCustomerID]))" & _
"And ([LastName] Like [Forms]![CustomerSearch]![txtLastName] " & _
"Or IsNull([Forms]![CustomerSearch]![txtLastName]))" & _
"And ([FirstName] Like [Forms]![CustomerSearch]![txtFirstName] " & _
"Or IsNull([Forms]![CustomerSearch]![txtFirstName]))" & _
"And ([PhoneNumber] Like [Forms]![CustomerSearch]![txtPhone] " & _
"Or IsNull([Forms]![CustomerSearch]![txtPhone]))" & _
"And ([Email] Like [Forms]![CustomerSearch]![txtEmail] " & _
"Or IsNull([Forms]![CustomerSearch]![txtEmail]))" & _
"And ([ZipPostal] Like [Forms]![CustomerSearch]![txtPostal] " & _
"Or IsNull([Forms]![CustomerSearch]![txtPostal]))", ""
This code calls the ApplyFilter method passing in the selection criteria. For each field that is in the Form Header, the logic checks to see if the database field matches what was specified or if a value was not specified. For the fields except CustomerID the comparison uses the Like operator. This allows for a partial value to be supplied, such as "Ad*".
Select the Clear button and for its OnClick event, select Event Procedure and click on the ellipses. In the VBA editor, enter the following code for the Clear_Click method. This code simply clears whatever values were supplied in the unbound controls.
txtCustomerID.Value = Null
txtLastName.Value = Null
txtFirstName.Value = Null
txtPhone.Value = Null
txtEmail.Value = Null
txtPostal.Value = Null
When the form is first loaded, it will display all the records in the Customer table, because the filter is not applied until the Search button is clicked. To keep it from doing that, you can set a default filter that will return no rows. In the Property Sheet, select the Form object and the Data tab. Enter [CustomerID] = CLng('0') for the Filter property. Also set the Filter On Load property to Yes. The CustomerID field is auto assigned and starts at 1, so it will never be 0. This is an effective and efficient way to keep any records from being loaded until the search criteria is entered.
Save the form and try it out by switching to the Form View. Try searching on various fields and using the wildcard character (*). You can also try entering a CustomerID value. The results should look like Figure 7-8.
Figure 7.8. The completed CustomerSearch form
There's one more thing that you'll need to change to make this work. The CustomerSearch form will be launched from another form (the CheckOut form, in this case) for the purpose of selecting a customer. Once the user has closed the dialog, the main form will need to know if a customer was selected and, if so, which one. The solution to this is that the Cancel button will simply close the form. The OK button, however, will not close the form but merely hide it. To the user this will appear the same but hiding the form will leave it available to the calling form so the selected record can be determined.
We'll start by looking at the default implementation of the OK and Cancel buttons. Follow these steps:
1. Open the CustomerSearch form in the Design View.
2. Right-click the Cancel button and click the Build Event link. This will display the same Macro Designer that you used in Chapter 3 to create your data macros. The available actions are different, because you're working with Windows controls instead of database objects.
TIP
You can use this technique to view and edit the VBA event handlers as well. Right-click the Search button and click the Build Event link. This will display the VBA editor if the event handler is implemented in VBA.
3. This macro simply calls the CloseWindow action, which will close the dialog. The Save parameter is set to Prompt, which will prompt the users and ask if the changes (if there have been any) should be saved or discarded. Change that now and choose the No option. The warning icon is letting you know that this is an unsafe action because changes could be lost. For this form, we don't want any changes made, so this is OK. The macro should look like Figure 7-9.
Figure 7.9. Updated Cancel macro
4. Click the Save button in the ribbon to save this change and then click the Close button to close the editor.
5. If you look at the event handler for the OK button, you'll see that it is exactly the same as for the Cancel button. Select the OK button. In the Event tab of the Property Sheet, change the On Click property from Embedded Macro to Event Procedure. Instead of executing a macro, the OnClick event handler will now invoke some VBA code. Click the ellipses to display the VBA editor.
6. In the Command1_Click method, enter the following code:
If (Me.CurrentRecord = 0) Then
MsgBox "Please select a customer", vbExclamation, "No Customer Selected"]
Else
Me.Visible = False
End If
This code checks to see if they selected a record. If not, an error message is displayed. Otherwise, the Visible property is set to False, which will hide the form.
WARNING
The calling form must be sure to close the CustomerSearch form after it has determined the selected customer.
Building a CustomerDisplay Form
The first thing that the user will do with the CheckOut form is to invoke the CustomerSearch dialog and select a customer. The selected customer should be displayed on the form. This is just for display purposes only; in fact, it would best to display this like a mailing label rather than a series of controls. In order to be able to reuse this on other forms, you'll build this as a stand-alone form now, which can be included on the CheckOut form as a subform. Follow these steps:
1. From the Create tab of the ribbon, click the Blank Form button.
2. Switch to the Design View. In the Property Sheet, select the Form object and the Data tab.
3. Select the Customer table for the Record Source property. Set the Allow Additions, Allow Deletions, and Allow Edits properties to No.
4. Click the Add Existing Fields button in the ribbon, and double-click each of the fields that are listed to add them to the form. These controls will be used for retrieving the data from the record and will not be visible. Delete each of the associated labels.
5. Select all of the data-bound controls. In the Format tab of the Property Sheet, set the Visible property to No. Resize the controls to be about 1 character wide and drag them to the top-left corner of the form. The form will look like Figure 7-10.
Figure 7.10. Initial CustomerDisplay form layout
6. Now you'll add the controls that will display the formatted customer information. Add five TextBox controls to the form with the following names:
lblName
lblAddress
lblRegion
lblEmail
lblPhone
7. Delete all of the associated labels. Now select the five unbound controls and from the Arrange tab in the ribbon, click the Stacked button. This will create a single column of controls.
8. Click the Insert Right button to add a second column. Drag the lblPhone control to the right of the lblEmail control, and delete the empty row. Select the lblName and the empty cell next to it, and click the Merge button. In the same way, merge the lblAddress and lblRegion control with the empty cell next to them.
9. Click the Select Layout button to select all of the controls. Drag the layout to the upper-left corner of the form. While the layout is still selected, click the Control Padding button in the ribbon and click the None link as shown in Figure 7-11.
Figure 7.11. Selecting no cell padding
10. While the layout is still selected, in the Format tab of the Property Sheet, clear the Border Color property (delete the text in the property value). When you tab off this property, the value will be changed to "#000000." Then set the Border Style to Transparent. When you click on the Border Color property, it should change to No Color. Also, set the Back Style property to Transparent. Finally, set the Height property to .2.″
TIP
You can make a TextBox control look like a Label by removing the border and making the background transparent, as you did here.
11. Resize the form to make it as small as possible, with no extra space below or to the right of the controls. Save the form and enter the name CustomerDisplay when prompted. The layout should look like Figure 7-12.
Figure 7.12. The CustomerDisplay form layout
12. Now you'll need to write some code to format the text in the unbound controls. In the Property Sheet, select the Form object and the Event tab. For the OnCurrent event, select Event Procedure, and then click the ellipses to display the VBA editor.
13. In the Form_Current method, enter the code shown in Listing 7-2.
Example 7.2. Implementation of the OnCurrent Event for the CustomerDisplay Form
If (Len(LastName) > 0) Then
lblName = LTrim(RTrim(FirstName) & " ") & RTrim(LastName) & " " & _
CStr(CustomerID)
lblAddress = Trim(Address)
lblRegion = RTrim(City) & ", " & LTrim(RTrim(StateProvince) & " ") & _
RTrim(ZIPPostal)
lblEmail = RTrim(Email)
lblPhone = RTrim(PhoneNumber)
Else
lblName = ""
lblAddress = ""
lblRegion = ""
lblEmail = ""
lblPhone = ""
End If
This code determines if a record was selected by checking the LastName field. If there is no record, the controls are all cleared. Otherwise, the appropriate fields are concatenated together to format a mailing label.
Save the code and save the form changes. Switch to the Form View. The format should look like Figure 7-13.
Figure 7.13. The final output of the CustomerDisplay form
Go back to the Design View. This form should display a single record so you'll need to remove all the navigation controls. In the Property Sheet, select the Form object and the Format tab, and then set the following properties:
Border Style: None
Record Selectors: No
Navigation Buttons: No
Scroll Bars: Neither
Control Box: No
Close Button: No
Min Max Buttons: None
In the Data tab, set the Filter property as [CustomerID] = CustomerID and set the Filter On Load property to Yes. When you drop this form onto another form, you'll set up the parent/child linkage so the correct record will be returned based on the parent form.
Creating the CheckOut Form
Now you are ready to create a CheckOut form that will use both the CustomerSearch and CustomerDisplay forms. The controls for the customer search feature will all be in the Form Header; you'll save the Detail section for the items being checked out.
ADDING THE CUSTOMER CONTROLS
Now you'll add controls to the Form Header that you'll use for searching for and displaying the selected customer.
1. From the Create tab of the ribbon, click the Blank Form button. Switch to the Design View and then right-click the form and click the Form Header/Footer link. Expand the Form Header so you'll have some room to work with.
2. Add a TextBox control to the Form Header and change the Name property to txtCustomerID. Set the Caption of its associated label to CustomerID:. Set the Text Align property for the label to Right.
3. Add a command button to the Form Header and cancel the Command Button Wizard. Change the Name property to Search and set the Caption property to Search...
4. Select all three controls. From the Arrange tab of the ribbon, click the Stacked button. This should create two columns.
5. Click the Insert Right button on the ribbon to create a third column.
6. Drag the command button to the last column of the first row.
7. Merge all three cells of the second row. Enlarge the second row to be about 1-inch high.
8. From the Design tab of the ribbon, click the Subform button and then click Form Header section. This will display the SubForm Wizard. Select the CustomerDisplay, as shown in Figure 7-14.
Figure 7.14. Selecting the CustomerDisplay form
TIP
If the SubForm Wizard does not appear, you probably have the control wizards turned off. To enable them, expand the list of controls in the Design tab of the ribbon so all controls are displayed. Then click the Use Control Wizards link.
9. In the next dialog box, use the default value, CustomerDisplay, for the name of the subform. Click the Finish button.
10. In addition to the subform, a Label control is created, delete this. Drag the subform to the second row of the layout control.
11. Switch to the Layout View and resize the cells as appropriate.
12. Save the form and enter the name CheckOut when prompted.
The final layout should look like Figure 7-15
Figure 7.15. The layout of the Form Header section
CONNECTING THE CONTROLS
Now you'll tie the pieces together so this will function correctly. The first step is to link the subform, CustomerDisplay, with its parent form. The parent form, CheckOut has a txtCustomerID control that will specify the selected customer. The child form has a CustomerID data-bound control that is also used as the filter for the form. You'll now link these control together.
1. Go back to the Design View. In the Property Sheet, select the CustomerDisplay object and the Data tab.
2. Enter txtCustomerID for the "Link Master Fields" property. Likewise, enter CustomerID for the "Link Child Fields" property. The Property Sheet will look like Figure 7-16.
Figure 7.16. The subform data properties
3. Go to the Format tab and set the Visible property to No. This will keep the subform hidden until there is data to display.
4. Now you'll add code to handle the scenario where the user enters a customer ID directly into the form. Select the txtCustomerID control. In the Event tab of the Property Sheet, select Event Procedure for the On Lost Focus property. Then click the ellipses to edit the associated VBA code. Enter the following code for the txtCustomerID_LostFocus method:
CustomerDisplay!lblName = ""
CustomerDisplay!lblAddress = ""
CustomerDisplay!lblRegion = ""
CustomerDisplay!lblEmail = ""
CustomerDisplay!lblPhone = ""
DoCmd.Requery "CustomerDisplay"
If (Len(CustomerDisplay!lblName) > 0) Then
CustomerDisplay.Visible = True
Else
CustomerDisplay.Visible = False
End If
This code first clears the labels and then forces a re-query against the database. It then checks to see if there is any data to display. If not, the subform is hidden. If an invalid customer ID is entered then no data will be displayed.
Save the code changes and the form. Try it out by switching to the Form View. Enter a number in the CustomerID field and then tab off the field. The form should look similar to Figure 7-17.
Figure 7.17. The Form Header displaying the customer information
Try entering an invalid ID and verify that the subform is hidden.
INVOKING THE SEARCH DIALOG
Now you'll add code to invoke the CustomerSearch dialog when the Search button is clicked. This code will also determine the customer that was selected.
1. Go back to the Design View. Right-click the Search button and click the Build Event link. Because there is no code currently associated with this event, the dialog box in Figure 7-18 is displayed for you to select the type of event handler.
Figure 7.18. Selecting the type of event handler
2. Choose the Code Builder option and click the OK button.
3. Enter the code shown in Listing 7-3 for the implementation of the Search_Click method.
Example 7.3. Implementation of the Search_Click Method
Dim sForm As String
sForm = "CustomerSearch"
' Open the search form
DoCmd.OpenForm sForm, acNormal, , , , acDialog
' If the form is not loaded, the user clicked the Cancel button
If (IsLoaded(sForm)) Then
txtCustomerID = Forms(sForm)!CustomerID
DoCmd.Close acForm, sForm
Else
txtCustomerID = ""
End If
' Force a refresh if the CustomerDisplay subform
txtCustomerID_LostFocus
This code opens the CustomerSearch form, which is implemented as a modal dialog. Because it is modal, the OpenForm method does not return until the form has been closed or hidden. The code then checks to see if the form is still loaded. If it is, the CustomerID is obtained and the form is closed. Finally, the LostFocus event handler is called, which you just implemented. This will cause the CustomerDisplay subform to be updated with the selected customer, or hidden if no customer was selected.
This code uses a function called IsLoaded. You will need to implement that as well. Since this will be needed from several forms, you should implement this in the Main module so it can be shared by all the forms. To add a module to the VBA code, right-click the Library project and click the Insert
This will open up a new file. Add the following code to this file:
Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function
Save the code and enter Main when prompted for the file name. The SysCmd method is a built-in function provided by Access, which can be used for a number of purposes depending on the action that is specified in the first parameter. This code uses it to check the status of the specified form; to see if it is loaded. If it is, the code then checks to see if it is opened in Design View.
NOTE
The IsLoaded function is provided by Microsoft. You can find more information about it at http://msdn.microsoft.com/en-us/library/aa141272(office.10).aspx. In my opinion, this should be provided as a built-in function. Because it is not, you'll need to make sure you add it to the Main module. If you forget, you'll get an error when your code executes.
Go to the Form View and click the Search button. Enter some search criteria and select one the records as shown in Figure 7-19. The record selector to the left of the row will indicate which record was selected.
Figure 7.19. Searching for a customer
Click the OK button. You should see the selected customer displayed in the Form Header, as shown in Figure 7-20.
Figure 7.20. The Form Header showing the selected customer
Try searching for a customer and clicking the Cancel button. In this case, no customer data should be shown.
Providing a CheckOut Feature
At this point the CheckOut form allows you to enter a customer ID or search for a customer and then display their information. Now that the preliminaries are out of the way, you can build a feature that will check out items to the selected customer.
The checkout process will start by entering an InventoryItemID. It is assumed that the item will have an inventory tag on it that can be scanned or entered so you won't need a search feature. When this ID has been entered, it's a good idea to look up the item and display details of the item, including its current condition and status. If the item is available, the form will allow the item to be checked out.
An item is checked out by creating a Loan record, which requires only the CustomerID and InventoryItemID fields. The data macros that you implemented in Chapter 3 will take care of any other updates that are needed, such as updating the InventoryItem record. The Loan record will be added to the Detail section of the CheckOut form. As additional items are checked out, they are added to the form so you can keep a running total of everything being checked out.
You will now implement this feature in the following steps:
1. Create a form for displaying details of an InventoryItem record.
2. Embed this as a subform in the CheckOut Form Header
3. Add logic to generate the Loan records when the item is checked out.
4. Implement some finalization code when the checkout process is complete.
Building an InventoryItemLookup Form
You'll now create a form that will display a single inventory item. You already have an InventoryItem form that is used as a subform on the Item form. This form will not work here, because it was designed to be used in the context of an Item record. You'll need a form that provides details from both the Item and InventoryItem tables for a single InventoryItem record.
You'll create a blank form and build an ad-hoc query as the record source. Then you'll add the fields to the form and use a layout control to arrange them. You'll set up a filter to display a single record and remove the selection and navigation control just like you did with the CustomerDisplay form.
From the Create tab of the ribbon, click the Blank Form button. Switch to the Design View.
CREATING AN AD-HOC QUERY
Now you'll design an ad-hoc query that will return the appropriate subset of records from the InventoryItem table.
1. In the Property Sheet, select the Form object and the Data tab. Click the ellipses next to the Record Source property. This will display a blank query in Design View.
2. Add the following tables to the query (the query should look like Figure 7-21):
InventoryItem
Item
Category
Media
Figure 7.21. The query design for the InventoryItemLookup form
3. Double-click the following fields to add them to the query:
InventoryItem.InventoryItemID
InventoryItem.Status
InventoryItem.Condition
InventoryItem.Comment
Item.Author
Item.Title
Item.Description
Category.CategoryDescription
Media.MediaDescription
Media.LoanPeriod
4. Click the Close button. You'll see the dialog box shown in Figure 7-22. Click the Yes button to update the Record Source property.
Figure 7.22. Dialog box confirming the property change
ADDING THE FIELDS TO THE FORM
The fields from this query will now be added to the form and the initial form layout will be designed.
1. Click the Add Existing Fields button in the ribbon, which will list all of the available fields. Double-click each one to add it to the form.
2. Delete the label for the InventoryItemID control. The InventoryItemID field will be hidden and only used for linking with the parent form. Select this control and in the Format tab of the Property Sheet set the Visible property to No. Drag this control to the top-left corner of the form.
3. Select all of the controls except InventoryItemID and their associated labels. From the Arrange tab of the ribbon, click the Stacked button to create a layout control. Click the Insert Right button twice to add two more columns. Click the Insert Above button twice to add two blank rows at the top of the form.
4. To save space on the form, delete the labels for all of the fields except Comment and LoanPeriod. The data for these fields should be fairly self-explanatory.
5. Drag the Title field to the top-left cell. Then select this cell and the three cells to the right and click the Merge button.
6. Drag the Author control to the first cell in the second row. Merge this cell with the cell next to it (the Author field will use the first two cells of this row).
7. Delete the Status control. This was generated as a ComboBox control but we don't want this field editable so you'll replace it with a TextBox control. From the Design tab of the ribbon, click the Text Box button and then draw a rectangle in the form to create the control. Delete the associated label control. Select the new TextBox control and change its Name property to Status. For the Control Source property, select the Status field. Drag this control just to the right of the Author control.
8. Drag the Condition control to the right of the Status control. You'll leave this as a ComboBox control, because the user will be able to modify the condition of the item.
9. Drag the Comment control to the second cell in the third row. Its label should move to the first cell automatically. Merge this control with the two empty cells beside it.
10. Drag the CategoryDescription control to the first cell in the fourth row. Drag the MediaDescription control to the second cell. Drag the LoanPeriod control to the fourth cell. Again, its label should move to the third cell.
11. Drag the Description control to the fifth row. Merge all the cells on this fifth row into a single cell.
MODIFYING THE FORM DESIGN
Next you'll perform some cleanup, removing unused cells and setting an appropriate cell height.
1. Remove all of the empty rows.
2. Select one of the controls in the layout and click the Select Layout button to select all of the controls. Click the Control Padding button, and then click the None link.
3. You may have noticed that the blank rows at the top of the layout are not as high as the other rows. To assure uniformity, select all the cells and in the Format tab of the Property Sheet, set the Height property to .22.″ The Description control will need to be larger, however. Select only the Description control and resize it to be about 3 lines high.
4. Select the entire layout and drag it to the top-left corner of the form.
5. Go to the Layout View and resize the controls as necessary so the data fits properly.
6. Go back to the Design View and shrink the form size to remove all unused space.
FINALIZING THE FORM DETAILS
In the final step, you'll lock down the fields that should not be editable and finalize the form implementation.
1. Select the following controls and in the Data tab of the Property Sheet, set the Locked property to Yes. This will prevent the user from modifying these fields because the only fields that will be editable are Condition and Comment.
Title
Author
Status
CategoryDescription
MediaDescription
LoanPeriod
Description
2. Save the form and enter the name InventoryItemLookup when prompted.
3. You will need an unbound control that you can use to indicate if a record was read successful. Add a TextBox control to the form and set its Name property to lblTitle. Also set its Visible property to No. Delete the associated label.
4. In the Property Sheet, select the Form object and the Event tab. For the On Current property select Event Procedure and click the ellipses. Enter the following code for the implementation of this event handler:
lblTitle = Title
This code simply updates the unbound control with whatever is in the Title field.
TIP
The code that checks to see if a record was read successfully first sets one of the fields to a blank string. It then re-queries the form and checks to see if the value is still blank. You can't do this with a bound control, because setting it to blank will update the record. Instead you'll use the lblTitle control, which is copied from the bound Title control when a record is read.
The final layout should look like Figure 7-23.
Figure 7.23. The layout of the InventoryItemLookup form
5. Just like the CustomerDisplay form that you designed earlier, this form should display a single record so you'll need to remove all the navigation controls. In the Property Sheet, select the Form object and the Format tab. Set the following property values:
Border Style: None
Record Selectors: No
Navigation Buttons: No
Scroll Bars: Neither
Control Box: No
Close Button: No
Min Max Buttons: None
6. Also, in the Data tab, set the Filter property as [InventoryItemID] = InventoryItemID and set the Filter On Load property to Yes. Test out the form change switching to the Design View. The form should look like Figure 7-24.
Figure 7.24. The completed InventoryItemLookup form
Linking the InventoryItemLookup Subform
Now you're ready to add the InventoryItemLookup form as a subform to the CheckOut form. This will be done just like you added the CustomerDisplay form. You'll add a TextBox control where the user can enter the ID of the inventory item. This will be linked to the child form so the details can be displayed.
1. Close the InventoryItemLookup form and then open the CheckOut form in the Design View.
2. You'll need to add some more cells to drop the new controls into. Go the Arrange tab of the ribbon. Select the existing layout control and click the Insert Right button five times and the Insert Below button once.
3. Add a TextBox control to the Form Header and set its Name property to txtInventoryItemID. Enter the Caption for the associated label as InventoryID: and set the Text Align property to Right. Drag the txtInventoryItemID control to the top row, leaving two blank cells to the right of the Search button. The label should move to the left of this control, leaving a single blank cell after the Search button.
4. Merge the last four cells of the last two rows into a single cell. This will leave a blank column between the existing customer controls and the new inventory item controls.
5. From the Design tab of the ribbon, click the Subform button and then click inside the Form Header. This will start the Subform Wizard. Select the InventoryItemLookup form, as shown in Figure 7-25.
Figure 7.25. Selecting the existing InventoryItemLookup form
6. In the next dialog box, accept the default name, which should be InventoryItemLookup.
7. Delete the associated label that was generated and drag the subform to the merged cell at the bottom right of the Form Header.
8. Switch to the Layout View and resize the cells so the subform fits properly. The layout should look like Figure 7-26.
TIP
To increase the vertical size of the merged cell without affecting the cells used by the CustomerDisplay form, select the blank cell underneath the CustomerDisplay form and change its height. Likewise, to increase the horizontal size, select the cell to the right of the txtInventoryItemID control and increase its width.
Figure 7.26. The layout of the CheckOut Form Header
9. Go back to the Design View and select the InventoryItemLookup subform. In the Data tab of the Property Sheet, enter txtInventoryItemID for the Link Master Fields property and InventoryItemID for the Link Child Fields property. This will establish the link between the parent and child forms. Also, in the Format tab, set the Visible property to No.
10. The last step is to implement the LostFocus event for the txtInventoryItemID control. Select this control and in the Event tab of the Property Sheet, select Event Procedure for the On Lost Focus property. Then click the ellipses to display the VBA code.
11. For the txtInventoryItemID_LostFocus method, enter the following implementation:
InventoryItemLookup!lblTitle = ""
DoCmd.Requery "InventoryItemLookup"
If (Len(InventoryItemLookup!lblTitle) > 0) Then
InventoryItemLookup.Visible = True
Else
InventoryItemLookup.Visible = False
End If
This code should be familiar to you since it's very similar to the LostFocus event that you implemented for the txtCustomerID control. It clears the unbound TextBox control (lblTitle), re-queries the form and then checks to see if the control is still blank.
Save the code and save the form. Test out the CheckOut form by switching to the Form View. Enter an InventoryItemID and verify that it displays the data correctly. Try entering a comment and changing the condition. Also try entering an invalid InventoryItemID and verify that no information is displayed.
Designing the CheckOut Details
When an item is checked out, a Loan record is created. The Detail section will contain these Loan records; as items are checked out, they will be added to the Detail section of the form. The record source for the form will be the Loan tabled, filtered to only include those records loaned out to the current customer.
However, the customer could have items that were previously checked out so the query needs to only retrieve the current items. To accomplish that, you'll need to modify the Loan table to add an InProgress field. You'll set the default value to be Yes so all new records will show as in progress. When the checkout has completed, you'll reset this flag.
ALTERING THE LOAN TABLE
Open the Loan table in the Design View. Add a new field named InProgress and select Yes/No for the Data Type. In the Field Properties window, set the Default Value property to 1, as shown in Figure 7-27.
Figure 7.27. Adding the InProgress field
CREATING A LOANDETAIL QUERY
The next step is to create a query that returns all in progress records for the current customer. From the Create tab of the ribbon, click the Query Design button. Add the following tables to the query:
Loan
InventoryItem
Item
There are two relationships between the Loan and InventoryItem tables; delete the one between Loan.LoanID and InventoryItem.CurrentLoanID. (This only affects the current query and not the underlying table relationships.) Add the following fields to the query:
Loan.CustomerID
Loan.InventoryItemID
Loan.CheckedOut
Loan.DueDate
Loan.InProgress
Item.Title
For the CustomerID field, enter [Forms]![CheckOut]![txtCustomerID] for the criteria. For the InProgress field, enter True for the Criteria. Save the query and enter the name as LoanDetail when prompted. The query design should look like Figure 7-28.
Figure 7.28. The query design for LoadDetail
DESIGNING THE DETAIL SECTION
Now you'll set up the new LoanDetail query as the record source for the CheckOut form and add data-bound controls to the Detail section. This will display the items that have been checked out so far.
1. Open the CheckOut form in the Design View. In the Property Sheet, select the Form object and the Data tab. For the Record Source property, select the LoanDetail query.
2. Click the Add Existing Fields button in the ribbon. Double-click each of the following fields to add it to the form:
InventoryItemID
Title
DueDate
CustomerID
3. Select all of these controls. From the Arrange tab of the ribbon, click the Tabular button. The labels will be placed in the Form Header.
4. Select all of the labels and drag them to the bottom of the Form Header. Select all of the data-bound controls and drag then to the top of the Detail section. Select both the controls and the labels and drag them to the left edge of the form.
5. Make the Title and DueDate controls wider.
6. The InventoryItemID and CustomerID controls were created as ComboBox controls. These will be read-only fields, so you'll need to replace them with TextBox controls. Delete both controls.
7. Add a TextBox control to the Detail section. Delete the associated label. Change the Name property to InventoryItemID, and select InventoryItemID as the Control Source.
8. In the same manner add another TextBox control, delete the associated label, set the Name property to CustomerID, and select CustomerID as the Control Source.
9. Drag these controls into the cells where the ComboBox controls were.
10. Delete the label for the CustomerID field. Select the CustomerID control and set the Visible property to No. The CustomerID control is used for inserting records and does not need to be displayed.
11. Select all the data-bound controls in the Detail section and set the Locked property to Yes.
12. In the Property Sheet, select the Form object and set the Default View property to be Continuous Form.
ADDING THE HEADER AND FOOTER CONTROLS
There are just a few more things to finish up the form design. You'll need a CheckOut button in the Form Header that will create the Loan record. You'll also need a Complete button in the Form Footer that will execute the final step of the process. You'll also add a control to the Form Footer to keep a running count of the number of items being checked out.
1. Add a command button to the Form Header. Cancel the Command Button Wizard and drag the control to the top-right cell. Set the Name property to CheckOut and the Caption property to CheckOut as well. Set the Visible property to No.
2. Add a command button to the Form Footer. Again, cancel the wizard and enter the Name and Caption properties as Complete.
3. Add a TextBox control to the Form Footer. Enter the Caption of the associated label as Items checked out: and set the Text Align property to Right. Change the control name to txtCount. Enter =Count(*) for the Control Source property.
4. Select all three fields in the Form Footer. In the Arrange tab of the ribbon, click the Stacked button. Click the Insert Right button twice to add two more columns.
5. Drag the Complete button the right-most cell in the first row and delete the empty row.
6. Drag the layout control to the top-left corner of the Form Footer. Resize the third cell to move the Complete button to the right.
The final layout should look like Figure 7-29.
Figure 7.29. The layout of the CheckOut form
Implementing the CheckOut Logic
Now you're ready to implement the checkout process. At this point, the user can select a customer and an inventory item. That's all you need to create a Loan record. The first thing you'll need to do is hide the CheckOut button until both a CustomerID and InventoryItemID have been entered. Then the CheckOut button needs to be implemented so it inserts a record into the Loan table. Finally, you'll implement the Complete button that will finalize the process.
ENABLING THE CHECKOUT BUTTON
Edit the VBA code for the txtInventoryItemID LostFocus event and add some code to hide or display the CheckOut button. Listing 7-4 shows the modified code and the new lines are shown in bold.
Example 7.4. The modified LostFocus Event
InventoryItemLookup!lblTitle = ""
DoCmd.Requery "InventoryItemLookup"
If (Len(InventoryItemLookup!lblTitle) > 0) Then
InventoryItemLookup.Visible = True
If (Len(CustomerDisplay!lblName) > 0) Then
CheckOut.Visible = True
If (InventoryItemLookup!Status = "Available") Then
CheckOut.Enabled = True
Else
CheckOut.Enabled = False
End If
Else
CheckOut.Visible = False
End If
Else
InventoryItemLookup.Visible = False
CheckOut.Visible = False
End If
The new code makes the button visible if there is an inventory item displayed in the InventoryItemLookup subform. The button is then enabled if the item is available. Otherwise, the button is disabled. If the user scans an item that is not available for some reason, they'll see a grayed CheckOut button that will indicate the item cannot be checked out. The details in the subform such as Status and Comment should explain why it is not available.
IMPLEMENTING THE CHECKOUT BUTTON
If the item is available to be loaned out, the user can click the CheckOut button. This will insert a record into the Loan table and perform some clean-up to prepare for additional records to be checked out.
Right-click the CheckOut button and click the Build Event link. At the prompt choose Code Editor. Enter the implementation of the CheckOut button using the code from Listing 7-5.
TIP
The OnClick event is the default event for a command button so you can use the shortcut approach to creating an event handler. For other events, you should select the event in the Property Sheet and click the ellipses like you have done before.
Example 7.5. The Implementation of the CheckOut Button
' Go to a new record
DoCmd.GoToRecord acDataForm, "CheckOut", acNewRec
CustomerID = txtCustomerID
InventoryItemID = txtInventoryItemID
' Cause the focus to move off the current record, which
' will cause it to be saved
DoCmd.GoToRecord acDataForm, "CheckOut", acNext
DoCmd.GoToRecord acDataForm, "CheckOut", acLast
' Reset the form controls
txtInventoryItemID = ""
DoCmd.Requery "InventoryItemLookup"
InventoryItemLookup.Visible = False
CheckOut.Visible = False
This code manipulates the form to insert a record rather than executing a SQL command directly. The GoToRecord method allows you to navigate through the records on the form just like the navigation controls at the bottom of the form. The last parameter of this function species the navigation option such as first, next, or last. In this case, you used the acNewRec constant, which goes to the record just after the last one. This is where you would add a new record to the form.
The form is now on a new, blank record. The code then sets the values of the two required fields, CustomerID and InventoryItemID. It uses the unbound controls in the Form Header, which contain the correct values. The GoToRecord method is called again to go to the next record. Just as with navigating a form manually, when you navigate off the current record, the updates are saved to the database. This causes the record to be inserted into the Loan table. GoToRecord is called one more time to make the row that was just added the current record.
The code then clears the txtInventoryItemId control. It also hides the InventoryItemLookup subform and the CheckOut button.
IMPLEMENTING THE COMPLETE BUTTON
After all the items have been entered, the user will use the Complete button to finalize the process. This clears the InProgress flag and then resets the form so it's ready for the next customer.
Right-click the Complete button and click the Build Event link. At the prompt, choose Code Editor. Enter the implementation of the Complete button using the code from Listing 7-6.
Example 7.6. The Implementation of the Complete Button
Dim sSQL As String
If (Len(txtCustomerID) > 0) Then
sSQL = "UPDATE Loan SET Loan.InProgress = False WHERE Loan.InProgress=True " & _
"AND Loan.CustomerID=" & txtCustomerID & ";"
Application.CurrentDb.Execute sSQL, dbFailOnError
End If
txtCustomerID = ""
txtInventoryItemID = ""
Me.Requery
CustomerLookup.Visible = False
InventoryItemLookup.Visible = False
CheckOut.Visible = False
txtCustomerID.SetFocus
As I mentioned earlier, the Loan records that are added during this "session" need to be displayed on the form without including items that were previously checked out. You did this by only including records where the InProgress flag was true. This is the default value of this field so all new records have the InProgress flag set. Now that the checkout process is complete, you'll need to clear these flags. This is done by executing a SQL command.
The remainder of the code clears the input fields and hides the sub forms. The Me.Requery statement causes the main form to refresh its data. Since the txtCustomerID field has been cleared, it will not find any matching records and the form will be empty.
Testing the Application
The CheckOut form is now complete, so let's test it.
1. Open the form in the Form View. Select a customer by entering the ID or using the Search feature.
2. Then enter an InventoryItemID. Try some that are available and some that are not to see if the CheckOut button is enabled correctly.
3. Then try clicking the CheckOut button and add a few items to the form. The form should look like Figure 7-30.
Figure 7.30. The final CheckOut form with items added
4. When you're done, click the Complete button and verify that the form is reset and ready for the next customer. You should also look at the Loan table and verify the records that were added by the form.
I have included the complete code file for the CheckOut form in Listing 7-7.
Example 7.7. The Complete Code Module for the CheckOut Form
Option Compare Database
Private Sub CheckOut_Click()
DoCmd.GoToRecord acDataForm, "CheckOut", acNewRec
CustomerID = txtCustomerID
InventoryItemID = txtInventoryItemID
' Cause the focus to move off the current record, which
' will cause it to be saved
DoCmd.GoToRecord acDataForm, "CheckOut", acNext
DoCmd.GoToRecord acDataForm, "CheckOut", acLast
' Reset the form controls
txtInventoryItemID = ""
DoCmd.Requery "InventoryItemLookup"
InventoryItemLookup.Visible = False
CheckOut.Visible = False
End Sub
Private Sub Complete_Click()
Dim sSQL As String
If (Len(txtCustomerID) > 0) Then
sSQL = "UPDATE Loan SET Loan.InProgress = False " & _
"WHERE Loan.InProgress=True " & _
"AND Loan.CustomerID=" & txtCustomerID & ";"
Application.CurrentDb.Execute sSQL, dbFailOnError
End If
txtCustomerID = ""
txtInventoryItemID = ""
Me.Requery
CustomerDisplay.Visible = False
InventoryItemLookup.Visible = False
CheckOut.Visible = False
txtCustomerID.SetFocus
End Sub
Private Sub Search_Click()
Dim sForm As String
sForm = "CustomerSearch"
' Open the search form
DoCmd.OpenForm sForm, acNormal, , , , acDialog
' If the form is not loaded, the user clicked the Cancel button
If (IsLoaded(sForm)) Then
txtCustomerID = Forms(sForm)!CustomerID
DoCmd.Close acForm, sForm
Else
txtCustomerID = ""
End If
' Force a refresh if the CustomerDisplay subform
txtCustomerID_LostFocus
End Sub
Private Sub txtCustomerID_LostFocus()
CustomerDisplay!lblName = ""
CustomerDisplay!lblAddress = ""
CustomerDisplay!lblRegion = ""
CustomerDisplay!lblEmail = ""
CustomerDisplay!lblPhone = ""
DoCmd.Requery "CustomerDisplay"
If (Len(CustomerDisplay!lblName) > 0) Then
CustomerDisplay.Visible = True
Else
CustomerDisplay.Visible = False
End If
End Sub
Private Sub txtInventoryItemID_LostFocus()
InventoryItemLookup!lblTitle = ""
DoCmd.Requery "InventoryItemLookup"
If (Len(InventoryItemLookup!lblTitle) > 0) Then
InventoryItemLookup.Visible = True
If (Len(CustomerDisplay!lblName) > 0) Then
CheckOut.Visible = True
If (InventoryItemLookup!Status = "Available") Then
CheckOut.Enabled = True
Else
CheckOut.Enabled = False
End If
Else
CheckOut.Visible = False
End If
Else
InventoryItemLookup.Visible = False
CheckOut.Visible = False
End If
End Sub
Summary
In this chapter you created a form that allows items to be loaned out to a customer. Along the way, you implemented a number of techniques that you will undoubtedly find an application for in many future projects. Some of these will also be used in subsequent chapters of this book.
The new design approaches that you implemented include:
Implementing a search form as a modal dialog
Creating a reusable data-bound subform
Manipulating the form records through VBA code
Executing a SQL command
You also used the layout control and the Layout View to format several forms.
Chapter 8. Creating a Customer Admin Form
In the previous chapter, you created a CheckOut form that demonstrated some advanced techniques with Access forms. Now, I'll continue along the same theme, digging a little bit deeper into how forms work and explaining ways to configure them. I'll also demonstrate the TabControl, which is useful for organizing a lot of information.
In this chapter, you'll build a customer administration form that can be used to create and update customer records, show a history of what they have previously checked out, and display the items that are currently due. This form will also allow you to renew items that are checked out. You will reuse some of what was developed in Chapter 7, including the CustomerSearch form. Hopefully, I will instill in you the benefits of designing reusable forms.
Building the Customer Profile Tab
The form that you'll build will use a TabControl and the first page will show the customer's name and contact information. It will also allow you to update this information and to set up a new customer. Once you have that working, I'll show you how to add other features in subsequent pages.
You'll start by creating a stand-alone form for adding and updating customer information. You will then drop this on the main form along with the existing CustomerDisplay form. You'll then need to write some code to tie these together.
Creating a CustomerUpdate Form
In the last chapter, you created a CustomerDisplay form that presented the customer's name, address, email, and phone in a compact form that looks much like a mailing label. This was handy to drop on a form without taking up much space. However, this was not designed to allow updates; you'll need a different form for that, which you'll design now.
You'll use the standard form template to create a new form based on the Customer table.
1. Select the Customer table in the Navigation pane then click the Form button in the Create tab of the ribbon. The initial form, which is displayed in the Layout View, should look like Figure 8-1.
Figure 8.1. Initial CustomerUpdate form
2. Select one of the data-bound controls and drag the right edge to the left to shrink the width to about 1 inch.
3. Finally, add two more columns using the Insert Right button on the Arrange tab of the ribbon.
The CustomerID field will not be displayed, as I discussed in Chapter 6. This surrogate key is not meaningful to the end user, so you won't clutter the form with this information. However, you'll need the CustomerID control on the form, because it will be used to link this form to a parent form. You will hide this control and delete the associated label. Even though the control is not visible, it still takes up a cell in the layout control. Access will not allow you to put two controls in the same cell. To resolve this, you'll need to remove this control from the layout control. You can then move it to wherever you want it, even on top of another control. It doesn't really matter, because it is not visible.
NOTE
You cannot remove a control from the layout while in the Layout View. You'll need to first switch to the Design View.
1. Switch to the Design View. Select the CustomerID control. From the Arrange tab in the ribbon, click the Remove Layout button, as shown in Figure 8-2.
Figure 8.2. Removing the CustomerID control from the layout
2. When you removed the control, its associated label is also removed. Because all the cells on that row are now empty, the row is deleted, moving all the controls up one row. This can be a little confusing, because the CustomerID control is still where it was but now the LastName control is on top of it. Drag the LastName control to one of the empty cells on the right as shown in Figure 8-3 and you'll be able to see the CustomerID control.
Figure 8.3. Temporarily moving the LastName control
TIP
At run time, the CustomerID control is not visible. However, when designing the form, you'll need to be able select it and modify its properties. What I suggest with these "floating" controls is that you resize them to make them small enough to completely fit inside of one of the layout cells with space around it. Then bring the control to the front so other controls are not hiding it. With this approach, you can see and manipulate the control without hiding any of the visible controls.
3. Select the layout control and drag it down about half an inch to expose the CustomerID control. Delete the associated label for the CustomerID control.
4. Shrink the CustomerID control to about half of its original height and width. In the Format tab of the Property Sheet, set its Visible property to No.
5. Right-click the CustomerID control and click the Position
Figure 8.4. Bringing the CustomerID control to the front
6. Drag the CustomerID control to the bottom of the form. You'll move it to a permanent location once the form layout is complete.
7. Switch back to the Layout View.
8. Change some of label captions (these can be edited directly on the form):
Country Region: Country
State Province: State
Zip Postal: Postal
PhoneNumber: Phone
9. Select all the cells in the layout and, in the Format tab of the Property Sheet, set the Height property to .22.′
10. Select all the label controls and set their Text Align property to Right.
11. Drag the entire layout control to the top-left corner of the form.
12. Arrange the controls and size the cells, as shown in Figure 8-5.
Figure 8.5. The modified CustomerUpdate form layout
13. When you try to drag the CustomerID control on top of one of the cells in the layout, Access will try to insert a row in the layout. You'll see an orange line appear in between two rows indicating where a row will be created to accept the control. This is not what you want. Instead, drag it near the layout, just to the right of the Address control. Click the left edge of the CustomerID control and drag it to the left until it is well inside the Address control. Then click the right edge and shrink the control back to its original size.
14. Finally, shrink the height and width of the form to remove any unused space. You may notice that it won't let you shrink the width. You can't make the form smaller than the collection of controls placed on it. The Form Header has a Label control that is as large as the original form; you'll need to first resize this to make it smaller than the layout control in the Detail section.
15. Save the form and enter the name CustomerUpdate when prompted.
Open the form in Form View, which should look like Figure 8-6.
Figure 8.6. Completed design of the CustomerUpdate form
Configuring the Form Controls
In Chapter 7, I gave some instructions for setting various form properties without explaining what they were for. I'll now explain the form controls that Access generates for you, how they work, and how to configure them using the form Property Sheet. Figure 8-7 shows the CustomerUpdate form, and I have circled and numbered the controls.
Figure 8.7. Identifying the form controls
1. Record Selector: The record selector serves two purposes. In the Datasheet View, the record selector indicates which row is currently selected. In Form View, this is not needed, because only a single record is displayed. However, the second purpose is to indicate if the data has changed. If any of the fields on the selected record have changed and the change has not been saved yet, the arrow will change to a pencil, indicating a change has been made. You can also click on the record selector to force the changes to be saved. The CustomerDisplay form you created in Chapter 7 was read-only so the record selector was unnecessary. To remove this, set the Record Selectors property to No.
2. Navigation Controls: The navigation controls are at the bottom of the form. The Navigation Buttons property determines if this row of controls is visible or not. The "1 of 443" text lets you know there are 443 records currently available and you're on the first one. There are buttons to go to the first, previous, next, and last records. I've circled the area where most of the controls are, but the entire row is used for navigation functions. If the Navigation Buttons property is set to No, the areas marked as 3, 4, and 5 are not displayed either.
3. Navigation Caption: The word "Record" is defined by the Navigation Caption property. If no value is specified, the default text, "Record," is used. If you wanted this to be "Customer," just enter that text in the Navigation Caption property.
4. New Record: The control just to the right of the Last Record button is used to create a new record. This is disabled if the Allow Additions property is set to No.
5. Filter: Access allows the end user to define ad-hoc filters, which is a really nice feature. The control here indicates if a user-defined filter has been defined. If not, the control will display "No Filter." If a filter has been applied, the text will be "Filtered." You can temporarily toggle a filter, which will display all the records. In this case, the text on this control will be "Unfiltered." If a user-defined filter has been defined, you can toggle between Filtered and Unfiltered by clicking on this control. You can turn off the filtering feature by setting the Allow Filters property to No.
NOTE
Filters defined at design time or programmatically are not affected by the Allow Filters property. You can turn this feature off to prevent the user from using filters and still define a default filter at design time and apply a filter using code.
6. Caption: You can set the Caption property to specify what text will appear in the form tab. If none is specified, the form will use the contents of the Name property. In the Design View, this is ignored and the Name property is used on the tab heading.
7. Close: The close button at the top-right corner of the form closes the form. You can disable this by setting the Close Button to No. This will prevent you from closing the form in any view except the Design View.
NOTE
When a form is used as a subform, neither the Caption nor the Close Button properties are used. These do not apply to child forms.
Now with this understanding, you'll configure the CustomerUpdate form that you just created. The record selector would be useful for indicating when there are unsaved changes. It also works as a Save button. Just like the CustomerDisplay form, this form will be configured to show the record associated with the parent form. So there's no need for the navigation controls.
Set the following properties; the remaining form properties can be left at their default values.
Record Selectors: Yes
Navigation Buttons: No
Filter: [CustomerID] = CustomerID
Filter On Load: Yes
Finally, you'll need to remove the Form Header that was generated by the template. It contains a logo image and a Label control. Delete both of these controls and the drag the Detail section up to shrink the Form Header so there is no space between them. Save the form and switch to the Form View. The form should look like Figure 8-8.
Figure 8.8. The final CustomerUpdate form
Creating the CustomerAdmin Form
Now you're ready to create the CustomerAdmin form and implement the first page. You'll provide a search facility in the Form Header just like the CheckOut form. When a customer is selected, the existing CustomerDisplay form will be included in the Profile page to show their contact information. This page will also have a Modify button. If the user needs to change this information, the Modify button will show the new CustomerUpdate form that they will use to update the data.
If no customer was selected, a New button will appear that will allow the user to create a new Customer record. It will use the same CustomerUpdate form to enter the details for the new customer. When the update is complete, the user will click the Finish button, which will hide the CustomerUpdate form and refresh the CustomerDisplay form.
DESIGNING THE FORM HEADER
In the Form Header you'll add a TextBox control and a Search button to enter or find the desired customer, just like you did with the CheckOut form in the last chapter.
1. From the Create tab of the ribbon, click the Blank Form button. Switch to the Design View and then right-click the form and click the Form Header/Footer link. Expand the Form Header to about 1-inch high.
2. Add a TextBox control to the Form Header and change the Name property to txtCustomerID. Set the Caption of its associated label to CustomerID:. Set the Text Align property for the label to Right.
3. Add a command button to the Form Header and cancel the Command Button Wizard. Change the Name property to Search and set the Caption property to Search...
TIP
You can disable the wizards, such as the Command Button Wizard, when adding controls to the form. Sometimes they're helpful, and other times they're not. It's pretty easy to just cancel them if you don't want a wizard for a particular control. But you can also turn these off. In the Design tab of the ribbon, click the dropdown icon in the Controls section, which will expand this area and show all the available controls. Just click the Use Control Wizards link as shown in Figure 8-9. To enable the wizards, repeat this step to toggle them back on.
Figure 8.9. Disabling the control wizards
4. Select all three controls in the Form Header. From the Arrange tab of the ribbon, click the Stacked button. This should create two columns.
5. Click the Insert Right button on the ribbon to create a third column.
6. Drag the command button to the last column of the first row. Delete the empty second row.
7. Select the entire layout and drag it to the top-left corner of the Form Header. Shrink the Form Header so there is no empty space beneath these controls.
The Form Header layout should look like Figure 8-10.
Figure 8.10. The layout of the Form Header
USING A TAB CONTROL
A tab control in Access works basically the same as in any .NET application. Each page is like its own mini-form. You design the controls on each page independently from the other pages.
NOTE
In a tab control, you will have multiple pages of controls. The tab is the small portion at the top that contains the label describing the data for that page. The object below the tab is called the page. Think of a file folder, where the tab is the part that sticks out where you add a label to identify it. Often the term tab is used to refer to the page as well. We can generally infer based on the context what is being referred to. I will call them pages; if you're used to them being called tabs, I hope this will not be confusing.
One thing to keep in mind when designing a tab control is that the pages all need to be the same size. More specifically, the size of all of the pages will be defined by the size of the largest one. As you plan what should be on each page try to keep this size rule in the back of your mind. This is a relatively minor point; it is far more important to keep the pages organized logically. Don't put a control on a page just because there is more room on that page. Let's get started.
1. From the Design tab of the ribbon, click the Tab Control button. Then draw a rectangle in the Detail section that is about the size of the entire section. This will create a TabControl with two tabs (pages). The label on the tab will be, initially, the name of the Page control associated with the tab. In my case, the first one is Page11. (I must have created 10 pages before I added this one.)
2. Click just below the tab to highlight a rectangle around the page. The Property Sheet should indicate that the Page11 object is selected (or whatever your page was named).
3. In the Format tab, change the Caption property to Profile. In the Other tab, change the Name property to Profile.
WARNING
When adding a control to a page, make sure that the page is selected first. An orange rectangle that is just below that tabs is used to show the page is selected. If there are already controls on the page, you can just select one of these controls. That will also put the page in focus. If the orange rectangle goes around the tabs then you have selected the TabControl not a particular page on that control. If the control is not included on a specific page, it won't be really obvious...until you go to another tab and you notice that the control is still visible. Let's just say I've learned from experience.
DESIGNING THE PROFILE PAGE
The Profile page will contain the CustomerDisplay form that you implemented in Chapter 7 and the CustomerUpdate form that you just created. You will also need a few command buttons to tie everything together.
1. Make sure the control wizards are enabled and the Profile page is selected. Click the Subform button in the ribbon, and then click inside the Profile page. In the Subform Wizard, select the CustomerDisplay form as shown in Figure 8-11 and click the Next button.
Figure 8.11. Selecting the CustomerDisplay form
2. In the next dialog box, use the default name, CustomerDisplay and click the Finish button. The wizard also generated an associated label; delete this.
3. Select the Subform control and, in the Arrange tab of the ribbon, click the Stacked button. Because there is only one control, the layout will have a single row and column. Click the Insert Below button twice to add two rows. Click the Insert Right button once to add another column.
4. Merge both cells in the last row into a single cell.
5. From the Design tab of the ribbon, click the Subform button again and then click on the Profile page. In the Subform Wizard, select the CustomerUpdate form as shown in Figure 8-12 and then click the Next button.
Figure 8.12. Selecting the CustomerUpdate form
6. In the next dialog box, use the default name, CustomerUpdate and click the Finish button. Again, delete the associated label.
7. Drag this Subform to the bottom-left cell of the layout control.
8. Switch to the Layout View and size the cells to fit the subforms correctly.
TIP
The cell for the CustomerDisplay subform should already be sized correctly. To adjust for the CustomerUpdate subform without affecting the CustomerDisplay subform, increase the vertical size of the last row and the horizontal size of the top-right cell.
9. Drag the entire layout to the top-left corner of the Profile page.
10. You will add three command buttons to the row of cells between the two subforms. Select the left cell of this row and click the Split Horizontally button. This will split the cell into two cells. Select one of these and click the Split Horizontally button again. Now you'll have three cells where there used to be one.
11. Change the width of these three cells so they are about the same size. This will adjust the size of the subforms so you'll need to keep an eye of that as well.
12. Create three command buttons, one in each cell. Enter the following Name and Caption properties:
ModifyCustomer: Modify...
NewCustomer: New...
Finish: Finish
13. You may need to adjust the height of the row containing the command buttons.
14. Select all the controls (the two subforms and the three buttons) and in the Format tab of the Property Sheet, set the Visible property to No. You will display these controls with VBA code as appropriate.
15. Save the form and enter the name CustomerAdmin when prompted.
The form layout should look like Figure 8-13.
Figure 8.13. The layout of the Profile page
IMPLEMENTING THE CONNECTING CODE
The last step of making this all work is implementing the code that connects the forms. First you'll set up the links between the parent page and the sub forms. Then you'll implement several event handlers.
1. Select the CustomerDisplay subform. In the Data tab of the Property Sheet, enter txtCustomerID for the Link Master Fields property and CustomerID for the Link Child Fields property.
2. Select the CustomerUpdate subform and enter the exact same values for these two properties. You probably noticed that, as soon as you did this, the data disappeared from the subforms. That's because there is no value defined for the txtCustomerID control in the Form Header. Now you just need to implement the VBA code. Most of this should be familiar, because it is similar to what you implemented in Chapter 7.
TIP
So far, to implement an event handler you selected the Event Procedure option on the Property Sheet for the appropriate event and then clicked the ellipses, which generated the method and displayed the VBA editor. As an alternative, you can right-click the command button and choose the Build Event link, which accomplished the same thing. If you know the name of the events for which you want to generate handlers, you can go directly to the VBA code. From the Design tab of the ribbon, click the View Code button. This will display the VBA editor and generate a code file for the form, if necessary. Then you can create the event handlers in VBA without having to enter anything in the Property Sheet.
3. From the Design tab of the ribbon, click the View Code button. This will launch the VBA editor and display an empty file for this form. Enter the methods shown in Listing 8-1.
Example 8.1. Event Handlers for the Profile Page
Private Sub txtCustomerID_LostFocus()
CustomerDisplay!lblName = ""
DoCmd.Requery "CustomerDisplay"
If (Len(CustomerDisplay!lblName) > 0) Then
CustomerDisplay.Visible = True
ModifyCustomer.Visible = True
NewCustomer.Visible = False
Else
CustomerDisplay.Visible = False
NewCustomer.Visible = True
NewCustomer.SetFocus
ModifyCustomer.Visible = False
End If
End Sub
Private Sub Search_Click()
Dim sForm As String
sForm = "CustomerSearch"
' Open the search form
DoCmd.OpenForm sForm, acNormal, , , , acDialog
' If the form is not loaded, the user clicked the Cancel button
If (IsLoaded(sForm)) Then
txtCustomerID = Forms(sForm)!CustomerID
DoCmd.Close acForm, sForm
Else
txtCustomerID = ""
End If
' Force a refresh if the CustomerDisplay subform
txtCustomerID_LostFocus
End Sub
Private Sub ModifyCustomer_Click()
CustomerUpdate.Visible = True
Finish.Visible = True
Finish.SetFocus
ModifyCustomer.Visible = False
End Sub
Private Sub NewCustomer_Click()
CustomerUpdate.Visible = True
Finish.Visible = True
Finish.SetFocus
NewCustomer.Visible = False
End Sub
Private Sub Finish_Click()
ModifyCustomer.Visible = True
CustomerUpdate.Visible = False
ModifyCustomer.SetFocus
Finish.Visible = False
' Force a refresh
txtCustomerID = CustomerUpdate!CustomerID
txtCustomerID_LostFocus
End Sub
The implementation of txtCustomerID_LostFocus is almost identical to the version from Chapter 7 except that it has to take care of showing or hiding the Modify and New buttons. If a customer was found, it shows the Modify button and if not, the New button is shown. In either case, only one is visible; whichever one is shown, the other is hidden.
The implementation of Search_Click is identical to what you implemented in Chapter 7. The last three methods handle the OnClick event of the three buttons. The first two, ModifyCustomer_Click and NewCustomer_Click are basically the same. They show the CustomerUpdate form and the Finish button and hide themselves.
The implementation of Finish_Click is a little more interesting. It hides the CustomerUpdate form and itself and shows the Modify button. That puts everything back like it was before the first Modify button was clicked. The last part of this method gets the CustomerID from the CustomerUpdate form and updates the TextBox control in the Form Header. This is done to handle new records. The ID is not generated until the record is saved. So, once the insert has completed, the ID is retrieved and stored in the header. The txtCustomerID_LostFocus handler is also called to refresh the CustomerDisplay form.
Notice that there is no code to save the record. Remember, when you move the focus off of the updated record, the changes are automatically saved. The Finish button is on the parent form, so, when this is clicked, the focus moves off the CustomerDisplay form and the record is saved.
Testing the Profile Page
Save the code file and the CustomerAdmin form. Test it out by switching to the Form view. After selecting a customer and clicking the Modify button, the CustomerUpdate form appears, as shown in Figure 8-14.
Figure 8.14. The implemented Profile page
If you change anything, the arrow changes to a pencil. You can undo your changes by pressing the Esc key. To save your changes, click on the Finish button. The CustomerUpdate form will be hidden and the CustomerDisplay form will be refreshed with the updated data.
Now try creating a new customer. Clear the CustomerID field, and tab off of the field. Because there will be no matching customer, the form will be blank except for the New button. Click the New button and an empty CustomerUpdate will be displayed as shown in Figure 8-15.
Figure 8.15. Adding a new customer
Enter a name and contact info and click the Finish button. The form should be updated to show this information in the CustomerDisplay form as shown in Figure 8-16.
Figure 8.16. Updating the CustomerDisplay form
Building the Items on Loan Tab
The next page in the CustomerAdmin form will list all the items that are on loan for that customer. They can select one to see more details and have the option to renew the item. This is a perfect application for a Split Form, which you used in Chapter 6. The only problem is, you cannot use a Split Form as a subform. To work around this problem, you'll create two subforms on this page; a Datasheet View to list the items and a Form View to display the selected record. Then you'll need to keep the two in sync with VBA code.
Enhancing the LoanDetails Query
In the last chapter, you created a LoanDetails query to support the CheckOut form. You'll use the same query to retrieve the items that are currently on loan. For the purposes here, you'll need to also join the Category and Media tables and add a few more fields to the query results.
Open the LoanDetails query in the Design View. Click the Show Table button in the ribbon and add the following tables to the query:
Category
Media
The join relationships to these tables should be set up for you. The query design should look like Figure 8-17.
Figure 8.17. The modified LoanDetails query design
Double-click on the following fields to add them to the query results:
Loan.LoanID
Loan.CheckedIn
Loan.Renewals
Loan.OverdueFee
Item.ItemID
Item.Author
Item.Description
Category.CategoryDescription
Media.MediaDescription
Media.LoanPeriod
Media.RenewalsAllowed
You should also define the sort order for this query. In the CheckedOut field, select Descending for the Sort property. This will return the items in the order they were checked out with the most recent first. Save the query changes and close the LoanDetail query.
Designing a CustomerLoan Form
The next step is to design a form that will be used for the two subforms. You'll actually design a single form that will be used for both. One copy of the form will use the Form View and the other will use the Datasheet View. In Chapter 6 I told you that the layout of the Datasheet View was independent of the Form View. Now, you'll have a good opportunity to see that in action.
CREATING THE INITIAL CUSTOMERLOANS FORM
You'll start by creating a standard data-bound form using the Form button in the ribbon. This is the quickest way to generate a simple form.
1. Select the LoanDetail query in the Navigation pane and click the Form button in the Create tab of the ribbon. This will generate a form with a label and data-bound control for each field in the query.
2. Select the InProgress control and delete it, which should also remove its associated label. You will not need this field on either version of this form.
3. There are several fields that will not be shown on the Form View, but are needed for the subsequent form logic that you will write. Just like with the CustomerID field in the CustomerUpdate for that you implemented earlier, you will remove these from the layout and make them invisible.
4. First, shrink the two columns in the layout to make the size of the form a little easier to work with. Then switch to the Design View. Select the following controls and their associated labels:
CustomerID
InventoryItemID
LoanID
ItemID
5. In the Arrange tab of the ribbon, click the Remove layout button. Select the layout and drag it to the right to reveal the controls that were removed from the layout. Delete their associated labels.
6. The CustomerID and InventoryItemID controls were generated as ComboBox controls. You'll need to replace them with TextBox controls. Delete both ComboBox controls, and then add two TextBox controls. Delete the associated labels. Set the Name property of the new controls to CustomerID and InventoryItemID. In the Data tab of the Property Sheet, set the control source to the corresponding field in the query.
7. Select all four data-bound controls and set their Visible flag to No.
ARRANGING THE CONTROLS
Now let's work on arranging the remaining controls. This will be used as a continuous form so you should make efficient use of space to create a form that is as small as possible.
1. First, change some of the label captions as follows:
Renewals: Renewals
RenewalsAllowed: Allowed Renewals
2. Then delete the labels for the following controls (the text in these fields is fairly self-explanatory):
Title
Author
Description
CategoryDescription
MediaDescription
3. Switch to the Layout View. You will need a total of four columns in the layout. Select one of the data-bound controls and click the Insert Right button (in the Arrange tab of the ribbon) twice.
4. Drag the CheckedOut and DueDate controls and their labels to empty cells on the lower-right part of the layout.
5. Merge all the cells in the top row into one cell and drag the Title control to this cell.
6. Merge the first three cells in the second row and drag the Author field to this row.
7. Drag the CheckedOut control and its label to the second and first cells of the third row. Drag the DueDate control and its label just below this.
8. Drag the CategoryDescription control to the right of the CheckedOut control. Drag the MediaDescription control to the right of the DueDate control.
9. The CheckedIn and Renewals controls can stay where they are. Drag the OverdueFee and its label down one row. Drag the RenewalsAllowed control and its label just above the OverdueFee control.
10. Drag the LoanPeriod control to the right of the Renewals control, and drag its label just above it.
11. Merge the cell that has the Description control with all the other cells on that row.
12. Merge the empty cells in the rightmost column into a single cell.
MODIFYING THE FORM LAYOUT
Lastly, you'll add some fine tuning to the layout of the form and configure the filter and navigation properties.
1. Select the entire layout, click the Control Padding button in the ribbon, and click the None link. While everything is still selected, from the Format tab of the Property Sheet, set the Height property to .22.″
2. Select all the labels in the first column and set the Text Align property to Right.
3. Drag the entire layout to the top-left corner of the form.
4. Remove the controls in the Form Header, which should remove the Form Header as well.
5. Create a command button and place it in the cell just below the LoanPeriod control. Set the Name and Caption properties to Renew. This form is designed to display a single record and should not allow any changes. You won't need the record selector or the navigation controls.
6. Set the following form properties in the Property Sheet:
Allow Additions: No
Allow Deletions: No
Allow Edits: No
Allow Filters: No
Record Selectors: No
Navigation Buttons: No
7. Save the form and enter the name CustomerLoans when prompted.
Switch to the Form View. The form should look like Figure 8-18.
Figure 8.18. The CustomerLoans form layout
Configuring the Datasheet View
Now you'll configure the layout of the Datasheet View, which should not affect the Form View that you just designed.
1. Go to the Design View. In the Property Sheet select the Form object and the Format tab.
2. Set the Allow Datasheet View property to Yes and switch to the Datasheet View.
3. To change the order of the column, select a column and then drag it to the desired position. Using this method, put first nine fields in the following order. The rest of the fields should come after these nine.
Title
CheckedOut
DueDate
CheckedIn
Renewals
OverdueFee
Author
CategoryDescription
MediaDescription
4. For the remaining fields the order is not important. Shrink the column width down to as small as possible.
5. Select the CategoryDescription column. In the Other tab of the Property Sheet set the Datasheet Caption property to Category. In the same way, select the MediaDescription column and set the Datasheet Caption property to Media.
The Datasheet View should look like Figure 8-19.
Figure 8.19. The CustomerLoans form Datasheet View
Save the form changes and go back to the Form View to make sure that that looks as expected.
Designing the Items on Loan Page
Now you're ready to design the second page of the CustomerAdmin form. This will be a fairly simple matter of dropping two copies of the CustomerLoans form onto this page. Then you'll write a little VBA code to connect everything together.
1. Close the CustomerLoans form and open the CustomerAdmin form in the Layout View.
2. Click the second tab (mine is called Page12, yours may be a different name). In the Other tab of the Property Sheet, change the Name property to OnLoan. In the Format tab, set the Caption property to Items on Loan.
3. In the Design tab of the ribbon, click the Subform button and then click on the OnLoan page. This will add a Subform control and an associated label. Delete the label. Select the Subform control and change its Name property to CustomerLoans.
4. In the Data tab of the Property Sheet, select the CustomerLoans form for the Source Object property.
NOTE
These instructions assume you have the wizards turned off. If the wizards are on, just select the CustomerLoans as the source form and use the default name.
5. Enter txtCustomerID in the Link Master Fields property and CustomerID in the Link Child Fields property. The Property Sheet will look like Figure 8-20.
Figure 8.20. The Data tab of the Property Sheet
6. Add another Subform control to the OnLoan page. Follow the exact same steps, except set the Name property to CustomerLoansForm. The form should go below the first Subform control.
7. Now you'll arrange these to take up the full width and height of the page. The layout control should have two columns. The cells in the left column had the labels but are now empty. Merge the two cells on the top row into one cell. Drag the lower control to the left column.
WARNING
When dragging the Subform control, be careful not to click inside the control and select a control inside the subform. If you do, when you drag the selected object you will be re-arranging the controls on the subform.
8. The lower control will have the Form View version and so make sure you have it sized so the entire form will fit correctly. The upper control can use whatever space is left. The layout should look like Figure 8-21.
Figure 8.21. The layout of the OnLoan page
Connecting the Pieces
There are a couple of interesting challenges to deal with. First, you probably noticed that both copies of the CustomerLoans form use the Form View. That's because this is determined by the Default View property on the form. At design-time, this can only have one value. You'll add code to the Form_Load event to switch the view on one of the forms.
The other issue is not as obvious. With the other subforms you've used, you set the form's Filter property which was based on a control on the subform. You then linked that control with a control on the parent form. In this case, however, the filter for the two forms needs to be different. The filter on the Datasheet View will use the CustomerID because it will include all the Loan records for the customer. In the Form View, the filter will use the LoanID, because it will only display a single record. Again, you'll need to specify the filters at run-time.
Along the same lines, you'll need to configure the navigation controls also. These were disabled for the Form View but will be necessary for the Datasheet View.
To sort this out, switch to the Design View. In the Design tab of the ribbon, click the View Code button to display the VBA editor. This should open the code file for the CustomerAdmin form. Add the method shown in Listing 8-2.
Example 8.2. Implementation of the Form_Load Event Handler
Private Sub Form_Load()
' Set the form properties for the Datasheet View
CustomerLoans.Form.Filter = "[CustomerID] = CustomerID " & _
"And IsNull([CheckedIn]) = True"
CustomerLoans.Form.FilterOn = True
CustomerLoans.Form.NavigationButtons = True
CustomerLoans.Form.NavigationCaption = "Loan"
CustomerLoans.Form.AllowFilters = False
CustomerLoans.Form.AllowAdditions = False
' Switch to the Datasheet View
' The RunCommand function requires the form to be in focus
Me.CustomerLoans.SetFocus
DoCmd.RunCommand acCmdSubformDatasheetView
' Go back to the first page
Me.Profile.SetFocus
End Sub
The form properties at design-time were configured for the Form View. As I indicated, this code configures the properties for the copy that is used for the Datasheet View. It sets the filter and configures the navigation controls. The filter limits the view to loans made by the current customer and that have not been checked in.
TIP
Notice the code uses CustomerLoans.Form syntax. This is something that is often overlooked. The CustomerLoans object is a Subform object, not the actual form. You need to use its Form property to access properties of the form.
The second part of this code switches the form to the Datasheet View. It uses the RunCommand method, which executes a menu or ribbon command. It does the same thing as clicking the Datasheet View button on the ribbon. For this to work, the form has to have the focus, or, in other words, is the current form. The code first uses the SetFocus method to ensure that it is. After the view is changed, the focus is put back on the first page of the form.
Open the CustomerLoans form in the Design View. In the Design tab of the ribbon, click the View Code button. This should create a code file for this form. Enter the code shown in Listing 8-3.
Example 8.3. Implementation of the CustomerLoans Code File
Private Sub DisplayCurrentLoan()
' If this is the Datasheet View synchronize the Form View
If (CurrentView = acCurViewDatasheet And IsNull(CheckedIn)) Then
' Find the Form View
Dim loanForm As Form_CustomerLoans
' Ignore any error if the form cannot be found
On Error Resume Next
Set loanForm = Parent.CustomerLoansForm.Form
If (Not loanForm Is Nothing) Then
loanForm.Filter = "[LoanID] = " & LoanID
loanForm.FilterOn = True
End If
End If
End Sub
Private Sub Form_Click()
DisplayCurrentLoan
End Sub
Private Sub Form_Current()
DisplayCurrentLoan
' Enabled the Renew button
If (CurrentView = acCurViewFormBrowse) Then
If (Renewals < RenewalsAllowed And DueDate > Now() - 1) Then
Renew.Enabled = True
Else
Renew.Enabled = False
End If
End If
End Sub
Private Sub Renew_Click()
Dim sSQL As String
If (LoanID > 0) Then
' Renew the loan
sSQL = "UPDATE Loan SET Loan.DueDate = FormatDateTime(Now()+" & _
LoanPeriod & ",2) WHERE LoanID=" & LoanID & ";"
Application.CurrentDb.Execute sSQL, dbFailOnError
' Find the Datasheet View
Dim loanForm As Form_CustomerLoans
' Ignore any error if the form cannot be found
On Error Resume Next
Set loanForm = Parent.CustomerLoans.Form
If (Not loanForm Is Nothing) Then
loanForm.Requery
End If
End If
Renew.Enabled = False
Requery
End Sub
The DisplayCurrentLoan method synchronizes the Form View with the record selected in the Datasheet View. It only does that when the CurrentView property indicates this is the Datasheet View instance. Keep in mind that both copies of the form with be executing these event handlers. It uses the parent form's reference to the other Subform control and then set's the Filter property so the Form View instance displays the currently selected loan.
The On Error Resume Next tells the runtime to essentially ignore any errors. It is conceivable that this form could be reused somewhere else where there is no Form View version of it to synchronize. In this case, the error that will result will be ignored.
The DisplayCurrentLoan method is executed in both the OnCurrent and OnClick events. OnCurrent is raised when the form is first displayed. OnClick is raised when the user selects a different record in the view. In both cases, the Form View will be synchronized with the currently selected loan.
If this is the Form View, the OnCurrent event handler also checks to see if the Renew button should be enabled. It will be enabled if the item is not overdue and if the allowed number of renewals has not been exceeded.
The Renew_Click method is called when the user clicks the Renew button. It executes a SQL statement that updates the DueDate. The DueDate is determined by adding the appropriate LoanPeriod to the current date. It then finds the Datasheet Form and calls its Requery method, which refreshes the data. This code also calls the current form's Requery method. This ensures that both views are updated.
Testing the Page
Open the CustomerAdmin form in the Form View and select a customer; their information should be displayed. Now go to the Items On Loan tab and see the items they have checked out as demonstrated in Figure 8-22. (If there are no items currently on loan, you can use the CheckOut form that you implemented in Chapter 7 to create some loans for this customer.)
Figure 8.22. The OnLoan page
Try selecting different records and verify that the Form View is synchronized with the selected record. Try renewing an item to verify that the Renewals field is incremented and the DueDate is updated.
Building the Loan History Tab
The Loan History tab will show all loans made by the customer, including those that have been returned and those still outstanding. This is really easy to implement. You just need to drop the CustomerLoans form on the page. In the Form_Load event you'll configure this as a Datasheet View and set the appropriate filter.
1. Open the CustomerAdmin form in the Layout View. Right-click the Items On Loan tab and click the Insert Page link as shown in Figure 8-23.
Figure 8.23. Adding a new tab
2. In the Other tab of the Property Sheet set the Name property to History. In the Format tab set the Caption property to Loan History.
3. In the Design tab of the ribbon, click the Subform button and then click on the History page. Delete the label that is created.
4. Select the Subform control and set its Name property to CustomerLoansHistory.
5. In the Data tab, select CustomerLoans for the Source Object property. Enter txtCustomerID in the Link Master Fields property and CustomerID in the Link Child Fields property. Enlarge the layout to take up the entire area of the page.
6. Go to the VBA editor and modify the Form_Load event handler in the CustomerAdmin code file. The complete event handler is shown in Listing 8-4 and the new code is shown in bold.
Example 8.4. Modified version of the Form_Load Event Handler
Private Sub Form_Load()
' Set the form properties for the Datasheet View
CustomerLoans.Form.Filter = "[CustomerID] = CustomerID " & _
"And IsNull([CheckedIn]) = True"
CustomerLoans.Form.FilterOn = True
CustomerLoans.Form.NavigationButtons = True
CustomerLoans.Form.NavigationCaption = "Loan"
CustomerLoans.Form.AllowFilters = False
CustomerLoans.Form.AllowAdditions = False
' Set the form properties for the loan history tab
CustomerLoansHistory.Form.Filter = "[CustomerID] = CustomerID"
CustomerLoansHistory.Form.FilterOn = True
CustomerLoansHistory.Form.NavigationButtons = True
CustomerLoansHistory.Form.NavigationCaption = "Loan"
CustomerLoansHistory.Form.AllowFilters = False
CustomerLoansHistory.Form.AllowAdditions = False
CustomerLoansHistory.Form.OrderBy = "[CheckedOut] DESC"
' Switch to the Datasheet View
' The RunCommand function requires the form to be in focus
Me.CustomerLoans.SetFocus
DoCmd.RunCommand acCmdSubformDatasheetView
' Switch the history form to a Datasheet View
Me.CustomerLoansHistory.SetFocus
DoCmd.RunCommand acCmdSubformDatasheetView
' Go back to the first page
Me.Profile.SetFocus
End Sub
The new code that you've added sets the filter and navigation properties on the CustomerLoansHistory form just like you did earlier for the CustomerLoans form. It sets the OrderBy property to show the item most recently checked out first. It also uses the RunCommand method to change the form to use the Datasheet View.
Now try out this new feature. Open the CustomerAdmin form in the Form View. Select a customer and then go to the Loan History tab. The page should look similar to Figure 8-24.
Figure 8.24. The History page
TIP
Controls on a form must have unique names. Although pages of a TabControl are designed independently from the other pages, they are still part of the main form. This means that the control names for all of the pages must be unique. For instance, if you have a Modify button on several pages, they must have different names. They can have the same caption so they appear the same but must have unique names. If you have a lot of pages with similar controls, you might want to prefix each control name with the Page name.
Summary
In this chapter you built a CustomerAdmin form using a TabControl to allow multiple pages. Each page has one or more subforms on it. With this pattern you can easily add new pages for additional features. For example, you could add a page to manage requested items or a page to show fines that are due or have been paid.
The Form Header provides a feature for entering or searching for the appropriate customer. By linking this with the various subforms, they automatically retrieve the appropriate records for the selected customer. After looking up the customer, all of the pages will re-query their content.
Some of the specific topics that were covered include:
Dealing with floating controls (hidden controls not anchored with the layout control)
Understanding the form properties that control record navigation
Simulating a Split Form using two subforms
Configuring form properties at run-time
Supporting multiple instances of the same form
Invoking the RunCommand method
As we progress through these chapters, you're building a collection of reusable forms that can be easily dropped on to another form or page. A good example is the Loan History page. With just setting a few properties and writing a few lines of code, an existing form was re-purposed to solve a new requirement.
You are also building your repertoire of design and implementation techniques. In the next chapter, you'll add some more. In particular you'll add data-bound images to your database and embed a web browser that will take the application to another level.
In the previous chapter, you created several forms using the design tools provided by Access. This is what Access does really well, and this is why it is such a popular platform for creating database solutions. It is really easy to create forms for your existing tables. The primary constraint, however, is that the standard forms can only access a single table. There are some ways around this. For example, you can use a query to join related tables and then the form can access all the fields in the query. This is really helpful, especially with a normalized database. You can also use subforms when a parent/child relationship exists between the tables.
As most of us have seen from some painful experiences, there are times when you need a form that just doesn't behave that way. The CheckOut form that you'll create in this chapter is a perfect example. It needs to do the following:
Find and display Customer data
Look up and display Item and InventoryItem information
Insert records into the Loan table
In this chapter as well as the next, you'll build some complex solutions that will require Visual Basic for Applications (VBA) code to connect the pieces together. The forms themselves still rely on the foundational concepts presented in the previous chapter.
The process of building a checkout form will be described in the following steps:
1. Building a reusable CustomerSearch form.
2. Creating the CheckOut form, invoking the CustomerSearch form, and displaying the customer details.
3. Looking up and displaying an InventoryItem record.
4. Adding an item to the CheckOut form and finalizing the checkout process.
Implementing a Customer Search Feature
The first step during the checkout process is to identify the customer that is borrowing the items. In many cases, the customer will have a library card with them, and their CustomerID can be entered from that. We'll also need to allow a customer to be searched for based on name, address, phone, and so on. The search form will be implemented as a modal dialog so it can be reused in other places.
Populating the Customer Table
The initial database that you created in Chapter 2 only has a few records in the Customer table. You'll need more records than that to test the search capability. To easily populate additional records, I have downloaded contact information for each of the members of the United States House of Representatives.
If you're starting each chapter with the online version of the previous chapter's Library database, then you already have these loaded. If not, you can download the Library_06.accdb file from www.apress.com, and use that as the starting point for this chapter's projects. If you want to keep using your own database, you can also download the Customer.xlsx file. Then copy and paste all the records (except the header) into your database. The Customer table should look like Figure 7-1.
Figure 7.1. Customer table populated with additional records
Creating the CustomerSearch Dialog Box
From the Create tab of the ribbon, click the More Forms button, and then click the Modal Dialog link, as shown in Figure 7-2.
Figure 7.2. Creating a modal dialog form
This creates a blank form that has OK and Cancel buttons on it. You'll add un-bound controls to the Form Header. The user will enter their search criteria in these fields. The Detail section will use standard data-bound fields that will display the records that match the specified criteria.
To display the Form Header section, right click on the form and click the Form Header/Footer link, as shown in Figure 7-3.
Figure 7.3. Displaying the Form Header section
The OK and Cancel buttons should be in the Form Footer. You can't drag controls from one section to another. Instead, use the cut and paste operations to move them to the footer.
ADDING THE DETAIL FIELDS
You'll start by adding the data-bound controls in the Detail section. In the previous chapter, this step was done for you, but you'll find it's fairly easy to do yourself. Follow these steps:
1. In the Property Sheet, select the Form object and the Data tab. Notice that the Record Source property is blank. There is a dropdown button that allows you to select an existing table or query. There is also a button with ellipses. This will launch the Query Designer that you used in Chapter 4. You can use this to create an ad-hoc query as the record source for this form. In this case, just select the Customer table from the dropdown list.
2. While you're here, set the Allow Additions and Allow Deletions properties to No. This form is for viewing only and should not be used for modifying records.
NOTE
ou can't set Allow Edits to No, because the user will need to edit the unbound controls that you will add later. Instead, you'll set the Locked property on the controls in the Detail section to prevent the user from modifying them.
3. From the Create tab of the ribbon, click the Add Existing Fields button. This will display a list of the available fields in the selected record source (the Customer table). You'll double-click a field in this list to add it to the form. Add the following fields:
CustomerID
LastName
FirstName
Address
PhoneNumber
4. To quickly format the fields on the form, select all of the controls, including labels. In the Arrange tab of the ribbon, click the Tabular button. This will be a Continuous Form and you'll want the labels in the Form Header, which is what the Tabular button does. From the Property Sheet, select the Form object and the Format tab. Change the Default View property to Continuous Form.
5. Drag the labels to the bottom of the Form Header, and drag the data-bound controls to the top of the Detail section. Shrink the Detail section so there is just enough room for one row of controls. You'll also need to resize the data-bound controls, based on data that will be contained in each. You can switch to the Layout View to see how the actual data will appear.
6. Change the Caption property of the CustomerID label to ID. Save the form and enter the name CustomerSearch when prompted. The initial layout should be similar to Figure 7-4.
Figure 7.4. Initial layout of the CustomerSearch form
You'll need to make the following few adjustments to the Form's properties:
In the Property Sheet, select the Form object.
In the Format tab, change the Record Selectors property to Yes. Also set the Scroll Bars property to Vertical Only.
Select all of the data-bound controls and from the Property Sheet, set the Locked property (on the Data tab) to Yes. This will prevent the user from modifying the data.
7. Save the form changes. Try out the form by selecting the Form View. The dialog box should look like Figure 7-5.
Figure 7.5. The initial implementation of the CustomerSearch dialog
ADDING THE SEARCH FIELDS
At this point, the form returns all of the records in the Customer table. You'll now add unbound controls to the Form Header that the user will use to enter the search criteria. Follow these steps:
1. To add a TextBox control to a form, click the Text Box button in the Design tab of the ribbon.
2. Draw a rectangle on the form where control should go. This will create a TextBox control and an associated Label control. You'll also need to change the name of the control. Because there are controls in the Detail section with similar names, prefix these controls with "txt," for example, txtCustomerID. You'll then edit the Caption property of the Label, which you can do directly on the form, jut edit the text that is displayed.
3. Add the following TextBox controls to the Form Header. Don't worry about the size or alignment; you'll take care of that with a layout control. The Form Header will look similar to Figure 7-6.
CustomerID
Phone
LastName
FirstName
Postal
Figure 7.6. Initial Form Header layout with unbound controls
4. Now select all of these controls and the associated labels, being careful not to include the column heading labels for the Detail section.
5. In the Arrange tab of the ribbon, click the Stacked button. This will stack all of the controls vertically. Click the Insert Right button in the ribbon four times to create four more columns. Click the Select Layout button to select all of the controls and drag everything to the left.
Follow these steps to arrange the control in the Form Header layout control:
1. Drag the txtLastName control and its label just to the right of the txtCustomerID control and remove the empty row (where the txtLastName control used to be).
2. In the same way, drag the txtFirstName control and its label just to the right of the txtPhone control and remove the empty row.
3. Drag the txtPostal control and its label to the right of the txtEmail control, leaving an empty cell between them. Delete the empty row.
4. Select the txtEmail control and the empty cell next to it and click the Merge button.
5. Select all of the Label controls and in the Format tab of the Property Sheet, set the Text Align property to Right.
6. Select one of the column heading labels and click the Select Row button to select the rest of them. Position this row of labels so they are under the last unbound control.
7. Shrink the height of the Form Header to remove any unused space beneath the column headings.
8. Also shrink the width of the Form to remove any unused space.
Next, you'll need two command buttons. The first will execute the search and the second will clear the search criteria. Follow these steps:
1. In the Design tab of the ribbon, click the Button button and draw a rectangle in the Form Header. The Command Button Wizard will pop up, which allows you to select from some standard functions. Click the Cancel button, because you will be implementing this yourself.
2. Drag this control to the top-right corner cell of the layout grid. In the Format tab of the Property Sheet change the Caption property to Search and in the Other tab, change the Name property to Search.
3. In the same way, add a second button with the Caption and Name properties set to Clear. Drag this to the cell just below the Search button. The final layout should look like Figure 7-7.
Figure 7.7. The final layout of the CustomerSearch form
IMPLEMENTING THE SEARCH LOGIC
The search form is designed to display records from the Customer table. At this point, it is displaying all of the records. All you need to do now is apply a filter so the appropriate subset of records is returned. You'll do that using VBA code.
Select the Search button. In the Property Sheet, select the Event tab to list all of the events that you can write a handler for. For a command button, you normally add code to the OnClick event. In the dropdown for this event, select Event Procedure and then click the ellipses next to it. This will display the VBA editor. In the Search_Click method, enter the implementation shown in Listing 7-1.
Example 7.1. The OnClick Event
DoCmd.ApplyFilter "", _
"([CustomerID] = [Forms]![CustomerSearch]![txtCustomerID] " & _
"Or IsNull([Forms]![CustomerSearch]![txtCustomerID]))" & _
"And ([LastName] Like [Forms]![CustomerSearch]![txtLastName] " & _
"Or IsNull([Forms]![CustomerSearch]![txtLastName]))" & _
"And ([FirstName] Like [Forms]![CustomerSearch]![txtFirstName] " & _
"Or IsNull([Forms]![CustomerSearch]![txtFirstName]))" & _
"And ([PhoneNumber] Like [Forms]![CustomerSearch]![txtPhone] " & _
"Or IsNull([Forms]![CustomerSearch]![txtPhone]))" & _
"And ([Email] Like [Forms]![CustomerSearch]![txtEmail] " & _
"Or IsNull([Forms]![CustomerSearch]![txtEmail]))" & _
"And ([ZipPostal] Like [Forms]![CustomerSearch]![txtPostal] " & _
"Or IsNull([Forms]![CustomerSearch]![txtPostal]))", ""
This code calls the ApplyFilter method passing in the selection criteria. For each field that is in the Form Header, the logic checks to see if the database field matches what was specified or if a value was not specified. For the fields except CustomerID the comparison uses the Like operator. This allows for a partial value to be supplied, such as "Ad*".
Select the Clear button and for its OnClick event, select Event Procedure and click on the ellipses. In the VBA editor, enter the following code for the Clear_Click method. This code simply clears whatever values were supplied in the unbound controls.
txtCustomerID.Value = Null
txtLastName.Value = Null
txtFirstName.Value = Null
txtPhone.Value = Null
txtEmail.Value = Null
txtPostal.Value = Null
When the form is first loaded, it will display all the records in the Customer table, because the filter is not applied until the Search button is clicked. To keep it from doing that, you can set a default filter that will return no rows. In the Property Sheet, select the Form object and the Data tab. Enter [CustomerID] = CLng('0') for the Filter property. Also set the Filter On Load property to Yes. The CustomerID field is auto assigned and starts at 1, so it will never be 0. This is an effective and efficient way to keep any records from being loaded until the search criteria is entered.
Save the form and try it out by switching to the Form View. Try searching on various fields and using the wildcard character (*). You can also try entering a CustomerID value. The results should look like Figure 7-8.
Figure 7.8. The completed CustomerSearch form
There's one more thing that you'll need to change to make this work. The CustomerSearch form will be launched from another form (the CheckOut form, in this case) for the purpose of selecting a customer. Once the user has closed the dialog, the main form will need to know if a customer was selected and, if so, which one. The solution to this is that the Cancel button will simply close the form. The OK button, however, will not close the form but merely hide it. To the user this will appear the same but hiding the form will leave it available to the calling form so the selected record can be determined.
We'll start by looking at the default implementation of the OK and Cancel buttons. Follow these steps:
1. Open the CustomerSearch form in the Design View.
2. Right-click the Cancel button and click the Build Event link. This will display the same Macro Designer that you used in Chapter 3 to create your data macros. The available actions are different, because you're working with Windows controls instead of database objects.
TIP
You can use this technique to view and edit the VBA event handlers as well. Right-click the Search button and click the Build Event link. This will display the VBA editor if the event handler is implemented in VBA.
3. This macro simply calls the CloseWindow action, which will close the dialog. The Save parameter is set to Prompt, which will prompt the users and ask if the changes (if there have been any) should be saved or discarded. Change that now and choose the No option. The warning icon is letting you know that this is an unsafe action because changes could be lost. For this form, we don't want any changes made, so this is OK. The macro should look like Figure 7-9.
Figure 7.9. Updated Cancel macro
4. Click the Save button in the ribbon to save this change and then click the Close button to close the editor.
5. If you look at the event handler for the OK button, you'll see that it is exactly the same as for the Cancel button. Select the OK button. In the Event tab of the Property Sheet, change the On Click property from Embedded Macro to Event Procedure. Instead of executing a macro, the OnClick event handler will now invoke some VBA code. Click the ellipses to display the VBA editor.
6. In the Command1_Click method, enter the following code:
If (Me.CurrentRecord = 0) Then
MsgBox "Please select a customer", vbExclamation, "No Customer Selected"]
Else
Me.Visible = False
End If
This code checks to see if they selected a record. If not, an error message is displayed. Otherwise, the Visible property is set to False, which will hide the form.
WARNING
The calling form must be sure to close the CustomerSearch form after it has determined the selected customer.
Building a CustomerDisplay Form
The first thing that the user will do with the CheckOut form is to invoke the CustomerSearch dialog and select a customer. The selected customer should be displayed on the form. This is just for display purposes only; in fact, it would best to display this like a mailing label rather than a series of controls. In order to be able to reuse this on other forms, you'll build this as a stand-alone form now, which can be included on the CheckOut form as a subform. Follow these steps:
1. From the Create tab of the ribbon, click the Blank Form button.
2. Switch to the Design View. In the Property Sheet, select the Form object and the Data tab.
3. Select the Customer table for the Record Source property. Set the Allow Additions, Allow Deletions, and Allow Edits properties to No.
4. Click the Add Existing Fields button in the ribbon, and double-click each of the fields that are listed to add them to the form. These controls will be used for retrieving the data from the record and will not be visible. Delete each of the associated labels.
5. Select all of the data-bound controls. In the Format tab of the Property Sheet, set the Visible property to No. Resize the controls to be about 1 character wide and drag them to the top-left corner of the form. The form will look like Figure 7-10.
Figure 7.10. Initial CustomerDisplay form layout
6. Now you'll add the controls that will display the formatted customer information. Add five TextBox controls to the form with the following names:
lblName
lblAddress
lblRegion
lblEmail
lblPhone
7. Delete all of the associated labels. Now select the five unbound controls and from the Arrange tab in the ribbon, click the Stacked button. This will create a single column of controls.
8. Click the Insert Right button to add a second column. Drag the lblPhone control to the right of the lblEmail control, and delete the empty row. Select the lblName and the empty cell next to it, and click the Merge button. In the same way, merge the lblAddress and lblRegion control with the empty cell next to them.
9. Click the Select Layout button to select all of the controls. Drag the layout to the upper-left corner of the form. While the layout is still selected, click the Control Padding button in the ribbon and click the None link as shown in Figure 7-11.
Figure 7.11. Selecting no cell padding
10. While the layout is still selected, in the Format tab of the Property Sheet, clear the Border Color property (delete the text in the property value). When you tab off this property, the value will be changed to "#000000." Then set the Border Style to Transparent. When you click on the Border Color property, it should change to No Color. Also, set the Back Style property to Transparent. Finally, set the Height property to .2.″
TIP
You can make a TextBox control look like a Label by removing the border and making the background transparent, as you did here.
11. Resize the form to make it as small as possible, with no extra space below or to the right of the controls. Save the form and enter the name CustomerDisplay when prompted. The layout should look like Figure 7-12.
Figure 7.12. The CustomerDisplay form layout
12. Now you'll need to write some code to format the text in the unbound controls. In the Property Sheet, select the Form object and the Event tab. For the OnCurrent event, select Event Procedure, and then click the ellipses to display the VBA editor.
13. In the Form_Current method, enter the code shown in Listing 7-2.
Example 7.2. Implementation of the OnCurrent Event for the CustomerDisplay Form
If (Len(LastName) > 0) Then
lblName = LTrim(RTrim(FirstName) & " ") & RTrim(LastName) & " " & _
CStr(CustomerID)
lblAddress = Trim(Address)
lblRegion = RTrim(City) & ", " & LTrim(RTrim(StateProvince) & " ") & _
RTrim(ZIPPostal)
lblEmail = RTrim(Email)
lblPhone = RTrim(PhoneNumber)
Else
lblName = ""
lblAddress = ""
lblRegion = ""
lblEmail = ""
lblPhone = ""
End If
This code determines if a record was selected by checking the LastName field. If there is no record, the controls are all cleared. Otherwise, the appropriate fields are concatenated together to format a mailing label.
Save the code and save the form changes. Switch to the Form View. The format should look like Figure 7-13.
Figure 7.13. The final output of the CustomerDisplay form
Go back to the Design View. This form should display a single record so you'll need to remove all the navigation controls. In the Property Sheet, select the Form object and the Format tab, and then set the following properties:
Border Style: None
Record Selectors: No
Navigation Buttons: No
Scroll Bars: Neither
Control Box: No
Close Button: No
Min Max Buttons: None
In the Data tab, set the Filter property as [CustomerID] = CustomerID and set the Filter On Load property to Yes. When you drop this form onto another form, you'll set up the parent/child linkage so the correct record will be returned based on the parent form.
Creating the CheckOut Form
Now you are ready to create a CheckOut form that will use both the CustomerSearch and CustomerDisplay forms. The controls for the customer search feature will all be in the Form Header; you'll save the Detail section for the items being checked out.
ADDING THE CUSTOMER CONTROLS
Now you'll add controls to the Form Header that you'll use for searching for and displaying the selected customer.
1. From the Create tab of the ribbon, click the Blank Form button. Switch to the Design View and then right-click the form and click the Form Header/Footer link. Expand the Form Header so you'll have some room to work with.
2. Add a TextBox control to the Form Header and change the Name property to txtCustomerID. Set the Caption of its associated label to CustomerID:. Set the Text Align property for the label to Right.
3. Add a command button to the Form Header and cancel the Command Button Wizard. Change the Name property to Search and set the Caption property to Search...
4. Select all three controls. From the Arrange tab of the ribbon, click the Stacked button. This should create two columns.
5. Click the Insert Right button on the ribbon to create a third column.
6. Drag the command button to the last column of the first row.
7. Merge all three cells of the second row. Enlarge the second row to be about 1-inch high.
8. From the Design tab of the ribbon, click the Subform button and then click Form Header section. This will display the SubForm Wizard. Select the CustomerDisplay, as shown in Figure 7-14.
Figure 7.14. Selecting the CustomerDisplay form
TIP
If the SubForm Wizard does not appear, you probably have the control wizards turned off. To enable them, expand the list of controls in the Design tab of the ribbon so all controls are displayed. Then click the Use Control Wizards link.
9. In the next dialog box, use the default value, CustomerDisplay, for the name of the subform. Click the Finish button.
10. In addition to the subform, a Label control is created, delete this. Drag the subform to the second row of the layout control.
11. Switch to the Layout View and resize the cells as appropriate.
12. Save the form and enter the name CheckOut when prompted.
The final layout should look like Figure 7-15
Figure 7.15. The layout of the Form Header section
CONNECTING THE CONTROLS
Now you'll tie the pieces together so this will function correctly. The first step is to link the subform, CustomerDisplay, with its parent form. The parent form, CheckOut has a txtCustomerID control that will specify the selected customer. The child form has a CustomerID data-bound control that is also used as the filter for the form. You'll now link these control together.
1. Go back to the Design View. In the Property Sheet, select the CustomerDisplay object and the Data tab.
2. Enter txtCustomerID for the "Link Master Fields" property. Likewise, enter CustomerID for the "Link Child Fields" property. The Property Sheet will look like Figure 7-16.
Figure 7.16. The subform data properties
3. Go to the Format tab and set the Visible property to No. This will keep the subform hidden until there is data to display.
4. Now you'll add code to handle the scenario where the user enters a customer ID directly into the form. Select the txtCustomerID control. In the Event tab of the Property Sheet, select Event Procedure for the On Lost Focus property. Then click the ellipses to edit the associated VBA code. Enter the following code for the txtCustomerID_LostFocus method:
CustomerDisplay!lblName = ""
CustomerDisplay!lblAddress = ""
CustomerDisplay!lblRegion = ""
CustomerDisplay!lblEmail = ""
CustomerDisplay!lblPhone = ""
DoCmd.Requery "CustomerDisplay"
If (Len(CustomerDisplay!lblName) > 0) Then
CustomerDisplay.Visible = True
Else
CustomerDisplay.Visible = False
End If
This code first clears the labels and then forces a re-query against the database. It then checks to see if there is any data to display. If not, the subform is hidden. If an invalid customer ID is entered then no data will be displayed.
Save the code changes and the form. Try it out by switching to the Form View. Enter a number in the CustomerID field and then tab off the field. The form should look similar to Figure 7-17.
Figure 7.17. The Form Header displaying the customer information
Try entering an invalid ID and verify that the subform is hidden.
INVOKING THE SEARCH DIALOG
Now you'll add code to invoke the CustomerSearch dialog when the Search button is clicked. This code will also determine the customer that was selected.
1. Go back to the Design View. Right-click the Search button and click the Build Event link. Because there is no code currently associated with this event, the dialog box in Figure 7-18 is displayed for you to select the type of event handler.
Figure 7.18. Selecting the type of event handler
2. Choose the Code Builder option and click the OK button.
3. Enter the code shown in Listing 7-3 for the implementation of the Search_Click method.
Example 7.3. Implementation of the Search_Click Method
Dim sForm As String
sForm = "CustomerSearch"
' Open the search form
DoCmd.OpenForm sForm, acNormal, , , , acDialog
' If the form is not loaded, the user clicked the Cancel button
If (IsLoaded(sForm)) Then
txtCustomerID = Forms(sForm)!CustomerID
DoCmd.Close acForm, sForm
Else
txtCustomerID = ""
End If
' Force a refresh if the CustomerDisplay subform
txtCustomerID_LostFocus
This code opens the CustomerSearch form, which is implemented as a modal dialog. Because it is modal, the OpenForm method does not return until the form has been closed or hidden. The code then checks to see if the form is still loaded. If it is, the CustomerID is obtained and the form is closed. Finally, the LostFocus event handler is called, which you just implemented. This will cause the CustomerDisplay subform to be updated with the selected customer, or hidden if no customer was selected.
This code uses a function called IsLoaded. You will need to implement that as well. Since this will be needed from several forms, you should implement this in the Main module so it can be shared by all the forms. To add a module to the VBA code, right-click the Library project and click the Insert
This will open up a new file. Add the following code to this file:
Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function
Save the code and enter Main when prompted for the file name. The SysCmd method is a built-in function provided by Access, which can be used for a number of purposes depending on the action that is specified in the first parameter. This code uses it to check the status of the specified form; to see if it is loaded. If it is, the code then checks to see if it is opened in Design View.
NOTE
The IsLoaded function is provided by Microsoft. You can find more information about it at http://msdn.microsoft.com/en-us/library/aa141272(office.10).aspx. In my opinion, this should be provided as a built-in function. Because it is not, you'll need to make sure you add it to the Main module. If you forget, you'll get an error when your code executes.
Go to the Form View and click the Search button. Enter some search criteria and select one the records as shown in Figure 7-19. The record selector to the left of the row will indicate which record was selected.
Figure 7.19. Searching for a customer
Click the OK button. You should see the selected customer displayed in the Form Header, as shown in Figure 7-20.
Figure 7.20. The Form Header showing the selected customer
Try searching for a customer and clicking the Cancel button. In this case, no customer data should be shown.
Providing a CheckOut Feature
At this point the CheckOut form allows you to enter a customer ID or search for a customer and then display their information. Now that the preliminaries are out of the way, you can build a feature that will check out items to the selected customer.
The checkout process will start by entering an InventoryItemID. It is assumed that the item will have an inventory tag on it that can be scanned or entered so you won't need a search feature. When this ID has been entered, it's a good idea to look up the item and display details of the item, including its current condition and status. If the item is available, the form will allow the item to be checked out.
An item is checked out by creating a Loan record, which requires only the CustomerID and InventoryItemID fields. The data macros that you implemented in Chapter 3 will take care of any other updates that are needed, such as updating the InventoryItem record. The Loan record will be added to the Detail section of the CheckOut form. As additional items are checked out, they are added to the form so you can keep a running total of everything being checked out.
You will now implement this feature in the following steps:
1. Create a form for displaying details of an InventoryItem record.
2. Embed this as a subform in the CheckOut Form Header
3. Add logic to generate the Loan records when the item is checked out.
4. Implement some finalization code when the checkout process is complete.
Building an InventoryItemLookup Form
You'll now create a form that will display a single inventory item. You already have an InventoryItem form that is used as a subform on the Item form. This form will not work here, because it was designed to be used in the context of an Item record. You'll need a form that provides details from both the Item and InventoryItem tables for a single InventoryItem record.
You'll create a blank form and build an ad-hoc query as the record source. Then you'll add the fields to the form and use a layout control to arrange them. You'll set up a filter to display a single record and remove the selection and navigation control just like you did with the CustomerDisplay form.
From the Create tab of the ribbon, click the Blank Form button. Switch to the Design View.
CREATING AN AD-HOC QUERY
Now you'll design an ad-hoc query that will return the appropriate subset of records from the InventoryItem table.
1. In the Property Sheet, select the Form object and the Data tab. Click the ellipses next to the Record Source property. This will display a blank query in Design View.
2. Add the following tables to the query (the query should look like Figure 7-21):
InventoryItem
Item
Category
Media
Figure 7.21. The query design for the InventoryItemLookup form
3. Double-click the following fields to add them to the query:
InventoryItem.InventoryItemID
InventoryItem.Status
InventoryItem.Condition
InventoryItem.Comment
Item.Author
Item.Title
Item.Description
Category.CategoryDescription
Media.MediaDescription
Media.LoanPeriod
4. Click the Close button. You'll see the dialog box shown in Figure 7-22. Click the Yes button to update the Record Source property.
Figure 7.22. Dialog box confirming the property change
ADDING THE FIELDS TO THE FORM
The fields from this query will now be added to the form and the initial form layout will be designed.
1. Click the Add Existing Fields button in the ribbon, which will list all of the available fields. Double-click each one to add it to the form.
2. Delete the label for the InventoryItemID control. The InventoryItemID field will be hidden and only used for linking with the parent form. Select this control and in the Format tab of the Property Sheet set the Visible property to No. Drag this control to the top-left corner of the form.
3. Select all of the controls except InventoryItemID and their associated labels. From the Arrange tab of the ribbon, click the Stacked button to create a layout control. Click the Insert Right button twice to add two more columns. Click the Insert Above button twice to add two blank rows at the top of the form.
4. To save space on the form, delete the labels for all of the fields except Comment and LoanPeriod. The data for these fields should be fairly self-explanatory.
5. Drag the Title field to the top-left cell. Then select this cell and the three cells to the right and click the Merge button.
6. Drag the Author control to the first cell in the second row. Merge this cell with the cell next to it (the Author field will use the first two cells of this row).
7. Delete the Status control. This was generated as a ComboBox control but we don't want this field editable so you'll replace it with a TextBox control. From the Design tab of the ribbon, click the Text Box button and then draw a rectangle in the form to create the control. Delete the associated label control. Select the new TextBox control and change its Name property to Status. For the Control Source property, select the Status field. Drag this control just to the right of the Author control.
8. Drag the Condition control to the right of the Status control. You'll leave this as a ComboBox control, because the user will be able to modify the condition of the item.
9. Drag the Comment control to the second cell in the third row. Its label should move to the first cell automatically. Merge this control with the two empty cells beside it.
10. Drag the CategoryDescription control to the first cell in the fourth row. Drag the MediaDescription control to the second cell. Drag the LoanPeriod control to the fourth cell. Again, its label should move to the third cell.
11. Drag the Description control to the fifth row. Merge all the cells on this fifth row into a single cell.
MODIFYING THE FORM DESIGN
Next you'll perform some cleanup, removing unused cells and setting an appropriate cell height.
1. Remove all of the empty rows.
2. Select one of the controls in the layout and click the Select Layout button to select all of the controls. Click the Control Padding button, and then click the None link.
3. You may have noticed that the blank rows at the top of the layout are not as high as the other rows. To assure uniformity, select all the cells and in the Format tab of the Property Sheet, set the Height property to .22.″ The Description control will need to be larger, however. Select only the Description control and resize it to be about 3 lines high.
4. Select the entire layout and drag it to the top-left corner of the form.
5. Go to the Layout View and resize the controls as necessary so the data fits properly.
6. Go back to the Design View and shrink the form size to remove all unused space.
FINALIZING THE FORM DETAILS
In the final step, you'll lock down the fields that should not be editable and finalize the form implementation.
1. Select the following controls and in the Data tab of the Property Sheet, set the Locked property to Yes. This will prevent the user from modifying these fields because the only fields that will be editable are Condition and Comment.
Title
Author
Status
CategoryDescription
MediaDescription
LoanPeriod
Description
2. Save the form and enter the name InventoryItemLookup when prompted.
3. You will need an unbound control that you can use to indicate if a record was read successful. Add a TextBox control to the form and set its Name property to lblTitle. Also set its Visible property to No. Delete the associated label.
4. In the Property Sheet, select the Form object and the Event tab. For the On Current property select Event Procedure and click the ellipses. Enter the following code for the implementation of this event handler:
lblTitle = Title
This code simply updates the unbound control with whatever is in the Title field.
TIP
The code that checks to see if a record was read successfully first sets one of the fields to a blank string. It then re-queries the form and checks to see if the value is still blank. You can't do this with a bound control, because setting it to blank will update the record. Instead you'll use the lblTitle control, which is copied from the bound Title control when a record is read.
The final layout should look like Figure 7-23.
Figure 7.23. The layout of the InventoryItemLookup form
5. Just like the CustomerDisplay form that you designed earlier, this form should display a single record so you'll need to remove all the navigation controls. In the Property Sheet, select the Form object and the Format tab. Set the following property values:
Border Style: None
Record Selectors: No
Navigation Buttons: No
Scroll Bars: Neither
Control Box: No
Close Button: No
Min Max Buttons: None
6. Also, in the Data tab, set the Filter property as [InventoryItemID] = InventoryItemID and set the Filter On Load property to Yes. Test out the form change switching to the Design View. The form should look like Figure 7-24.
Figure 7.24. The completed InventoryItemLookup form
Linking the InventoryItemLookup Subform
Now you're ready to add the InventoryItemLookup form as a subform to the CheckOut form. This will be done just like you added the CustomerDisplay form. You'll add a TextBox control where the user can enter the ID of the inventory item. This will be linked to the child form so the details can be displayed.
1. Close the InventoryItemLookup form and then open the CheckOut form in the Design View.
2. You'll need to add some more cells to drop the new controls into. Go the Arrange tab of the ribbon. Select the existing layout control and click the Insert Right button five times and the Insert Below button once.
3. Add a TextBox control to the Form Header and set its Name property to txtInventoryItemID. Enter the Caption for the associated label as InventoryID: and set the Text Align property to Right. Drag the txtInventoryItemID control to the top row, leaving two blank cells to the right of the Search button. The label should move to the left of this control, leaving a single blank cell after the Search button.
4. Merge the last four cells of the last two rows into a single cell. This will leave a blank column between the existing customer controls and the new inventory item controls.
5. From the Design tab of the ribbon, click the Subform button and then click inside the Form Header. This will start the Subform Wizard. Select the InventoryItemLookup form, as shown in Figure 7-25.
Figure 7.25. Selecting the existing InventoryItemLookup form
6. In the next dialog box, accept the default name, which should be InventoryItemLookup.
7. Delete the associated label that was generated and drag the subform to the merged cell at the bottom right of the Form Header.
8. Switch to the Layout View and resize the cells so the subform fits properly. The layout should look like Figure 7-26.
TIP
To increase the vertical size of the merged cell without affecting the cells used by the CustomerDisplay form, select the blank cell underneath the CustomerDisplay form and change its height. Likewise, to increase the horizontal size, select the cell to the right of the txtInventoryItemID control and increase its width.
Figure 7.26. The layout of the CheckOut Form Header
9. Go back to the Design View and select the InventoryItemLookup subform. In the Data tab of the Property Sheet, enter txtInventoryItemID for the Link Master Fields property and InventoryItemID for the Link Child Fields property. This will establish the link between the parent and child forms. Also, in the Format tab, set the Visible property to No.
10. The last step is to implement the LostFocus event for the txtInventoryItemID control. Select this control and in the Event tab of the Property Sheet, select Event Procedure for the On Lost Focus property. Then click the ellipses to display the VBA code.
11. For the txtInventoryItemID_LostFocus method, enter the following implementation:
InventoryItemLookup!lblTitle = ""
DoCmd.Requery "InventoryItemLookup"
If (Len(InventoryItemLookup!lblTitle) > 0) Then
InventoryItemLookup.Visible = True
Else
InventoryItemLookup.Visible = False
End If
This code should be familiar to you since it's very similar to the LostFocus event that you implemented for the txtCustomerID control. It clears the unbound TextBox control (lblTitle), re-queries the form and then checks to see if the control is still blank.
Save the code and save the form. Test out the CheckOut form by switching to the Form View. Enter an InventoryItemID and verify that it displays the data correctly. Try entering a comment and changing the condition. Also try entering an invalid InventoryItemID and verify that no information is displayed.
Designing the CheckOut Details
When an item is checked out, a Loan record is created. The Detail section will contain these Loan records; as items are checked out, they will be added to the Detail section of the form. The record source for the form will be the Loan tabled, filtered to only include those records loaned out to the current customer.
However, the customer could have items that were previously checked out so the query needs to only retrieve the current items. To accomplish that, you'll need to modify the Loan table to add an InProgress field. You'll set the default value to be Yes so all new records will show as in progress. When the checkout has completed, you'll reset this flag.
ALTERING THE LOAN TABLE
Open the Loan table in the Design View. Add a new field named InProgress and select Yes/No for the Data Type. In the Field Properties window, set the Default Value property to 1, as shown in Figure 7-27.
Figure 7.27. Adding the InProgress field
CREATING A LOANDETAIL QUERY
The next step is to create a query that returns all in progress records for the current customer. From the Create tab of the ribbon, click the Query Design button. Add the following tables to the query:
Loan
InventoryItem
Item
There are two relationships between the Loan and InventoryItem tables; delete the one between Loan.LoanID and InventoryItem.CurrentLoanID. (This only affects the current query and not the underlying table relationships.) Add the following fields to the query:
Loan.CustomerID
Loan.InventoryItemID
Loan.CheckedOut
Loan.DueDate
Loan.InProgress
Item.Title
For the CustomerID field, enter [Forms]![CheckOut]![txtCustomerID] for the criteria. For the InProgress field, enter True for the Criteria. Save the query and enter the name as LoanDetail when prompted. The query design should look like Figure 7-28.
Figure 7.28. The query design for LoadDetail
DESIGNING THE DETAIL SECTION
Now you'll set up the new LoanDetail query as the record source for the CheckOut form and add data-bound controls to the Detail section. This will display the items that have been checked out so far.
1. Open the CheckOut form in the Design View. In the Property Sheet, select the Form object and the Data tab. For the Record Source property, select the LoanDetail query.
2. Click the Add Existing Fields button in the ribbon. Double-click each of the following fields to add it to the form:
InventoryItemID
Title
DueDate
CustomerID
3. Select all of these controls. From the Arrange tab of the ribbon, click the Tabular button. The labels will be placed in the Form Header.
4. Select all of the labels and drag them to the bottom of the Form Header. Select all of the data-bound controls and drag then to the top of the Detail section. Select both the controls and the labels and drag them to the left edge of the form.
5. Make the Title and DueDate controls wider.
6. The InventoryItemID and CustomerID controls were created as ComboBox controls. These will be read-only fields, so you'll need to replace them with TextBox controls. Delete both controls.
7. Add a TextBox control to the Detail section. Delete the associated label. Change the Name property to InventoryItemID, and select InventoryItemID as the Control Source.
8. In the same manner add another TextBox control, delete the associated label, set the Name property to CustomerID, and select CustomerID as the Control Source.
9. Drag these controls into the cells where the ComboBox controls were.
10. Delete the label for the CustomerID field. Select the CustomerID control and set the Visible property to No. The CustomerID control is used for inserting records and does not need to be displayed.
11. Select all the data-bound controls in the Detail section and set the Locked property to Yes.
12. In the Property Sheet, select the Form object and set the Default View property to be Continuous Form.
ADDING THE HEADER AND FOOTER CONTROLS
There are just a few more things to finish up the form design. You'll need a CheckOut button in the Form Header that will create the Loan record. You'll also need a Complete button in the Form Footer that will execute the final step of the process. You'll also add a control to the Form Footer to keep a running count of the number of items being checked out.
1. Add a command button to the Form Header. Cancel the Command Button Wizard and drag the control to the top-right cell. Set the Name property to CheckOut and the Caption property to CheckOut as well. Set the Visible property to No.
2. Add a command button to the Form Footer. Again, cancel the wizard and enter the Name and Caption properties as Complete.
3. Add a TextBox control to the Form Footer. Enter the Caption of the associated label as Items checked out: and set the Text Align property to Right. Change the control name to txtCount. Enter =Count(*) for the Control Source property.
4. Select all three fields in the Form Footer. In the Arrange tab of the ribbon, click the Stacked button. Click the Insert Right button twice to add two more columns.
5. Drag the Complete button the right-most cell in the first row and delete the empty row.
6. Drag the layout control to the top-left corner of the Form Footer. Resize the third cell to move the Complete button to the right.
The final layout should look like Figure 7-29.
Figure 7.29. The layout of the CheckOut form
Implementing the CheckOut Logic
Now you're ready to implement the checkout process. At this point, the user can select a customer and an inventory item. That's all you need to create a Loan record. The first thing you'll need to do is hide the CheckOut button until both a CustomerID and InventoryItemID have been entered. Then the CheckOut button needs to be implemented so it inserts a record into the Loan table. Finally, you'll implement the Complete button that will finalize the process.
ENABLING THE CHECKOUT BUTTON
Edit the VBA code for the txtInventoryItemID LostFocus event and add some code to hide or display the CheckOut button. Listing 7-4 shows the modified code and the new lines are shown in bold.
Example 7.4. The modified LostFocus Event
InventoryItemLookup!lblTitle = ""
DoCmd.Requery "InventoryItemLookup"
If (Len(InventoryItemLookup!lblTitle) > 0) Then
InventoryItemLookup.Visible = True
If (Len(CustomerDisplay!lblName) > 0) Then
CheckOut.Visible = True
If (InventoryItemLookup!Status = "Available") Then
CheckOut.Enabled = True
Else
CheckOut.Enabled = False
End If
Else
CheckOut.Visible = False
End If
Else
InventoryItemLookup.Visible = False
CheckOut.Visible = False
End If
The new code makes the button visible if there is an inventory item displayed in the InventoryItemLookup subform. The button is then enabled if the item is available. Otherwise, the button is disabled. If the user scans an item that is not available for some reason, they'll see a grayed CheckOut button that will indicate the item cannot be checked out. The details in the subform such as Status and Comment should explain why it is not available.
IMPLEMENTING THE CHECKOUT BUTTON
If the item is available to be loaned out, the user can click the CheckOut button. This will insert a record into the Loan table and perform some clean-up to prepare for additional records to be checked out.
Right-click the CheckOut button and click the Build Event link. At the prompt choose Code Editor. Enter the implementation of the CheckOut button using the code from Listing 7-5.
TIP
The OnClick event is the default event for a command button so you can use the shortcut approach to creating an event handler. For other events, you should select the event in the Property Sheet and click the ellipses like you have done before.
Example 7.5. The Implementation of the CheckOut Button
' Go to a new record
DoCmd.GoToRecord acDataForm, "CheckOut", acNewRec
CustomerID = txtCustomerID
InventoryItemID = txtInventoryItemID
' Cause the focus to move off the current record, which
' will cause it to be saved
DoCmd.GoToRecord acDataForm, "CheckOut", acNext
DoCmd.GoToRecord acDataForm, "CheckOut", acLast
' Reset the form controls
txtInventoryItemID = ""
DoCmd.Requery "InventoryItemLookup"
InventoryItemLookup.Visible = False
CheckOut.Visible = False
This code manipulates the form to insert a record rather than executing a SQL command directly. The GoToRecord method allows you to navigate through the records on the form just like the navigation controls at the bottom of the form. The last parameter of this function species the navigation option such as first, next, or last. In this case, you used the acNewRec constant, which goes to the record just after the last one. This is where you would add a new record to the form.
The form is now on a new, blank record. The code then sets the values of the two required fields, CustomerID and InventoryItemID. It uses the unbound controls in the Form Header, which contain the correct values. The GoToRecord method is called again to go to the next record. Just as with navigating a form manually, when you navigate off the current record, the updates are saved to the database. This causes the record to be inserted into the Loan table. GoToRecord is called one more time to make the row that was just added the current record.
The code then clears the txtInventoryItemId control. It also hides the InventoryItemLookup subform and the CheckOut button.
IMPLEMENTING THE COMPLETE BUTTON
After all the items have been entered, the user will use the Complete button to finalize the process. This clears the InProgress flag and then resets the form so it's ready for the next customer.
Right-click the Complete button and click the Build Event link. At the prompt, choose Code Editor. Enter the implementation of the Complete button using the code from Listing 7-6.
Example 7.6. The Implementation of the Complete Button
Dim sSQL As String
If (Len(txtCustomerID) > 0) Then
sSQL = "UPDATE Loan SET Loan.InProgress = False WHERE Loan.InProgress=True " & _
"AND Loan.CustomerID=" & txtCustomerID & ";"
Application.CurrentDb.Execute sSQL, dbFailOnError
End If
txtCustomerID = ""
txtInventoryItemID = ""
Me.Requery
CustomerLookup.Visible = False
InventoryItemLookup.Visible = False
CheckOut.Visible = False
txtCustomerID.SetFocus
As I mentioned earlier, the Loan records that are added during this "session" need to be displayed on the form without including items that were previously checked out. You did this by only including records where the InProgress flag was true. This is the default value of this field so all new records have the InProgress flag set. Now that the checkout process is complete, you'll need to clear these flags. This is done by executing a SQL command.
The remainder of the code clears the input fields and hides the sub forms. The Me.Requery statement causes the main form to refresh its data. Since the txtCustomerID field has been cleared, it will not find any matching records and the form will be empty.
Testing the Application
The CheckOut form is now complete, so let's test it.
1. Open the form in the Form View. Select a customer by entering the ID or using the Search feature.
2. Then enter an InventoryItemID. Try some that are available and some that are not to see if the CheckOut button is enabled correctly.
3. Then try clicking the CheckOut button and add a few items to the form. The form should look like Figure 7-30.
Figure 7.30. The final CheckOut form with items added
4. When you're done, click the Complete button and verify that the form is reset and ready for the next customer. You should also look at the Loan table and verify the records that were added by the form.
I have included the complete code file for the CheckOut form in Listing 7-7.
Example 7.7. The Complete Code Module for the CheckOut Form
Option Compare Database
Private Sub CheckOut_Click()
DoCmd.GoToRecord acDataForm, "CheckOut", acNewRec
CustomerID = txtCustomerID
InventoryItemID = txtInventoryItemID
' Cause the focus to move off the current record, which
' will cause it to be saved
DoCmd.GoToRecord acDataForm, "CheckOut", acNext
DoCmd.GoToRecord acDataForm, "CheckOut", acLast
' Reset the form controls
txtInventoryItemID = ""
DoCmd.Requery "InventoryItemLookup"
InventoryItemLookup.Visible = False
CheckOut.Visible = False
End Sub
Private Sub Complete_Click()
Dim sSQL As String
If (Len(txtCustomerID) > 0) Then
sSQL = "UPDATE Loan SET Loan.InProgress = False " & _
"WHERE Loan.InProgress=True " & _
"AND Loan.CustomerID=" & txtCustomerID & ";"
Application.CurrentDb.Execute sSQL, dbFailOnError
End If
txtCustomerID = ""
txtInventoryItemID = ""
Me.Requery
CustomerDisplay.Visible = False
InventoryItemLookup.Visible = False
CheckOut.Visible = False
txtCustomerID.SetFocus
End Sub
Private Sub Search_Click()
Dim sForm As String
sForm = "CustomerSearch"
' Open the search form
DoCmd.OpenForm sForm, acNormal, , , , acDialog
' If the form is not loaded, the user clicked the Cancel button
If (IsLoaded(sForm)) Then
txtCustomerID = Forms(sForm)!CustomerID
DoCmd.Close acForm, sForm
Else
txtCustomerID = ""
End If
' Force a refresh if the CustomerDisplay subform
txtCustomerID_LostFocus
End Sub
Private Sub txtCustomerID_LostFocus()
CustomerDisplay!lblName = ""
CustomerDisplay!lblAddress = ""
CustomerDisplay!lblRegion = ""
CustomerDisplay!lblEmail = ""
CustomerDisplay!lblPhone = ""
DoCmd.Requery "CustomerDisplay"
If (Len(CustomerDisplay!lblName) > 0) Then
CustomerDisplay.Visible = True
Else
CustomerDisplay.Visible = False
End If
End Sub
Private Sub txtInventoryItemID_LostFocus()
InventoryItemLookup!lblTitle = ""
DoCmd.Requery "InventoryItemLookup"
If (Len(InventoryItemLookup!lblTitle) > 0) Then
InventoryItemLookup.Visible = True
If (Len(CustomerDisplay!lblName) > 0) Then
CheckOut.Visible = True
If (InventoryItemLookup!Status = "Available") Then
CheckOut.Enabled = True
Else
CheckOut.Enabled = False
End If
Else
CheckOut.Visible = False
End If
Else
InventoryItemLookup.Visible = False
CheckOut.Visible = False
End If
End Sub
Summary
In this chapter you created a form that allows items to be loaned out to a customer. Along the way, you implemented a number of techniques that you will undoubtedly find an application for in many future projects. Some of these will also be used in subsequent chapters of this book.
The new design approaches that you implemented include:
Implementing a search form as a modal dialog
Creating a reusable data-bound subform
Manipulating the form records through VBA code
Executing a SQL command
You also used the layout control and the Layout View to format several forms.
Chapter 8. Creating a Customer Admin Form
In the previous chapter, you created a CheckOut form that demonstrated some advanced techniques with Access forms. Now, I'll continue along the same theme, digging a little bit deeper into how forms work and explaining ways to configure them. I'll also demonstrate the TabControl, which is useful for organizing a lot of information.
In this chapter, you'll build a customer administration form that can be used to create and update customer records, show a history of what they have previously checked out, and display the items that are currently due. This form will also allow you to renew items that are checked out. You will reuse some of what was developed in Chapter 7, including the CustomerSearch form. Hopefully, I will instill in you the benefits of designing reusable forms.
Building the Customer Profile Tab
The form that you'll build will use a TabControl and the first page will show the customer's name and contact information. It will also allow you to update this information and to set up a new customer. Once you have that working, I'll show you how to add other features in subsequent pages.
You'll start by creating a stand-alone form for adding and updating customer information. You will then drop this on the main form along with the existing CustomerDisplay form. You'll then need to write some code to tie these together.
Creating a CustomerUpdate Form
In the last chapter, you created a CustomerDisplay form that presented the customer's name, address, email, and phone in a compact form that looks much like a mailing label. This was handy to drop on a form without taking up much space. However, this was not designed to allow updates; you'll need a different form for that, which you'll design now.
You'll use the standard form template to create a new form based on the Customer table.
1. Select the Customer table in the Navigation pane then click the Form button in the Create tab of the ribbon. The initial form, which is displayed in the Layout View, should look like Figure 8-1.
Figure 8.1. Initial CustomerUpdate form
2. Select one of the data-bound controls and drag the right edge to the left to shrink the width to about 1 inch.
3. Finally, add two more columns using the Insert Right button on the Arrange tab of the ribbon.
The CustomerID field will not be displayed, as I discussed in Chapter 6. This surrogate key is not meaningful to the end user, so you won't clutter the form with this information. However, you'll need the CustomerID control on the form, because it will be used to link this form to a parent form. You will hide this control and delete the associated label. Even though the control is not visible, it still takes up a cell in the layout control. Access will not allow you to put two controls in the same cell. To resolve this, you'll need to remove this control from the layout control. You can then move it to wherever you want it, even on top of another control. It doesn't really matter, because it is not visible.
NOTE
You cannot remove a control from the layout while in the Layout View. You'll need to first switch to the Design View.
1. Switch to the Design View. Select the CustomerID control. From the Arrange tab in the ribbon, click the Remove Layout button, as shown in Figure 8-2.
Figure 8.2. Removing the CustomerID control from the layout
2. When you removed the control, its associated label is also removed. Because all the cells on that row are now empty, the row is deleted, moving all the controls up one row. This can be a little confusing, because the CustomerID control is still where it was but now the LastName control is on top of it. Drag the LastName control to one of the empty cells on the right as shown in Figure 8-3 and you'll be able to see the CustomerID control.
Figure 8.3. Temporarily moving the LastName control
TIP
At run time, the CustomerID control is not visible. However, when designing the form, you'll need to be able select it and modify its properties. What I suggest with these "floating" controls is that you resize them to make them small enough to completely fit inside of one of the layout cells with space around it. Then bring the control to the front so other controls are not hiding it. With this approach, you can see and manipulate the control without hiding any of the visible controls.
3. Select the layout control and drag it down about half an inch to expose the CustomerID control. Delete the associated label for the CustomerID control.
4. Shrink the CustomerID control to about half of its original height and width. In the Format tab of the Property Sheet, set its Visible property to No.
5. Right-click the CustomerID control and click the Position
Figure 8.4. Bringing the CustomerID control to the front
6. Drag the CustomerID control to the bottom of the form. You'll move it to a permanent location once the form layout is complete.
7. Switch back to the Layout View.
8. Change some of label captions (these can be edited directly on the form):
Country Region: Country
State Province: State
Zip Postal: Postal
PhoneNumber: Phone
9. Select all the cells in the layout and, in the Format tab of the Property Sheet, set the Height property to .22.′
10. Select all the label controls and set their Text Align property to Right.
11. Drag the entire layout control to the top-left corner of the form.
12. Arrange the controls and size the cells, as shown in Figure 8-5.
Figure 8.5. The modified CustomerUpdate form layout
13. When you try to drag the CustomerID control on top of one of the cells in the layout, Access will try to insert a row in the layout. You'll see an orange line appear in between two rows indicating where a row will be created to accept the control. This is not what you want. Instead, drag it near the layout, just to the right of the Address control. Click the left edge of the CustomerID control and drag it to the left until it is well inside the Address control. Then click the right edge and shrink the control back to its original size.
14. Finally, shrink the height and width of the form to remove any unused space. You may notice that it won't let you shrink the width. You can't make the form smaller than the collection of controls placed on it. The Form Header has a Label control that is as large as the original form; you'll need to first resize this to make it smaller than the layout control in the Detail section.
15. Save the form and enter the name CustomerUpdate when prompted.
Open the form in Form View, which should look like Figure 8-6.
Figure 8.6. Completed design of the CustomerUpdate form
Configuring the Form Controls
In Chapter 7, I gave some instructions for setting various form properties without explaining what they were for. I'll now explain the form controls that Access generates for you, how they work, and how to configure them using the form Property Sheet. Figure 8-7 shows the CustomerUpdate form, and I have circled and numbered the controls.
Figure 8.7. Identifying the form controls
1. Record Selector: The record selector serves two purposes. In the Datasheet View, the record selector indicates which row is currently selected. In Form View, this is not needed, because only a single record is displayed. However, the second purpose is to indicate if the data has changed. If any of the fields on the selected record have changed and the change has not been saved yet, the arrow will change to a pencil, indicating a change has been made. You can also click on the record selector to force the changes to be saved. The CustomerDisplay form you created in Chapter 7 was read-only so the record selector was unnecessary. To remove this, set the Record Selectors property to No.
2. Navigation Controls: The navigation controls are at the bottom of the form. The Navigation Buttons property determines if this row of controls is visible or not. The "1 of 443" text lets you know there are 443 records currently available and you're on the first one. There are buttons to go to the first, previous, next, and last records. I've circled the area where most of the controls are, but the entire row is used for navigation functions. If the Navigation Buttons property is set to No, the areas marked as 3, 4, and 5 are not displayed either.
3. Navigation Caption: The word "Record" is defined by the Navigation Caption property. If no value is specified, the default text, "Record," is used. If you wanted this to be "Customer," just enter that text in the Navigation Caption property.
4. New Record: The control just to the right of the Last Record button is used to create a new record. This is disabled if the Allow Additions property is set to No.
5. Filter: Access allows the end user to define ad-hoc filters, which is a really nice feature. The control here indicates if a user-defined filter has been defined. If not, the control will display "No Filter." If a filter has been applied, the text will be "Filtered." You can temporarily toggle a filter, which will display all the records. In this case, the text on this control will be "Unfiltered." If a user-defined filter has been defined, you can toggle between Filtered and Unfiltered by clicking on this control. You can turn off the filtering feature by setting the Allow Filters property to No.
NOTE
Filters defined at design time or programmatically are not affected by the Allow Filters property. You can turn this feature off to prevent the user from using filters and still define a default filter at design time and apply a filter using code.
6. Caption: You can set the Caption property to specify what text will appear in the form tab. If none is specified, the form will use the contents of the Name property. In the Design View, this is ignored and the Name property is used on the tab heading.
7. Close: The close button at the top-right corner of the form closes the form. You can disable this by setting the Close Button to No. This will prevent you from closing the form in any view except the Design View.
NOTE
When a form is used as a subform, neither the Caption nor the Close Button properties are used. These do not apply to child forms.
Now with this understanding, you'll configure the CustomerUpdate form that you just created. The record selector would be useful for indicating when there are unsaved changes. It also works as a Save button. Just like the CustomerDisplay form, this form will be configured to show the record associated with the parent form. So there's no need for the navigation controls.
Set the following properties; the remaining form properties can be left at their default values.
Record Selectors: Yes
Navigation Buttons: No
Filter: [CustomerID] = CustomerID
Filter On Load: Yes
Finally, you'll need to remove the Form Header that was generated by the template. It contains a logo image and a Label control. Delete both of these controls and the drag the Detail section up to shrink the Form Header so there is no space between them. Save the form and switch to the Form View. The form should look like Figure 8-8.
Figure 8.8. The final CustomerUpdate form
Creating the CustomerAdmin Form
Now you're ready to create the CustomerAdmin form and implement the first page. You'll provide a search facility in the Form Header just like the CheckOut form. When a customer is selected, the existing CustomerDisplay form will be included in the Profile page to show their contact information. This page will also have a Modify button. If the user needs to change this information, the Modify button will show the new CustomerUpdate form that they will use to update the data.
If no customer was selected, a New button will appear that will allow the user to create a new Customer record. It will use the same CustomerUpdate form to enter the details for the new customer. When the update is complete, the user will click the Finish button, which will hide the CustomerUpdate form and refresh the CustomerDisplay form.
DESIGNING THE FORM HEADER
In the Form Header you'll add a TextBox control and a Search button to enter or find the desired customer, just like you did with the CheckOut form in the last chapter.
1. From the Create tab of the ribbon, click the Blank Form button. Switch to the Design View and then right-click the form and click the Form Header/Footer link. Expand the Form Header to about 1-inch high.
2. Add a TextBox control to the Form Header and change the Name property to txtCustomerID. Set the Caption of its associated label to CustomerID:. Set the Text Align property for the label to Right.
3. Add a command button to the Form Header and cancel the Command Button Wizard. Change the Name property to Search and set the Caption property to Search...
TIP
You can disable the wizards, such as the Command Button Wizard, when adding controls to the form. Sometimes they're helpful, and other times they're not. It's pretty easy to just cancel them if you don't want a wizard for a particular control. But you can also turn these off. In the Design tab of the ribbon, click the dropdown icon in the Controls section, which will expand this area and show all the available controls. Just click the Use Control Wizards link as shown in Figure 8-9. To enable the wizards, repeat this step to toggle them back on.
Figure 8.9. Disabling the control wizards
4. Select all three controls in the Form Header. From the Arrange tab of the ribbon, click the Stacked button. This should create two columns.
5. Click the Insert Right button on the ribbon to create a third column.
6. Drag the command button to the last column of the first row. Delete the empty second row.
7. Select the entire layout and drag it to the top-left corner of the Form Header. Shrink the Form Header so there is no empty space beneath these controls.
The Form Header layout should look like Figure 8-10.
Figure 8.10. The layout of the Form Header
USING A TAB CONTROL
A tab control in Access works basically the same as in any .NET application. Each page is like its own mini-form. You design the controls on each page independently from the other pages.
NOTE
In a tab control, you will have multiple pages of controls. The tab is the small portion at the top that contains the label describing the data for that page. The object below the tab is called the page. Think of a file folder, where the tab is the part that sticks out where you add a label to identify it. Often the term tab is used to refer to the page as well. We can generally infer based on the context what is being referred to. I will call them pages; if you're used to them being called tabs, I hope this will not be confusing.
One thing to keep in mind when designing a tab control is that the pages all need to be the same size. More specifically, the size of all of the pages will be defined by the size of the largest one. As you plan what should be on each page try to keep this size rule in the back of your mind. This is a relatively minor point; it is far more important to keep the pages organized logically. Don't put a control on a page just because there is more room on that page. Let's get started.
1. From the Design tab of the ribbon, click the Tab Control button. Then draw a rectangle in the Detail section that is about the size of the entire section. This will create a TabControl with two tabs (pages). The label on the tab will be, initially, the name of the Page control associated with the tab. In my case, the first one is Page11. (I must have created 10 pages before I added this one.)
2. Click just below the tab to highlight a rectangle around the page. The Property Sheet should indicate that the Page11 object is selected (or whatever your page was named).
3. In the Format tab, change the Caption property to Profile. In the Other tab, change the Name property to Profile.
WARNING
When adding a control to a page, make sure that the page is selected first. An orange rectangle that is just below that tabs is used to show the page is selected. If there are already controls on the page, you can just select one of these controls. That will also put the page in focus. If the orange rectangle goes around the tabs then you have selected the TabControl not a particular page on that control. If the control is not included on a specific page, it won't be really obvious...until you go to another tab and you notice that the control is still visible. Let's just say I've learned from experience.
DESIGNING THE PROFILE PAGE
The Profile page will contain the CustomerDisplay form that you implemented in Chapter 7 and the CustomerUpdate form that you just created. You will also need a few command buttons to tie everything together.
1. Make sure the control wizards are enabled and the Profile page is selected. Click the Subform button in the ribbon, and then click inside the Profile page. In the Subform Wizard, select the CustomerDisplay form as shown in Figure 8-11 and click the Next button.
Figure 8.11. Selecting the CustomerDisplay form
2. In the next dialog box, use the default name, CustomerDisplay and click the Finish button. The wizard also generated an associated label; delete this.
3. Select the Subform control and, in the Arrange tab of the ribbon, click the Stacked button. Because there is only one control, the layout will have a single row and column. Click the Insert Below button twice to add two rows. Click the Insert Right button once to add another column.
4. Merge both cells in the last row into a single cell.
5. From the Design tab of the ribbon, click the Subform button again and then click on the Profile page. In the Subform Wizard, select the CustomerUpdate form as shown in Figure 8-12 and then click the Next button.
Figure 8.12. Selecting the CustomerUpdate form
6. In the next dialog box, use the default name, CustomerUpdate and click the Finish button. Again, delete the associated label.
7. Drag this Subform to the bottom-left cell of the layout control.
8. Switch to the Layout View and size the cells to fit the subforms correctly.
TIP
The cell for the CustomerDisplay subform should already be sized correctly. To adjust for the CustomerUpdate subform without affecting the CustomerDisplay subform, increase the vertical size of the last row and the horizontal size of the top-right cell.
9. Drag the entire layout to the top-left corner of the Profile page.
10. You will add three command buttons to the row of cells between the two subforms. Select the left cell of this row and click the Split Horizontally button. This will split the cell into two cells. Select one of these and click the Split Horizontally button again. Now you'll have three cells where there used to be one.
11. Change the width of these three cells so they are about the same size. This will adjust the size of the subforms so you'll need to keep an eye of that as well.
12. Create three command buttons, one in each cell. Enter the following Name and Caption properties:
ModifyCustomer: Modify...
NewCustomer: New...
Finish: Finish
13. You may need to adjust the height of the row containing the command buttons.
14. Select all the controls (the two subforms and the three buttons) and in the Format tab of the Property Sheet, set the Visible property to No. You will display these controls with VBA code as appropriate.
15. Save the form and enter the name CustomerAdmin when prompted.
The form layout should look like Figure 8-13.
Figure 8.13. The layout of the Profile page
IMPLEMENTING THE CONNECTING CODE
The last step of making this all work is implementing the code that connects the forms. First you'll set up the links between the parent page and the sub forms. Then you'll implement several event handlers.
1. Select the CustomerDisplay subform. In the Data tab of the Property Sheet, enter txtCustomerID for the Link Master Fields property and CustomerID for the Link Child Fields property.
2. Select the CustomerUpdate subform and enter the exact same values for these two properties. You probably noticed that, as soon as you did this, the data disappeared from the subforms. That's because there is no value defined for the txtCustomerID control in the Form Header. Now you just need to implement the VBA code. Most of this should be familiar, because it is similar to what you implemented in Chapter 7.
TIP
So far, to implement an event handler you selected the Event Procedure option on the Property Sheet for the appropriate event and then clicked the ellipses, which generated the method and displayed the VBA editor. As an alternative, you can right-click the command button and choose the Build Event link, which accomplished the same thing. If you know the name of the events for which you want to generate handlers, you can go directly to the VBA code. From the Design tab of the ribbon, click the View Code button. This will display the VBA editor and generate a code file for the form, if necessary. Then you can create the event handlers in VBA without having to enter anything in the Property Sheet.
3. From the Design tab of the ribbon, click the View Code button. This will launch the VBA editor and display an empty file for this form. Enter the methods shown in Listing 8-1.
Example 8.1. Event Handlers for the Profile Page
Private Sub txtCustomerID_LostFocus()
CustomerDisplay!lblName = ""
DoCmd.Requery "CustomerDisplay"
If (Len(CustomerDisplay!lblName) > 0) Then
CustomerDisplay.Visible = True
ModifyCustomer.Visible = True
NewCustomer.Visible = False
Else
CustomerDisplay.Visible = False
NewCustomer.Visible = True
NewCustomer.SetFocus
ModifyCustomer.Visible = False
End If
End Sub
Private Sub Search_Click()
Dim sForm As String
sForm = "CustomerSearch"
' Open the search form
DoCmd.OpenForm sForm, acNormal, , , , acDialog
' If the form is not loaded, the user clicked the Cancel button
If (IsLoaded(sForm)) Then
txtCustomerID = Forms(sForm)!CustomerID
DoCmd.Close acForm, sForm
Else
txtCustomerID = ""
End If
' Force a refresh if the CustomerDisplay subform
txtCustomerID_LostFocus
End Sub
Private Sub ModifyCustomer_Click()
CustomerUpdate.Visible = True
Finish.Visible = True
Finish.SetFocus
ModifyCustomer.Visible = False
End Sub
Private Sub NewCustomer_Click()
CustomerUpdate.Visible = True
Finish.Visible = True
Finish.SetFocus
NewCustomer.Visible = False
End Sub
Private Sub Finish_Click()
ModifyCustomer.Visible = True
CustomerUpdate.Visible = False
ModifyCustomer.SetFocus
Finish.Visible = False
' Force a refresh
txtCustomerID = CustomerUpdate!CustomerID
txtCustomerID_LostFocus
End Sub
The implementation of txtCustomerID_LostFocus is almost identical to the version from Chapter 7 except that it has to take care of showing or hiding the Modify and New buttons. If a customer was found, it shows the Modify button and if not, the New button is shown. In either case, only one is visible; whichever one is shown, the other is hidden.
The implementation of Search_Click is identical to what you implemented in Chapter 7. The last three methods handle the OnClick event of the three buttons. The first two, ModifyCustomer_Click and NewCustomer_Click are basically the same. They show the CustomerUpdate form and the Finish button and hide themselves.
The implementation of Finish_Click is a little more interesting. It hides the CustomerUpdate form and itself and shows the Modify button. That puts everything back like it was before the first Modify button was clicked. The last part of this method gets the CustomerID from the CustomerUpdate form and updates the TextBox control in the Form Header. This is done to handle new records. The ID is not generated until the record is saved. So, once the insert has completed, the ID is retrieved and stored in the header. The txtCustomerID_LostFocus handler is also called to refresh the CustomerDisplay form.
Notice that there is no code to save the record. Remember, when you move the focus off of the updated record, the changes are automatically saved. The Finish button is on the parent form, so, when this is clicked, the focus moves off the CustomerDisplay form and the record is saved.
Testing the Profile Page
Save the code file and the CustomerAdmin form. Test it out by switching to the Form view. After selecting a customer and clicking the Modify button, the CustomerUpdate form appears, as shown in Figure 8-14.
Figure 8.14. The implemented Profile page
If you change anything, the arrow changes to a pencil. You can undo your changes by pressing the Esc key. To save your changes, click on the Finish button. The CustomerUpdate form will be hidden and the CustomerDisplay form will be refreshed with the updated data.
Now try creating a new customer. Clear the CustomerID field, and tab off of the field. Because there will be no matching customer, the form will be blank except for the New button. Click the New button and an empty CustomerUpdate will be displayed as shown in Figure 8-15.
Figure 8.15. Adding a new customer
Enter a name and contact info and click the Finish button. The form should be updated to show this information in the CustomerDisplay form as shown in Figure 8-16.
Figure 8.16. Updating the CustomerDisplay form
Building the Items on Loan Tab
The next page in the CustomerAdmin form will list all the items that are on loan for that customer. They can select one to see more details and have the option to renew the item. This is a perfect application for a Split Form, which you used in Chapter 6. The only problem is, you cannot use a Split Form as a subform. To work around this problem, you'll create two subforms on this page; a Datasheet View to list the items and a Form View to display the selected record. Then you'll need to keep the two in sync with VBA code.
Enhancing the LoanDetails Query
In the last chapter, you created a LoanDetails query to support the CheckOut form. You'll use the same query to retrieve the items that are currently on loan. For the purposes here, you'll need to also join the Category and Media tables and add a few more fields to the query results.
Open the LoanDetails query in the Design View. Click the Show Table button in the ribbon and add the following tables to the query:
Category
Media
The join relationships to these tables should be set up for you. The query design should look like Figure 8-17.
Figure 8.17. The modified LoanDetails query design
Double-click on the following fields to add them to the query results:
Loan.LoanID
Loan.CheckedIn
Loan.Renewals
Loan.OverdueFee
Item.ItemID
Item.Author
Item.Description
Category.CategoryDescription
Media.MediaDescription
Media.LoanPeriod
Media.RenewalsAllowed
You should also define the sort order for this query. In the CheckedOut field, select Descending for the Sort property. This will return the items in the order they were checked out with the most recent first. Save the query changes and close the LoanDetail query.
Designing a CustomerLoan Form
The next step is to design a form that will be used for the two subforms. You'll actually design a single form that will be used for both. One copy of the form will use the Form View and the other will use the Datasheet View. In Chapter 6 I told you that the layout of the Datasheet View was independent of the Form View. Now, you'll have a good opportunity to see that in action.
CREATING THE INITIAL CUSTOMERLOANS FORM
You'll start by creating a standard data-bound form using the Form button in the ribbon. This is the quickest way to generate a simple form.
1. Select the LoanDetail query in the Navigation pane and click the Form button in the Create tab of the ribbon. This will generate a form with a label and data-bound control for each field in the query.
2. Select the InProgress control and delete it, which should also remove its associated label. You will not need this field on either version of this form.
3. There are several fields that will not be shown on the Form View, but are needed for the subsequent form logic that you will write. Just like with the CustomerID field in the CustomerUpdate for that you implemented earlier, you will remove these from the layout and make them invisible.
4. First, shrink the two columns in the layout to make the size of the form a little easier to work with. Then switch to the Design View. Select the following controls and their associated labels:
CustomerID
InventoryItemID
LoanID
ItemID
5. In the Arrange tab of the ribbon, click the Remove layout button. Select the layout and drag it to the right to reveal the controls that were removed from the layout. Delete their associated labels.
6. The CustomerID and InventoryItemID controls were generated as ComboBox controls. You'll need to replace them with TextBox controls. Delete both ComboBox controls, and then add two TextBox controls. Delete the associated labels. Set the Name property of the new controls to CustomerID and InventoryItemID. In the Data tab of the Property Sheet, set the control source to the corresponding field in the query.
7. Select all four data-bound controls and set their Visible flag to No.
ARRANGING THE CONTROLS
Now let's work on arranging the remaining controls. This will be used as a continuous form so you should make efficient use of space to create a form that is as small as possible.
1. First, change some of the label captions as follows:
Renewals: Renewals
RenewalsAllowed: Allowed Renewals
2. Then delete the labels for the following controls (the text in these fields is fairly self-explanatory):
Title
Author
Description
CategoryDescription
MediaDescription
3. Switch to the Layout View. You will need a total of four columns in the layout. Select one of the data-bound controls and click the Insert Right button (in the Arrange tab of the ribbon) twice.
4. Drag the CheckedOut and DueDate controls and their labels to empty cells on the lower-right part of the layout.
5. Merge all the cells in the top row into one cell and drag the Title control to this cell.
6. Merge the first three cells in the second row and drag the Author field to this row.
7. Drag the CheckedOut control and its label to the second and first cells of the third row. Drag the DueDate control and its label just below this.
8. Drag the CategoryDescription control to the right of the CheckedOut control. Drag the MediaDescription control to the right of the DueDate control.
9. The CheckedIn and Renewals controls can stay where they are. Drag the OverdueFee and its label down one row. Drag the RenewalsAllowed control and its label just above the OverdueFee control.
10. Drag the LoanPeriod control to the right of the Renewals control, and drag its label just above it.
11. Merge the cell that has the Description control with all the other cells on that row.
12. Merge the empty cells in the rightmost column into a single cell.
MODIFYING THE FORM LAYOUT
Lastly, you'll add some fine tuning to the layout of the form and configure the filter and navigation properties.
1. Select the entire layout, click the Control Padding button in the ribbon, and click the None link. While everything is still selected, from the Format tab of the Property Sheet, set the Height property to .22.″
2. Select all the labels in the first column and set the Text Align property to Right.
3. Drag the entire layout to the top-left corner of the form.
4. Remove the controls in the Form Header, which should remove the Form Header as well.
5. Create a command button and place it in the cell just below the LoanPeriod control. Set the Name and Caption properties to Renew. This form is designed to display a single record and should not allow any changes. You won't need the record selector or the navigation controls.
6. Set the following form properties in the Property Sheet:
Allow Additions: No
Allow Deletions: No
Allow Edits: No
Allow Filters: No
Record Selectors: No
Navigation Buttons: No
7. Save the form and enter the name CustomerLoans when prompted.
Switch to the Form View. The form should look like Figure 8-18.
Figure 8.18. The CustomerLoans form layout
Configuring the Datasheet View
Now you'll configure the layout of the Datasheet View, which should not affect the Form View that you just designed.
1. Go to the Design View. In the Property Sheet select the Form object and the Format tab.
2. Set the Allow Datasheet View property to Yes and switch to the Datasheet View.
3. To change the order of the column, select a column and then drag it to the desired position. Using this method, put first nine fields in the following order. The rest of the fields should come after these nine.
Title
CheckedOut
DueDate
CheckedIn
Renewals
OverdueFee
Author
CategoryDescription
MediaDescription
4. For the remaining fields the order is not important. Shrink the column width down to as small as possible.
5. Select the CategoryDescription column. In the Other tab of the Property Sheet set the Datasheet Caption property to Category. In the same way, select the MediaDescription column and set the Datasheet Caption property to Media.
The Datasheet View should look like Figure 8-19.
Figure 8.19. The CustomerLoans form Datasheet View
Save the form changes and go back to the Form View to make sure that that looks as expected.
Designing the Items on Loan Page
Now you're ready to design the second page of the CustomerAdmin form. This will be a fairly simple matter of dropping two copies of the CustomerLoans form onto this page. Then you'll write a little VBA code to connect everything together.
1. Close the CustomerLoans form and open the CustomerAdmin form in the Layout View.
2. Click the second tab (mine is called Page12, yours may be a different name). In the Other tab of the Property Sheet, change the Name property to OnLoan. In the Format tab, set the Caption property to Items on Loan.
3. In the Design tab of the ribbon, click the Subform button and then click on the OnLoan page. This will add a Subform control and an associated label. Delete the label. Select the Subform control and change its Name property to CustomerLoans.
4. In the Data tab of the Property Sheet, select the CustomerLoans form for the Source Object property.
NOTE
These instructions assume you have the wizards turned off. If the wizards are on, just select the CustomerLoans as the source form and use the default name.
5. Enter txtCustomerID in the Link Master Fields property and CustomerID in the Link Child Fields property. The Property Sheet will look like Figure 8-20.
Figure 8.20. The Data tab of the Property Sheet
6. Add another Subform control to the OnLoan page. Follow the exact same steps, except set the Name property to CustomerLoansForm. The form should go below the first Subform control.
7. Now you'll arrange these to take up the full width and height of the page. The layout control should have two columns. The cells in the left column had the labels but are now empty. Merge the two cells on the top row into one cell. Drag the lower control to the left column.
WARNING
When dragging the Subform control, be careful not to click inside the control and select a control inside the subform. If you do, when you drag the selected object you will be re-arranging the controls on the subform.
8. The lower control will have the Form View version and so make sure you have it sized so the entire form will fit correctly. The upper control can use whatever space is left. The layout should look like Figure 8-21.
Figure 8.21. The layout of the OnLoan page
Connecting the Pieces
There are a couple of interesting challenges to deal with. First, you probably noticed that both copies of the CustomerLoans form use the Form View. That's because this is determined by the Default View property on the form. At design-time, this can only have one value. You'll add code to the Form_Load event to switch the view on one of the forms.
The other issue is not as obvious. With the other subforms you've used, you set the form's Filter property which was based on a control on the subform. You then linked that control with a control on the parent form. In this case, however, the filter for the two forms needs to be different. The filter on the Datasheet View will use the CustomerID because it will include all the Loan records for the customer. In the Form View, the filter will use the LoanID, because it will only display a single record. Again, you'll need to specify the filters at run-time.
Along the same lines, you'll need to configure the navigation controls also. These were disabled for the Form View but will be necessary for the Datasheet View.
To sort this out, switch to the Design View. In the Design tab of the ribbon, click the View Code button to display the VBA editor. This should open the code file for the CustomerAdmin form. Add the method shown in Listing 8-2.
Example 8.2. Implementation of the Form_Load Event Handler
Private Sub Form_Load()
' Set the form properties for the Datasheet View
CustomerLoans.Form.Filter = "[CustomerID] = CustomerID " & _
"And IsNull([CheckedIn]) = True"
CustomerLoans.Form.FilterOn = True
CustomerLoans.Form.NavigationButtons = True
CustomerLoans.Form.NavigationCaption = "Loan"
CustomerLoans.Form.AllowFilters = False
CustomerLoans.Form.AllowAdditions = False
' Switch to the Datasheet View
' The RunCommand function requires the form to be in focus
Me.CustomerLoans.SetFocus
DoCmd.RunCommand acCmdSubformDatasheetView
' Go back to the first page
Me.Profile.SetFocus
End Sub
The form properties at design-time were configured for the Form View. As I indicated, this code configures the properties for the copy that is used for the Datasheet View. It sets the filter and configures the navigation controls. The filter limits the view to loans made by the current customer and that have not been checked in.
TIP
Notice the code uses CustomerLoans.Form syntax. This is something that is often overlooked. The CustomerLoans object is a Subform object, not the actual form. You need to use its Form property to access properties of the form.
The second part of this code switches the form to the Datasheet View. It uses the RunCommand method, which executes a menu or ribbon command. It does the same thing as clicking the Datasheet View button on the ribbon. For this to work, the form has to have the focus, or, in other words, is the current form. The code first uses the SetFocus method to ensure that it is. After the view is changed, the focus is put back on the first page of the form.
Open the CustomerLoans form in the Design View. In the Design tab of the ribbon, click the View Code button. This should create a code file for this form. Enter the code shown in Listing 8-3.
Example 8.3. Implementation of the CustomerLoans Code File
Private Sub DisplayCurrentLoan()
' If this is the Datasheet View synchronize the Form View
If (CurrentView = acCurViewDatasheet And IsNull(CheckedIn)) Then
' Find the Form View
Dim loanForm As Form_CustomerLoans
' Ignore any error if the form cannot be found
On Error Resume Next
Set loanForm = Parent.CustomerLoansForm.Form
If (Not loanForm Is Nothing) Then
loanForm.Filter = "[LoanID] = " & LoanID
loanForm.FilterOn = True
End If
End If
End Sub
Private Sub Form_Click()
DisplayCurrentLoan
End Sub
Private Sub Form_Current()
DisplayCurrentLoan
' Enabled the Renew button
If (CurrentView = acCurViewFormBrowse) Then
If (Renewals < RenewalsAllowed And DueDate > Now() - 1) Then
Renew.Enabled = True
Else
Renew.Enabled = False
End If
End If
End Sub
Private Sub Renew_Click()
Dim sSQL As String
If (LoanID > 0) Then
' Renew the loan
sSQL = "UPDATE Loan SET Loan.DueDate = FormatDateTime(Now()+" & _
LoanPeriod & ",2) WHERE LoanID=" & LoanID & ";"
Application.CurrentDb.Execute sSQL, dbFailOnError
' Find the Datasheet View
Dim loanForm As Form_CustomerLoans
' Ignore any error if the form cannot be found
On Error Resume Next
Set loanForm = Parent.CustomerLoans.Form
If (Not loanForm Is Nothing) Then
loanForm.Requery
End If
End If
Renew.Enabled = False
Requery
End Sub
The DisplayCurrentLoan method synchronizes the Form View with the record selected in the Datasheet View. It only does that when the CurrentView property indicates this is the Datasheet View instance. Keep in mind that both copies of the form with be executing these event handlers. It uses the parent form's reference to the other Subform control and then set's the Filter property so the Form View instance displays the currently selected loan.
The On Error Resume Next tells the runtime to essentially ignore any errors. It is conceivable that this form could be reused somewhere else where there is no Form View version of it to synchronize. In this case, the error that will result will be ignored.
The DisplayCurrentLoan method is executed in both the OnCurrent and OnClick events. OnCurrent is raised when the form is first displayed. OnClick is raised when the user selects a different record in the view. In both cases, the Form View will be synchronized with the currently selected loan.
If this is the Form View, the OnCurrent event handler also checks to see if the Renew button should be enabled. It will be enabled if the item is not overdue and if the allowed number of renewals has not been exceeded.
The Renew_Click method is called when the user clicks the Renew button. It executes a SQL statement that updates the DueDate. The DueDate is determined by adding the appropriate LoanPeriod to the current date. It then finds the Datasheet Form and calls its Requery method, which refreshes the data. This code also calls the current form's Requery method. This ensures that both views are updated.
Testing the Page
Open the CustomerAdmin form in the Form View and select a customer; their information should be displayed. Now go to the Items On Loan tab and see the items they have checked out as demonstrated in Figure 8-22. (If there are no items currently on loan, you can use the CheckOut form that you implemented in Chapter 7 to create some loans for this customer.)
Figure 8.22. The OnLoan page
Try selecting different records and verify that the Form View is synchronized with the selected record. Try renewing an item to verify that the Renewals field is incremented and the DueDate is updated.
Building the Loan History Tab
The Loan History tab will show all loans made by the customer, including those that have been returned and those still outstanding. This is really easy to implement. You just need to drop the CustomerLoans form on the page. In the Form_Load event you'll configure this as a Datasheet View and set the appropriate filter.
1. Open the CustomerAdmin form in the Layout View. Right-click the Items On Loan tab and click the Insert Page link as shown in Figure 8-23.
Figure 8.23. Adding a new tab
2. In the Other tab of the Property Sheet set the Name property to History. In the Format tab set the Caption property to Loan History.
3. In the Design tab of the ribbon, click the Subform button and then click on the History page. Delete the label that is created.
4. Select the Subform control and set its Name property to CustomerLoansHistory.
5. In the Data tab, select CustomerLoans for the Source Object property. Enter txtCustomerID in the Link Master Fields property and CustomerID in the Link Child Fields property. Enlarge the layout to take up the entire area of the page.
6. Go to the VBA editor and modify the Form_Load event handler in the CustomerAdmin code file. The complete event handler is shown in Listing 8-4 and the new code is shown in bold.
Example 8.4. Modified version of the Form_Load Event Handler
Private Sub Form_Load()
' Set the form properties for the Datasheet View
CustomerLoans.Form.Filter = "[CustomerID] = CustomerID " & _
"And IsNull([CheckedIn]) = True"
CustomerLoans.Form.FilterOn = True
CustomerLoans.Form.NavigationButtons = True
CustomerLoans.Form.NavigationCaption = "Loan"
CustomerLoans.Form.AllowFilters = False
CustomerLoans.Form.AllowAdditions = False
' Set the form properties for the loan history tab
CustomerLoansHistory.Form.Filter = "[CustomerID] = CustomerID"
CustomerLoansHistory.Form.FilterOn = True
CustomerLoansHistory.Form.NavigationButtons = True
CustomerLoansHistory.Form.NavigationCaption = "Loan"
CustomerLoansHistory.Form.AllowFilters = False
CustomerLoansHistory.Form.AllowAdditions = False
CustomerLoansHistory.Form.OrderBy = "[CheckedOut] DESC"
' Switch to the Datasheet View
' The RunCommand function requires the form to be in focus
Me.CustomerLoans.SetFocus
DoCmd.RunCommand acCmdSubformDatasheetView
' Switch the history form to a Datasheet View
Me.CustomerLoansHistory.SetFocus
DoCmd.RunCommand acCmdSubformDatasheetView
' Go back to the first page
Me.Profile.SetFocus
End Sub
The new code that you've added sets the filter and navigation properties on the CustomerLoansHistory form just like you did earlier for the CustomerLoans form. It sets the OrderBy property to show the item most recently checked out first. It also uses the RunCommand method to change the form to use the Datasheet View.
Now try out this new feature. Open the CustomerAdmin form in the Form View. Select a customer and then go to the Loan History tab. The page should look similar to Figure 8-24.
Figure 8.24. The History page
TIP
Controls on a form must have unique names. Although pages of a TabControl are designed independently from the other pages, they are still part of the main form. This means that the control names for all of the pages must be unique. For instance, if you have a Modify button on several pages, they must have different names. They can have the same caption so they appear the same but must have unique names. If you have a lot of pages with similar controls, you might want to prefix each control name with the Page name.
Summary
In this chapter you built a CustomerAdmin form using a TabControl to allow multiple pages. Each page has one or more subforms on it. With this pattern you can easily add new pages for additional features. For example, you could add a page to manage requested items or a page to show fines that are due or have been paid.
The Form Header provides a feature for entering or searching for the appropriate customer. By linking this with the various subforms, they automatically retrieve the appropriate records for the selected customer. After looking up the customer, all of the pages will re-query their content.
Some of the specific topics that were covered include:
Dealing with floating controls (hidden controls not anchored with the layout control)
Understanding the form properties that control record navigation
Simulating a Split Form using two subforms
Configuring form properties at run-time
Supporting multiple instances of the same form
Invoking the RunCommand method
As we progress through these chapters, you're building a collection of reusable forms that can be easily dropped on to another form or page. A good example is the Loan History page. With just setting a few properties and writing a few lines of code, an existing form was re-purposed to solve a new requirement.
You are also building your repertoire of design and implementation techniques. In the next chapter, you'll add some more. In particular you'll add data-bound images to your database and embed a web browser that will take the application to another level.
If you're trying hard to lose fat then you absolutely need to start using this totally brand new custom keto meal plan diet.
ReplyDeleteTo design this service, certified nutritionists, fitness trainers, and cooks have joined together to produce keto meal plans that are effective, convenient, cost-efficient, and satisfying.
Since their first launch in January 2019, 1000's of people have already transformed their figure and well-being with the benefits a great keto meal plan diet can provide.
Speaking of benefits: clicking this link, you'll discover 8 scientifically-certified ones provided by the keto meal plan diet.