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.
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