Export to excel using nested query as recordset? (1 Viewer)

marcadam

New member
Local time
Today, 01:49
Joined
Feb 4, 2018
Messages
2
Hi everyone, this it my first post for help, I pride myself on being able to google and read up to solve problems but this one has got me beat. I am in the final stages of building an access database for my company, part of which involves two tables [StaffDetails] and [Holidays]. The junior managers will be using the db to approve and record staff holidays, once all holidays have been entered they press a button and the names & details of the staff are sent across to the excel spreadsheet they use for producing the rotas for a given period.

Everything works great until I try exporting to excel, I keep getting the 'too few parameters error'. I have tried exporting using the two queries independently without out issue, so I either get staff details (PRNo, name, contracted hours etc) or holiday details (PRNo, dates, hours taken) but not both:banghead:

The code below works as intended when using a straight up selct query, but when I add in the holidays it won't play ball. Any help would be greatly appreciated, thanks.

Code:
SELECT tblstaffdetails.firstname, tblstaffdetails.hours, tbllocations.areaid, tblstaffdetails.prno, tblstaffdetails.dbs, tblstaffdetails.ref1, tblstaffdetails.ref2, tblstaffdetails.idphotos, tblstaffdetails.leavedate, tblstaffdetails.emailaddress, qryholidaysheetnametest.holidaydates, qryholidaysheetnametest.hourstaken, qryholidaysheetnametest.sheetname 
FROM   (tbllocations 
        INNER JOIN tblstaffdetails 
                ON tbllocations.areaid = tblstaffdetails.[basearea]) 
       LEFT JOIN qryholidaysheetnametest 
              ON tblstaffdetails.prno = qryholidaysheetnametest.prno 
WHERE  ( ( ( tbllocations.areaid ) = "solihull" 
            OR ( tbllocations.areaid ) = "solihull" ) 
         AND ( ( tblstaffdetails.dbs ) IS NOT NULL ) 
         AND ( ( tblstaffdetails.ref1 ) IS NOT NULL ) 
         AND ( ( tblstaffdetails.ref2 ) IS NOT NULL ) 
         AND ( ( tblstaffdetails.leavedate ) IS NULL 
                OR ( tblstaffdetails.leavedate ) > Now() ) ) 
ORDER  BY tblstaffdetails.prno;


Code:
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
Dim Period As String

blnEXCEL = False

On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

xlx.Visible = True

Period = [TempVars]![SheetName]

Set xlw = xlx.Workbooks.Open("Z:\Rota\Test.xlsm")

Set xls = xlw.Worksheets([Period])

Set xlc = xls.Range("A1")

Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset("qryRotaSolihull", dbOpenDynaset, dbReadOnly)

If rst.EOF = False And rst.BOF = False Then

      rst.MoveFirst

      If blnHeaderRow = True Then
            For lngColumn = 0 To rst.Fields.Count - 1
                  xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
            Next lngColumn
            Set xlc = xlc.Offset(1, 0)
      End If

      ' write data to worksheet
      Do While rst.EOF = False
            For lngColumn = 0 To rst.Fields.Count - 1
                  xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
            Next lngColumn
            rst.MoveNext
            Set xlc = xlc.Offset(1, 0)
      Loop

End If

rst.Close
Set rst = Nothing

dbs.Close
Set dbs = Nothing

' Close the EXCEL file while saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close True   ' close the EXCEL file and save the new data
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:49
Joined
Aug 30, 2003
Messages
36,118
Do any of the queries have form parameters? If so, simplest solution is to wrap each in the Eval() function.
 

marcadam

New member
Local time
Today, 01:49
Joined
Feb 4, 2018
Messages
2
Do any of the queries have form parameters? If so, simplest solution is to wrap each in the Eval() function.

Thanks for pointing that out, I was definitely overthinking this problem and couldn't see the wood for the trees. The simple solution was Eval("[TempVars]![SheetName]"):D
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:49
Joined
Aug 30, 2003
Messages
36,118
No problem and welcome to the site by the way!
 

Users who are viewing this thread

Top Bottom