10.3. Event Property and Procedure Examples
The remainder of this chapter will apply all of the information covered in the previous sections to get you started on the automation of Access Forms. The examples are in no particular order and do not rely on each other for reader comprehension. In case an example is associated to another, it will be noted. This allows for the reading of only the property examples that are pertinent to you.
10.3.1. Form_Open(): Passing the OpenArgs Parameter
The Form Open event property triggers whenever a form is opened. Each form is only opened once, so this event should theoretically trigger only one time. ("Theoretically" is used here because there are cases when events should fire only once, but they end up firing more than once. Don't take this warning too seriously, but if you suddenly have trouble with a procedure, trace it and see if this is happening.)
In the OpenForm method of the DoCmd object, there is a parameter called OpenArgs that will allow you to pass a string to the called Form. There is no structure to the parameter value, other than it needs to be a string.
For example, the following will open the form called Orders and pass it the string value.
Docmd.OpenForm "Orders", OpenArgs:="CallingProc | MainMenu"
When the form opens, you can examine the OpenArgs property using the Me object. See the earlier section You Talking to Me? for more information. The following example will examine the OpenArgs property of the form, and react to it.
Private Sub Form_Open(Cancel As Integer)
If Len( Me.OpenArgs) > 0 Then
'Do Something
Else
Cancel = True
End If
End SubIn the previous code example, the application checks to see if a value was provided for the OpenArgs property. This is accomplished by checking the length of the value of the property. The Len() function will return the length of any string.
So, if the length of the OpenArgs property is more than 0, then a value has been supplied. If this is true then some processing will occur, otherwise the Cancel variable will be set to True.
Previously during the discussion on properties, it was mentioned that some event procedures could be cancelled. The Open event procedure is one of them. When the Cancel value is set to True, the event property will not complete the operation. In this example, the Open property will not complete, meaning that the Form will not finish opening. So, if the OpenArgs value is not provided, then the form will not open. This is a sneaky way of preventing users from opening a form on their own from the Database window.
10.3.2. OnTimer(): Performing an Action on an Interval
The On Timer property is an event that executes at a time specified by the Form's TimerInterval property. This is not a time like 5:34 p.m.; it is entered in milliseconds, which is 1/1000 of a second. So, if you want the On Timer property to execute every 3 seconds, then you would set the TimerInterval to 3000.
One practical use of the On Timer event property is to trigger code to notify users that they are to exit the application, perhaps for maintenance or an update. It is not uncommon to open a form, keep it hidden, and then execute On Timer to check a flag set by the administrator.
Private Sub Form_Timer()
If DLookup("Kickout", "ztblAppInfo") = True Then
MsgBox "The application must be closed", _vbInformation, "Administrative Request"
ApplicationExit 'Custom Procedure to exit application
End If
End SubThe above example assumes that there is a table called ztblAppInfo, which has a Boolean field called Kickout. Using the DLookup function, the code will examine the Kickout field, and if it is true, it will display the message and exit the application.
10.3.3. OnClick(): Open a Form Based on Value on Current Form
The On Click event is triggered when you press the mouse button. The colloquial terms for mouse button actions are left click and right click, but the official Windows terminology is primary and secondary. The On Click event will only respond to the primary or the left click action. The same is true for the On Dbl Click event property as well. If you have selected to switch your mouse button, then these properties will trigger when the right mouse button is pressed.
As you can imagine, the On Click property is heavily used, as there is typically at least one command button on every form. Close the form, exit the application, print a report, or open another form are all common examples of On Click use.
For example, suppose that there are two forms, frmCustomer and frmOrder, which display information about customers and orders respectively. As you are browsing the customer information, perhaps you would like to quickly browse the order information, on frmOrder, that pertains only to the current customer. With the help of a command button, and some VBA code, this possibility is an easy reality.
The final pieces of information that complete this puzzle are the field names that correspond between the frmCustomer CustomerID and the frmOrder CustomerID. Typically, the table for each of these entities contains the field called CustomerID. Also, the name of the control that is bound to the CustomerID on frmCustomer is needed. So, assume that it is called txtCustomerID.
Put all of this together, and you can add the following type of code to any form to open another, where a dependency exists.
Private Sub cmdShowOrders_Click()
If Not Me.NewRecord Then
DoCmd.OpenForm "frmOrder", WhereCondition:="CustomerID=" & txtCustomerID
End If
End SubThe example above starts with the name of the command button. Following a standard naming convention, the prefix cmd signifies that this is a command button, and not a toggle button. Since the button will show the Orders form, the remainder of the name ShowOrders will make debugging and the maintenance of the code easy to recognize in the future. Finally, the event property is On Click.
The NewRecord property is a Boolean value that signifies if the user is on the New Record of the form. (In Access, all records are added on the New Record.) If the user is entering a new customer, then there should not be any existing orders, thus there is no need to open the Orders form.
The OpenForm method will open the form called frmOrder, and issue a Where clause to specify which records to display in the form. Some people call this a filter, others a query; either way, the records on the form will be restricted to only those that meets the criteria. In this case, the criteria specified will restrict the data to only those customer IDs that match the value in the text box called txtCustomerID. For clarification, the field name on the left of the "equals to" symbol (=) refers to the field on the opened object. The field name on the right refers to a field on the calling object.
To take this example one step further, suppose that each order has one invoice printed for it. When you are viewing the Order record, you can print the invoice for the order. The code is nearly identical, except that a Report is being opened.
Private Sub cmdShowInvoice_Click()
If Not Me.NewRecord Then
DoCmd.OpenReport "rptInvoice", WhereCondition:="OrderID=" & txtOrderID
End If
End Sub10.3.4. OnCurrent(): Opening Existing Records as "Read-Only"
The On Current event property triggers each and every time a record is accessed. Thus, adding a Procedure to this event will increase the load time for the user, so utilize this procedure with caution. If the user is accustomed to instantaneously browsing through multiple records, and you add a procedure that causes a noticeable delay, expect to receive a nasty e-mail.
For this example, suppose that you want to always start a record in a read-only state, and when the user is ready to edit the record, he or she must click a command button. The idea of this example is that you want to protect the user from accidentally making a change, or worse yet, an unauthorized deletion. This methodology could be implemented as a part of your security system, as only authorized users can see the Edit command button.
To do this, the On Current procedure can be used to set the form into the read-only state. A command button and its On Click procedure can be used to prepare the form to allow data modifications.
Private Sub Form_Current() 'When user navigates to an existing record, disable the 'ability to Add, Edit, and Delete. 'For new records, enable all modification abilities. Dim fStatus As Boolean 'Determine if user is on the NewRecord fStatus = Me.NewRecord 'Set the modification abilities for the Form Me.AllowEdits = fStatus Me.AllowDeletions = fStatus cmdEdit.Enabled = Not fStatus End Sub
This procedure would be associated to a Form's On Current event property. It would execute every time the user moves to a different record. The NewRecord property is examined to determine if the user is on the New Record. In Access all new records are added at the New Record. It can be accessed by clicking on the New Record button on the record navigation bar, or going one record past the last existing record.
The NewRecord property of the Me object returns a Boolean value that signifies if the user is currently on the New Record. If the user is on this record, then allow them to Add, Edit, or Delete the record. Otherwise, it would mean that they are on an existing record, so restrict their editing capabilities.
The AllowEdits and AllowDeletions are Form (Me) properties that specify whether or not the user can edit and delete records. Properties similar to these are AllowAdditions and DataEntry. The AllowAdditions property specifies if new records can be added. The DataEntry property sets the form into a mode in which the user can only enter new records or edit the records that they have just entered.
It stands to reason that for the completion of this example, there would need to be an Edit command button that would set the edit capabilities to allow edits to occur. (If you're not familiar with the On Click event, please review the sample code for it.) There isn't any logic needed for this event procedure, for a couple of reasons. First, if the user is on the New Record, then the Edit button is not enabled (or disabled). When the user clicks the Edit button, it is assumed that he or she only wants to do one thing, which is to gain the ability to edit the record.
Private Sub cmdEditRecord_Click() 'When user clicks this button 'Disable the Edit Button, Enable the Edit capabilities 'Set the modification abilities for the Form Me.AllowEdits = True Me.AllowDeletions = True 'It is not possible to disable the active control. 'Doing so will result in an error. To avoid the error, 'set the focus to any another control. txtFirstName.Setfocus cmdEdit.Enabled = False End Sub
This example requires that a field called txtFirstName exist on the form. When the user clicks the button, the AllowEdits and AllowDeletion properties would be turned on, and after passing the control to the First Name field, the Edit button would be disabled.
10.3.5. BeforeUpdate(): Performing Data Validation
The BeforeUpdate property is triggered before a change or entry to a form or control is committed. This step allows for intervention between the time the user sets the value and when the value is actually saved.
In the following example, a sample of performing data validation is demonstrated. Data validation is the process of ensuring that the value entered by the user is logical and/or does not validate other business rule logic.
Private Sub txtEndDate_BeforeUpdate(Cancel As Integer)
'Len returns the length of the values entered in the control
'i.e. Len("Abcdef") = 6
'If one of them is zero, then the product will be zero
If Len(txtEndDate) * Len(txtStartDate) > 0 Then
'Verify that the Start Date is before the End DateIf txtEndDate > txtStartDate Then
'Alert the user and Cancel the update
Cancel = MsgBox("Start Date must be before the End Date", _
vbInformation, "Data Validation Failure")
End If
End IfThis less than orthodox example starts with verification to ensure that both the Start Date and the End Date have been entered. (Unless you enforce the order in which fields are entered, the user may enter information in ways you've never imagined.) So, you would not want the error message to appear if the user had not entered the Start Date. Thus taking the length of both fields and multiplying them together will either result in 0 or a value greater than 0. When greater than 0, it means that both fields have a value.
It is assumed for this example that the format of the date entry is handled somewhere else. One method would be to use the InputMask. Assuming that if there is an entry, it will be a date, then all that remains to validate is whether or not the Start Date value is less than the End Date value.
Finally, if the validation fails, then the error message is displayed using the MsgBox function, and the Cancel parameter is set at the same time. The trick utilized here plays on the fact that the MsgBox function always returns a value greater than 0. In Access, 0 is the same as False, but any other value is considered to be True. So, combining the setting of the Cancel parameter and the execution of the MsgBox makes for clean, concise VBA code.
If the Cancel parameter does get set to True, then the BeforeUpdate event property is not finished, which prevents the save of the End Date.
10.3.6. AfterUpdate(): Synchronizing Two Combo Boxes
The AfterUpdate property is triggered after the form or control is updated. If you have not read about the BeforeUpdate property, it is important to know that the BeforeUpdate property must trigger and pass, then the record is saved (which may have issues due to constraints contained within the database itself), and only then can the AfterUpdate property trigger. But, after some values are updated, you may need to react to the newly saved value.
In this example, a common problem with combo boxes is addressed. There are times when you display the values in one combo box based on a value selected in another combo box.
Private Sub cboCategory_AfterUpdate()
'Create a new rowsource for the Product Combo Box
'after a category is selected
'Verify that the user selected a category
If Len(cboCategory) > 0 Then
With cboProduct
'Set the Product Rowsource to find
'only products in the selected category
.RowSource = _
"SELECT ProductID, ProductName " & _
"FROM Products WHERE CategoryID = " & cboCategory
'Repopulate the Product Combo Box.Requery
'Add Dazzle by displaying list for the user
.SetFocus
.Dropdown
End With
End If
End SubThe example above assumes that there are two combo boxes, cboCategory and cboProduct. cboCategory has a rowsource that uses the Category table (straight from the table or a query), and retains the CategoryID as the BoundColumn.
cboProduct can initially be based on the entire Product table, but after you select a value from cboCategory, then the rowsource for cboProduct is recreated. After cboCategory is updated, there is a check to ensure that a nonblank value is chosen (don't forget that updating a value from something to nothing is still an update), and then an SQL statement is created to extract only the records that contain a CategoryID that matches the CategoryID from cboCategory. (If you are not familiar with creating SQL statements, see Chapter 14 for more information.)
After the rowsource property of the dependent combo box is created and set, the work is not completed. You still need to instruct Access to repopulate the data, which is the job of the Requery method. If you omit this step, then the combo box will continue to display the same information as it did before.
Finally, for an extra touch of fun and excitement for the user, the dependent combo box is opened to display the new population of Product items. One thing to note is that the DropDown method can only be executed on the combo box that has the focus.
10.3.7. OnChange(): Late Bind a Subform on a Tab Control
The degradation of performance is a never-ending battle for the Access developer as users want more and more information, on the same form no less. But, no matter how accommodating you are to satiate their every need and desire, the smallest of performance hits causes your phone to ring and e-mail inbox to overflow with complaints. Even when you tell them ahead of time!
The reduction of Access performance sometimes sneaks up on you slowly, other times it's instantaneous. In case you're new, the instantaneous ones are a blessing, as you can quickly reverse whatever change you just made. The sneaky ones are typically a combination of a myriad of causes, of which you can spend several hours trying to revert, reprogram, or find a suitable workaround to restore the application to a usable performance level.
Now, with this kind of introduction, you might assume that this section is going to discuss some incredibly complex topics such as benchmarking, timing execution, or some other such fancy methodology. Unfortunately, that is the farthest thing from the truth. The objective of this particular example is to demonstrate how to work around a very typical performance drain.
In Access, with multiuser applications, it is not feasible to have data remain on each user's local machine, as the synchronization of the data would be very difficult. So, the next obvious step is to place a single database file (.mdb) on the network, and let all users share it. If you have ever done this, you have learned about the concept of corruption and know that it doesn't take long for users to begin complaining about the performance. The next step a developer can take is to split all of the user interface (UI) objects from the data into separate database files. Then each user can have a copy of the UI.mdb on his or her local machine, and only the data is shared. This action causes a significant increase in performance as form, query, and report definitions no longer need to pass from the network to each user machine. This reduction of network traffic is a significant factor in the increase. So, when all of these steps are taken, the only thing remaining to cause performance problems is the retrieval of data from the network.
When a form is fairly small, lightly populated with few controls, the ability for that form to load is fairly snappy. But as users ask for more, you find yourself adding a combo box here, a subform there, and the next thing you know, the form requires 30 seconds just to open. Of course your development machine has both databases on your local hard disk, so all of your tests don't net the same results. You might argue with the user about their sense of time, then physically walk to their machine, dramatically snap your arm to display your calculator wristwatch, and start your stopwatch as they try to open the form. You stare in horror as your regular load time is surpassed then abused, and you start looking for the nearest fire alarm to cause a distraction while you slip back to your office.
Don't let this be you.
It is not uncommon to create a tab control, and on each tab, place a different subform. In even the simplest of forms, the number of subforms can quickly increase. For example, customers can have multiple addresses, telephone numbers, e-mail addresses, contact journal entries, payment history, and of course the orders themselves. If a subform is made for each of these data points, you can imagine that the form is going to begin to get sluggish.
One final note to bring this point home is that maybe not every tab is used during every data entry session. There may be one set of users who only use one subset of the tabs, while another set of users uses a different subset. Also, in the example of surveys, there may be some tabs that are skipped if a question is answered a certain way.
For all of these reasons, never assume that all subforms need to be populated every time the form is opened. On the basis of this concept, it stands to reason that there is a way to prevent the data from being loaded to the subform. Being able to prevent it first requires you to understand how it works.
A subform control is just as generic of a control as a text box or combo box, but has no functionality until several properties are set. The following properties are necessary forasubform to load and display the data.
If the only way that you have created a subform on a main form is by using the Form Wizard, the Subform Control Wizard, or by dragging and dropping a form object from the Database window to the main form, then you may not have ever had the need to populate these properties. As you can see from the numerous techniques, Access does a very good job of insulating a user from having to deal with the minute details. This is good for development in speed and ease of use, but not so good when it is time to crawl under the hood and figure out what makes the motor run.
When a main form loads, and it encounters a subform, the SourceObject property is examined, and then all of the event procedures of that form (the subform) have to be executed. When it is finished, the main form returns to finish any remaining load tasks. Imagine having 10 subforms, each based on a complex query, and each with some code that needs to execute during the Load, Open, and/or Current event procedures. You can see that the ticks of the clock will quickly add up to a nasty e-mail from an unhappy user.
So, suppose there was a way to prevent the data from loading, and any of the event procedures from triggering, until such a time that the user absolutely needed to view that data. With each subform located on a separate tab, then the On Change event of the tab control itself could be used to implement an On Demand load system for the form.
Imagine the joy of the user when they go from 45 seconds of load time to only 5 seconds. (It has been done.) They'll think you're a genius. The remainder of this chapter will demonstrate how to implement a rather generic On Demand subform retrieval system that utilizes the concept of binding the subform when needed, and not automatically. This concept is also known as Late Binding, and can also be applied to combo boxes, list boxes, OLE bound object frames, charts, and any other object that requires data retrieval or has code associated to it.
The following sub procedure is a generic routine that binds a subform to the main form and then resets the LinkChildField property.
Sub BindSubform( _
psfrm As SubForm, _
pstrSourceObject As String, _
pstrLinkChildField As String)
'Generically Bind a Subform to a Main Form
'Example Proc Call: BindSubform(sfrmTest, "frmSome_Test", "TestID")
With psfrm
'Only Bind if not previously bound
If Len(.SourceObject) = 0 Then
'Specify the name of the subform
.SourceObject = pstrSourceObject
'Set the Link Child Field
.LinkChildFields = pstrLinkChildField
End If
End With
End SubIt's important to note that for this workaround (to save load time) to be successful, all of the subforms that are placed on the main form must not have a value entered for the SourceObject properties. (During development, this is not needed, but just before deployment, be sure to remove the entries.) By not having the subforms connected, there is no load time incurred against their existence. A caveat to this is that you may have one or two subforms on the first tab that the user absolutely insists on displaying the first tab. If that is the case, then populate it or them via the normal method.
In the code sample above, there is a check to see if the SourceObject has been populated. If not, then it gets populated, which will cause the loading of the subform data to be executed. This generic procedure can be called any number of times from within the forms, and saves the repetition of the same code being written for every subform that needs to be bound.
The code to handle the call to the BindSubform procedure, when the user changes the Tab on the Tab control, is in the next example.
Private Sub tabProj_Change()
'This procedure handles any needs of the user after
'they select a different tab on a tab control
'Using the control name of each tab, determine what needs to
'happen on that tab.
Select Case tabProj.Pages(tabProj.Value).Name
Case "pgStates"
'Perform data validation that prevents loading this tab
'until all data entry is correct on Tab1
If IsValidate_Tab1 Then
'Bind the States subform
BindSubform sfrmStates, "frmProject_States", "ProjectID"
End If
Case "pgLocations"
'Bind Locations
BindSubform sfrmLocation, _
"frmProject_Locations", "ProjectStateID"
Case Else
'Performing any special processing for other tabs
'...
End Select
End SubThis example starts with the declaration of the On Change event procedure for a tab control that is named tabProj. The prefix tab specifies that the control is a tab control. A Select...Case...End Select structure is used, because there may be many subforms on the main form, so an If...Then...Else...End If structure would become quite convoluted.
The Select structure examines the Pages collection of the tab control. tabProj.Value is the number value found in the PageIndex property of the tab. By using that number as the index for the Pages collection, it is the same as referring directly to the tab itself. From there, the inspection of the Name property returns the tab's assigned control name.
What is gained by taking this route is the ability to refer to the assigned tab name, instead of a number, which adds to the readability and debug-ability of the code. For starters, the PageIndex value starts at 0, which can be easily forgotten, or overlooked, and cause a problem. Next, the PageIndex of a tab can change, as it specifies the order of the tabs, but the Name stays the same. So, if you base your code on the name and then if you decide to move the order of the tabs, your code does not need to be changed to retain the same functionality.
The call to the BindSubform procedure requires some manual intervention in that you need to know the exact names of the subform control, the source object form that is to be bound, and the LinkChildField to be used.
This example is very long, and probably difficult for even intermediate developers to understand completely. But, hopefully the knowledge that there is this kind of control over a seemingly uncontrollable situation may change your outlook of Access and VBA programming. Knowing that you are in control may make other programming challenges easier for you to master.
10.3.8. NotInList(): Adding a Value to a Combo Box at Runtime
The NotInList event property is triggered when you enter a value into a combo box that is not contained within the list. This event property and procedure works in conjunction with the LimitToList property, which is a Boolean property of a combo box that instructs Access to restrict selections to values in the list.
This example comes from the need to add a new value to the underlying table used to populate a combo box list. Because a combo box was designed to display values from a table, it means that the value must already be in the table in order to be displayed.
In some combo boxes, it may be important to restrict the data to a finite list; for example, hotel room numbers. You would not want to allow a customer to be assigned to a room number that doesn't physically exist. If the hotel were to build another wing, then the new room numbers would be added by the administrator, for the use of the desk clerk.
On the other hand, there may be situations where it is not feasible or cost-effective to stop data entry and request that the Admin enter a new value. Instead, the combo box is used to increase data entry speed, but only to limit excessive similar entries from being added. For example, job titles are sometimes very common, but other times outrageously unique. Receptionist, Administrative Assistant, and Project Manager are all standard values that are repeatable values, but Senior Herbal Fragrance Arranger may be a less common choice.
You would not like to restrict the user from being able to enter the unique titles, but on the other hand, you would like to try to prevent a myriad of permutations of similar values from being entered. For example, Admin Asst, Administrative Asst, and Admin Assistant all convey the same information. So, given these constraints, you should not lock down the list, but you can at least try to prevent the endless variety of entries, by presenting the user with those common values, so that they can quickly choose one and move to the next field, without a second thought.
This is the premise of the example, and the following code provides a method to perform this type of action. There are two functions supplied in the excerpt: AddItem_Prompt and AddItem_ToTable. The prompting routine is only called once from the data addition procedure, but supposing that there are other procedures that called the prompt routine would be reason enough to separate it.
Private Function AddItem_Prompt( _
pstrItemType As String, pstrNewData As String) As Boolean
'Prompt the user to add a new value to the combo box
'strItemType - Type of Item to be added(Customer, Category, Product)
'varNewData - NewData from Combo Box
Dim strMsg As String
'Create a custom prompt for the type of data to be collected
strMsg = "'" & pstrNewData & "' is not in the list." & _
vbNewLine & vbNewLine & _
"Would you like to add it?"'Show the prompt, and get response
If vbYes = MsgBox(strMsg, vbYesNo + vbQuestion, _
"Unknown " & pstrItemType) Then
AddItem_Prompt = True
End If
End Function
Public Function AddItem_ToTable( _
pstrNewData As String, pstrTable As String, _
ParamArray pastrFields()) As Integer
'This routine will prompt the user to add data when an
'unknown value is entered into a Combo Box
'ENTRY:
' strItem = Unknown data entered by user
' strTable = Lookup Table
' strFields(0) = Prompt for Input Box
' strFields(1) = Field Name in table
'Call Example 1: Add 'FMS, Inc.' to Company Table
'AddItem("FMS, Inc.", "tblCompany", "Company Name", "CoName")
'Example 2: Add 'IS' & "Information Systems" to Department Table
'AddItem("IS", "tblDepartment", "Department Code", _
"DeptCode", "Department Name", "DeptName")
'Example 3: Add 'Clark' & User entered First Name and Middle Initial
to Customer Table
'AddItem("Clark", "tblCustomer", _
"Last Name", "LName", _
"First Name","Fname", _
"Middle Initial", "MI")
Dim strMsg As String
Dim i As Integer
Dim varVal As Variant
Dim rs As New ADODB.Recordset
'Begin by setting the default response to be to show the
'standard error message
AddItem_ToTable = acDataErrDisplay
'Prompt the user to add the new value
If AddItem_Prompt(CStr(pastrFields(0)), pstrNewData) Then
'Store the number of parameters passed in procedure call
intMax = UBound(pastrFields(), 1) + 1
'Create a recordset based on the tablename provided
'in the procedure call
If OpenADORecordset(rs, pstrTable, _
adLockOptimistic, , adCmdTable) Then
With rs
'Start an new record
.AddNew
'Write the data to the specified field
.Fields(pastrFields(1)) = pstrNewData
i = 2
'Repeat this process for each parameter passed
Do While i < intMax
'Prompt the user for any additional information
varVal = _
InputBox("Enter " & pastrFields(i), "Add Item to List")'Store the additional information
.Fields(pastrFields(i + 1)) = varVal
i = i + 2
Loop
'Save the new record
.Update
End With
AddItem_ToTable = acDataErrAdded
End If
End If
End FunctionThe explanation of this example starts with the return values that are assigned to the AddItem_ToTable procedure. Although it is declared as an integer, it is really an intrinsic constant value that is native to Access.
An intrinsic constant is simply a variable that is supplied natively with Access and assigned a permanent value. It is something that is easier to remember than a number like 1, 64, or 256. The following table lists the possibilities for the value.
Looking through the code, you will notice that only two of the intrinsic constants are used. The third, AcDataErrContinue, is not used. When the custom routine above finishes, either the error message will show, or the data will be added, and the data is refreshed. Now that you understand what these values mean, you need to know where the value is applied. To understand that, refer to the following code:
Private Sub cboTitle_NotInList(NewData As String, Response As Integer)
Response = AddItem_ToTable(NewData, "tblTitle", _
"Title", "Title")
End SubThe code above is the declaration of the NotInList event procedure for a combo box. It is important to examine the two parameters that are exposed to the developer. The two values are NewData and Response.
10.3.8.1. NewData
Remember that the object of this event procedure is to execute code when the value entered is not in the list. With this in mind, it makes sense that the NewData parameter is that unknown value. It is exposed to the developer for your convenience. Strangely enough, you can write to this parameter, but doing so will lose the unknown value.
10.3.8.2. Response
On the other hand, the Response parameter, which was covered in the table above, is used to instruct Access what action to take when the procedure terminates. In the code, you can see that the Response parameter is set to the value returned by the AddItem_ToTable function. If you review the code to this function, you will see that initially, the function is set to acDataErrDisplay. At the end of the function, when the function is successful, meaning that the unknown value was added to the data table, the parameter is set to acDataErrAdded.
10.3.8.3. ParamArray
Continuing with the exploration of the AddItem_ToTable function, notice that there is a procedure parameter that starts with the reserved word ParamArray. The ParamArray is an Array (a data storage mechanism in memory) that can accept any number of parameters. The Array is always set to the Variant datatype, and it is unlimited as to the number of values that it can receive. So, much like the OpenArgs parameter of the form, this is a way to pass anything you could possibly think of or ever need.
In the case of this particular procedure, the ParamArray is used to assist in the capture of even more data than just the one value that was entered in the combo box. Implementing this gives the ability to enter a second, third, or nth piece of information pertaining to the new data value. For example, suppose a new department code is added, you code-prompt the user for the new department name as well. If you capture only the code, then it would require for someone to return to the Department Table (or a form that displays the table's data) to update the Department Name. So, it is a luxury to this process, but quite useful. You should refrain from prompting for too many values, as the user really does want to finish the work at hand, not answer prompts all afternoon. After two prompts, it may be a better idea to require the opening of an entire data entry form to capture the information.
Included in the code is a call to a custom procedure called OpenADORecordset. This is a function that takes care of setting a connection string, issuing the appropriate lock, and then populating the recordset that is passed to it. Your code may not compile due to this procedure, so feel free to either replace it or create your own to fulfill the need.
The rest of the procedure is spent stepping through each of the parameter values, prompting for needed values, entering the value into the table in the accompanying field name, and then returning either a success or failure value to the calling procedure.
10.3.9. On Close(): Save a Setting to the Registry
In case you are unfamiliar with Windows, there is a very important database called the registry. In Windows 3.1, the registration database stored OLE registration information and file associations, while program settings were stored in initialization (.ini) files. With the introduction of Windows 95 and later versions, the registry became the central repository for all this information.
As you have read, the registry database is vital to the operation of all applications installed on your computer. Along with its everyday operation, you can also use it to store any information that you need to retain about your own application—for example, simple things like user preferences, state and/or status of scheduled routines, or even the location or sizes of screens.
Before proceeding, a little more background may be necessary to understand the need for storing information into the registry. In most cases, your custom Access Application resides in two separate databases, data.mdb and application.mdb. During the life of the application, users will request changes, which will require an update to the UI objects contained within it.
To perform the changes, you will want to make them on a development machine, without affecting any live data or existing functionality. Once the changes are completed, and tested thoroughly, you will need to deploy the new application version to all users. The easiest way to do this is to copy the new application.mdb to the user's machine. Here is where the registry is useful.
If there are any personalization settings stored within the application.mdb, perhaps in a local table, then there would need to be a mechanism to copy those values from the old version of the application to the new version. As you can imagine, it can be a hassle to perform, maintain, and control the execution of the process. So, to make a long story short, if there is some other place to store these settings that would not be overwritten with each update, then using it may save you some time, effort, and headaches.
To make it easy for you to create, retrieve, and delete values to the registry database, VBA offers the following functions:
When a setting is written to the registry, it is done in a directory structure that is similar to Windows Explorer. The base directory for all settings created by the CreateSetting function is
My ComputerHKEY_CURRENT_USERSoftwareVB and VBA Program Settings
Stored within this base folder, you create Application, Section, and Key names. Inside the Key names are the actual settings. So, when you execute the CreateSetting function, the rest of the registry setting is created like the following:
Application NameSection NameKey NameSetting Name
For example, the following statement would create a new value in the registry that contains the text "MySetting":
SaveSetting "MyApp", "MySection", "MyKey", "MySetting"
The following statement would be stored in the registry, and displayed as shown in Figure 10-4.
Figure 10.4. Figure 10-4
To summarize this example, a folder called "My App" is created under the "VB and VBA Program Settings" folder. A folder called "MySection" is created below the application name. Contained within "MySection" folder, the "MyKey" key holds the actual setting, which in this case is "MySetting".
For a real example, suppose that when users interact with a particular form, they are allowed to move and resize the form as they wish. When they reopen that form, it would be nice to be able to position it in the same place with the same size. This functionality can be implemented with the help of the registry. By storing the values when the form is closed, then the same values can be applied when the form is opened.
The following code is an example of saving the form attributes to the registry:
Private Sub Form_Close()
'Save user form preference to the Windows Registry Database
Const cstrAppName As String = "MyApplication"
Dim strSectionName As String
'Create a registry section based on the name of the Form
strSectionName = Me.Name
'Write Settings to the Windows Registry Database
SaveSetting cstrAppName, strSectionName, _
"WindowHeight", Me.WindowHeight
SaveSetting cstrAppName, strSectionName, _
"WindowLeft", Me.WindowLeft
SaveSetting cstrAppName, strSectionName, _
"WindowTop", Me.WindowTop
SaveSetting cstrAppName, strSectionName, _
"WindowWidth", Me.WindowWidth
End SubIn the following code example, a constant variable is created for use throughout the remainder of the procedure. If you prefer to use an application global value, that would work as well. Next, the form name is used to create a Section in the registry. This will facilitate finding the values later in code. Within each Section, the form's Height, Left, Top, and Width are written to the respective keys.
Private Sub Form_Open(Cancel As Integer)
'Reset the window to the saved settings
Const cstrAppName As String = "MyApplication"
Me.Move _
GetSetting(cstrAppName, .Name, "WindowLeft", 1000), _
GetSetting(cstrAppName, .Name, "WindowTop", 1000), _
GetSetting(cstrAppName, .Name, "WindowWidth", 3000), _
GetSetting(cstrAppName, .Name, "WindowHeight", 3000)
End SubFigure 10-5 displays the result of the registry after the user values are saved.
Figure 10.5. Figure 10-5
10.3.10. Creating Forms and Controls with VBA
Access has built-in functions for creating new form and control objects programmatically. These are the same functions that the wizards use to create new objects and work the same regardless of whether you are using an MDB or ADP file.
The CreateForm method creates a form and returns a Form object.
CreateForm([database[, formtemplate]])
The CreateForm method has the following arguments.
Add code similar to the Click event of a command button:
Private Sub cmdCreateNewForm_Click() Dim frmNewForm As Access.Form Set frmNewForm = CreateForm() 'Form is created in a minimized state. DoCmd.Restore ' Set caption, resize and reposition. frmNewForm.Caption = "My New Form" DoCmd.MoveSize 500, 500, 8000, 4000 ' Save new form DoCmd.Save acForm, frmNewForm.Name End Sub
The following is the CreateControl function:
CreateControl(formname, controltype[, section[, parent[, columnname[, left[, top[, width[, height]]]]]]] )
The CreateControl function to create controls has a few more arguments.
The following code can add a control to the above form:
Private Sub cmdCreateControl_Click()
Dim frmNewForm As Access.Form
Dim ctlNewControl As Access.Control
' Form1 is the default name of the form create earlier
Set frmNewForm = Application.Forms("form1")
' Create the new control
Set ctlNewControl = CreateControl(frmNewForm.Name, acTextBox, _
acDetail, , , frmNewForm.WindowLeft + 250, _
frmNewForm.WindowTop + 250, 1400, 500)
' Name it & save the form
ctlNewControl.Name = "txtNewTextbox"
DoCmd.Save acForm, frmNewForm.Name
End Sub10.3.11. Managing Multiple Form Instances
Handling multiple instances of the same form is not as straightforward as it might seem. The reason is that the default opening behavior of a form is all the same instance of the form. Understanding the concept of an instance is as simple as counting to 1. There is only one form, and every time that form is opened, it is the same form. The form opens and displays the data specified in the Recordsource property.
This limitation is not typically a problem for the average application, as a user is happy to view data in this manner. For example, opening the customer form, based on the Customers table or a query thereof, displays all customers in the data source. The customer can navigate to the desired customer, and perform any maintenance. Now, imagine that there is a data entry form for stock market ticker symbols and their daily closing prices. With only one form, comparing two or more stocks may be rather difficult. Performing a side-by-side comparison of the vital statistics of two or more stocks would be challenging when only one can be displayed at a time.
So, now that a need has been identified, the discussion turns to the execution of multiple instances of the form. (Just in case you are thinking about expanding this same concept for reports, please understand that it is not possible. The report preview window is controlled outside of Access and therefore there is nothing you can do to work around its limitations.) The way to create more instances of a form is to create an object variable based on the desired form. The following code example performs this step:
Private Sub CreateFormInstance() 'Declare the variable to be an existing form Dim frmTest1 As Form_Orders 'Set the variable to a new instance of the form Set frmTest1 = New Form_Orders 'Show the Form, as setting it does not make it visible End Sub
Notice that the visible property of the form must be explicitly set to True, because the form is not visible by default using this opening method. Something also to note is that using this method would cause the form to only blink across the screen momentarily when the code is run. This occurs because the scope of the variable is local to the procedure in which it is declared. To allow the form to be more persistent requires that the variable be declared in the general declarations section of a module. Variables defined in this Global section will stay in scope throughout the life of the application. The scope in the above procedure is only until the procedure ends, which causes the flicker, as the variable is destroyed at the End Sub statement.
The following code is an example of the creation of multiple instances of the same form that persist throughout the life of the application:
'Declare the first instance Dim frmTest1 As Form_Orders 'Declare the second instance Dim frmTest2 As Form_Orders Private Sub cmdOpenForms_Click() 'Create the first instance Set frmTest1 = New Form_Orders 'Create the second instance Set frmTest2 = New Form_Orders 'Make both instances visible frmTest1.Visible = True frmTest2.Visible = True End Sub
The above code declares the global variables, instantiates them, and makes them visible. Before you run off and try to program your next stock market application, you should understand that this example presents limited usefulness, because it requires knowing how many instances are required in advance of execution. This is also known as hard-coding, which does not bode well in dynamic situations. If the number of times that the form needs to open is unknown (that is, the number of stocks that you would like to compare at once), then the hard-coded solution may include too many instances or not enough.
The next step in the multi-instance ladder is maintaining a collection of form objects, created on demand by the user. To understand the concept of a collection, think about a stamp, marble, or baseball card collection. You have a binder for stamps, bag for marbles, and a shoebox for cards. A collection is the empty container, awaiting items to be placed within it. The following code demonstrates the creation of the collection, and the addition of members to it:
'Create the global collection Dim colTest As New Collection Private Sub cmdOpenForms_Click() 'Declare an object variable as an existing form Dim frmTest As Form_Orders
'Set the object to a new instance of the form Set frmTest = New Form_Orders 'Add the form instance to the collection colTest.Add frmTest frmTest.Visible = True End Sub
Each time the above code is executed a new instance of the form is added to the collection, and then the form is displayed to the user. This may accomplish your goal, but it is very important to understand that every instance that is created requires some amount of memory of the computer. If you continue to create instances, eventually all of the memory is allocated and strange but interesting error messages from Access and Windows are displayed. To avoid creating a form instance landfill inside the machine, proper housekeeping procedures must be implemented.
Housekeeping, with regard to VBA programming, is the proper destruction and reallocation of free memory throughout the life of the application. Just like in your home, if you open it, close it; if you get it dirty, clean it; if you turn it on, then turn it off. The same applies to created objects, because failing to close or destroy them can lead to memory leaks or other undesirable results.
So, if a form instance is closed by a user, then the memory allocated to the use of that object variable is still retained. The collection also maintains the form within its population as well. Therefore, the creator of the instance must make provisions to destroy the instance.
The ideal place to remove an object instance from the collection is within the On Close event procedure of the form instance. Just as the collection has an Add method, it also has a Remove method. It is used to destroy the instance from memory. In order to know which instance to destroy requires knowing where it is within the collection. Collection items can be referred to using an ordinal value, but it only relates to the sequential order in which the forms were instantiated. This is typically not useful when users are allowed to randomly open and close the instances (that is, user opens stock XYZ, ABC, and RRR. He or she decides against ABC and closes the form. It would be time then to destroy the ABC instance.)
To facilitate the specific reference of an instance, you may specify a unique index during the call to the Add method of the Collection object. The Key parameter is a string expression that can optionally be added for the direct reference to the instance within the collection. It is important to note that the supplied text value must be unique from any other Key value within the collection. Otherwise, destroying one instance would destroy all instances with the same name.
Specifying a unique string value presents an interesting challenge, as there needs to be some mechanism to create it. As each form opens, you need to determine something about that form instance that is unique from any other instance. In the case of the stock market example, the stock ticker symbol could be used. If motor vehicles are the basis of the form, then perhaps the VIN number could serve this purpose.
In the following example, multiple instances of an Order form are displayed. As most OrderID values are generated with an autonumber, this value is an obvious choice to use as unique index for the collection. To visually assist the user when multiple instances are opened, the caption can be modified to display the OrderID as well.
Public colOrders As New Collection Private Sub cmdShowOrder_Click() Dim frmOrder As Form_Orders Dim strOrderID As String 'Retrieve the desired OrderID strOrderID = Me!sfrmOrders.Form!txtOrderID 'Open a new Orders form Set frmOrder = New Form_Orders 'Set the form filter criteria frmOrder.Filter = "OrderID = " & strOrderID frmOrder.FilterOn = True 'Set the caption to reflect the unique id frmOrder.Caption = "Order: " & strOrderID 'Add the form instance to the Collection. ' Specify the Caption property for the 'Key' parameter ' The order number is used as the unique index colOrders.Add frmOrder, strOrderID 'Display the form instance frmOrder.Visible = True End Sub
The code example above creates the collection in the global declarations section. When the Show Order command button is clicked by the user, a new instance of the form is created in memory. On the subform is the desired OrderID to be displayed on the form. Therefore, when the form is opened, a filter is applied to show only that record. The caption is updated to visually enhance the user experience, by specifying the order that is being displayed. The instance is added to the collection, using the OrderID as the unique key index. Finally, the form instance is displayed. To better understand the use of this code, see Figure 10-6.
Figure 10-6 displays an order selection form. The user places the cursor on the desired record and then clicks the Show Orders button. Each time the button is clicked, a new instance of the form is opened.
The following code demonstrates the ability to destroy a specific instance of a form, within a collection, based on the unique Key value in the collection. For obvious reasons, the execution of this code is from the On Close event procedure of the form instance that is closing.
Private Sub Form_Close() 'Remove the instance of this form from the collection Form_dlgViewOrders.colOrders.Remove CStr(OrderID) End Sub
The above code example executes the Remove method of the colOrders collection. To specify the exact collection, the code module name must be supplied, so that there is no ambiguity as to which collection is to be used. In this case, the View Orders dialog box (dlgViewOrders) contains the collection. The Remove method is executed with the OrderID, converted to a string, and passed as the Key Index parameter.
Figure 10.6. Figure 10-6
