Editing and deleting objects
<< Back to Classes index page
This example shows how to extend our clsOrder object with methods that allow you to Edit and Delete the database records.
- Create a new class module clsOrder1 with this code (clsOrder1.txt).
- Experiment with this new object in the Debug Window as follows:
' Create an new instance of object in memory Set oOrd = New clsOrder1 ' Add properties to "empty" object oOrd.CustomerID = 1 oOrd.Item = "Widget" oOrd.Qty = 200 oOrd.UnitPrice = 5 ' Save to database ? oOrd.Create True ' Check new ID ? oOrd.OrderID 3 ' Confirm then change value of Qty in object ? oOrd.Qty 200 oOrd.Qty = 1000 ? oOrd.Update True ' Load data from database again and check change has been done ? oOrd.OrderID 3 ? oOrd.Load(3) True ? oOrd.Qty 1000 ' Now delete the object from database ? oOrd.Delete True ' And now try and load it again ? oOrd.Load(3) False
If you want to do this in a test code snippet instead, don't forget to change the "?" to "Debug.Print" (but if you've got this far, you'll know that already).
Things to note about the code
- This time we've made use of the Nz function when
reading data from the database in the Load function:
Me.Item = Nz(!Item)This copes with the problem (as you'll probably have already found) of trying to put a Null value from the database into one of your object's variables. You could get over this by declaring all your object class properties as type Variant, but this defeats the point of having typed values at all.
- The Update method limits the size of the recordset
it works on by using a query. This way also provides a quick check -
using the RecordCount property - if the particular record isn't there.
sQry = "SELECT * FROM " & scTABLE & _ " WHERE ([OrderID]=" & Me.OrderID & ");" Set rs = CurrentDb().OpenRecordset(sQry, dbOpenDynaset) With rs If .RecordCount = 0 Then .Close Err.Raise 40000, , "Cannot find OrderID=" & Me.OrderID GoTo Done End If .Edit SaveFieldsToDb rs .Update .Close End With
- If we use a Private procedure in a class module,
like the SaveFieldsToDb subroutine, it can be used within the
class module just like any other local procedure, but you cannot use it externally.
Set oOrd as New clsOrder1 oOrd.SaveFieldsToDbDeclaring the procedure Private means just that: whatever it does is private to the module and cannot be accidentally used by an "outsider".
- The Delete method just uses a simple delete query.
Note the use of the dbFailOnError parameter to make sure any errors are trapped.
sQry = "DELETE * FROM " & scTABLE & _ " WHERE ([OrderID]=" & Me.OrderID & ");" CurrentDb().Execute sQry, dbFailOnError
- Even though this code might look a bit complicated, once you've written it once, it can easily be copied to other objects and changed with just one global edit.
- What happens if you try and save an object that doesn't have all it's properties set? We'll discuss this problem in a later example.
In the next example, we'll see how to use Collections to manage an order that has more than one detail. [Sorry. That's all folks!]
<< Back |
Any comments, feedback, questions: please send us a message.
This page first published circa 2000. Last updated 11 June 2014