Automating Multiple reports via a ComboBox filter

[Tom72Bus]

Hi,

I've built a database which produces several different reports, by using combo box's to specify certain requirements.

These will now be published, but will need to be on seperate pdf's by one of the critera's in the combo box - "area". What's the best way to approach this?

My initial thoughts are to write a code which then input's each of the different Area's into the combo box in series. The only gripe would be maintenance of this, new area's spring up regaularly so it would be a manual process to continually change them. I've got a table with the areas in, is there a way of getting a code to look through a table and enter the amounts into the combo box one by one?

Thanks - any help, pointers or suggestions would be greatly appreciated!

Tom

 

[MarkK]

Check out the RowSource property of a ComboBox control. Set this property to a SQL expression and the elements of the combo will be drawn directly from sources specified by that SQL, including tables.
Other related properties you probably need to set doing this job are, RowSourceType, ColumnCount, ColumnWidth.
You may also want to set ListRows and ListWidth.
Cheers,

 

[Tom72Bus]

Hi,

Thanks lagbolt, the Rowsource for the ComboBox SQL is as below

SELECT DISTINCT Tbl_Areas.Area
FROM Tbl_Areas
ORDER BY Tbl_Areas.Area;

And i've got the existing on click code,

Private Sub Rpt_AllArea_Click()
On Error GoTo Err_Rpt_AllArea_Click
    Dim stDocName As String
    stDocName = "Rpt_AllAreabyReg"
    DoCmd.OpenReport stDocName, acPreview
Exit_Rpt_AllArea_Click:
    Exit Sub
Err_Rpt_AllArea_Click:
    MsgBox Err.Description
    Resume Exit_Rpt_AllArea_Click
 
End Sub

I've tried applying the SQL statement via RunSQL command, but i've read that I can't use RunSQL for select's. What could I use in the above code so that it runs through all of the top SQL statement and opens a report for each Area (22 reports in total)?

 

[MarkK]

Maybe I've misunderstood. I thought you were having trouble populating a combo box with values. I don't understand what you mean by 'applying the SQL'. Applying it to what?
Also, if you want to 'run through' a list then you need a loop structure, maybe For...Next or Do...Loop.
If you want to open the same report with different criteria each time, then you might need to open a recordset and use a loop....

Sub OpenReportsUsingLoop()
   Dim rst As DAO.Recordset
   Set rst = CurrentDb.OpenRecordset( _
      "SELECT Field FROM Table;")
   With rst
      Do While Not .EOF
[COLOR="Green"]         'open report with where condition using data from recordset[/COLOR]
         DoCmd.OpenReport "YourReport", acViewPreview, , "Area = " & !field
         .MoveNext
      Loop
      .Close
   End With
End Sub