Potentially, additional coding needs to be done for multiuser programming. Again, it depends on how robust and powerful you want your application to be. What's meant by power in this case is the following:
How fast do you want the application to be?
How easy to use do you want it to be for users?
How complete do you want to make the error handling for multiuser situations?
Caution
This discussion isn't for the faint-hearted when it comes to code. But although these routines are somewhat complicated, most of them need to be created only once and then called from forms thereafter. One thing to remember is that this code is a great place to start; however, not every scenario that arises when using unbound forms can be covered in this single discussion.
This is one part of the book that doubles your pleasure. The following sections show the code displayed first by using DAO, and then ADO, so you can take advantage of using unbound forms with either object model. The section walking through the code is labeled appropriately.
The big part of coming up with useful routines is to make them as generic as possible so that you can use them again and again. Most of the code discussed in this chapter has been placed in a global module so that it can be created once and reused. You'll also want to simply copy and paste the form for the next one you want to create because the routines, as well as the command buttons needed, are the same. Figure 22.13 shows the sample form with the added command buttons.
DAO and ADO versions of the sample data have been created for your edification. A table is presented for each version, showing you where you can find the objects to examine. Both versions are on the accompanying CD-ROM in the ExamplesChap22 folder.
First, the overall example is discussed and the common parts shown. For this, the DAO example is used, with the common objects mentioned. Table 22.3 shows the parts for the DAO example.
Object Name | Type | Location |
---|---|---|
tblEmployee | Table | Chap22BE(DAO).mdb |
qryEmployee | Query | Chap22(DAO).mdb |
qryEmployeeValueLocate | Query | Chap22(DAO).mdb |
frmEmployee(DAO) | Form | Chap22(DAO).mdb |
modCommonRoutines | Module | Chap22(DAO).mdb |
modUnboundForms(DAO) | Module | Chap22(DAO).mdb |
Note
Features will be mentioned as each piece is used. Some features have been purposefully left out because they would complicate the code; they can be added in the future as you get comfortable with the code.
The DAO version of the sample application uses a linked table for tblEmployee, whereas the ADO version creates a connection to the back-end database. The code for the link is located in the form Open event and is shown in Listing 22.1.
Normally, the code for linking tables is used in the startup routines for the application.
The following steps are presented and intermixed with the pieces for that part of the example for setting up a form for use as an unbound form. The steps for using the completed pieces are laid out at the end of the section as a checklist for dealing with unbound forms.
1. |
Create the form by first binding it to a recordset. In this case, the recordset is named qryEmployee and is assigned as the form's record source. Figure 22.14 shows the query in Design view. Figure 22.14. Each field is laid out in the qryEmployee query. |
Tip
Laying out each field individually in the query comes in handy later when the various routines are comparing the controls on the form to the fields in the query.
2. | |
3. |
Create the command buttons necessary to control adding, editing, and saving information. (The code for this information is discussed shortly.) |
4. |
After the form is set up with the field information, unbind the form by deleting the ControlSource properties on the fields, and then deleting the RecordSource property for the form itself. Figure 22.15 shows the unbound Employee form and the property sheet for the LastName field. Figure 22.15. Although the ControlSource properties of the controls are cleared, the Name properties remain. |
Note
In addition to removing the ControlSource on each control, make sure that the record selectors and navigation buttons are turned off to control how users get to the various records.
5. |
Place the name of the RecordSource with the name of the key field for the form into the Tag property of the Employee form (see Figure 22.16). Figure 22.16. Notice the RecordSource and key field placed in the Tag property of the Employee form. |
Now that the form itself is created and the objects are defined, look at the code attached to the command buttons.
Before going over the specific tasks, look at a couple of support routines that help retrieve the RecordSource and key field name from the form's Tag property. These two routines and most of the rest of the functions are located in the modCommonRoutines standard module.
Note
The routines listed here are the same in both DAO and ADO versions of the sample application.
The first function returns the RecordSource name and is named ap_GetRecordSource() (see Listing 22.2). The current form—in this case, the frmEmployee(DAO) form—is passed to both functions to work with.
The next function, ap_GetKey(), retrieves the key field name (see Listing 22.3).
Function ap_GetKey(frmCurr As Form) As Variant On Error Resume Next '-- Returns the Key Field stored in the Tag property of the form ap_GetKey = Mid$(frmCurr.Tag, InStr(frmCurr.Tag, ";") + 1) End Function |
This is where the two versions of the sample code vary somewhat. Each version has had the DAO and ADO initials added to the calls that might be different for the version. If you're interested only in the ADO version, skip to the section “Setting Up the ADO Version of the Sample Application.”
The first task to examine is adding a new record. See how to do this by using DAO.
Listing 22.4 shows the event procedure that's attached to the cmdNew command button's OnClick event.
Private Sub cmdNew_Click() Dim flgFormSaved As Boolean flgFormSaved = True If pflgFormEdited Then flgFormSaved = ap_DAOSaveRecord(Me, True) End If If flgFormSaved Then ap_DAONewRecord Me, False End If End Sub |
First, the cmdNew_Click routine calls the ap_DAOSaveRecord routine to save the current record, if it has been edited. pflgFormEdited is a public variable declared in the modUnboundForm(DAO) module.
When adding a record on the unbound form, you're actually setting the controls to either the default value of the control on the form or to null.
Tip
You could change this code to default to DefaultValue in the corresponding recordset field value. It depends on whether you want the design changes to the table reflected directly.
The function used for this, aptly named ap_DAONewRecord(), takes two arguments: the current form being called from and another named flgDisable. flgDisable tells ap_DAONewRecord() whether it's being called simply to create a new record, or to clear the values of the controls and disable the controls to search for a record to edit. This feature is discussed later in the section “Locating and Editing a Record on the Unbound Form.”
Listing 22.5 shows the actual code for ap_DAONewRecord, which is called from the cmdNew command button.
The code is pretty well documented, so I'll discuss only the major points. The main task of the routine is to go through each control on the form, performed with the following code lines:
'–– For each of the controls on the form For Each ctlCurrent In frmCurr
The next task is to check whether the current control name can be found in the form's recordset:
'-- If an error occurs, handle it On Error Resume Next '-- Attempts to match a field in the recordset up with the '-- current control from the form. pvarDummy = qdfCurr.Fields(ctlCurr.Name).Name '-- If no error, that means the field exists If Err = 0 Then
As you can see from this code, the idea is to be as generic as possible. This routine also handles the following details:
If the flgDisable flag is true and the control's Tag field contains the value LookUp, the field is enabled for searching for a record. (This is covered in greater detail in the next section.)
The opposite is true if the flgDisable flag is false and the Tag property has the value DisplayOnly. This handles those controls that you want only to display the information.
Various DefaultValue possibilities are covered as well, whether it's a function (=Date()), string (WA), or value (5).
This routine sets the focus on the first control in the tab index order.
The next routines are the functions that take care of locating and loading an existing record, starting with ap_DAOOpenWithChoice(). Figure 22.17 displays the call for this function attached to the OnClick event of the cmdOpen command button. Listing 22.6 shows the code for the ap_DAOOpenWithChoice() function.
This function calls the save routine, if applicable. Then, if told to continue, it calls ap_DAONewRecord to clear the values of the controls on the form. Finally, it locates the first field with the text LookUp in the Tag property and sets the focus to that field (see Figure 22.18).
The next part to look at is the function that's called when the lookup field is updated with a value to locate. This function, named ap_DAOLookUpAfterUpdate(), is located in the modUnboundForms(DAO) module (see Listing 22.7).
Function ap_DAOLookUpAfterUpdate(frmCurr As Form) If pflgPerformLookup Then ap_DAOValueStandardSearch frmCurr Else ap_FormEdited End If End Function |
This function is called from the AfterUpdate event of the control that's used to look up the record and has the text LookUp in the Tag property. You might notice that the global variable pflgPerformLookup was set previously in the function ap_DAOOpenWithChoice(), shown earlier in Listing 22.6.
Before going into detail on the ap_DAOValueStandardSearch routine, notice the call to ap_FormEdited() in the modCommonRoutines module. This call is actually attached to all the other AfterUpdate events to flag the form as having been edited. Here's the code for this function:
Function ap_FormEdited() '–– Sets the form edited flag to True pflgFormEdited = True End Function
You've seen the pflgFormEdited variable used in other routines to test whether the form has been edited.
Note
This example is using only LastName as a value to search for a record to open. In the real world, you should use a more unique value for this task.
The next routine to look at is also called from LastName_AfterUpdate. Named ap_DAOValueStandardSearch, it locates the value of the lookup field (see Listing 22.8).
One potentially confusing piece of this routine is the following lines:
'–– Set a querydef to the query created for finding the value Set qdfSearch = dbLocal.QueryDefs(ap_GetRecordSource(frmCurrForm) _ & "ValueLocate")
These code lines combine the record source for the form, retrieved by the ap_GetRecordSource() function, with the literal ValueToLocate. A QueryDef variable, qdfSearch, is set to reference the saved query named qryEmployeeValueLocate.
Note
The qryEmployeeValueLocate query was created by taking the qryEmployee query and copying it, adding ValueLocate to the original name, and then adding the parameter mentioned in the next paragraph.
The next step is to set the parameter of the query by combining the name of the active control with the literal ToLocate. In this case, the parameter LastNameToLocate is set to the LastName control value:
'–– Set the parameter to the current value qdfSearch.PARAMETERS(Screen.ActiveControl.Name & "ToLocate") _ = Screen.ActiveControl.Value
Figure 22.19 shows the qryEmployeeValueLocate query.
If a record is found for the name, the values are loaded into the control on the form. The lines that call the routine are as follows:
'–– If found, load up the controls ap_DAOLoadRecord dynSearch, frmCurr
The recordset that contains the record found and the current form are passed to ap_DAOLoadRecord as arguments. Listing 22.9 shows the code for ap_DAOLoadRecord.
When the record loads, you see the information that was in the record, as in Figure 22.20.
The code listed for ap_DAOLoadRecord is pretty straightforward in that it just whips through each control on the form, as in the other routines discussed already.
The last task to look at when working with unbound forms is saving an updated record. The name of the function to perform this is ap_DAOSaveRecord(). The code is listed in its entirety (see Listing 22.10) and then broken up to explain individual sections.
This routine takes the following major actions:
1. |
If the argument flgAskSave is yes, users are prompted with a message box for saving changes to the information (see Figure 22.21). Figure 22.21. The routine prompts the user about saving changes.The code lines that perform this are as follows: '-- If necessary to request whether the user wants to save ' the information, do so. If flgAskSave Then Beep flgPerformSave = _ MsgBox("Would you like to save the information on '" & _ frmCurr.Caption & "'?", 36, "Save Information?") = vbYes Else flgPerformSave = True End If |
2. |
The next section checks to see whether a new record is being added or a current record is being updated, and acts accordingly: '-- If adding a record, perform the addnew command. If frmCurr!AddRec = -1 Then dynCurr.AddNew Else '-- If editing, find the record to update and perform ' the edit method dynCurr.FindFirst "[" & strKeyName & "] = " & frmCurr(strKeyName) If dynCurr.NoMatch Then Beep MsgBox "An error has occurred locating the current record!", _ vbCritical, "Locating Error" Exit Function End If dynCurr.Edit End If |
3. |
The last main section whips through all the controls on the form. If the control is in the recordset, the routine checks to see whether it's an AutoIncrement field. If it is, the routine skips the control; otherwise, it checks to see whether the value has changed and handles it accordingly: '-- If the current control is a keyfield and a counter, ' don't mess with it. If ctlCurr.Name = strKeyName Then If dynCurr(ctlCurr.Name).Attributes And DB_AUTOINCRFIELD Then flgCheckField = False End If End If If flgCheckField Then '-- If data has changed, then update it. If IsNull(dynCurr(ctlCurr.Name).Value) And Not _ IsNull(ctlCurr.Value) Then dynCurr(ctlCurr.Name).Value = ctlCurr.Value ElseIf Not IsNull(dynCurr(ctlCurr.Name).Value) And _ IsNull(ctlCurr.Value) Then dynCurr(ctlCurr.Name).Value = ctlCurr.Value ElseIf dynCurr(ctlCurr.Name).Value <> ctlCurr.Value Then dynCurr(ctlCurr.Name).Value = ctlCurr.Value End If End If |
Again, after these routines are created, you only have to cut and paste to new forms. The following steps do just that:
1. |
Copy the frmEmployee(DAO) form to your new form. |
2. |
Delete all the controls that refer to fields from the new form, leaving the controls on the top of the form. |
Note
Leave the AddRec and EmployeeKey fields in place for now.
3. |
Place the name of the record source in the RecordSource property of the form. |
4. | |
5. |
After the fields are set up the way you want them, delete the control source of each field. |
6. |
Highlight all the fields that aren't lookup and display only. Then type =ap_FormEdited() in the AfterUpdate event of the multiple selection. |
7. |
Highlight all the fields and set the Enable property to false. |
8. |
In the field on which you want to perform a lookup, place the word LookUp in the Tag property. Then place the command =ap_DAOLookUpAfterUpdate([Form]) in the AfterUpdate event of the field. |
9. |
Create the RecordSource and Locate queries, as described earlier in the section “Locating and Editing a Record on the Unbound Form.” |
10. |
Place the RecordSource name, along with the key field name, in the Tag property of the new form. |
11. |
That's all there is to it. After you do it a couple of times, it should take about only five minutes to create new forms.
To get started with the ADO-specific version of the sample application, it would be good to once again lay out all the objects that will be used (see Table 22.4).
Object Name | Type | Location |
---|---|---|
tblEmployee | Table | Chap22BE(ADO).mdb |
qryEmployee | Query | Chap22BE(ADO).mdb |
qryEmployeeValueLocate | Query | Chap22BE(ADO).mdb |
frmEmployee(ADO) | Form | Chap22(ADO).mdb |
modCommonRoutines | Module | Chap22(ADO).mdb |
modUnboundForms(ADO) | Module | Chap22(ADO).mdb |
Note
A big difference between the DAO and ADO examples is that ADO example also has the queries—pulled out into the back end—show how they could be used and could later be pulled into a ADP with the same code base.
The frmEmployee(ADO) form, although named differently from its DAO counterpart, is the same in its setup and calling of routines. The main differences are that it adds the ADO to each of its calls and doesn't include the linking code found in the form open event of frmEmployee(DAO).
The common routines (modCommonRoutines), found in both databases, are the same in each code base.
As with the DAO version (refer to Listing 22.2), the code in the cmdNew_Click event first offers to save the current record (if required), and then clears the form by using ap_ADONewRecord (see Listing 22.11).
The first major new step requiring ADO is in the first section of code. Here, the Connect and Recordset objects are declared and assigned:
Dim strConnect As String Dim cnnBE As New ADODB.Connection Dim rstCurr As New ADODB.Recordset Dim ctlCurrent As Control Dim strFirstControl As String On Error GoTo Error_ap_ADONewRecord '-- Open the ADO connection strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" strConnect = strConnect & CurrentProject.Path & "Chap22BE(ADO).mdb;" cnnBE.Open strConnect rstCurr.Open "Select * From " & ap_GetRecordSource(frmCurr) & _ " Where False;", cnnBE, adOpenStatic
Tip
Unlike ADO, there's no real clean way to examine a record's fields without opening it up. In DAO, the QueryDef object would be opened and the Fields collection examined. In ADO, one trick—as performed here—is to open the recordset as false. That way, the engine returns no records fairly quickly and you can crack it open to see the Fields collection.
The rest of the routine follows the DAO example.
For the code in locating a record, the change comes in the ap_ADOValueStandardSearch routine because it opens the qryEmployeeValueLocate query and performs a search for the value given.
Notice in Listing 22.12 that two ADO object models are used: ADODB and ADOX.
Again, most changes take place in the beginning of the routine. This code reflects the ADO changes:
Dim strConnect As String Dim cnnBE As New ADODB.Connection Dim catCurr As New ADOX.Catalog Dim cmdCurr As New ADODB.Command Dim rstSearch As New ADODB.Recordset Dim strCriteria As String On Error GoTo Error_ap_ADOValueStandardSearch DoCmd.Hourglass True '-- Open the ADO connection strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" strConnect = strConnect & CurrentProject.Path & "Chap22BE(ADO).mdb;" cnnBE.Open strConnect catCurr.ActiveConnection = cnnBE Set cmdCurr = catCurr.Procedures(ap_GetRecordSource(frmCurr) & _ "ValueLocate").Command cmdCurr.Parameters(Screen.ActiveControl.Name & "ToLocate") = _ Screen.ActiveControl.Value '-- Open the recordset rstSearch.Open cmdCurr, , adOpenForwardOnly, adLockReadOnly, _ adCmdStoredProc
The biggest trick to using ADO recordsets is getting the options correct for the Open command, as seen in the last line of code.
Loading the record requires the same code for ADO and DAO. The name has changed to ap_ADOLoadRecord, but is basically identical.
The final task to compare and look at is saving the record. ap_ADOSaveRecord performs this task (see Listing 22.13).
Although this has the same basic flow as the DAO version, the big thing to notice here is that for ADO you don't need to use the Edit method—in fact, there is none.
That's it for the unbound forms. Pick which flavor you want to use—DAO or ADO—and then dive into the corresponding sample application. Now, look at how to perform error handling for multiuser situations.
18.188.216.249