Chapter 13: Accessing Data with VBA Code
521
intAnswer = MsgBox(“Are you sure you “ _
& “ want to delete this invoice?”, _
vbQuestion + vbYesNo, “Delete Invoice”)
If intAnswer = vbNo Then
Exit Sub
End If
‘Delete payments for this invoice:
strSQL = “DELETE * FROM tblSalesPayments “ _
& “WHERE InvoiceNumber = “ & Me.InvoiceNumber
CurrentProject.Connection.Execute strSQL
‘Delete line items:
strSQL = “DELETE * FROM tblSalesLineItems “ _
& “WHERE InvoiceNumber = “ & Me.InvoiceNumber
CurrentProject.Connection.Execute strSQL
‘Delete invoice record:
RunCommand acCmdSelectRecord
RunCommand acCmdDeleteRecord
End SubThis procedure uses SQL statements to explicitly delete records in the child tables (tblSalesLi-
neItems and tblSalesPayments) before deleting the parent record in tblSales.
Notice that this procedure includes two different Exit Sub statements. The first is executed if the
current record happens to be a new record. Presumably, there is no reason to delete a new record,
and, in fact, an attempt to delete a new record raises an error.
The second Exit Sub executes if the user chooses not to delete the record (the MsgBox function
returns vbNo, in this case) after clicking the Delete button. If the user confirms the deletion (the
value of MsgBox, in this case, is vbYes), the code proceeds to delete the invoice records matching
the current InvoiceNumber, and then deletes the current record displayed on frmSales.
Many developers prefer using Exit Sub (or Exit Function) to jump out of a procedure as soon
as the code determines there is no need to continue. An Exit Sub statement provides an explicit
end to the procedure and is easily understood by anyone reviewing the code. When using nested
If statements, particularly when the procedure is quite long and involved, the End If statement
ending the If may be easily missed or misunderstood.
Summary
In the previous few chapters, you learned the basics of programming, reviewed some of the built-
in functions, and experienced the various logical constructs. You learned about ADO and how to
access data in tables and queries through SQL recordsets. You also learned a lot about forms and
queries in previous chapters. In this chapter, you used all this knowledge and learned how to dis-
play selected data in forms or reports using a combination of techniques involving forms, VBA
code, and queries.