Navigation:  Articles > Nov-1998 >

An Access Control Panel, Part 2

Previous pageReturn to chapter overviewNext page

Ivor Davies          
 
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.

199811_id1 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.
 

199811_id2 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