Assisting Data Entry
You can do a lot to help make sure the user of your application enters correct data by defining default values, input masks, and validation rules. But what can you do if the default values come from a related table? How can you assist a user who needs to enter a value that’s not in the row source of a combo box? How do you make the display text in a hyperlink more readable? Is there a way you can make it easier for your user to pick dates and times? And how do you help the user edit linked picture files? You can find the answers to these questions in the following sections.
Filling In Related Data
The tblContactProducts table in the Conrad Systems Contacts database has a SoldPrice field that reflects the actual sales price at the time of a sale. The tblProducts table has a UnitPrice field that contains the normal selling price of the product. When the user is working in the Contacts form (frmContacts) and wants to sell a new product, you don’t want the user to have to go look up the current product price before entering it into the record.
You learned in Chapter 13, “Advanced Form Design,” how to build a form with subforms nested two levels to edit contacts, the default company for each contact, and the products sold to that company and registered to the current contact. However, if you open frmContacts and click the Products tab, as shown in Figure 20–1, you’ll notice that there doesn’t appear to be any linking company data between contacts and the products sold. (The subform to display contact products isn’t nested inside another subform to show the companies for the current contact.) Again, the user shouldn’t have to look up the default company ID for the current contact before selling a product.
Figure 20–1: Selling a product to a contact involves filling in the price and the default company.
As you can see, a combo box on the subform (fsubContactProducts) helps the choose the product to sell. Part of the secret to setting the price (the SoldPrice field in tblContactProducts) automatically is in the row source query for the combo box, qlkpProductsForContacts, as shown in Figure 20–2.
Figure 20–2: The qlkpProductsForContacts query is the row source for the Product combo box on fsubContactProducts.
You certainly need the ProductID field for the new record in tblContactProducts. Displaying the ProductName field in the combo box is more meaningful than showing the ProductID number, and, as you can see in Figure 20–1, the list in the combo box also shows you the CategoryDescription and whether the product is a trial version. But why did we include the UnitPrice, TrialExpire, and PreRequisite columns in the query’s design grid?
As it turns out, you can retrieve any of these fields from the current row in the combo box by referencing the combo box Column property. (You’ll see later in this chapter, in “Validating Complex Data” on page 1071, how other code behind the form uses the additional fields to make sure the contact already owns any prerequisite product.) You can see the simple line of code that copies the UnitPrice field by opening the Visual Basic module behind the fsubContactProducts form. Go to the Navigation Pane, select the fsubContactProducts form, right-click the form and click Design View on the menu, and then click the View Code button in the Tools group on the Design tab. In the Visual Basic Editor (VBE) Code window, scroll down until you find the cmbProductID_After-Update procedure. The code is as follows:
Private Sub cmbProductID_AfterUpdate() ' Grab the default price from the hidden 5th column Me.SoldPrice = Me.cmbProductID.Column(4) End Sub
Notice that you use an index number to fetch the column you want and that the index starts at zero. You can reference the fifth column in the query (UnitPrice) by asking for the Column(4) property of the combo box. Notice also that the code uses the Me shortcut object to reference the form object where this code is running. So, every time you pick a different product, the AfterUpdate event occurs for the ProductID combo box, and this code fills in the related price automatically.
If you open the frmContacts form in Design view, select the fsubContactProducts form on the Products tab, and examine the Link Child Fields and Link Master Fields properties, you’ll find that the two forms are linked on ContactID. However, the tblContact-Products table also needs a CompanyID field in its primary key. Code in the module for the fsubContactProducts form handles fetching the default CompanyID for the current contact, so you don’t need an intermediary subform that would clutter the form design.
If you still have the module for the fsubContactProducts form open in the VBE window, you can find the code in the Form_BeforeInsert procedure. The code is as follows:
Private Sub Form_BeforeInsert(Cancel As Integer) Dim varCompanyID As Variant ' First, disallow insert if nothing in outer form If IsNothing(Me.Parent.ContactID) Then MsgBox "You must define the contact information on a new row before " & _ "attempting to sell a product", vbCritical, gstrAppTitle Cancel = True Exit Sub End If ' Try to lookup this contact's Company ID varCompanyID = DLookup("CompanyID", "qryContactDefaultCompany", _ "(ContactID = " & Me.Parent.ContactID.Value & ")") If IsNothing(varCompanyID) Then ' If not found, then disallow product sale MsgBox "You cannot sell a product to a Contact that does not have a " & _ "related Company that is marked as the default for this Contact." & _ " Press Esc to clear your edits and click on the Companies tab " & _ "to define the default Company for this Contact.", vbCritical, _ gstrAppTitle Cancel = True Else ' Assign the company ID behind the scenes Me.CompanyID = varCompanyID End If End Sub This procedure executes whenever the user sets any value on a new row in the subform. First, it makes sure that the outer form has a valid ContactID. Next, the code uses the DLookup domain function to attempt to fetch the default company ID for the current contact. The query includes a filter to return only the rows from tblCompanyContacts where the DefaultForContact field is True. If the function returns a valid value, the code sets the required CompanyID field automatically. If it can’t find a CompanyID, the code uses the MsgBox statement to tell the user about the error.
| Note | The IsNothing function that you see used in code throughout all the sample applications is not a built-in Visual Basic function. This function tests the value you pass to it for “nothing”-Null, zero, or a zero length string. You can find this function in the modUtility standard module in all the sample databases. |
| Inside Out-Understanding the Useful Domain Functions | Quite frequently in code, in a query, or in the control source of a control on a form or report, you might need to look up a single value from one of the tables or queries in your database. Although you can certainly go to the trouble of defining and opening a recordset in code, Access provides a set of functions, called domain functions, that can provide the value you need with a single function call. The available functions are as follows:
The syntax to call a domain function is as follows:
<function name>(<field expression>, <domain name> [, <criteria>]) where <function name> is the name of one of the functions in the preceding list <field expression> is a string literal or name of a string variable containing the name of a field or an expression using fields from the specified domain <domain name> is a string literal or name of a string variable containing the name of a table or query in your database <criteria> is a string literal or name of a string variable containing a Boolean comparison expression to filter the records in the domain Note that when a domain function finds no records, the returned value is a Null, so you should always assign the result to a Variant data type variable. When you construct a criteria expression, you must enclose string literals in quotes and date/time literals in the # character. (If you use double quotes to delimit the criteria string literal, then use single quotes around literals inside the string, and vice versa.) For example, to find the lowest work postal code value for all contacts where the contact type is customer and the birth date is before January 1,1970, enter:
DMin("WorkPostalCode", "tblContacts", "[ContactType] = 'customer' And [BirthDate] < #01/01/1970#") |
Open table as spreadsheet
Handling the NotInList Event
In almost every data entry form you’ll ever build, you’ll need to provide a way for the user to set the foreign key of the edited record on the many side of a relationship to point back to the correct one side record-for example, to set the ProductID field in the tblContactProducts table when selling a product on the Products tab of the frmContacts form. But what if the user needs to create a new product? Should the user have to open the form to edit products first to create the new product before selling it? The answer is a resounding no, but you must write code in the NotInList event of the combo box to handle new values and provide a way to create new rows in the tblProducts table.
Figure 20–3 shows you what happens when the user tries to type a product name that’s not already in the tblProducts table. In this case, the customer wants to purchase a twoyear support contract instead of the already available one-year product. You can see that something has intercepted the new product name to confirm that the user wants to add the new product.
Figure 20–3: When you enter a product that isn’t defined in the database, the application asks if you want to add the new product.
First, the combo box has been defined with its Limit To List property set to Yes. Second, there’s an event procedure defined to handle the NotInList event of the combo box, and it is this code that’s asking whether the user wants to add a product. If the user clicks Yes to confirm adding this product, the event procedure opens the frmProductAdd form in Dialog mode to let the user enter the new data, as shown in Figure 20–4. Opening a form in Dialog mode forces the user to respond before the application resumes execution. The code that opens this form passes the product name entered and the product type that the user selected before entering a new product name. The user can fill in the price and other details. The user can also click Cancel to avoid saving the record and close the form. If the user clicks Save, the form saves the new product record and closes to allow the code in the NotInList event procedure to continue.
Figure 20–4: The frmProductAdd form lets you define the details for the new product.
To see how this works, open the fsubContactProducts form in Design view, click the cmbProductlD combo box, find the On Not In List event property in the Properties window, and click the Build button to open the code. The code for the procedure is shown here:
Private Sub cmbProductID_NotInList(NewData As String, Response As Integer) Dim strType As String, strWhere As String ' User has typed in a product name that doesn't exist strType=NewData ' Set up the test predicate strWhere="[ProductName]=""" & strType & """" ' Ask if they want to add this product If vbYes=MsgBox("Product " & NewData & "is not defined." & _ "Do you want to add this Product?", vbYesNo+vbQuestion+_ vbDefaultButton2, gstrAppTitle) Then ' Yup. Open the product add form and pass it the new name ' - and the pre-selected Category DoCmd.OpenForm "frmProductAdd", DataMode:=acFormAdd, _ WindowMode:=acDialog, _ OpenArgs:=strType & ";" & Me.cmbCategoryDescription ' Verify that the product really got added If IsNull(DLookup("ProductID", "tblProducts", strWhere)) Then ' Nope. MsgBox "You failed to add a Product that matched what you entered." & _ ' Please try again.", vbInformation, gstrAppTitle ' Tell Access to continue - we trapped the error Response = acDataErrContinue Else ' Product added OK - tell Access so that combo gets requeried Response = acDataErrAdded End If Else ' Don't want to add - let Access display normal error Response = acDataErrDisplay End If End Sub As you can see, Access passes two parameters to the NotInList event. The first parameter (NewData) contains the string you typed in the combo box. You can set the value of the second parameter (Response) before you exit the sub procedure to tell Access what you want to do. You wouldn’t have access to these parameters in a macro, so you can see that this event requires a Visual Basic procedure to handle it properly.
The procedure first creates the criteria string that it uses later to verify that the user saved the product. Next the procedure uses the MsgBox function to ask whether the user wants to add this product to the database (the result shown in Figure 20–3). If you’ve ever looked at the MsgBox function Help topic, you know that the second parameter is a number that’s the sum of all the options you want. Fortunately, Visual Basic provides named constants for these options, so you don’t have to remember the number codes. In this case, the procedure asks for a question mark icon (vbQuestion) and for the Yes and No buttons (vbYesNo) to be displayed. It also specifies that the default button is the second button (vbDefaultButton2)-the No button-just in case the user quickly presses Enter upon seeing the message.
If the user clicks Yes in the message box, the procedure uses DoCmd.OpenForm to open the frmProductAdd form in Dialog mode and passes it the product name entered and the product type selected by setting the form’s OpenArgs property. Note the use of the named parameter syntax in the call to DoCmd.OpenForm to make it easy to set the parameters you want. You must open the form in Dialog mode. If you don’t, your code continues to run while the form opens. Whenever a dialog box form is open, Visual Basic code execution stops until the dialog box closes, which is critical in this case because you need the record to be saved or canceled before you can continue with other tests.
After the frmProductAdd form closes, the next statement calls the DLookup function to verify that the product really was added to the database. If the code can’t find a new matching product name (the user either changed the product name in the add form or clicked Cancel), it uses the MsgBox statement to inform the user of the problem and sets a return value in the Response parameter to tell Access that the value hasn’t been added but that Access can continue without issuing its own error message (acDataErrContinue).
If the matching product name now exists (indicating the user clicked Save on the frmProductAdd form), the code tells Access that the new product now exists by setting Response to acDataErrAdded. Access requeries the combo box and attempts a new match. Finally, if the user clicks No in the message box shown in Figure 20–3, the procedure sets Response to acDataErrDisplay to tell Access to display its normal error message.
The other critical piece of code is in the Load event for the frmProductAdd form. The code is as follows:
Private Sub Form_Load() Dim intI As Integer If Not IsNothing(Me.OpenArgs) Then ' If called from "not in list", Openargs should have ' Product Name; Category Description ' Look for the semi-colon separating the two intI = InStr(Me.OpenArgs, ";") ' If not found, then all we have is a product name If intI = 0 Then Me.ProductName = Me.OpenArgs Else Me.ProductName = Left(Me.OpenArgs, intI - 1) Me.CategoryDescription = Mid(Me.OpenArgs, intI + 1) ' lock the category Me.CategoryDescription.Locked = True Me.CategoryDescription.Enabled = False ' .. and clear the tool tip Me.CategoryDescription.ControlTipText = "" End If End If End Sub
If you remember, the cmbProductlD NotInList event procedure passes the original string the user entered and selected the product type (the CategoryDescription field) as the OpenArgs parameter to the OpenForm method. This sets the OpenArgs property of the form being opened. The OpenArgs property should contain the new product name, a semicolon, and the selected product type, so the Form_Load procedure parses the product name and product type by using the InStr function to look for the semicolon. (The InStr function returns the offset into the string in the first parameter where it finds the string specified in the second parameter, and it returns 0) if it doesn’t find the search string.) The code then uses the two values it finds to set the ProductName and CategoryDescription fields. Also, when the code finds a category description, it locks that combo box so that the user can’t change it to something other than what was selected on the new product row in the original form.
Fixing an E-Mail Hyperlink
As you learned in Chapter 7, “Creating and Working with Simple Queries,” one of the easiest ways to enter a hyperlink is to use the Insert Hyperlink feature. However, you can also type the hyperlink address directly into the field in a datasheet or form. Remember that a hyperlink field can contain up to four parts: display text, hyperlink address, bookmark, and ScreenTip text. If a user simply enters an e-mail address into a hyperlink field, Access 2007 recognizes the format, adds the mailto: protocol, and uses what the user typed as the display text. For example, if the user enters
jconrad@mvps.org
Access stores in the hyperlink field
jconrad@mvps.org#mai1to: jconrad@mvps.org#
Rather than repeat the e-mail address as the display text, the end result might look better if the display text is the person’s name rather than a repeat of the e-mail address. One of the forms that has an e-mail address is the frmContacts form in the Conrad Systems Contacts application. You can find the code that examines and attempts to fix the address in the AfterUpdate event procedure for the EmailName text box. (If the user enters some valid protocol other than http:// or mailto:, this code won’t fix it.) The code is as follows:
Private Sub EmailName_AfterUpdate() ' If you just type in an email name: Somebody@hotmail.com ' Access changes it to: Somebody@hotmail.com#mailto:somebody@hotmail.com# ' This code replaces the display field with the user name Dim intI As Integer ' Don't do anything if email is empty If IsNothing(Me.EmailName) Then Exit Sub ' Fix up http:// if it's there ' This was an old bug in 2003 and earlier, but fixed in 2007 Me.EmailName = Replace(Me.EmailName, "http://", "mailto:") ' Now look for the first "#" that delimits the hyperlink display name intI = InStr(Me.EmailName, "#") ' And put the person name there instead if found If intI > 0 Then Me.EmailName = (Me.FirstName + " ") & Me.LastName & _ Mid(Me.EmailName, intI) End If End Sub
If the user clears the EmailName text box, the code doesn’t do anything. If there’s something in the text box, the code uses the Replace function to search for an incorrect http:// and replace it with the correct mailto: protocol identifier. As you know, a hyperlink field can optionally contain text that is displayed instead of the hyperlink, a # character delimiter, and the actual hyperlink address. The code uses the InStr function to check for the presence of the delimiter. (The InStr function returns the offset into the string in the first parameter where it finds the string specified in the second parameter.) If the code finds the delimiter, it replaces the contents of the field with the person’s first and last name as display text followed by the text starting with the # delimiter. (The Mid function called with no length specification-the optional third parameter-returns all characters starting at the specified offset.)
| Note | In Access 2003 and earlier, when you typed an e-mail address without the mailto: protocol prefix into a hyperlink field, Access would store the hyperlink with the http:// protocol prefix in error. This bug has been fixed in Access 2007, but the preceding code will fix that problem if you use it in the earlier versions. |
Providing a Graphical Calendar
You can always provide an input mask to help a user enter a date and time value correctly, but an input mask can be awkward-always requiring, for example, that the user type a two-digit month. An input mask also can conflict with any default value that you might want to assign. It’s much more helpful if the user can choose the date using a graphical calendar.
Access 2007 provides a new Show Date Picker property for text boxes. You can set this property to For Dates to instruct Access to display a calendar icon next to the control when it contains a date/time value and has the focus. The user can click the button to pop open a graphical calendar to select a date value. But Show Date Picker isn’t available for controls other than the text box control, and the date picker lets the user enter only a date, not a date and time.
Both the Conrad Systems Contacts and the Housing Reservations sample applications provide sample calendar forms and code you can use to set a date/time value in any control. The two applications actually have two different versions of a calendar form-one that employs the Calendar ActiveX control (frmCalendarOCX), and a second (frmCalendar) that uses Visual Basic code to “draw” the calendar on a form using an option group and toggle button controls. Both forms provide an option to enter a time as well as select a date.
Troubleshooting
 |
Why isn’t Access setting my defined default value for a date/time field? Did you also define an Input Mask property? If so, then that’s your problem. A date/time field is actually a floating-point number, but Access always converts and displays the character value in table and query datasheets and forms and reports. When you define an Input Mask property, any Default Value setting must match the restrictions imposed by the input mask. If the value violates the restrictions, Access won’t use the default value. When you assign a default value to a date/time field, you typically use the Date or Now built-in functions. These functions return a valid date/time floating-point value- which probably won’t match your input mask restrictions. To have Access use the default value, you must format it to match your input mask. For example, if your input mask is 90/00/0000\ 00:00, then you should set the Default Value property of the field or control to =Format(Now(), “mm/dd/yyyy hh:nn”. This forces Access to return a string value as the default that matches your input mask.
|
This graphical facility is available in the sample applications wherever you see a small command button next to a control containing a date or date/time field on a form. Click the button to open the calendar and set the value. One control that uses the ActiveX version of the calendar is the ContactDateTime control on the Events tab of the frmContacts form. You can see the calendar open in Figure 20–5.
Figure 20–5: Click the calendar command button next to the ContactDateTime control on the Events tab of the frmContacts form to open a graphical form to select the date and enter the time.
The code in the Click event of this command button calls a public function to open the form and pass it the related control that should receive the resulting date value. You can find this code, shown here, in the module for the fsubContactEvents form.
Private Sub cmdContactTimeCal_Click() Dim varReturn As Variant ' Clicked the calendar icon asking for graphical help ' Put the focus on the control to be updated Me.ContactDateTime.SetFocus ' Call the get a date function varReturn = GetDate(Me.ContactDateTime, False) End Sub
When the user clicks the command button, Access moves the focus to it. The code moves the focus back to the date field to be edited and calls the public function where the real action happens. You can find the code for the GetDateOCX function in the modCalendar module; the code is also listed here:
Option Compare Database Option Explicit ' Place holder for the form class Dim frmCalOCX As Form_frmCalendarOCX ' End Declarations Section Function GetDateOCX(ctlToUpdate As Control, _ Optional intDateOnly As Integer = 0) '----------------------------------------------------------- ' Inputs: A Control object containing a date/time value ' Optional "date only" (no time value) flag ' Outputs: Sets the Control to the value returned by frmCalendar ' Created By: JLV 11/15/02 ' Last Revised: JLV 11/15/02 '----------------------------------------------------------- ' Set an error trap On Error GoTo ProcErr ' Open the OCX calendar form by setting a new object ' NOTE: Uses a module variable in the Declarations section ' so that the form doesn't go away when this code exits Set frmCalOCX = New Form_frmCalendarOCX ' Call the calendar form's public method to ' pass it the control to update and the "date only" flag Set frmCalOCX.ctlToUpdate(intDateOnly) = ctlToUpdate ' Put the focus on the OCX calendar form frmCalOCX.SetFocus ProcExit: ' Done Exit Function ProcErr: MsgBox "An error has occured in GetDateOCX. " _ & "Error number " & Err.Number & ": " & Err.Description _ & vbCrLf & vbCrLf & _ "If this problem persists, note the error message and " _ & "call your programmer.", , "Ooops . . . (unexpected error)" Resume ProcExit End Function
The function begins by setting an error trap that executes the code at the ProcErr label if anything goes wrong. You might remember from the previous chapter that you can open a form that has code behind it by setting an object to a new instance of the form’s class module. This is exactly what this function does to get the form open. In addition, it calls the Property Set procedure for the form’s ctlToUpdate property to pass it the control object that should be updated after the user picks a date value. The function also passes along an optional variable to indicate whether the control needs a date and time or a date only (intDateOnly). After the calendar form is open and has the control it needs to update, this function is finished. Notice that the object variable used to open the form is declared in this module’s Declarations section. It cannot be declared inside the function because the variable would go out of scope (and the form would close) when the GetDateOCX function exits.
The final pieces of code that make all of this work are in the module behind the frmCalendarOCX form. The portion of the code that initializes the form is listed here:
Option Compare Database Option Explicit ' This form demonstrates both using a custom control (MSCal.OCX) ' and manipulating a Class via Property Set ' See also the GetDateOCX function that activates this form/module. ' Place to save the "date only" indicator Dim intDateOnly As Integer ' Variable for the Property Set Dim ctlThisControl As Control ' Optional variable for the Property Set Dim intSet As Integer ' Place to save the date value Dim varDate As Variant ' End Declarations Section Private Sub Form_Load() ' Hide myself until properties are set Me.Visible = False End Sub Public Property Set ctlToUpdate(Optional intD As Integer = 0, ctl As Control) ' This procedure is called as a property of the Class Module ' GetDateOCX opens this form by creating a new instance of the class ' and then sets the required properties via a SET statement. ' First, validate the kind of control passed Select Case ctl.ControlType ' Text box, combo box, and list box are OK Case acTextBox, acListBox, acComboBox Case Else MsgBox "Invalid control passed to the Calendar." DoCmd.Close acForm, Me.Name End Select ' Save the pointer to the control to update Set ctlThisControl = ctl ' Save the date only value intDateOnly = intD ' If "date only" If (intDateOnly = -1) Then ' Resize my window DoCmd.MoveSize , , , 3935 ' Hide some stuff just to be sure Me.txtHour.Visible = False Me.txtMinute.Visible = False Me.lblColon.Visible = False Me.lblTimeInstruct.Visible = False Me.SetFocus End If ' Set the flag to indicate we got the pointer intSet = True ' Save the "current" value of the control varDate = ctlThisControl.Value ' Make sure we got a valid date value If Not IsDate(varDate) Then ' If not, set the default to today varDate = Now Me.Calendar1.Value = Date Me.txtHour = Format(Hour(varDate), "00") Me.txtMinute = Format(Minute(varDate), "00") Else ' Otherwise, set the date/time to the one in the control ' Make sure we have a Date data type, not just text varDate = CDate(varDate) Me.Calendar1.Value = varDate Me.txtHour = Format(Hour(varDate), "00") Me.txtMinute = Format(Minute(varDate), "00") End If End Property
We know it looks complicated, but it really isn’t. The first event that happens is the Load event for the form, and code in that event procedure hides the form until the GetDateOCX function uses the Property Set statement to pass the control to update to the form. The ctlToUpdate Property Set procedure saves the Control object (the control next to the button the user clicked on the form) in a variable in the Declarations section. If the optional intDateOnly variable is True (the control needs only a date value, not a date and time value), the form shrinks to hide those text boxes. Because the event date/time field needs a time value, this parameter is False, so you should be able to see the hour and minute text boxes. Next, the code checks to see if the control already has a value, and initializes the calendar value and two text boxes to display an optional hour and minute using either the value already in the control or the system date and time.
After the initialization code finishes, the form waits until the user enters a value and clicks Save or decides to not change the value by clicking Cancel. The code for the two procedures that respond to the command buttons is as follows:
Public Sub cmdCancel_Click() ' Close without saving DoCmd.Close acForm, Me.Name End Sub Private Sub cmdSave_Click() ' Saves the changed value back in the calling control ' Do some error trapping here in case the calling control can't ' accept a date/time value. On Error GoTo Save_Error ' Make sure we got a valid control to point to If intSet Then ' OK - save the value If (intDateOnly = -1) Then ' Passing back date only ctlThisControl.Value = Me.Calendar1.Value Else ' Do date and time ctlThisControl.Value = Me.Calendar1.Value + _ TimeValue(Me.txtHour & ":" & Me.txtMinute) End If End If Save_Exit: DoCmd.Close acForm, Me.Name Exit Sub Save_Error: MsgBox "An error occurred attempting to save the date value.", _ vbCritical, gstrAppTitle ErrorLog "frmCalendarOCX_Save", Err, Error Resume Save_Exit End Sub
Clicking the Cancel button (cmdCancel_Click) simply closes the form without changing any value in the control passed to the form. The code that saves the value the user selects on the graphical calendar is in the Click event for the cmdSave command button. This code verifies that the Property Set procedure executed correctly and then saves the selected value back into the control object-which happens to point to the control on the form that should be updated.
| Inside Out-ActiveX or Not ActiveX, That Is the Question! | If you look behind all the little calendar buttons that activate a graphical way to set a date or date/time value, you’ll find that only the button next to the Birth Date field on the frmContacts form and the button next to the Date/Time field on the fsubContact-Events form use the ActiveX version of the calendar discussed in this chapter. All the others use the custom form we designed with some complex code to actually build and manipulate a calendar created with an option group control. So why did we do that? As you learned in Chapter 13, you can probably find dozens of ActiveX controls registered on your computer, but only some of them work in Access. To complicate matters, Microsoft has issued different versions of some of these controls with each new version of Access. You can use these controls with confidence if you’re installing your application on a computer that has the exact same version and service pack level of Access that you used to create the application. However, if you install your application in Runtime mode on a computer that has a different version of Access installed, your ActiveX control might not work at all. (See Chapter 25, “Distributing Your Application,” for details about creating a distributable installation of your application.) The user sees a “Can’t create object” error message when opening a form that uses the ActiveX control. We discovered that even the simple Calendar ActiveX control was giving us problems when we tried to distribute applications that used it in prior versions of Access. So, we came up with a way to provide a very similar interface using standard Access controls and Visual Basic. This is why several of the calendar buttons use our custom form (frmCalendar) instead of the form that depends on the ActiveX control. We recommend that you try the ActiveX control first If you find that your application has problems on some computers on which you attempt to install your application, you might need to get creative and build your own solution using native Access tools. |
Working with Linked Photos
Although you can certainly store and display photos in an Access application using the OLE Object data type, if your application might need to interact with a Web application, you cannot use this feature. Web applications cannot handle the internal format of a stored OLE object. Also, if your application needs to handle hundreds or thousands of photos, you could easily exceed the 2, gigabyte file size limit for an .accdb file. If you’re working with your data on the Web using Microsoft Windows SharePoint Services (version 3), you can use the new Attachment data type for your photos that can store attachments more efficiently, but you still might run into file size limitations if you need to store many photos. The alternative method is to store the pictures as files and save the picture path as a text field in your tables.
The good news is the image control in Access 2007 now lets you specify a Control Source property. When this property points to a field containing a folder and file location as a text string, the image control will load the photo for you from that location. However, you should still provide features in your forms to help users to easily edit the file location information.
The Housing Reservations database (Housing.accdb) is designed to work on the Web. Open the Housing.accdb sample database and then open the frmEmployeesPlain form, as shown in Figure 20–6. The employee picture you see on the frmEmployees and frmEmployeesPlain forms is fetched by the image control from the path stored in the Photo field of the table.
Figure 20–6: The image control loads the photo on the Employees form from a picture path.
Notice that the user cannot see the contents of the Photo field that contains the picture path information. However, we’ve provided two command buttons to make it easy for the user to edit or delete the photo path information.
Deleting and Updating an Image Path
Clearing the file name saved in the record is the easy part, so let’s take a look at that first. Behind the Delete button that you can see on the frmEmployeesPlain form, you can find the following code:
Private Sub cmdDelete_Click() ' User asked to remove the picture ' Clear photo Me.txtPhoto = Null ' Set the message Me.lblMsg.Caption = "Click Add to create a photo for this employee." ' Make it visible ' Put focus in a safe place Me.FirstName.SetFocus End Sub
When the user clicks the command button asking to delete the photo, the code sets the photo path to Null and displays the informative label. Setting the Photo field to Null causes the image control to remove the image. Because the background of the image control is transparent, the label control hidden behind it shows through, displaying an informative message.
The tricky part is to provide the user with a way to enter the picture path to add or update a picture in a record. Although you could certainly use the InputBox function to ask the user for the path, it’s much more professional to call the Windows Open File dialog box so that the user can navigate to the desired picture using familiar tools. The bad news is calling any procedure in Windows is complex and usually involves setting up parameter structures and a special declaration of the external function. The good news is the 2007 Microsoft Office system includes a special FileDialog object that greatly simplifies this process. You need to add a reference to the Microsoft Office library to make it easy to use this object-from the VBE window, choose References from the Tools menu and be sure the Microsoft Office 12.0 Object Library is selected. After you do this, you can include code using the FileDialog object to load a picture path. You can find the following code behind the Click event of the Add button (cmdAdd) in the frmEmployeesPlain form:
Private Sub cmdAdd_Click() ' User asked to add a new photo Dim strPath As String ' Grab a copy of the Office file dialog With Application.FileDialog(msoFileDialogFilePicker) ' Select only one file .AllowMultiSelect = False ' Set the dialog title .Title = "Locate the Employee picture file" ' Set the button caption .ButtonName = "Choose" ' Make sure the filter list is clear .Filters.Clear ' Add two filters .Filters.Add "JPEGs", "*.jpg" .Filters.Add "Bitmaps", "*.bmp" ' Set the filter index to 2 .FilterIndex = 2 ' Set the initial path name .InitialFileName = CurrentProject.Path & "\Pictures" ' Show files as thumbnails .InitialView = msoFileDialogViewThumbnail ' Show the dialog and test the return If .Show = 0 Then ' Didn't pick a file - bail Exit Sub End If ' Should be only one filename - grab it strPath = Trim(.SelectedItems(1)) ' Set an error trap On Error Resume Next ' Set the image Me.txtPhoto = strPath ' Set the message in case Image control couldn't find it Me.lblMsg.Caption = "Failed to load the picture you selected." & _ " Click Add to try again." End With ' Put focus in a safe place Me.FirstName.SetFocus End Sub
The code establishes a pointer to the FileDialog object using a With statement, sets the various properties of the object including the allowed file extensions and the initial path, and then uses the Show method to display the Windows Open File dialog box. Setting the Photo field causes the image control to load the new picture, but the code also sets the message hidden behind the image control just in case the image control had a problem loading the file.
EAN: 2147483647
Pages: 234