word automation - sending multiple records to a table in word

[mazza]

I am trying to create a word document combining varous records sets from Acces.
The first part is easy as my first record set only produces 1 record and I use book marks to populate word.
The problem I have is when I have a variable number of records, in my code here sqlservicetask I only need 2 fields (TblCheckList.item, TblCheckList.code) per record in a table format at the end (prefereable a a new page) of my document. the number of records varies depending on my input parameters.

As you can see from the code below I stopped when I came to opening the datastring sqlservicetask as i have no idea how to tackle this


could anybody help me in the right direction how to tackle this


code so far
Function CreateServiceJobSheet()
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Dim sqlservicetask As String
Dim WordObj As Object

Set WordObj = CreateObject("Word.Application")
WordObj.Visible = True

WordObj.Documents.Add "c:\program files\compair service management\servicejob.dot"

Set db = CurrentDb()
sql = "SELECT TblServiceJobDetails.ServiceId, TblServiceJobDetails.visitdate, TblServiceJobDetails.JobNumber, TblServiceJobDetails.JobType, " & _
"TblServiceJobDetails.servicehrs, TblServiceJobDetails.ShortJobDesc, TblServiceJobDetails.remarks, TblJobStatus.Status, TblServiceJobDetails.SerialNumber," & _
"TblServiceJobDetails.ProdDescr, TblServiceJobDetails.SiteContact, TblServiceJobDetails.CustomerId, TblServiceJobDetails.CustomerName, " & _
"TblServiceJobDetails.CustomerAddress, TblServiceJobDetails.CustomerTown, TblServiceJobDetails.CustomerPostCode, TblServiceJobDetails.CustomerTel, " & _
"TblServiceJobDetails.Customercontact, TblServiceJobDetails.CustomerOrder, TblServiceJobDetails.CustomerOrderDate, TblServiceJobDetails.servicecontractNr," & _
"TblServiceJobDetails.solution, TblServiceJobDetails.Partordernr, TblServiceJobDetails.OilCarbon, TblServiceJobDetails.oiltmake, TblServiceJobDetails.oiltype, " & _
"TblServiceJobDetails.oilgrade , TblServiceJobDetails.meter, TblServiceJobDetails.cooling, TblServiceJobDetails.comproomtemp, TblServiceJobDetails.compressortemp , " & _
"TblServiceJobDetails.MotorSerial, TblServiceJobDetails.motortype, TblServiceJobDetails.switchgearmake, TblServiceJobDetails.lastchangeoil, " & _
"TblServiceJobDetails.lastchangeoilfilter, TblServiceJobDetails.lastchangeairfilter, TblServiceJobDetails.lastchangeseparator, " & _
"TblServiceJobDetails.tempambient, TblServiceJobDetails.tempintake, TblServiceJobDetails.Tempoutlet, TblServiceJobDetails.tempCompr, " & _
"TblServiceJobDetails.environment, TblServiceJobDetails.steamclean, TblServiceJobDetails.motorpulleysize, TblServiceJobDetails.CompPulleysize, " & _
"TblServiceJobDetails.typeofvbelts, TblServiceJobDetails.NoofVbelts, TblServiceJobDetails.lenghtofvbelts, TblServiceJobDetails.emotorload, " & _
"TblServiceJobDetails.emotornoload, TblServiceJobDetails.voltage, TblServiceJobDetails.ServiceEngineer, TbLEngineer.Name " & _
"FROM (TblServiceJobDetails INNER JOIN TblJobStatus ON TblServiceJobDetails.status = TblJobStatus.StId) INNER JOIN TbLEngineer ON TblServiceJobDetails.ServiceEngineer = TbLEngineer.EngineerCode " & _
"WHERE (((TblServiceJobDetails.ServiceId)= " & [Forms]![Frmservicejobdetails]![serviceId] & "));"

Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

With WordObj.ActiveDocument.Bookmarks 'Using the info in the Access form, populate the template

.Item("BKJobno").Range.Text = rs!Jobnumber
.Item("BKCustname").Range.Text = rs!Customername
.Item("BKAddress").Range.Text = rs!CustomerAddress
.Item("BKTown").Range.Text = rs!CustomerTown
.Item("BKPostcode").Range.Text = rs!CustomerPostCode
.Item("BKTel").Range.Text = rs!CustomerTel
.Item("BKContact").Range.Text = rs!Customercontact
.Item("BKOrder").Range.Text = rs!CustomerOrder
.Item("BKContract").Range.Text = rs!servicecontractNr
.Item("BKSerial").Range.Text = rs!SerialNumber

End With

sqlservicetask = "SELECT TblCheckList.item, TblCheckList.code, TblCheckList.modeltype, TblCheckList.serv1, TblCheckList.serv2, TblCheckList.serv3, TblCheckList.serv4 " & _
"FROM TblCheckList " & _
"WHERE (((TblCheckList.code) = [Forms]![Frmservicejobdetails]![TxtServDesc]) And ((TblCheckList.modeltype) = [Forms]![Frmservicejobdetails]![TxtType])) " & _
"Or (((TblCheckList.modeltype) = [Forms]![Frmservicejobdetails]![TxtType]) And ((TblCheckList.serv1) = [Forms]![Frmservicejobdetails]![TxtServDesc])) " & _
"Or (((TblCheckList.modeltype) = [Forms]![Frmservicejobdetails]![TxtType]) And ((TblCheckList.serv2) = [Forms]![Frmservicejobdetails]![TxtServDesc])) " & _
"Or (((TblCheckList.modeltype) = [Forms]![Frmservicejobdetails]![TxtType]) And ((TblCheckList.serv3) = [Forms]![Frmservicejobdetails]![TxtServDesc])) " & _
"Or (((TblCheckList.modeltype) = [Forms]![Frmservicejobdetails]![TxtType]) And ((TblCheckList.serv4) = [Forms]![Frmservicejobdetails]![TxtServDesc])) " & _
"ORDER BY TblCheckList.code;"

Set rs = db.OpenRecordset(sqlservicetask, dbOpenDynaset, dbSeeChanges)



WordObj.ActiveDocument.SaveAs rs!Jobnumber & ".doc"