An Access Control Panel, Part 2 |
  
|
An Access Control Panel, Part 2 |
|
Ivor Davies Bronze Collection
The first article in this series described how to capture information on database objects (see "Building an Access Control Panel" in the September 1998 issue). Part 2 examines how to store that information using DAO and the Documents collection.
Part 1 of this article described how to build a set of tools for recording and displaying the properties of Access objects. But sometimes what you really need isn't just a listing of what an Object does, but why it does it and where it fits into the overall scheme of your project. In this article, I'll describe how you can use elements of both DAO and Access to create an electronic equivalent of the sticky note, and how you can make that information easily available from a customized menu bar. I'll also make use of the list of reports that I generated in the first part of this article to build a central report launcher that your end users will find friendly and very productive. The complete project is available in the accompanying Download file.
Objects and containers
As you look at the code, you'll see that I use two different techniques for appending a description to an object. That's because I'm dealing with two different kinds of objects: DAO objects and Access objects.
For starters, there are the DAO objects that are created using Jet, the database engine that does the actual data processing for Access. For DAO objects like Tables and QueryDefs, I take advantage of DAO's ability to create user-defined properties. If you've ever added text to a field description when designing a table, then you've taken advantage of this facility. As you type in your text, Access is using DAO's CreateProperty method to add a Description to the field's property collection (see Gary Warren King's article, "Properties Without Fear," in the September 1998 issue of Smart Access for more information on creating custom properties). I use the same process to store and retrieve my own object description properties for DAO objects.
However, the aim of my project was to create descriptive labels for all of the objects in an Access database, not just the DAO ones. Forms, Reports, Macros, and Modules are created and managed by Access, not by DAO. While I'll only describe how to use Forms and Reports in this article, you can easily extend these techniques to Macros and Modules. Since these Access objects don't have a CreateProperty method, they require a different approach than the DAO objects. For the Access objects, I use the Documents collection instead of using the objects' Properties collection.
Each type of object in an Access database (Tables, Forms, and so forth) has a corresponding Container object managed by DAO. Each Container object has a Documents collection, and the Documents collection holds an object that describes each instance of the type specified by the Container. As an example, the Forms container holds a Documents collection that contains one Document object for each of your Forms. The same is true for Reports, Macros, and so on. Since Containers and Documents are DAO objects, they do have a CreateProperty method that allows me to create user-defined properties.
Figure 1
Figure 1 shows the form that allows you to enter and retrieve a descriptive text for all objects, both DAO and Access. Behind the form, this code is used to initialize the module-level variables that are used to identify which object is being used.
Private Sub cmdAccept_Click() strDescriptionText = Me!txtDescription.Value mpintState = 1 ChooseProcedure End Sub
Private Sub cmdAmend_Click() strDescriptionText = Me!txtDescription.Value mpintState = 2 ChooseProcedure End Sub
Public Sub ChooseProcedure() Select Case mpstrProcedureIdentifier Case Is = "T" CreateTableDescription Case Is = "Q" CreateQueryDescription Case Is = "F" CreateFormDescription Case Is = "R" CreateReportDescription Case Else MsgBox "Not working" Exit Sub End Select
DoCmd.Close acForm, "frmdescriptions", acSaveNo End Sub |
The ChooseProcedure subroutine works along with the OriginateDescription subroutine to identify the class to which the selected Object belongs. Here's that routine:
Public Sub OriginateDescription()
Dim strCon As String mpintState = 0 On Error GoTo errOD strCon = Application.CurrentObjectName
Select Case strCon Case "zfrmTableData" mpstrProcedureIdentifier = "T" CreateTableDescription Case "zfrmQryInf" mpstrProcedureIdentifier = "Q" CreateQueryDescription Case "zfrmFormInfo" mpstrProcedureIdentifier = "F" CreateFormDescription Case "zfrmReportInfo" mpstrProcedureIdentifier = "R" CreateReportDescription Case "zfrmPrintreports" mpstrProcedureIdentifier = "R" CreateReportDescription Case Else SCvarCot End Select
exiterrOD: Exit Sub errOD: MsgBox "Error " & Err.Description, _ vbCritical, "Error" Resume exiterrOD End Sub |
In the routine shown previously, I use Access's Application.CurrentObjectName property. This property provides the name of the object currently being used. If the user is using one of my forms to select another object in the database, then Application.CurrentObjectName will have the name of my form. I use this information to identify from which of my forms the user has selected the object name. If, for example, the calling form is zfrmPrintreports, then the object selected is a report; if zfrmFormInfo is being used, then the user has selected a form, and so on. However, I didn't want the scope of my utility to be limited to entering descriptions from my forms. I also wanted to be able to document the object that I'm currently using without going to one of these forms. To achieve that, OriginateObject calls the SCvarCot routine whenever one of my forms isn't being used:
Public Sub SCvarCot() Dim VarCOT As Variant Dim strCon As String
On Error GoTo errSCC
VarCOT = Application.CurrentObjectType strCon = Application.CurrentObjectName Select Case VarCOT Case 0 ' VB intrinsic constant for acTable mpstrTblName = strCon mpstrProcedureIdentifier = "T" CreateTableDescription Case 1 'acquery mpstrProcedureIdentifier = "Q" CreateQueryDescription Case 2 'acForm mpstrProcedureIdentifier = "F" CreateFormDescription Case 3 'acreport mpstrProcedureIdentifier = "R" CreateReportDescription Case Else MsgBox "Error" & Err.Number & " " & _ Err.Description, vbCritical End Select
exiterrSCC: Exit Sub errSCC: Resume exiterrSCC
End Sub |
The SCVarCot routine uses the Application.CurrentObjectType property to determine the current object's class, and the Application.CurrentObjectName property to determine its name. The combination of these two pieces of information enables me to initialize mpstrProcedureIdentifier and call the appropriate description-creating procedure.
These techniques for identifying the objects in use within the database can be used for other purposes. You could, for instance, log which objects are being used and for how long they have focus in your application.
Working with DAO objects
Another important variable in this utility is mpintState. This module-level Integer is used to determine whether the object is a DAO object (and can have a property created directly on it) or an Access object (and must have its properties created on its Document object). Here's the code for a typical DAO routine, the one used to create descriptions for QueryDefs:
Public Sub CreateQueryDescription()
Dim varPropText As Variant Dim PrpNew As Property Dim strOld As String Dim strChoice As String Dim dbscurr As Database Dim strCon As String
On Error GoTo ErrorCreate
Select Case mpintState Case Is = 1 varPropText = strDescriptionText Set PrpNew = qdCurrent.CreateProperty("Description", _ dbMemo, varPropText) qdCurrent.Properties.Append PrpNew qdCurrent.Properties.Refresh mpintState = 0 Set dbscurr = Nothing Exit Sub Case Is = 2 qdCurrent.Properties.Delete ("Description") varPropText = strDescriptionText Set PrpNew = qdCurrent.CreateProperty("Description", _ dbMemo, varPropText) qdCurrent.Properties.Append PrpNew qdCurrent.Properties.Refresh mpintState = 0 Set dbscurr = Nothing Exit Sub End Select
Set dbscurr = CurrentDb strCon = Application.CurrentObjectName mpstrQryName = strCon
If mpstrQryName = "zfrmQryInf" Then 'retrieves name of query from the Form mpstrQryName = Forms!zfrmQryInf!cboQryNames.Value 'and sets the variable to the retrieved name Set qdCurrent = dbscurr.QueryDefs(mpstrQryName) Else Set qdCurrent = dbscurr.QueryDefs(mpstrQryName) End If
'retrieves existing description value 'error handler is tripped if value doesn't exist strOld = qdCurrent.Properties("Description").Value 'if there's an existing description If IsNull(strOld) = False Or IsEmpty(strOld) = False Then strChoice = _ MsgBox("Do you want to change the comment?", _ vbYesNo, "COMMENT") Else DoCmd.OpenForm "frmdescriptions", acNormal, _ , , , acDialog Exit Sub End If
'if the choice is to delete existing description If strChoice = vbYes Then qdCurrent.Properties.Delete ("Description") DoCmd.OpenForm "frmdescriptions", acNormal _ , , , , acDialog Exit Sub Else Exit Sub End If
ErrorCreate: 'if there's no existing description value If Err.Number = 3270 Then GoTo Continue Else ErrorProc End If End Sub |
At the heart of the definition-creating procedure is the CreateProperty method. Here I'm using it with its fourth parameter, the DDL (Data Definition Language) parameter, set to False. This allows users to change and delete the property after it's created. If you want to create a property that users can't change, set the DDL to True. I've also set my new property's data type by passing dbMemo as the CreateProperty's third parameter, the DataType attribute. You can see the full list of data types for the CreateProperty method in Access's Help system.
There's no point in saving all this information if you can't retrieve it. Here's the code I use to retrieve TableDef properties:
Public Sub ViewTableDescription() Dim strStored As String Dim strCon As String Dim dbscurr As Database
Set dbscurr = CurrentDb strCon = Application.CurrentObjectName On Error GoTo TableErrorHandle
'retrieves the Table name If strCon = "zfrmTableData" Then mpstrTblName = Forms!zfrmTableData!cboListTbl.Value Set tblCurrent = dbscurr.TableDefs(mpstrTblName) Else mpstrTblName = strCon Set tblCurrent = dbscurr.TableDefs(mpstrTblName) End If
'if there isn't a stored description property If _ IsNull(tblCurrent.Properties("Description").Value) Or _ IsEmpty(tblCurrent.Properties("Description").Value) _ Then MsgBox "Cannot retrieve a description", _ vbExclamation, "Error" Else strStored = tblCurrent.Properties("Description").Value strDescriptionText = strStored Form_frmDescriptions.Caption = "View Description" Form_frmDescriptions!txtDescription.Value = _ strDescriptionText DoCmd.OpenForm "frmdescriptions", acNormal, _ , , , acDialog End If Set dbscurr = Nothing
Exit Sub TableErrorHandle: If Err.Number = 3270 Then Resume Next Else ErrorProc End If End Sub |
Working with Access objects
The procedures for adding, deleting, and retrieving descriptions for Access objects is slightly different. Instead of adding directly to the Object's properties, as I did with TableDefs and QueryDefs, I add properties to the object's document in the relevant Documents collection. Here's the code for appending a description to a Form object:
Public Sub CreateFormDescription() Dim varPropText As Variant Dim strFrmName As String Dim PrpNew As Property Dim strOld As String Dim strChoice As String Dim dbscurr As Database Dim contCur As Container Dim strCon As String
On Error GoTo ErrorCreate
Select Case mpintState Case Is = 1 varPropText = strDescriptionText Set PrpNew = _ DocCurrent.CreateProperty("Description", _ dbMemo, varPropText) DocCurrent.Properties.Append PrpNew DocCurrent.Properties.Refresh mpintState = 0 Set dbscurr = Nothing Exit Sub Case Is = 2 DocCurrent.Properties.Delete ("Description") varPropText = strDescriptionText Set PrpNew = _ DocCurrent.CreateProperty("Description", _ dbMemo, varPropText) DocCurrent.Properties.Append PrpNew DocCurrent.Properties.Refresh mpintState = 0 Set dbscurr = Nothing Exit Sub End Select
Set dbscurr = CurrentDb
strCon = Application.CurrentObjectName If strCon = "zfrmFormInfo" Then 'retrieves name of table from the Form strFrmName = Forms!zfrmFormInfo!cboListForms.Value 'and sets the variable to the retrieved name Set contCur = dbscurr.Containers!Forms Set DocCurrent = contCur.Documents(strFrmName) Else strFrmName = strCon Set contCur = dbscurr.Containers!Forms Set DocCurrent = contCur.Documents(strFrmName) End If
'retrieves existing description value 'error handler is tripped if value doesn't exist strOld = DocCurrent.Properties("Description").Value 'if there's an existing description If IsNull(strOld) = False Or _ IsEmpty(strOld) = False Then strChoice = MsgBox("Do you want to change _ the comment?", vbYesNo, "Comment") Else DoCmd.OpenForm "frmdescriptions", acNormal, _ , , , acDialog Exit Sub End If
'if the choice is to delete existing description If strChoice = vbYes Then DocCurrent.Properties.Delete ("Description") DoCmd.OpenForm "frmdescriptions", acNormal, _ , , , acDialog Exit Sub Else Exit Sub End If
Exit Sub ErrorCreate: 'if there's no existing description value If Err.Number = 3270 Then DoCmd.OpenForm "frmdescriptions", acNormal, _ , , , acDialog Exit Sub Else ErrorProc End If
End Sub |
Retrieving the description of Access objects is structurally the same as for DAO objects, the main difference being the reference to Containers and Documents. However, there's a difficulty in ensuring, when a report is selected from one of my forms, that the report and not the form that the report is selected from is identified as the object to be documented. To demonstrate this, I've selected the code to display a report's information as my sample code. As you can see, the code checks to see which form called this routine. If the form was my zfrmReportInfo, then I know that the user selected the report name from one of my forms:
Dim strStored As String
Dim contCur As Container Dim strRptName As String Dim strCon As String Dim dbscurr As Database
Set dbscurr = CurrentDb strCon = Application.CurrentObjectName On Error GoTo ReportErrorHandle
Select Case strCon Case "zfrmReportInfo" strRptName = _ Forms!zfrmReportInfo!cboListReports.Value Set contCur = dbscurr.Containers!Reports Set docCurrentReport = _ contCur.Documents(strRptName) Case "zfrmPrintReports" strRptName = _ Forms!zfrmPrintreports!cboListReports.Value Set contCur = dbscurr.Containers!Reports Set docCurrentReport = _ contCur.Documents(strRptName) Case Else strRptName = strCon Set contCur = dbscurr.Containers!Reports Set docCurrentReport = _ contCur.Documents(strRptName) End Select strStored = _ docCurrentReport.Properties("Description").Value
If IsNull(strStored) Or IsEmpty(strStored) Then MsgBox "Cannot retrieve a description.", _ vbExclamation, "Error" Else strStored = _ docCurrentReport.Properties("Description").Value strDescriptionText = strStored Form_frmDescriptions.Caption = "View Description" Form_frmDescriptions!txtDescription.Value = _ strDescriptionText DoCmd.OpenForm "frmdescriptions", _ acNormal, , , , acDialog End If Set dbscurr = Nothing
Exit Sub
ReportErrorHandle:
If Err.Number = 3270 Then Resume Next Else ErrorProc End If End Sub |
All of these procedures use ErrorProc to handle errors. The full listing for ErrorProc is available in the accompanying Download file.
All of this code to attach and retrieve information about objects wouldn't be much use without an easy way to access it. My utility provides a menu bar that allows the user to store and retrieve data. The following code creates that toolbar at runtime, but it requires that the Microsoft Office 8.00 Object Library be referenced in your project:
Public Sub CreateToolBar() Dim cbDesc As CommandBar Dim cbbView As CommandBarButton Dim cbbCreate As CommandBarButton
On Error GoTo errCmdHandle
Set cbDesc = CommandBars.Add(Name:="Description") cbDesc.Visible = True Set cbbView = cbDesc.Controls.Add(msoControlButton) With cbbView .OnAction = "ViewDescription" .Caption = "View Description" .Style = msoButtonCaption End With
Set cbbCreate = cbDesc.Controls.Add(msoControlButton) With cbbCreate .OnAction = "OriginateDescription" .Caption = "Create Description" .Style = msoButtonCaption End With
exitCMD: Exit Sub
Exit Sub errCmdHandle: If Err.Number = 5 Then MsgBox "CommandBar already exists", _ vbCritical, "Error" Resume exitCMD Else MsgBox "Error " & Err.Number & " " & _ Err.Description, vbCritical, "Error" End If End Sub |
The command to delete the toolbar is considerably simpler:
CommandBars("Description").Delete |
I've put command buttons to call this code and create and destroy the toolbar on the form zfrmTblInfo.
Next steps
The utility of this code extends beyond the development cycle. Our practice is now to delete all developer comments when we deliver the application. In the place of our developer comments, we substitute descriptions of the object's functions that are relevant to our end users. One implementation of this practice that's especially popular among our clients is a report information form that displays all the reports in the database (see Figure 2). Anyone using this form can examine each report, read our notes about its function, and then decide whether the report does the job they want. As our applications sometimes run to dozens of reports, this tool can be a real time saver for our users.
Figure 2
However, if our user tries to open a report from the report information form, they can get an error. This happens because many of our reports depend on getting parameters from a specific form in the database. If our user uses our report information form to launch a report, the form that the report depends on won't be open and the report will fail. To handle this, I added the following code to the report display form:
Public Sub ErrOpenForm() Dim strEm As String Dim Trim1 As String Dim strStrip1 As Integer Dim strStrip2 As Integer Dim strFileName As String
'if error message is "file not found" If Err.Number = 2450 Then strEm = Err.Description 'strip out any leading or trailing spaces Trim1 = LTrim(RTrim(strEm)) 'counts & returns the number of characters 'in stripped string strStrip1 = Len(Trim1) strStrip2 = strStrip1 - 277 Debug.Print strStrip2 strFileName = Mid(strEm, 39, [strStrip2]) Debug.Print strFileName 'open the required Form using this string DoCmd.OpenForm [strFileName], acNormal DoCmd.Close acForm, [strFileName], acSaveNo Else MsgBox "Error" & Err.Number & " " & Err.Description, _ vbCritical, "Error" Exit Sub End If
End Sub |
This code gets the Description property of the last error and parses out the name of the form mentioned in the message. The code then opens the required form.
I have to admit that part of the appeal that this utility has for me is the way it demonstrates the power and flexibility of VBA. VBA's comparatively small set of easy-to-learn functions can be combined to achieve some very powerful effects. When added to DAO and Jet, the combination is big enough to handle just about anything with having to do data management that you could ever want to throw at it.
Read about the download ACCNPNL2.ZIP on this page