Reports in a drop down list 4

[powerlock2005]

All,
I would like to put reports in a drop down list and once the person chooses the report of their choice it will open that report.

Is there an easy way to do this?

 

[RoyVidar]

This link Programmatically Populated Combo Box or List Box Is Not Sorted in the Order Expected contains a couple of methods.

For opening the selected report, have a look at the openreport method of the docmd object, and pass the combo selection (after update event of the combo).

[tt]docmd.openreport me!cmbReports, acviewpreview[/tt]

Roy-Vidar

 

[powerlock2005]

Hi Roy,
I applied the following code, but my reports are not showing up in the value list.

What am I supposed to change in the code to get my reports to show?

Please help if you can.

Thanks so much.

 

[RoyVidar]

Hard to say without knowing which code you're using, how you're using it, and how you've set up the combo... post all.

Roy-Vidar

 

[TheAceMan1]

or try this . . .

Set the following properties for the combobox:
[ol][li][blue]Column Count [purple]1[/purple][/blue][/li]
[li][blue]Column Heads [purple]No[/purple][/blue][/li]
[li][blue]Row Source Type [purple]Table/Query[/purple][/blue][/li]
[li][blue]Row Source [purple]SELECT DISTINCTROW Name FROM MSysObjects GROUP BY Name, Type HAVING (Type=-32764) ORDER BY Name;[/purple][/blue][/li]
[li][blue]Bound Column [purple]1[/purple][/blue][/li][/ol]
Thats it . . . .

See Ya! . . . . . .

 

[powerlock2005]

Sorry.

Here is the code I used:

Option Compare Database
Option Explicit

Dim strArray() As String

Function fncGetReports(Ctrl As Control, varID As Variant, _
varRow As Variant, varCol As Variant, varCode As Variant) _
As Variant

'Assign all report names to the combo box.
Dim proj As CurrentProject

Set proj = Application.CurrentProject

Select Case varCode
Case acLBInitialize
fncGetReports = True
Case acLBOpen
fncGetReports = Timer
Case acLBGetRowCount
fncGetReports = proj.AllReports.Count
Case acLBGetColumnCount
fncGetReports = 1
Case acLBGetColumnWidth
fncGetReports = -1
Case acLBGetValue
fncGetReports = strArray(varRow + 1)
End Select
End Function

Sub subAddItem(MyList() As String, MyItem As String)
ReDim Preserve MyList(UBound(MyList) + 1)
MyList(UBound(MyList)) = MyItem
End Sub

Private Sub Form_Load()
Dim obj As AccessObject
Dim proj As CurrentProject

ReDim strArray(0)

Set proj = Application.CurrentProject

'Search for open AccessObject objects in AllReports collection.
For Each obj In proj.AllReports
subAddItem strArray, obj.Name
Next obj

subSort strArray
End Sub

Sub subSort(MyList() As String)
Dim intRet As Integer, intCompare As Integer, intLoopTimes As Integer
Dim strTemp As String

For intLoopTimes = 1 To UBound(MyList)
For intCompare = LBound(MyList) To UBound(MyList) - 1
intRet = StrComp(MyList(intCompare), MyList(intCompare + 1), _
vbTextCompare)
If intRet = 1 Then 'Current string is greater than previous.
strTemp = MyList(intCompare)
MyList(intCompare) = MyList(intCompare + 1)
MyList(intCompare + 1) = strTemp
End If
Next
Next
End Sub

 

[powerlock2005]

I followed the first set of instructions from the microsoft link you gave me, and I didn't do anything else.

Hope this helps.

 

[RoyVidar]

Couldn't make that code work here eihter, and I'm not going to start debugging it;-)

Anyway, here's a quickie to be put in the forms on open/load event, which reads the reports (using redim preserve on the array, which is a recourse drag, but you wouldn't have thousands of reports?), puts them in an array, sort them, and uses the join function to add it to the rowsource of a combo. RowSourceType must be ValueList. Should work on Access 2000+ versions.

[tt] Dim rpt As Object
Dim strRpt() As String
Dim lngInner As Long
Dim lngOuter As Long
Dim strTmp As String
For Each rpt In CurrentProject.AllReports
ReDim Preserve strRpt(lngInner)
strRpt(lngInner) = rpt.Name
lngInner = lngInner + 1
Next rpt
For lngOuter = 0 To UBound(strRpt) - 1
For lngInner = lngOuter + 1 To UBound(strRpt)
If strRpt(lngOuter) > strRpt(lngInner) Then
strTmp = strRpt(lngOuter)
strRpt(lngOuter) = strRpt(lngInner)
strRpt(lngInner) = strTmp
End If
Next lngInner
Next lngOuter
Me!Combo13.RowSource = Join(strRpt, ";")
Erase strRpt[/tt]

The sysobjects approach is outlined in the same article, with the following warning " Although this method will work with Microsoft Access 2000, it is not a highly recommended method, as the structure of system tables may change within future versions of Access."

Roy-Vidar

 

[powerlock2005]

With the suggestion that AceMan1 provides I now get the list of reports, but I get a #Name? in the field and if I click the down arrow for the value list, I see the reports but I can't select any of them

Now what?

 

[powerlock2005]

Roy,
Do I put that code you provided on the Open event of each report I want to appear in the combo list?

 

[RoyVidar]

