Select Access Report in Combo box 2

[VictoryHighway]

Hello,
Is there a way to populate a combo box on an Access form with a list of the reports contained within the database so that the user can choose which report he wants to view? Also, is it possible to display the description of the report in the combo box?

Thanks in advance for your help.

--Geoffrey

 

[PHV]

Take a look at the CurrentProject.AllReports and AccessObject.Properties collections.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tonyflavell]

Hi there

I have always used a query which accesses the invisible "MSysObjects" table. Reports are [Type] -32764,[Flags] are <>8 and [Name] is obvious.

This is a bit of SQL I enter in the "Row Source" attribute of the Combo box. You should be able to work it out from the query below.

SELECT DISTINCTROW MSysObjects.Name
FROM MSysObjects
WHERE (((Left$([Name],4))="Memb" Or (Left$([Name],4))="NALA" Or (Left$([Name],4))="SALT" Or (Left$([Name],4))="CORP" Or (Left$([Name],4))="GROU" Or (Left$([Name],4))="Indi") AND ((MSysObjects.Type)=-32764) AND ((MSysObjects.Flags)<>8))
ORDER BY MSysObjects.Name;

This list all reports which start with "Memb", "NALA", "SALT", "CORP", "GROU" or "Indi".

Just ideas

Regards

Tony

 

[TheAceMan1]

How are ya VictoryHighway . . . .

Yes you can get report names & description. Just be aware, for the current method presented, [purple]neither field can display more than 255 characters, and total character count is limited to 2048 (thats total all report character counts + total all description character counts) .[/purple] So if you have enough reports with descriptions that exceed the 2048 limit, an independent table will be required. The following method is for use with the [blue]Value List property[/blue] of the combobox:

Combobox Properties should be as follows:
[ol][li]Row Source Type [purple]Value List[/purple][/li]
[li] Row Source [purple]nothing here[/purple][/li]
[li]Column Count [purple]2[/purple][/li]
[li]Column Heads [purple]No[/purple][/li]
[li]Column Widths [purple]1";2"[/purple][/li]
[li]List Width [purple]3"[/purple][/li][/ol]
Play with widths to get it all in the list proper.

Now, in the [blue]On Load Event[/blue] of the form, copy/paste the following code ([blue]You![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim db As dao.Database, con As dao.Container
   Dim doc As dao.Document, Nam As Collection
   Dim x As Integer, y As Integer, hld, Build As String
   
   
   Set db = CurrentDb()
   Set con = db.Containers("Reports")
   Set Nam = New Collection
   
   If con.Documents.Count <> 0 Then [green]'Any Reports?[/green]
      [green]'Load collection for sorting.[/green]
      For Each doc In con.Documents
         Nam.Add doc.Name
      Next
      
      [green]'Sort collection.[/green]
      For x = 1 To Nam.Count - 1
         For y = x + 1 To Nam.Count
            If Nam(y) < Nam(x) Then
               hld = Nam(x)
               Nam(x) = Nam(y)
               Nam(y) = hld
            End If
         Next
      Next
      
      On Error GoTo PadErr [green]'Trap no description error.[/green]
      
      [green]'Build rowsource for combobox.[/green]
      For x = 1 To Nam.Count
         Build = Build & Nam(x) & ";"
         Build = Build & con.Documents(Nam(x)).Properties("Description") & ";"
PadRtn:
      Next
      
      [green]'Update combobox rowsource.[/green]
      Me![purple][b]YourComboboxName[/b][/purple] = Null
      Me![purple][b]YourComboboxName[/b][/purple].RowSource = Left(Build, Len(Build) - 1)
   End If
   
   Set Nam = Nothing
   Set con = Nothing
   Set db = Nothing
   Exit Sub

PadErr: [green]'Set for no description[/green]
   Build = Build & ";"
   Resume PadRtn[/blue]

[purple]Thats it! . . . . give it a whirl and let us know . . .[/purple]

See Ya! . . . . . .

 

[VictoryHighway]

Thanks guys!

 

[VictoryHighway]

The Ace Man,
I just tried your method and works like a charm! Thanks man.

--Geoffrey