Ok.
Preperation:
Create a query that groups records by company who have at least one car in for service for the time period specified. This will act as a parent query. Referred to as QryComp
Create a query grouped by company by car with the information in it that you want to send to Excel. Referred to As QryCars
Ensure that you have Microsoft DAO referenced on your mdb.
Ensure that you have Microsoft Excel Objects referenced in your mdb.
Step 1:
Create a public function in a standard module named ModExcel
Step2:
Create a public function within this module with the following metacode
Code:
Public Function ExportToExcel()
Dim RsComp As DAO.Recordset
Dim RsCars As DAO.Recordset
Dim CompID As Long
Dim CarsSQL As String
Set RsComp = CurrentDb.OpenRecordset("QryComp")
If Not RsComp.EOF And Not RsComp.BOF Then
Do Until RsComp.EOF
CompID = RsComp("CompanyID")
'For testing purposes
Debug.Print "Company Id" & vbTab & CompId
CarsSQL = "Select * From QryCars Where CompanyID = " CompId
Set RsCars = CurrentDb.OpenRecordset(CarsSQL)
If Not RsCars.EOF And Not RsCars.BOF Then
Do Until RsCars.EOF
'For Testing Purposes
Debug.Pring "Vehicles " & RsCars("CompId") & vbTab & RsCars("VehicleID")
RsCars.MoveNext
Loop
End If
RsCars.Close
RsComp.MoveNext
Loop
RsComp.Close
End If
Set RsComp = Nothing
Set RsCars = Nothing
End Function
Use the immediate window to test the results
You should get:
Company Id 1
Vehicle 1 abc123
Vehicle 1 xcd234
etc
Company Id 2
Vehicle 2 fgrr 567
Vehicle 2 ptg 683
etc
When you have achieved this then get back for the next stage
David