Reports in a List Box

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

First of all, want to say thanks for the guidance.

I tried this and I am getting a "Compile error: User-
defined type not defined" for the 'conn As
ADODB.Connection' statement. I am not too familar with
the ADO statement.

Thanks again in advance for your assistance.

m.
-----Original Message-----
Here is the procedure:

1. Create a table with the following fields:
Table Name: tblReportList
ReportID: AutoNumber - pk
ReportName: String

2. Populate table with your reports name.

3. Create a list box with the following:
ListBox Name: lstReportsMenu
Row sourceType: Table/ Query
Row Source: SELECT ReportName FROM tblReportList WHERE
reportName IS NOT NULL;

On the Double Click Event, enter the following code:

Private Sub lstReportsMenu_DblClick(Cancel As Integer)
If lstReportsMenu.Value <> " " Or lstReportsMenu.Value
<> "" Then
Dim conn As ADODB.Connection
Dim rstReportList As ADODB.Recordset
Set conn = CurrentProject.Connection
Set rstReportList = New ADODB.Recordset
rstReportList.Open "tblReportList",
conn, adOpenDynamic, adLockOptimistic

With rstReportList
Do While Not .EOF
If !rptListName=rptReportsMenu.Value
Then

DoCmd.OpenReport !rptListName,
acViewPreview

End If
.MoveNext
Loop
End With
Else
MsgBox "You must select a report you wish to
view", , "Reports"
End If

End Sub

Double clicking on a report will open the report. Remember
this will not open the datasheet type of reports.







.
Click to expand...
..
 
Mike:

1.) If you are getting that error, then your database is not using ADO as
its data access method, rather it is using DAO. (You can check this by
going to Tools->References when in a module and looking at the reference
items. If Microsoft Data Access Objects is listed then you're using DAO.
To fix the code then change these lines:

Dim conn As ADODB.Connection
To:

Dim db as DAO.Database
Dim rstReportList as DAO.Recordset
Set db = CurrentDb()
Set rstReportList = db.OpenRecordset("tblReportList", dbOpensnapshot)

2.) That said, I'm not sure why in the double click code the first responder
provided, they even look to the table to see if the report is listed. If
the list box is populated based on the table in the first place, then the
darn report should be there, all you need is

If Len(Me!rptListName)>0 Then _
DoCmd.OpenReport Me!rptListName, acViewPreview
 
Thanks. This got me pass the original error, but now I am
getting an "Item not found in this collection" error and
the "if !rptListName = rptReportsMenu.Value Then" line is
highlighted.

What am I doing wrong?

Thanks again for your time AND patience.
 
Back
Top