May 24 2010 5:11PM GMT

Building a Library of Routines for Updating – #7

Craig Hatmaker Profile: Craig Hatmaker

You are here (Click to enlarge)

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