Beyond Excel: VBA and Database Manipulation

Dec 8 2009   10:58PM GMT

Putting it all Together

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

In the last post we finalized frmPromptfrmPrompt provides a feature rich and professional looking method for getting parameters for our customer’s reports.  Today, we’re going to ignore some of those rich features in order to ease us into integrating frmPrompt into our Macro1()

Below is our new Macro1().  The first thing you will notice is the number of parameters it sets for other forms.  This is a good thing.  By putting everything into parameters, we can essentially put all of our coding efforts into this routine and the pivot routine – and that’s it.  For many reports, we can ignore all those support routines and forms we created and focus solely on Macro1().  The parameters prompt us for what we need to change so the guess work of what needs to be done is eliminated. 

The toughest part is creating the SQLs for the ellipse buttons and our report.  The ellipse buttons require two simple SQLs each.  They are nearly identical.  They both select the same two fields (one Code/ID field and one Description/Name field).  They both use the same table.  They differ in the Where and Order by clauses, with one filtering and ordering by ID, and the other by Name.  If you’re having trouble with SQL, use MQ Query like we did earlier to prototype.

Our main SQL is relatively complex with four tables joined.  Most reports I’ve seen do not require this many.  Even so, four can easily be prototyped with MS Query.  Once we have the skeleton SQL provided by MS Query, all we need to do is string it together with our parameters from frmPrompt.  As I mentioned at the beginning, for this exercise, I have ignored some of the complexities of integrating all of the features that frmPrompt provides us.  frmPrompt allows us to select wild card values and multiple values.  With what is posted here, we can’t use those.  That will be our next posts focus.  In the mean time, try this out making sure you only select one value each for Customers and Products.

Sub Macro1()
    Dim sSQL As String
    Dim sConnect As String
        sConnect = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
                   "DBQ=C:\Users\chatmaker\Documents\Northwind 2007.accdb;"
    With frmPrompt
        'Set up frmPrompt's labels and textboxes
        .pDateLbl = "Ordered Dates"        'Set the prompt for dates
        .pFromVisible = True               'Enable "From Date" text box
        .pFrom = "01/01/2006"              'Set default for "From" text box
        .pToVisible = True                 'Enable "To Date" text box
        .pTo = Format(Now(), "mm/dd/yyyy") 'Set default for "To" text box
        .pID1Visible = True                'Enable ID1's label and text box
        .pID1Lbl = "Customer ID(s)"        'Set the prompt for ID1
        .pID2Visible = True                'Enable ID2's label and text box
        .pID2Lbl = "Product Code(s)"       'Set the prompt for ID2
        'Set up elipse button for Customers
        .pTitle1 = "Select Customers"      'Set frmSelect_Multiple's title
        .pConnect1 = sConnect              'Set connection string
        .pLblCode1 = "Code"                'Set label for the codes/ID column
        'Create SQL string to use to search for Customer Codes
        .pSQLCode1 = "Select   ID as Code, " & _
                              "Company as Name " & vbCr & _
                     "From     Customers " & vbCr & _
                     "Where    ID like '%?%' " & vbCr & _
                     "Order By ID"
        .pLblDesc1 = "Company"             'Set description/name column label

        'Create SQL string to use to search for Customer's Company Names
        .pSQLDesc1 = "Select   ID as Code, " & _
                              "Company as Name " & vbCr & _
                     "From     Customers " & vbCr & _
                     "Where    Company like '%?%' " & vbCr & _
                     "Order By Company"
        'Set up elipse button for Products
        .pTitle2 = "Select Products"       'Set frmSelect_Multiple's title
        .pConnect2 = sConnect              'Set connection string

        .pLblCode2 = "Code"                'Set label for codes/ID column
        'Create SQL string to use to search for Product Codes

        .pSQLCode2 = "Select   `Product Code` as Code, " & _
                              "`Product Name` as Name " & vbCr & _
                     "From     Products " & vbCr & _
                     "Where    `Product Code` like '%?%' " & vbCr & _
                     "Order By `Product Code`"
        .pLblDesc2 = "Name"                'Set description/name column label

        'Create SQL string to use to search for Product Names
        .pSQLDesc2 = "Select   `Product Code` as Code, " & _
                              "`Product Name` as Name " & vbCr & _
                     "From     Products " & vbCr & _
                     "Where    `Product Name` like '%?%' " & vbCr & _
                     "Order By `Product Name`"
       .Show                               'Display the Prompt
        Do While .Visible                  'Wait on user
        If .pOK Then                       'OK button used to exit
            sSQL = "SELECT O.`Order ID`, O.`Customer ID`, " & vbCr & _
                   "       O.`Order Date`, C.`First Name`, " & vbCr & _
                   "       O.`Ship State/Province`, D.Quantity, " & vbCr & _
                   "       P.`Product Name` " & vbCr & _
                   "FROM   Customers C, Orders O, " & vbCr & _
                   "       `Order Details` D, Products P " & vbCr & _
                   "WHERE  O.`Customer ID` = C.ID " & vbCr & _
                   "  AND  O.`Order ID`    = D.`Order ID` " & vbCr & _
                   "  AND  D.`Product ID`  = P.ID " & vbCr & _
                   "  AND  O.`Customer ID` = " & Trim(.pID1) & " " & vbCr & _
                   "  AND  P.`Product Code`= '" & Trim(.pID2) & "' " & vbCr & _
                   "  AND  O.`Order Date` Between #" & _
                           Format(.pFrom, "mm/dd/yyyy") & "# And #" & _
                           Format(.pTo, "mm/dd/yyyy") & "# "
            SQLLoad sSQL, sConnect, "A4", "Data", "Data"
            If Range("Data").Rows.Count > 1 Then Pivot_Template
        End If
    End With
End Sub

2  Comments on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • RonWilson
    Thoroughly enjoying your blog and have progressed through without too much drama until now. When I run macro1 I get an error in frmPrompt - 
    Method or Data Member Not Found - with this section highlited .pLblCode =
    Any hints as to where I should be looking would be greatly appreciated.
    Ron Wilson
    0 pointsBadges:
  • Craig Hatmaker
    Hi Ron, Thanks for reading. My guess is a "1" or "2" is missing after .pLblCode Best wishes
    1,860 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: