Loop through table

  • Thread starter Thread starter Jobob
  • Start date Start date
J

Jobob

I have a form that drives a query that drive a report. The form is frmwall,
which drives query EA, which pulls report EA. The form passes a between data
parameter and a facility parameter to the query.

To run the report, I have a date parameter that dynamically populates based
on the most recent date in the DB, and then I select the facilities from a
combo box on the form (comb box = facility). What I would like to do is have
the VB loop through a table I have to select the facilities instead of having
to flip through the combo box and print all the reports.

I have a table (tblfacilities) that has a facility column that I use to
populate the combo box and join the table.

Here is the current SQL of the query:
SELECT [EA-1].Date, [sumofcost]/[sumofdollars] AS [%ofSales], [EA-1].Target,
[EA-1].Stretch, [EA-1].Facility
FROM [EA-1] INNER JOIN [EA-2] ON ([EA-1].Facility = [EA-2].Facility) AND
([EA-1].Date = [EA-2].Date)
WHERE ((([EA-1].Date) Between [Forms]![frmWall]![c100S] And
[Forms]![frmWall]![c100E]) AND
(([EA-1].Facility)=[Forms]![frmWall]![fACILITY]));

Any help would be greatly appreciated!
 
okay, so you have a block of code that prints the report for the facility
chosen in the combobox control, correct. and you have a RowSource for the
combobox control, either a table or a query or a SQL statement, correct?

you can write a procedure to open a recordset based on the same dataset
returned by the combobox control's RowSource, then loop through the records
in the recordset and run the print code in each loop pass. in the following
example, i'll say that qryFacilities is a query used as the combobox
control's RowSource, FacilityID is the primary key field in the facilities
table, and fkFacilityID is the foreign key field in the report's
RecordSource:

Dim rst As DAO.Recordset, strQueryName As String

Set rst = CurrentDb.OpenRecordset(strQueryName, dbOpenDynaset)

If rst.BOF And rst.EOF Then
MsgBox "There are no facilities available, sorry."
Else
rst.MoveLast
rst.MoveFirst
Do
'<put here the code that prints the report, replacing the
' combobox reference in the WHERE criteria with a
' recordset reference, see example next line>
DoCmd.OpenReport "EA", , , "fkFacilityID = " & rst("FacilityID")
Loop Until rst.EOF
End If

rst.Close
Set rst = Nothing

hth
 
Back
Top