Microsoft KB Archive/150058

= ACC95: Incorrect GetAccessReportList in "Building Applications" =

Article ID: 150058

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Excel 95 Standard Edition

-



This article was previously published under Q150058



SYMPTOMS
Advanced: Requires expert coding, interoperability, and multiuser skills.

The GetAccessReportList procedure on page 288 of the "Building Applications with Microsoft Access for Windows 95" manual causes the error "Object doesn't support this property or method." (run-time error 438)



CAUSE
This procedure refers to a variable called "dbs" but fails to declare and set this variable. Also, the "and" condition on the first If statement refers to "mdb" but should refer to "mda." Finally, the text argument of the AddItem method should be specified as a named argument.



RESOLUTION
This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access for Windows 95" manual.

The following steps show how to use the GetAccessReportList procedure in Microsoft Excel. The corrected GetAccessReportList procedure is included in these steps:  Start Microsoft Excel (version 5.0 or later). On the Insert menu, select Macro, and then click Module to create a new module within the workbook.  Type the following code in the module, which includes the corrected GetAccessReportList procedure: Option Explicit

Dim objAccess As Object

Sub GetAccessReportList Dim dbs As Object Dim strDBName As String Dim intReport As Integer

Set objAccess = CreateObject("Access.Application.7")

strDBName = DialogSheets(1).EditBoxes(1).Text If Right$(strDBName, 4) <> ".mdb" And _ Right$(strDBName, 4) <> ".mda" Then strDBName = strDBName & ".mdb" End If

With objAccess .OpenCurrentDatabase (strDBName) Set dbs = .DBengine(0)(0) With dbs.Containers("Reports") For intReport = 0 To .Documents.Count - 1 If Left$(.Documents(intReport).Name, 4) <> "~TMP" Then DialogSheets(1).ListBoxes(1).AddItem Text:= _ .Documents(intReport).Name End If            Next intReport End With End With End Sub

Sub Main DialogSheets(1).ListBoxes(1).RemoveAllItems DialogSheets(1).Show End Sub  On the Insert menu, select Macro, and then click Dialog to create a new dialog within the workbook. Use the Edit Box button from the Forms toolbox to draw an edit box inside the Dialog frame. (After clicking the Edit Box button, you must click and drag inside the Dialog frame to draw the edit box.) Use the List Box button to draw a list box inside the Dialog frame.</li> Using the right mouse button, click OK within the Dialog frame, and then click Format Object on the menu that appears.</li> Click to clear the Dismiss check box, and then click OK.</li> Using the right mouse button, click OK within the Dialog frame, and then click Assign Macro on the menu that appears.</li> Select GetAccessReportList and click OK.</li> Click the Sheet1 tab to activate a worksheet within the workbook.</li> On the Tools menu, click Macro.</li> Select Main and click Run. Your custom dialog box should appear.</li> Type a valid path and database name in the edit box and click OK, for example:

C:\Msoffice\Access\Samples\Northwind.mdb

</li></ol>

A list of reports should appear in the list box. Click Cancel to close.

Alternative GetAccessReportList Procedure
Below is a slightly faster version of the GetAccessReportList procedure: Sub GetAccessReportList Dim lstReports As list box Dim strDBName As String Dim intReport As Integer Dim docReports As Object Dim intCount As Integer Dim strDoc As String

Set lstReports = DialogSheets(1).ListBoxes(1) Set objAccess = CreateObject("Access.Application.7")

strDBName = DialogSheets(1).EditBoxes(1).Text If Right$(strDBName, 4) <> ".mdb" And _ Right$(strDBName, 4) <> ".mda" Then strDBName = strDBName & ".mdb" End If

objAccess.OpenCurrentDatabase strDBName With objAccess.DBengine(0)(0) Set docReports = .Containers("Reports").Documents intCount = docReports.Count - 1 For intReport = 0 To intCount strDoc = docReports.Item(intReport).Name If Left$(strDoc, 4) <> "~TMP" Then lstReports.AddItem Text:=strDoc End If      Next End With End Sub

<div class="references_section">