Best practice for running reports from a form? (1 Viewer)

[GingGangGoo]

I have a number of reports, all tied to various parameter queries and I would like to now make a switchboard form so that my end user can select a specific report and open it in print preview. This is a new area of db design for me and I'd like to make this as efficient as possible.

So first, I'm assuming I need to set up a table for report names and a query to grab those. From there I'm kind of hazy. I already have an unbound form so on that a combo box getting the report names from the query? An onevent routine which finds the report selected and opens it in print preview? I'm still a bit green with VBA, so any and all help here is much appreciated.

 

[sneuberg]

Access has a Allreports Collection. You could loop through that and build a Row Source for a combo box during a form open. The advantage is that the combo box would have all the reports if that's what you want. Another thing about this approach is that your can't sort a collection so if you want the report to appear in sort order you need to put them in an array and sort the array.

Here some code I used to build a combo box Row Source of forms which you could easily adapt for the AllReports collection. I didn't sort them in this case.

Private Function GetFormsRowSource() As String
   
Dim obj As AccessObject, dbs As Object
 
Set dbs = Application.CurrentProject
For Each obj In dbs.AllForms
    GetFormsRowSource = GetFormsRowSource & obj.Name & ";"
Next obj
If Len(GetFormsRowSource) > 0 Then
    GetFormsRowSource = Left(GetFormsRowSource, Len(GetFormsRowSource) - 1)
End If
 
End Function

Private Sub Form_Load()
 
Me.cboFormNames.RowSource = GetFormsRowSource
 
End Sub

 

[KenHigg]

You can create a linked config type table that would flag which reports are active etc.