WORKING IN VBA WITH UNBOUND FORMS

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.

Creating the Routines for Handling Unbound Forms

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.

Figure 22.13. The Employee Master form in Chap22(DAO). mdb is a good example of unbound form techniques.


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.

Table 22.3. Objects Used in the DAO Unbound Form 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.

Listing 22.1. Chap22(DAO).mdb: Linking the Table When Opening the Form
Private Sub Form_Open(Cancel As Integer)

    Dim dbLocal As Database
    Dim tdfLink As TableDef

    Set dbLocal = CurrentDb()

    '-- Make sure there is a link. Normally would have been
    '-- done at startup

    '-- Turn off error handling to delete the current link
    On Error Resume Next

    dbLocal.TableDefs.Delete "tblEmployee"

    '-- Turn error handling it back on.
    On Error GoTo Err_Form_Open

    Set tdfLink = dbLocal.CreateTableDef("tblEmployee")
    tdfLink.SourceTableName = "tblEmployee"
    tdfLink.Connect = ";DATABASE=" & CurrentProject.Path & _
                            "Chap22BE(DAO).mdb"

    dbLocal.TableDefs.Append tdfLink

    pflgFormEdited = False

    Exit Sub

Err_Form_Open:
    MsgBox Err.Description & " Closing Form!", vbCritical

    Cancel = True

    Exit Sub

End Sub

Normally, the code for linking tables is used in the startup routines for the application.

Walking Through the Steps of Using the Sample Form

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.
Place each field on the frmEmployee(DAO) form from the field list, just as you would create any other form.

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.

Using Support Routines

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.

Listing 22.2. Chap22(DAO).mdb: Getting the Record Source for the Form
Function ap_GetRecordSource(frmCurr As Form) As Variant

   On Error GoTo Error_ap_GetRecordSource

   '-- Returns the name of the record source stored in the
   '   Tag property of the form
   If InStr(frmCurr.Tag, ";") <> 0 Then

     ap_GetRecordSource = Left$(frmCurr.Tag, InStr(frmCurr.Tag, ";") - 1)

   Else

     ap_GetRecordSource = frmCurr.Tag

   End If

  Exit Function

Error_ap_GetRecordSource:

   MsgBox Err.Description
   Exit Function

End Function

The next function, ap_GetKey(), retrieves the key field name (see Listing 22.3).

Listing 22.3. Chap22.mdb: Getting the Key Field from the Form's Tag Property
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.”

Working with the DAO Version

The first task to examine is adding a new record. See how to do this by using DAO.

Adding a Record on the Unbound Form

Listing 22.4 shows the event procedure that's attached to the cmdNew command button's OnClick event.

Listing 22.4. Chap22(DAO).mdb: Saving the Current Information
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.

Listing 22.5. Chap22(DAO).mdb: Clearing the Form to Create a New Record
Function ap_DAONewRecord(frmCurr As Form, flgDisable As Boolean)

   Dim dbLocal As Database, qdfCurr As QueryDef
   Dim ctlCurr As Control, strFirstControl As String

   On Error GoTo Error_ap_DAONewRecord

   Set dbLocal = CurrentDb

   '-- Open the intended recordset for this form
   Set qdfCurr = dbLocal.QueryDefs(ap_GetRecordSource(frmCurr))

