17.4. Using Unbound Forms

Sometimes, the only way to get the form data to behave the way you want is to use what's called an "unbound" form, which is a form where any data display and manipulation is handled by the programmer instead of by Access.

17.4.1. Why Use Unbound Forms?

There are a lot of reasons to use unbound forms in Access. Sometimes there is just no other way to do what you want. Typical scenarios include the following:

• The ADO recordset is updatable directly but becomes read-only when bound to a form

• You have a trigger on a multitable SQL server view to allow insertion of new records but Access gives an error when bound to a form

• You want to use SQL Server application role security for data access

• You want to use DAO recordsets in an ADP or updatable ADO recordsets in an MDB

• You want to use server-side recordsets

• You want finer control over the recordset behavior

For example, you may have an ADO recordset that is completely updatable when using the recordset directly but it becomes read-only when bound to a form. Or you may have a trigger for a multitable view to handle insertions but you get errors trying to insert a new record from a form. In such cases, using an unbound form can allow the needed flexibility.

The primary drawback to using unbound forms is that there is no built-in method for displaying data in datasheet or continuous form view. Although it is possible to add ActiveX controls to an Access form that will allow for datasheet-type functionality, it may be more efficient to use another development environment like Visual Studio.NET. For forms that display single records at a time, however, unbound forms can be very effective.

Although it does take more lines of code to have an unbound form than a bound one, the code itself is not that complicated. Once a basic unbound form is written, you can also save it as a template for easy reuse in the future. As a general rule, DAO would normally be used in an MDB file and ADO would be used for an ADP file.

17.4.1.1. Creating Unbound Forms

It is normally easier to create a normal form and then convert it to an unbound form than it is to create an unbound form from scratch. When your form is bound, you are able to use the built-in form design tools to drag and drop fields onto the form and minimize the chance for misspelling a field name. Once you have your data fields added to the form, you can then convert it to an unbound form relatively easily.

To keep things simple, the Shippers table in the NorthwindCS SQL Server database will be used as an example. Since these forms are unbound, an ADO recordset can be used and the code will be the same for both an ADP and MDB file. If you want to have an unbound form that uses a DAO recordset, the steps would be similar but the DAO code would be slightly different. The following steps demonstrate how to create a simple regular Access form in an ADP and then convert it to an unbound form connected to a SQL Server table

17.4.1.1.1. Create a Regular Form as a Template

If your ADP is connected to the NorthwindCS database, the quickest method to create a regular form is to click New from the Forms tab of the Database window. A dialog box similar to the one shown in Figure 17-25 will then appear.

Specify AutoForm:Columnar and then select the Shippers table from the list. Access will then create an autogenerated form, as shown in Figure 17-26, that we can use as the basis for an unbound form. When finished, save the form as frmShippers.

17.4.1.1.2. Modify the Design of the Form

Because the record selectors and navigation buttons won't be usable when the form is unbound, those properties should be set to No on the Format tab of the Form Properties dialog box. Also, the Recordsource property on the Data tab of the form and the Control Source properties of the three text boxes will need to be cleared

Additionally, add nine command buttons to the form with the following names and captions:

Name	Caption
cmdEdit	Edit
cmdSave	Save
cmdCancel	Cancel
cmdNewRecord	Add New
cmdMoveFirst	First
cmdPrevious	Previous
cmdNext	Next
cmdMoveLast	Last
cmdExit	Exit

When finished, the form should look similar to Figure 17-27 while in Form view:

Note that the text boxes are blank because there is no record source defined for the form.

17.4.1.1.3. Creating theRecordset

Now that the basic form is created, code can be added to give it functionality. This section will discuss modifications that will allow the form to display custom data specified by the developer. Add the following code to the General Declarations section of the form's code module:

Dim rsTest As ADODB.Recordset
Dim cnTest As ADODB.Connection
Dim boolAddNewMode As Boolean

Private Sub pPopulateFields()
  ' Check to be sure the recordset is not in a BOF or EOF state.
  ' If it is then do nothing
  If Not rsTest.BOF And Not rsTest.EOF Then
   ' rsTest.Resync adAffectCurrent
   Me.ShipperID = rsTest.Fields("ShipperID")

Me.CompanyName = rsTest.Fields("CompanyName")
  Me.Phone = rsTest.Fields("Phone")
End If

' Set focus to the Exit control
Me.cmdExit.SetFocus

' Reset the text boxes and save/cancel buttons to a
' locked or disabled state.
Me.ShipperID.Locked = True
Me.CompanyName.Locked = True
Me.Phone.Locked = True
Me.cmdCancel.Enabled = False
Me.cmdSave.Enabled = False

' Reset the boolAddNewMode flag
boolAddNewMode = False
End Sub

The pPopulateFields() procedure will be used in several other events. Next, add the following to the Open event of the Form:

Private Sub Form_Open(Cancel As Integer)
  ' For simplicity, we will use the built-in connection of the ADP.
  ' If we were creating an MDB or connecting to a different SQL Server,
  ' then the full connection string would need to be supplied
  Set cnTest = New ADODB.Connection
  cnTest.ConnectionString = CurrentProject.BaseConnectionString
  cnTest.Open

  ' Create the recordset and move to the first record
  Set rsTest = New ADODB.Recordset
  rsTest.Open "shippers", cnTest, adOpenDynamic, adLockOptimistic
  rsTest.MoveFirst

  ' Populate the text boxes on the form with data
  pPopulateFields
