Saving to the database

Introduction

This lesson explains how to use class modules to capture data from an unbound form and save it to a database table.

The example shows how to handle a simple Order object by creating a new class module clsOrder, a table tblOrders in the database, and a form frmOrderCreate that will use the object to create a new record in the database.

Download the zipped file classes.mdb for the full example database.

.Bookmark = .LastUpdated	' WRONG

.Bookmark = .LastModified

Instructions

1. Create a new table tblOrders with the following fields:
    
   

   Field Name	Data Type	Properties
   OrderID	AutoNumber	Primary Index
   CustomerID	Number	Long integer
   Item	Text
   Qty	Number	Long integer
   UnitPrice	Currency

2. Create a one-to-many relationship between tblCustomers.CustomerID and tblOrders.CustomerID with enforced referential integrity.

   

3. Create a new class module clsOrder and enter the code (clsOrder.txt).
4. Create a new unbound form frmOrderCreate with the following controls:
    
   

   Control Name	Type	Properties
   cboCustomer	Combo Box	Bound to tblCustomers.CustomerID
   txtItem	Text Box
   txtQty	Text Box
   txtUnitPrice	Text Box
   txtValue	Text Box	Disabled; =[txtQty]*[txtUnitPrice]
   cmdClose	Command button	(see below)
   cmdOK	Command button	(see below)

5. Add the code (frmOrderCreate.txt) to the form.
6. Open the form and fill out with some sample data:

   

7. Click OK. This should create a new record in the tblOrders table of the database.

What we've done

• Again, this looks like a lot of effort to achieve apparently very little. We've let the user fill in some fields on a form and then saved the data as a record in the database.

  However, this example is meant to show the key elements of the technique. When you are dealing with complex objects and want to keep your users away from the database until the last minute, these techniques come into their own.

• The key to this example is the local object moOrder created in the declaration section of the form's module

  	Private moOrder As New clsOrder
  

• The form is not bound to the Orders table and can never do any damage until the appropriate time no matter what the user does.
• When it's time to do the damage, the code behind the form gathers the values for the object's properties and then uses the Create method of the object to create a new entry in the table.
• The actual code in the sample includes some rather lengthy code to screen for possible errors before saving, but the essential lines to gather the data are:

  	moOrder.CustomerID = Me.cboCustomer
  	moOrder.Item = Me.txtItem
  	moOrder.Qty = Me.txtQty
  	moOrder.UnitPrice = Me.txtUnitPrice
  

  Note how I've used the dot symbol Me.controlname to refer to the value of the controls in the form. Yes, I know the documentation says you should use the bang (!) symbol, but this way is not only perfectly valid syntax, at least in Access 97, but it will give you an immediate error when you try and compile the code, which the syntax Me!controlname won't.

  Do a global edit in all your form modules now to replace "Me!" with "Me.".

• The real damage is done by one simple command

  	moOrder.Create
  

  Whatever is actually to be done by that command is not the concern of the programmer creating the form, providing he's set all the properties correctly.
• Note how the code makes use of the return value from the Create method to see if it's been successful or not:

      If Not moOrder.Create Then
          MsgBox "Unable to create a new order", vbCritical
          GoTo Done
      Else
          MsgBox "Created new order with ID=" & moOrder.OrderID
      End If
  

  Not only that, the object has its new ID already stored as the property moOrder.OrderID.

• To be more sophisticated, you could wrap the method in a transaction:

      BeginTrans
      If Not moOrder.Create Then
          RollBack
          MsgBox "Sorry, Failed"
      Else
          CommitTrans
          MsgBox "Success!"
      End If
  

<< Back  |  Next >>

Feedback or questions: Contact DI Management.    Return to Tips and Tutorials Page.