'-- This flag is used at the end to place the cursor at the first control
   strFirstControl = ""

   '-- For each of the controls on the form
   For Each ctlCurr In frmCurr
      '-- 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

         '-- If the current control is first in the index, record
         If ctlCurr.TabIndex = 0 Then
            strFirstControl = ctlCurr.Name
         End If

         On Error GoTo Error_ap_DAONewRecord

         '-- If the argument flgDisable is set to true then
         '-- After setting the fields, un-enable the field
         '-- unless it is a Lookup field as assigned to the
         '-- Tag property of the control.

         If flgDisable Then

            If IsNull(ctlCurr.Tag) Or ctlCurr.Tag <> "LookUp" Then
               ctlCurr.Enabled = False
            ElseIf ctlCurr.Tag = "LookUp" Then

               ctlCurr.Enabled = True
            End If

            ctlCurr.Value = Null

         Else '-- Otherwise enable it unless told to DisplayOnly in the
              '-- Tag property of the control.

            If ctlCurr.Tag <> "DisplayOnly" Then
               ctlCurr.Enabled = True
            ElseIf IsNull(ctlCurr.Tag) Then
               ctlCurr.Enabled = True
            End If

            '-- Handle the default value assigned to the control.
            If IsNull(ctlCurr.DefaultValue) Then
               ctlCurr.Value = Null
            Else
               '-- Default value is a function.
               If Left$(ctlCurr.DefaultValue, 1) = "=" Then

                  ctlCurr.Value = Eval(Mid$(ctlCurr.DefaultValue, 2))
               Else

                  '-- Default value is a string.
                  If InStr(ctlCurr.DefaultValue, """") <> 0 Then

                     ctlCurr.Value = Eval(ctlCurr.DefaultValue)

                  Else '-- Default value any other type'
                     ctlCurr.Value = ctlCurr.DefaultValue
                  End If
               End If
            End If
         End If
      End If
      Err = 0
   Next

   '-- Null out the key value for the form.
   frmCurr(ap_GetKey(frmCurr)) = Null

   '-- Set the flag for adding a new record on the form.
   frmCurr!AddRec = -1

   qdfCurr.Close

   '-- Set the flag for allowing to look up based on the disabled flag.
   '-- True means opening current record, False means new record
   pflgPerformLookup = flgDisable

   '-- Go to the first control in the Tab Index.
   If Len(strFirstControl) > 0 And Not flgDisable Then
     frmCurr(strFirstControl).SetFocus
   End If

   Exit Function

Error_ap_DAONewRecord:

   MsgBox Err.Description
   Exit Function

End Function

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.

Locating and Editing a Record on the Unbound Form

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.

Figure 22.17. ap_DAOOpenWith Choice() is in the modUnboundForms (DAO) module.


Listing 22.6. Chap22(DAO).mdb: Giving the User a Choice of Lookup Fields
Function ap_DAOOpenWithChoice(frmCurr As Form)

   Dim ctlCurr As Control
   Dim flgFormSaved As Integer

   On Error GoTo Error_ap_DAOOpenWithChoice

   '-- If the form has been edited, call the routine to save it.
   flgFormSaved = True

   If pflgFormEdited Then
      flgFormSaved = ap_DAOSaveRecord(frmCurr, True)
   End If

   '-- If the save was successful, clear the controls on the form
   '-- then set the focus to the first lookup field.
   If flgFormSaved Then

      pflgPerformLookup = True

      pvarDummy = ap_DAONewRecord(frmCurr, True)

      For Each ctlCurr In frmCurr
         If ctlCurr.Tag = "LookUp" Then
            ctlCurr.SetFocus
            Exit For
         End If
      Next

   End If
   Exit Function

Error_ap_DAOOpenWithChoice:

   MsgBox Err.Description
   Exit Function

End 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).

Figure 22.18. The LastName field, by having the word LookUp in its Tag property, has informed ap_DAOOpenWithChoice() to use it.


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).

Listing 22.7. Chap22(DAO).mdb: Calling the Routine to Search for the Value
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).

Listing 22.8. Chap22(DAO).mdb: Looking Up a Value Entered
Sub ap_DAOValueStandardSearch(frmCurr As Form)

     Dim dbLocal As Database
     Dim qdfSearch As QueryDef
     Dim dynSearch As Recordset

     On Error GoTo Error_ap_DAOValueStandardSearch

     DoCmd.Hourglass True

     Set dbLocal = CurrentDb()
     '-- Set a querydef to the query created for finding the value
     Set qdfSearch = dbLocal.QueryDefs(ap_GetRecordSource(frmCurr) _
        & "ValueLocate")
     '-- Set the parameter to the current value
     qdfSearch.PARAMETERS(Screen.ActiveControl.Name & "ToLocate") _
        = Screen.ActiveControl.Value

     '-- Open the recordset
     Set dynSearch = qdfSearch.OpenRecordset()

     DoCmd.Hourglass False

     If dynSearch.RecordCount = 0 Then
        MsgBox "This " & Screen.ActiveControl.Name & " was not found!", _
           vbCritical, "Search Error"
     Else

        '-- If found, load up the controls
        ap_DAOLoadRecord dynSearch, frmCurr
     End If

     Exit Sub

Error_ap_DAOValueStandardSearch:

   DoCmd.Hourglass False

   MsgBox Err.Description

   Exit Sub

End Sub

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.

Figure 22.19. Notice the parameter LastNameToLocate in 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.

Listing 22.9. Chap22(DAO).mdb: Loading Existing Information
Sub ap_DAOLoadRecord(dynCurr As Recordset, frmCurr As Form)

   Dim ctlCurr As Control

   On Error GoTo Error_ap_DAOLoadRecord

   '-- Turn the lookup feature off when a record is first loaded
   pflgPerformLookup = False

   '-- For all the controls on the form
   For Each ctlCurr In frmCurr

      '-- If an error occurs, handle it
      On Error Resume Next

      '-- Since either data is now present, or new record allow editing
      ctlCurr.Enabled = True

      '-- Attempts to match a field in the recordset up with the
      '-- current control from the form.
      pvarDummy = dynCurr.Fields(ctlCurr.Name).Name

      '-- If no error, that means the field exists
      If Err = 0 Then

        On Error GoTo Error_ap_DAOLoadRecord

        '-- Store the recordset value into the form control
        ctlCurr.Value = dynCurr(ctlCurr.Name).Value

      End If
      Err = 0
   Next

   '-- Since a record has been loaded, we are not adding a record.
   frmCurr!AddRec = 0

   Exit Sub

Error_ap_DAOLoadRecord:

   MsgBox Err.Description
   Exit Sub

End Sub

When the record loads, you see the information that was in the record, as in Figure 22.20.

Figure 22.20. The information is loaded and ready for editing.


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.

Saving a Record on the Unbound Form

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.

Listing 22.10. Chap22(DAO).mdb: Saving the Current Record
Function ap_DAOSaveRecord(frmCurr As Form, flgAskSave As Boolean) _
  As Integer

  Dim ctlCurr As Control
  Dim flgPerformSave As Integer
  Dim flgCheckField As Integer
  Dim strKeyName As String

  On Error GoTo Error_ap_DAOSaveRecord

  '-- 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
  If flgPerformSave Then

     Dim dbLocal As Database, dynCurr As Recordset

     Set dbLocal = CurrentDb()

     '-- Create a recordset off the form's record source
     Set dynCurr = dbLocal.OpenRecordset(ap_GetRecordSource(frmCurr))

     On Error Resume Next

     '-- Get the name of the key field
     strKeyName = ap_GetKey(frmCurr)

     '-- 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

     For Each ctlCurr In frmCurr

       pvarDummy = dynCurr(ctlCurr.Name).Name

       If Err = 0 Then

          flgCheckField = True

          '-- 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

       End If
       Err = 0
     Next

     On Error GoTo Error_ap_DAOSaveRecord

     '-- Grab the keyfield value from the recordset
     frmCurr(strKeyName) = dynCurr(strKeyName)

     '-- Update the recordset
     dynCurr.UPDATE

     '-- Close the recordset and commit the transaction
     dynCurr.Close

     ap_DAOSaveRecord = True
  Else
     ap_DAOSaveRecord = True
  End If

  '-- set the flag that the form is edited to false.
  pflgFormEdited = False
  frmCurr!AddRec = 0

  Exit Function
Error_ap_DAOSaveRecord:

   MsgBox Err.Description
   Exit Function

End Function

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.
Place the fields from the field list where you want them on the form.

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.
Find the EmployeeKey field and change it to the key value for this form. It will be the unique value, such as an AutoIncrement field.

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.

Setting Up the ADO Version of the Sample Application

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).

Table 22.4. Objects Used in the ADO Unbound Form Example
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.

Adding a Record on the Unbound Form

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).

Listing 22.11. Chap22(ADO).mdb: Clearing the Form to Create a New Record with ADO
Function ap_ADONewRecord(frmCurr As Form, flgDisable As Boolean)

   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
   '-- This flag is used at the end to place the cursor at the
   '   first control
   strFirstControl = ""

   '-- For each of the controls on the form
   For Each ctlCurrent In frmCurr

     '-- 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 = rstCurr.Fields(ctlCurrent.Name).Name

     '-- If no error, that means the field
     If Err = 0 Then

       '-- If the current control is first in the index, record
       If ctlCurrent.TabIndex = 0 Then
          strFirstControl = ctlCurrent.Name
       End If

       On Error GoTo Error_ap_ADONewRecord

       '-- If the argument flgDisable is set to true then
       '-- After setting the fields, un-enable the field
       '-- unless it is a Lookup field as assigned to the
       '-- Tag property of the control.

       If flgDisable Then

          If IsNull(ctlCurrent.Tag) Or ctlCurrent.Tag <> "LookUp" Then
             ctlCurrent.Enabled = False
          ElseIf ctlCurrent.Tag = "LookUp" Then

             ctlCurrent.Enabled = True
          End If

          ctlCurrent.Value = Null

       Else '-- Otherwise enable it unless told to DisplayOnly in the
            '-- Tag property of the control.

          If ctlCurrent.Tag <> "DisplayOnly" Then
             ctlCurrent.Enabled = True
          ElseIf IsNull(ctlCurrent.Tag) Then
             ctlCurrent.Enabled = True
          End If

          '-- Handle the default value assigned to the control.
          If IsNull(ctlCurrent.DefaultValue) Then
             ctlCurrent.Value = Null
          Else

             '-- Default value is a function.
             If Left$(ctlCurrent.DefaultValue, 1) = "=" Then

                ctlCurrent.Value = Eval(Mid$(ctlCurrent.DefaultValue, 2))
             Else

                '-- Default value is a string.
                If InStr(ctlCurrent.DefaultValue, """") <> 0 Then

                   ctlCurrent.Value = Eval(ctlCurrent.DefaultValue)

                Else '-- Default value any other type'
                   ctlCurrent.Value = ctlCurrent.DefaultValue
                End If
             End If
          End If
       End If
     End If
     Err = 0
   Next

   '-- Null out the key value for the form.
   frmCurr(ap_GetKey(frmCurr)) = Null

   '-- Set the flag for adding a new record on the form.
   frmCurr!AddRec = -1

   '-- Set the flag for allowing to look up based on the disabled flag.
   '-- True means opening current record, False means new record
   pflgPerformLookup = flgDisable

   '-- Go to the first control in the Tab Index.
   If Len(strFirstControl) > 0 And Not flgDisable Then
     frmCurr(strFirstControl).SetFocus
   End If
   Exit Function

Error_ap_ADONewRecord:

   MsgBox Err.Description
   Exit Function

End Function

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.

Locating and Loading a Record with the Unbound Form

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.

Listing 22.12. Chap22(ADO).mdb: Opening and Executing a Query with ADO
Sub ap_ADOValueStandardSearch(frmCurr As Form)

    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

    DoCmd.Hourglass False

    If rstSearch.EOF Then
       MsgBox "This " & Screen.ActiveControl.Name & " was not found!", _
          vbCritical, "Search Error"

    Else

       '-- If found, load up the controls
       ap_ADOLoadRecord rstSearch, frmCurr

    End If

    Exit Sub

Error_ap_ADOValueStandardSearch:

   DoCmd.Hourglass False

   MsgBox Err.Description

   Exit Sub

End Sub

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.

Saving a Record on the Unbound Form

The final task to compare and look at is saving the record. ap_ADOSaveRecord performs this task (see Listing 22.13).

Listing 22.13. Chap22(ADO).mdb: Saving a Record with ADO
Function ap_ADOSaveRecord(frmCurr As Form, flgAskSave As Boolean) _
  As Integer

  Dim ctlCurrent As Control
  Dim flgPerformSave As Integer
  Dim flgCheckField As Integer
  Dim strKeyName As String

  On Error GoTo Error_ap_ADOSaveRecord

  '-- 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

  If flgPerformSave Then
    Dim strConnect As String
    Dim cnnBE As New ADODB.Connection
    Dim rstCurr As New ADODB.Recordset
    Dim strCriteria As String

    '-- Open the ADO connection
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    strConnect = strConnect & CurrentProject.Path & "Chap22BE(ADO).mdb;"
    cnnBE.Open strConnect

    '-- Open the ADO recordset.
    rstCurr.Open ap_GetRecordSource(frmCurr), cnnBE, adOpenKeyset, _
          adLockOptimistic, adCmdTable

    On Error Resume Next

    '-- Get the name of the key field
    strKeyName = ap_GetKey(frmCurr)

    '-- If adding a record, perform the addnew command.
    If frmCurr!AddRec = -1 Then

       rstCurr.AddNew

    Else

       '-- If editing, find the record to update and perform
       '   the edit method
       strCriteria = "[" & strKeyName & "] = " & _
              frmCurr(strKeyName)

       rstCurr.Find strCriteria

       If rstCurr.BOF Then
          Beep
          MsgBox "An error has occurred locating the current record!", _
             vbCritical, "Locating Error"
          Exit Function
       End If

    End If

    For Each ctlCurrent In frmCurr

      pvarDummy = rstCurr(ctlCurrent.Name).Name
      If Err = 0 Then

        flgCheckField = True

        '-- If the current control is a keyfield and a counter,
        '   don't mess with it.
        If ctlCurrent.Name = strKeyName Then

          If rstCurr.Fields(ctlCurrent.Name).Properties("AutoIncrement") _
             = True Then
                flgCheckField = False

          End If
        End If

        If flgCheckField Then
          '-- If data has changed, then update it.
          If IsNull(rstCurr(ctlCurrent.Name).Value) And Not _
           IsNull(ctlCurrent.Value) Then
             rstCurr(ctlCurrent.Name).Value = ctlCurrent.Value
          ElseIf Not IsNull(rstCurr(ctlCurrent.Name).Value) And _
           IsNull(ctlCurrent.Value) Then
             rstCurr(ctlCurrent.Name).Value = ctlCurrent.Value
          ElseIf rstCurr(ctlCurrent.Name).Value <> ctlCurrent.Value Then
             rstCurr(ctlCurrent.Name).Value = ctlCurrent.Value
          End If
        End If

      End If

      Err = 0

    Next

    On Error GoTo Error_ap_ADOSaveRecord

    '-- Grab the keyfield value from the recordset
    frmCurr(strKeyName) = rstCurr(strKeyName)

    '-- Update the recordset
    rstCurr.Update

    '-- Close the recordset and commit the transaction
    rstCurr.Close
    ap_ADOSaveRecord = True

  Else

     ap_ADOSaveRecord = True

  End If

  '-- set the flag that the form is edited to false.
  pflgFormEdited = False
  frmCurr!AddRec = 0

  Exit Function

Error_ap_ADOSaveRecord:

  MsgBox Err.Description
  rstCurr.CancelUpdate

  Resume 'Exit Function

End Function

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.188.216.249