You can do a lot to help make sure the user of your application enters correct data by using data macros and 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.
The tblContactProducts table in the Conrad Systems Contacts database (Contacts.accdb) 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 look up the current product price before entering it into the record.
You learned in Chapter 15, 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 in the Contacts.accdb sample database and click the Products tab, as shown in Figure 25-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. Note that in Figure 25-1, we navigated to the fourth contact record.
As you can see, a combo box on the subform (fsubContactProducts) helps the user 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 25-2.
Figure 25-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 25-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, that 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_AfterUpdate 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. Therefore, every time you pick a different product, the After Update event occurs for the ProductID combo box, and this code fills in the related price automatically. Close the fSubContactProducts form before continuing with the next section.
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 tblContactProducts 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 SubThis 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, Microsoft 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:
Function Name | Description |
|---|---|
DFirst, DLast | Return a random value from the specified domain (the table or query that’s the record source) |
DLookup | Looks up a value in the specified domain |
DMax | Returns the highest (Max) value in the specified domain |
DMin | Returns the lowest (Min) value in the specified domain |
DStDev, DstDevP | Return the standard deviation of a population sample or a population of the specified domain |
DSum | Returns the sum of an expression from a domain |
DVar, DVarP | Return the variance of a population sample or a population of the specified domain |
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 Format([BirthDate], 'mm/dd/yyyy') < #01/01/1970#")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 25-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 two-year 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 25-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 25-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.
To see how this works, open the fsubContactProducts form in Design view, select the cmbProductID combo box control from the Selection Type combo box on the Property Sheet window, find the On Not In List event property in the Properties window, and click Build to open the code. (We had you select the combo box from the Property Sheet window because the ProductName text box control overlays the cmbProductID combo box control on the form design grid.) 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 SubAs 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 25-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 that the use of the named parameter syntax in the call to DoCmd.OpenForm makes 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 (acDataErrAdded). Access re-queries the combo box and attempts a new match. Finally, if the user clicks No in the message box shown in Figure 25-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
' If called from fsubContactProducts,
' .. have category only
If intI > 1 Then
' Have a product name - grab it
Me.ProductName = Left(Me.OpenArgs, intI - 1)
End If
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 SubIf you remember, the cmbProductID NotInList event procedure passes the original string that 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.
As you learned in Chapter 9, 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 2010 recognizes the format, adds the mailto: protocol, and uses what the user typed as the display text. For example, if the user enters
jconrad@proseware.com
Access stores in the hyperlink field
jconrad@proseware.com#mailto:jconrad@proseware.com#
Rather than repeat the e-mail address as the display text, the 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 Access 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 SubIf 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 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 was fixed in Access 2007, but the preceding code will fix that problem if you use it in the earlier versions of the software.
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 2010 provides a 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. You also are restricted to moving backward and forward one month at a time using the left and right arrow keys on the date picker, so if you need to enter a date that is many months away from the current date, such as a birth date, you’ll have to click the arrow keys many times to reach the correct month and year.
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 have a calendar form—frmCalendar—that uses Visual Basic code to “draw” the calendar on a form using an option group and toggle button controls. This calendar form provides 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 our custom calendar form is the ContactDateTime control on the Events tab of the frmContacts form. You can see the calendar open in Figure 25-5.
Figure 25-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 the 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/time function varReturn = GetDate(Me.ContactDateTime, False) End Sub
Inside Out: Use Keyboard Shortcuts to Jump to Procedures and Functions
If you highlight the function call, GetDate in our previous example, and then press Shift+F2, Access takes you directly to that function in the modCalendar module.
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 function GetDate in the modCalendar module; the code is also listed here:
Option Compare Database
Option Explicit
Public Function GetDate(ctl As control, _
Optional intDateOnly As Integer = 0) As Integer
'-----------------------------------------------------------
' 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 09/05/01
' Last Revised: JLV 09/05/01
'-----------------------------------------------------------
Dim varDateTime As Variant
Dim strDateTime As String
Dim frm As Form
' Error trap just in case
On Error GoTo Error_Date
' 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
GetDate = False
Exit Function
End Select
' If the control has no value
If IsNothing(ctl.Value) Then
If intDateOnly Then
' Set default date
varDateTime = Date
Else
' .. or default date and time
varDateTime = Now
End If
Else
' Otherwise, pick up the current value
varDateTime = ctl.Value
' Make sure it's a date/time
If vbDate <> varType(varDateTime) Then
GetDate = False
Exit Function
End If
End If
' Turn the date and time into a string
' to pass to the form
strDateTime = Format(varDateTime, "mm/dd/yyyy hh:nn")
' Make sure we don't have an old copy of
' frmCalendar hanging around
If IsFormLoaded("frmCalendar") Then
DoCmd.Close acForm, "frmCalendar"
End If
' Open the calendar as a dialog so this code waits,
' and pass the date/time value
DoCmd.OpenForm "frmCalendar", WindowMode:=acDialog, _
OpenArgs:=strDateTime & "," & intDateOnly
' If the form is gone, user canceled the update
If Not IsFormLoaded("frmCalendar") Then Exit Function
' Get a pointer to the now-hidden form
Set frm = Forms!frmCalendar
' Grab the date part off the hidden text box
strDateTime = Format(frm.ctlCalendar.Value, "dd-mmm-yyyy")
If Not intDateOnly Then
' If looking for date and time,
' also grab the hour and minute
strDateTime = strDateTime & " " & frm.txtHour & _
":" & frm.txtMinute
End If
' Stuff the returned value back in the caller's control
ctl.Value = DateValue(strDateTime) + TimeValue(strDateTime)
' Close the calendar form to clean up
DoCmd.Close acForm, "frmCalendar"
GetDate = True
Exit_Date:
Exit Function
Error_Date:
' This code is pretty simple and does check for
' a usable control type,
' .. so this should never happen.
' But if it does, log it...
ErrorLog "GetDate", Err, Error
GetDate = False
Resume Exit_Date
End FunctionThe function begins by setting an error trap that executes the code at the Error_Date label if anything goes wrong. The function accepts two arguments—ctlToUpdate and intDateOnly. Access uses the ctlToUpdate argument to examine the control type and control value passed into the function. Access uses the optional intDateOnly argument to indicate whether the control needs a date and time or a date only. The function checks to see if the control passed in is a text box, combo box, or list box. If the control matches one of these control types, the function continues; otherwise, the function exists. The function then checks the value of the control passed in. If there is no value in the control, Access assigns the current date to the variant varDateTime or the current date and time, depending upon the value of the intDateOnly argument. If the control has a value, the function assigns the current value of the control to the variant and also verifies the value is a valid date and time. Next, the function converts the variant value to a string and checks to see if the frmCalendar form is open. If the function finds the calendar form currently open, the function closes it, and then reopens the form in Dialog mode. In the OpenForm call to frmCalendar, the function passes in the string value of the date and time and the intDateOnly value as OpenArgs parameters.
After the user clicks either the Save or Cancel button on the frmCalendar form, this function continues executing. If the user cancels the update by clicking the cmdCancel command button on the frmCalendar form, the function exits. If the user clicks the cmdSave command button on the frmCalendar form, the function grabs the selected date and time information off the form, sets the calling control’s value to the date and time data, closes the frmCalendar form, and then exits.
The final pieces of code that make all of this work are in the module behind the frmCalendar form. The code in the Load event of the form is listed here:
Private Sub Form_Load()
' Establish an initial value for the date
If IsNothing(Me.OpenArgs) Then
varDate = Date
Else
' Should have date, time, and "DateOnly"
' indicator in OpenArgs:
' mm/dd/yyyy hh:mm,-1
varDate = Left(Me.OpenArgs, 10)
Me.txtHour = Mid(Me.OpenArgs, 12, 2)
Me.txtMinute = Mid(Me.OpenArgs, 15, 2)
' If "date only"
If Right(Me.OpenArgs, 2) = "-1" Then
' Hide some stuff
Me.txtHour.Visible = False
Me.txtMinute.Visible = False
Me.lblColon.Visible = False
Me.lblTimeInstruct.Visible = False
Me.SetFocus
' .. and resize my window
DoCmd.MoveSize , , , 4295
End If
End If
' Initialize the month selector
Me.cmbMonth = Month(varDate)
' Initialize the year selector
Me.cmbYear = Year(varDate)
' Call the common calendar draw routine
SetDays
' Place the date/time value in a hidden control -
' The calling routine fetches it from here
Me.ctlCalendar = varDate
' Highlight the correct day box in the calendar
Me.optCalendar = Day(varDate)
End SubThis code first checks to see if any OpenArgs parameters are passed into the form. If no OpenArgs parameters are passed in, the code assigns the current date to a variant called varDate. If there are OpenArgs parameters passed in, the code parses out the OpenArgs elements for the date and possible time portion. If the optional intDateOnly variable from the GetDate function 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. The final parts of the code set up the calendar from control elements to match either the value already in the control or the system date and time. (Note that the code calls a SetDays procedure included in the form’s class module to set up the various from controls.)
After the setup code for the calendar form controls completes, the form waits until the user enters a value and clicks Save or decides not to change the value by clicking Cancel. The code for the two procedures that respond to the command buttons are as follows:
Public Sub cmdCancel_Click() ' Closing doesn't pass the value back DoCmd.Close acForm, Me.Name End Sub Private Sub cmdSave_Click() ' Hiding this dialog lets the calling code in GetDate continue Me.Visible = False 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 that the user selects on the graphical calendar is in the GetDate module. To save the value, the click event for the cmdSave command button simply hides the frmCalendar form.
Troubleshooting
Why can’t I find the ActiveX Calendar Control in Access 2010?
In Access 2010, Microsoft removed the ActiveX Calendar Control that shipped with many past versions of Access. If you want to show a graphical calendar to your users when they need to select a date, you’ll need to use the newer, built-in Date Picker control included with Access or create your own calendar form using Visual Basic, as we have in the Conrad Systems Contacts and the Housing Reservations sample applications.
Although you can certainly store and display photos in an Access application using the OLE Object data type, if your application needs to handle hundreds or thousands of photos, you could easily exceed the 2-GB file size limit for an .accdb file. You can also use the Attachment data type for your photos to store them 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 2010 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 use this functionality. Open the Housing.accdb sample database and then open the frmEmployeesPlain form, as shown in Figure 25-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.
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.
Clearing the file name saved in the record is the easy part, so let’s 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." ' 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 mage 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 Open File dialog box in Windows 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 Microsoft Office 2010 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—to do this, from the VBE window, choose References from the Tools menu and be sure the Microsoft Office 14.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 SubThe 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 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.