Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba
If we stopped with only what has been posted in this blog so far, we would have a powerful tool set to create meaningful charts and drill down pivot tables from data residing in just about any database. It only takes a couple of minutes to replace the SQL statement in Macro1() and to set parameters in a pivot table wrapper or two, at which point, your customers can see the relationships in their data and begin to identify opportunities for improvement.
Since starting this blog, my team’s customers have received spreadsheets that determine employee’s bonuses, identify underperforming accounts, measure staff efficiencies, respond to external customers’ requests for information, etc., etc., etc. There’s a lot you can do with what we’ve provided here but there’s more we can do to make our delivered product follow “good form.”
We have been using VBA’s “InputBox” function to ask users which records to pull. This works as long as there’s only one parameter and as long as your customer readily knows all values for it. Often, neither of these two situations apply. Often, transaction dates with employee IDs, or customer IDs, or account numbers, etc. determine which records to retrieve. For this, we need a good form.
Good forms improve the user’s experience and increase the power of your product by handling multiple parameters and checking those parameters before bothering the server with a meaningless request and frustrating our customers with unsatisfactory results. For the next several posts, we will be looking at two forms, and derivations of one, that address the most common prompts for parameters.
The smaller form, frmPrompt, is the initial form. By subtracting screen elements and passing parameters, we can customize this form to handle just about any request for data without changes to code; however, occasionally I do modify this form. In my environment, I often add a group box with radio buttons to select iSeries libraries so the same spreadsheet can be used over different companies sharing the same server. I suspect most other environments won’t need this capability so to simplify things, I have omitted that feature. My only reason for mentioning this is to emphasize something I pointed out on the first page of this blog – these techniques are designed for developers. What you, the developer, brings to these techniques is critical. Though most of the code is isolated so that changes are often not required, all of the code is provided so you can adapt it to your special requirements without hindrance. And with that said…
The larger form, frmSelect_Multiple, supports frmPrompt. It responds to the ellipse buttons (. . .) and facilitates searching for ID’s, Codes, Accounts, and such. Since creating this form many years ago, it has never needed modification of any type to accommodate any master file, or code file. Hopefully, you’ll be as lucky. But should you have a need, the code is there for you to change.
My next post will be a stripped down version of frmPrompt to get you introduced (assuming you haven’t been already) to the art of creating forms in VBA.