Reports in a List Box

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

Mike

Hello.

I have a database that contains a LOT of reports. I have
it set up so that the user can get to them one way or
another. I was just wondering if it was at all possible
to set up a list box that list the reports and if the user
clicks on the desired selection, the reports opens up and
if so, how?

And just to be greedy, is it possible to make it so that
if the user just clicks on it once, a description pops up
of what the report is, THEN if they double click on it, it
opens up.

I know this may be stretching it, but just that it would
be a nice feature to have. Any assistance would be
appreciated.

Thanks in advance,

Mike
 
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.
 
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.
 
Back
Top