Anyway, here's a quickie to be put in the forms on open/load event" - the form on which the combo resides.

Roy-Vidar

 

[TheAceMan1]

[blue] I would like to put reports in a drop down list and [purple]once the person chooses the report of their choice it will open that report.[/purple][/blue]

If this is all you want, then in the [blue]AfterUpdate[/blue] event of the combo all you need is:

[blue]   DoCmd.OpenReport Me![purple][b]YourComboboxName[/b][/purple], acViewPreview[/blue]

[blue]I now get the list of reports, but I get a [purple]#Name?[/purple] in the field and if I click the down arrow for the value list, [purple]I see the reports but I can't select any of them[/purple][/blue]

Delete and reinstantiate the combobox (cancel the wizard and setup the properties as before).

BTW, for the [purple]#Name[/purple] you received, are you talking the [blue]textbox portion[/blue] of the combo?

and the combo is unbound . . . Yes?

See Ya! . . . . . .

 

[powerlock2005]

Ok guys I almost got it, but now the report is not activating when I try to choose from the drop down list.

Here is what I have done:

1. I put the following code in the OnOpen event of the form with the combo box:

Dim rpt As Object
Dim strRpt() As String
Dim lngInner As Long
Dim lngOuter As Long
Dim strTmp As String
For Each rpt In CurrentProject.AllReports
ReDim Preserve strRpt(lngInner)
strRpt(lngInner) = rpt.Name
lngInner = lngInner + 1
Next rpt
For lngOuter = 0 To UBound(strRpt) - 1
For lngInner = lngOuter + 1 To UBound(strRpt)
If strRpt(lngOuter) > strRpt(lngInner) Then
strTmp = strRpt(lngOuter)
strRpt(lngOuter) = strRpt(lngInner)
strRpt(lngInner) = strTmp
End If
Next lngInner
Next lngOuter
Me!cmbReports.RowSource = Join(strRpt, ";")
Erase strRpt

2. The control source of the combo box has this name from the Microsoft website above: fncGetReports

3. The Row Source Type has this: ValueList

4. The AfterUpdate event of the combo box has this: DoCmd.OpenReport Me!cmbReports, acViewPreview

That's it.

Again, when I try to choose one of the reports from the drop down list, it doesn't activate opening a report.

Please help.

Thanks,

 

[RoyVidar]

Don't know, perhaps some of the other settings of the combo (locked, enabled), or for the form? The After Update code doesn't trigger at all (put a breakpoint in there (F9 on an executable line))?

Roy-Vidar

 

[FancyPrairie]

I prefer having the rowsource of my listbox based on a query from a table (tblReportList). You will see, later, that the user will be able to select his/her report based on a description of the report rather than the ambiguous name of the report. Also, if you add reports to your database, you don't have to go back into your code, simply add it to your table.

1. Create Table

The table, tblReportList, contains 2 fields:

strReportName .... Text
strDescription ... Text

When you define the strReportName field within the table, select the Lookup tab at the bottom and select combobox from the Display Control property. Then in the RowSource property enter this SQL statement (basically what AceMan1 indicated)

SELECT [MSysObjects].[Name] FROM MSysObjects WHERE ([MSysObjects].[Type]=-32764) ORDER BY [MSysObjects].[Name];

2. Select your report and enter a description for it

Now, open the table. In the first field of the table (strReportName) you should be able to select the report you want from a drop down list. Then provide a meaningful description of the report to displayed to the user within the field strDescription.

3. Setup your list box on the form

In the RowSource of your List box on your form, enter this: "Select strReportName, strDescription from tblReportList Order By strDescription". Set the Column Count property to 2 and the Column Width property to "0;1"

4. Launch the report
In the AfterUpdate event of the listbox, enter this code:

    DoCmd.OpenReport lstReport.Column(0)

 

[GingerR]

I built just like powerlock has it, and it worked fine. Powerlock--try starting oever with a blank form?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[powerlock2005]

Yep, that was it....I had to create another form, and that did the TRICK!!!!!!

Everybody gets stars today

I appreciate everybody helping me today. I hope everyone has a great weekend!!!!!!!

 

[neobyte]

This might not be that sophisticated, and it could be affected by network config/policies depending on the location of the report, but it gives you options to include reports or files of any type from any location inside or outside the database. And is fast & almost code-free.

Create a table.

tblReports
Field - ID
Field - ReportDescrip(text)
Field - Report(hyperlink)

Create a query from this, ReportsList

To add a report, open the Reports table, right-click in a Report field (this is a hyperlink field, select Edit Hyper Link, Object in This Database, Reports - Your Report - to capture the report as a hyperlink. Then type a description in the ReportDescription.

Create a form from ReportsList query.

Drive ReportsListForm from a Command button, make it popup modal, and the list of reports shows up - you can set the form to prevent edits so users can't add/delete etc. Touch a Report field with the mouse & the hyperlink cursor is active, click to open the report

And if you want to add reports from outside the database (including the

http://WWW),

you can use Existing File or Webpage - after the hyperlink is set, you can edit the display text using the right click menu to filter out all the extraneuous url prefixes.

There's some linking to manage between the ReportsList form & the parent form, but you can set this up as a subform and allow others to add/view reports or files linked to specific records. And it allows you to run reports/files/images/video etc from any other location accessed by URL. Also runs apps.