Building a Library of Routines for Updating – #7

As you can see from the simple DFD at right, we are almost done writing update routines. Hopefully you are asking an important question. Hopefully you are wondering why I haven’t covered an extremely important topic. Hopefully you want to know how we make sure the data is correct BEFORE we update anything.
Glad you asked.
If you click on the DFD you will see a routine we haven’t yet covered worksheet.Check_Entry. Its purpose is to review the user’s entries and look for any mistakes it can catch. As a programmer – you know that this is perhaps the hardest part of coding because it can change with every application. Since Check_Entry can be ‘worksheet’ specific, I place a unique instance of Check_Entry in any worksheet that updates data. And given that I sometimes create workbooks that contain multiple worksheets that update data, I cannot place Check_Entry in a generic module without modifying the routine I’m going to present today.
This routine first calls the Check_Entry routine of the worksheet that called it. There MUST be a routine named Check_Entry there. Happily, it is quite possible that almost all of your Check_Entry routines will require no modification. But it is almost certain that if you do more than a few of these spreadsheets, you are going to run into a data requirement that is so unique that the generic routines I will provide simply won’t cut it, in which case, you’re going to need to make some modifications to Check_Entry.
So back to today’s routine. As I said, this routine first calls Check_Entry and if Check_Entry reports no errors, this routine then passes control down to the routines you’ve been adding to your library. I’ve never had to modify any of these update routines and I fully expect that once you have placed them in your library, you can pretty much forget all about them.
Here is the code for Post:
Function Post(sConnect As String, sWorksheet As String, _
sHeaderFields As String, sDetailFields As String, _
sHeaderRange As String, sDetailRange As String, _
sTable As String) As Boolean
' Description:Post entries from a spreadsheet to a database table
' Parameters: sWorksheet Worksheet containing entries ' sHeaderFields Header Field descriptions range ' (on Tables Tab - Optional) ' sDetailFields Detail Field descriptions range ' sHeaderRange Range containing values common to all ' update records (Optional) ' sDetailRange Range containing entry rows ' sTable Table Name that will be updated ' (there can be only one) ' Example: bResult = Post(sConnectionString, _ ' sDataWorkSheet, _ ' sHeaderFieldDefinitions, _ ' sDetailFieldDefinitions, _ ' sHeaderData, _ ' sDetailData, _ ' sUpdateTable) ' Date Init Modification ' 01/12/06 CWH Initial Programming On Error GoTo ErrHandler ' Post = Failure 'Assume Something went wrong Settings "Save" 'Save current application settings Settings "Disable" 'Disable events, calcs, screen updates Dim lRow As Long Dim bResult As Boolean bResult = Worksheets(sWorksheet).Check_Entry( _ Range(sDetailRange & "_Data"), _ sDetailRange, sDetailFields) If bResult <> Success Then MsgBox "Errors exist. " & _ "Correct first, the press 'Done'", vbOKOnly, "Errors" Else bResult = Update_Entries(sConnect, sWorksheet, _ sHeaderRange, sDetailRange, _ sHeaderFields, sDetailFields, _ sTable, True) End If Post = bResult ErrHandler: If Err.Number <> 0 Then MsgBox _ "Post - Error#" & Err.Number & vbCrLf & Err.Description, _ vbCritical, "Error", Err.HelpFile, Err.HelpContext Settings "Restore" 'Restore application settings On Error GoTo 0
End Function
 Comment on this Post