End Sub

When the form is opened now, data as shown in Figure 17-28 should appear.

17.4.1.1.4. Adding Code to Scroll the Recordset

Although the form can now display our custom data, it does not yet have much functionality because we have no method to scroll through records or to change any data. This section will go over adding code to some of the buttons created earlier to allow for browsing records. Add the following code to the cmdMoveFirst button:

Private Sub cmdMoveFirst_Click()
  rsTest.MoveFirst
  pPopulateFields
End Sub

Add the following code to the cmdPrevious button:

Private Sub cmdPrevious_Click()
  ' Code is needed to check to see if the recordset is
  ' already before the beginning of the recordset.
  ' If it is, then do nothing
  If rsTest.BOF Then
    Exit Sub
  End If
  rsTest.MovePrevious

  ' Test for BOF again after moving the recordset
  ' If it is, then reverse the move and do nothing
  If rsTest.BOF Then
    rsTest.MoveNext
    Exit Sub
  End If

  ' If everything is fine, then repopulate the controls
  ' with data from the new record
  pPopulateFields
End Sub

Add the following code to the cmdNext button:

Private Sub cmdNext_Click()
  ' Code is needed to check if the recordset is
  ' already beyond the end of the recordset.
  ' If it is, then do nothing
  If rsTest.EOF Then
    Exit Sub
  End If
  rsTest.MoveNext

  ' Test for EOF again after moving the recordset
  ' If it is, then reverse the move and do nothing
  If rsTest.EOF Then
    rsTest.MovePrevious
    Exit Sub
  End If

  ' If everything is fine, then repopulate the controls
  ' with data from the new record
  pPopulateFields
End Sub

Add the following code to the cmdMoveLast button:

Private Sub cmdMoveLast_Click()
  rsTest.MoveLast
  pPopulateFields
End Sub

After adding the above code, you should now be able to click the next, previous, first, and last buttons to scroll through the records without generating any errors. However, the text boxes are locked so the user can't make any changes at this point.

17.4.1.1.5. Enable Records To Be Edited

Although an edit-type button is not absolutely necessary, it simplifies the coding necessary to keep track of changes. Otherwise, code would be needed each time the recordset is moved to compare values and see if anything needs to be updated. With this method, nothing is updated until the user clicks the Save button.

To enable modifications, add the following code to the Click event of the Edit button:

Private Sub cmdEdit_Click()
  ' Only allow edits if there is a current record.
  ' Note that the ShipperID Fiield does not get unlocked
  ' Because it is a Primary Key Field.
  If Not rsTest.BOF And Not rsTest.EOF Then
     Me.CompanyName.Locked = False
    Me.Phone.Locked = False
  End If

' Sets focus to the Company Name field
    Me.CompanyName.SetFocus

   ' Enable the save/cancel buttons
   Me.cmdSave.Enabled = True
   Me.cmdCancel.Enabled = True
End Sub

To enable saving the modifications, add the following code to the cmdSave button:

Private Sub cmdSave_Click()

  ' Check if this is for new Record or change to existing
  If boolAddNewMode = True Then
    rsTest.AddNew
  End If

  ' Update the recordset
  ' Be prepared to handle any errors that may occur
  rsTest.Fields("CompanyName") = Me.CompanyName
  rsTest.Fields("Phone") = Me.Phone
  rsTest.Update

  ' This command refreshes the newly added record
  If boolAddNewMode = True Then
    rsTest.MoveLast
  End If

  pPopulateFields
End Sub

To allow for cancelling a pending modification without saving it, add the following code to the cmdCancel button:

Private Sub cmdCancel_Click()

  ' Check if the recordset is in an add new state.
  ' Clear any data present, if needed
  If boolAddNewMode Then
    Me.ShipperID = ""
    Me.CompanyName = ""
    Me.Phone = ""
  End If

  pPopulateFields
End Sub

You should now be able to modify existing records and have them updated. To enable adding new records, add the following code to the Click event of the cmdNewRecord command button:

Private Sub cmdNewRecord_Click()

  ' Set the boolAddNewMode flag to true
  boolAddNewMode = True

' Clear data from the controls and unlock
    ' In this case the SHipperID is autogenerated
    ' so there is no need to unlock the shipperid field
    Me.ShipperID = ""
    Me.CompanyName = ""
    Me.Phone = ""
    Me.CompanyName.Locked = False
    Me.Phone.Locked = False

    ' Set focus to a field
    Me.CompanyName.SetFocus

    ' Enable the save/cancel buttons
    Me.cmdSave.Enabled = True
    Me.cmdCancel.Enabled = True
End Sub

Add some code to the cmdExit button to close the form:

Private Sub cmdExit_Click()
  DoCmd.Close
End Sub

At this point, the form should be fully functional when run and you should not generate any errors when testing it out. None of the code above is overly complex and could be modified to behave differently depending on your needs. This form could also be saved as a template and reused when needed to speed development of future unbound forms.