Saving to the database
<< Back to Classes index page
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.
- 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
- Create a one-to-many relationship between tblCustomers.CustomerID
and tblOrders.CustomerID with enforced referential integrity.
- Create a new class module clsOrder and enter the code (clsOrder.txt).
- 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)
- Add the code (frmOrderCreate.txt) to the form.
- Open the form and fill out with some sample data:
- 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.CreateWhatever 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
Any comments, feedback, questions: please send us a message.
This page first published circa 2000. Last updated 11 June 2014