Parameter issues

[tpickrel]

Hello
I have a combo box (EmployeeTimeInfoSelectFrmEmployeeCmb) that has 3 values, 1. EmployeeID (Bound), Lastname, Firstname,
When I select the lastname and enter the command to build a qry I get a parameter asking for the EmployeeID, When I Put double quotes around the combobox reference the qry works opens the form fine but puts the combobox reference into the qry string, then if I try and dump it to excel I get an To Few Parameters Expected 1 error (Red Letters Below). Any help sorry about the long code. The other combo boxes on the form work perfectly fine,

'This If Statement selects records by Employee ID Combo Box
If IsNull(Me.EmployeeTimeInfoSelectFrmEmployeeCmb) = False Or Me.EmployeeTimeInfoSelectFrmEmployeeCmb.Value <> "" Then
If IsNull(Me.EmployeeTimeInfoSelectFrmMonthInCmb) = True Or Me.EmployeeTimeInfoSelectFrmMonthInCmb.Value = "" Then
If IsNull(Me.EmployeeTimeInfoSelectFrmMonthOutCmb) = True Or Me.EmployeeTimeInfoSelectFrmMonthOutCmb.Value = "" Then

'This calls the RecordsByEmployeeID Function
If Not RecordsByEmployeeID(strSQL) Then
MsgBox "The is a problem with the code see the Database Administrator"
Exit Sub
End If

CurrentDb.QueryDefs("EmployeeTimeInfoSelectFrmQry").SQL = strSQL

DoCmd.OpenForm "EmployeeTimeInfoFrm", acNormal
End If
End If
End If






'This Function Builds my qry selected by the EmployeeId Combo box from the code above
Function RecordsByEmployeeID(strSQL As String)
Dim strSELECT As String
Dim strFROM As String
Dim strWhere As String

strSELECT = "s.* "
strFROM = "EmployeeHoursWorkedQry s "

'This line is where I am having a problem, if I leave as is it opens the qry as a parameter, I dont want that, but If I put quotes around the forms reference then
it opens the form fine. however see the note below
strWhere = strWhere & " AND s.EmployeeId = " & Forms!EmployeeTimeInfoSelectFrm.EmployeeTimeInfoSelectFrmEmployeeCmb
strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM

If strWhere <> "" Then strSQL = strSQL & "Where " & Mid$(strWhere, 6)
RecordsByEmployeeID = True
End Function




'This Code is the where I am trying to dump the data to Excel
Dim FlexTimeDB As Database
Dim EmployeeExportToExcelQry01 As Recordset
Dim ExcelBook As Object
Dim CurrentSystem, I, J As String


Set FlexTimeDB = CurrentDb
Set ExcelBook = CreateObject("excel.sheet")

ExcelBook.Application.Visible = True 'This makes the new Excel book visible
ExcelBook.Activate 'This line activate the new Excel Book for data insert

I = 2
J = 2
CurrentSystem = "1"

'When I try and send the data to Excel from the employeeID Combo box from above I get the too few parameters expect 1 error
Set EmployeeExportToExcelQry01 = FlexTimeDB.OpenRecordset("Select * From [EmployeeExportToExcelQry] ORDER BY [EmployeeId],[TimeIn],[TimeOut]")




Ignore these lines below they work fine!!!!


Do Until EmployeeExportToExcelQry01.EOF 'This checks entire table
If EmployeeExportToExcelQry01("EmployeeId") <> CurrentSystem Then 'This checks all employee Ids for new worksheets
ExcelBook.worksheetS(1).range("E2:l" & I - 1).NumberFormat = "m/d/yyyy h:mm:ss AM/PM" 'This creates the formatting for the TimeIn Data
ExcelBook.worksheetS(1).range("F2:l" & J - 1).NumberFormat = "m/d/yyyy h:mm:ss AM/PM" 'This creates the formatting for the TimeOut Data
ExcelBook.worksheetS(1).range("G2:l" & J - 1).NumberFormat = "##.#0" 'This creates the formatting for the TotalHours Data
ExcelBook.worksheetS(1).Columns("A:H").AutoFit 'This autofits the cell width to the value length
ExcelBook.worksheetS.Add ExcelBook.Sheets(1), , Count:=1 'This adds new worksheet for each EmployeeID Selected
ExcelBook.worksheetS(1).Name = EmployeeExportToExcelQry01("Lastname") 'This puts the Employee Lastname in the worksheet title
ExcelBook.worksheetS(1).Activate 'This Activates each worksheet
ExcelBook.worksheetS(1).cells(1, 1).Value = "Employee ID" 'This creates the Cell Title for the EmployeeID Data
ExcelBook.worksheetS(1).cells(1, 2).Value = "Lastname" 'This creates the Cell Title for the Lastname Data
ExcelBook.worksheetS(1).cells(1, 3).Value = "Firstname" 'This creates the Cell Title for the Firstname Data
ExcelBook.worksheetS(1).cells(1, 4).Value = "Department" 'This creates the Cell Title for the Department Data
ExcelBook.worksheetS(1).cells(1, 5).Value = "Time In" 'This creates the Cell Title for the Time In Data
ExcelBook.worksheetS(1).cells(1, 6).Value = "Time Out" 'This creates the Cell Title for the Time Out Data
ExcelBook.worksheetS(1).cells(1, 7).Value = "Total Hours" 'This creates the Cell Title for the Total Hours Data
ExcelBook.worksheetS(1).cells(1, 8).Value = "Notes" 'This creates the Cell Title for the Notes Data
CurrentSystem = EmployeeExportToExcelQry01("EmployeeId") 'This checks for a new EmployeeID change
I = 2
J = 2
Else
End If
ExcelBook.worksheetS(1).cells(I, 1).Value = EmployeeExportToExcelQry01("EmployeeId") 'This the EmployeeID Data into the cell
ExcelBook.worksheetS(1).cells(I, 2).Value = EmployeeExportToExcelQry01("Lastname") 'This the Lastname Data into the cell
ExcelBook.worksheetS(1).cells(I, 3).Value = EmployeeExportToExcelQry01("Firstname") 'This the Firstname Data into the cell
ExcelBook.worksheetS(1).cells(I, 4).Value = EmployeeExportToExcelQry01("Department") 'This the Department Data into the cell
ExcelBook.worksheetS(1).cells(I, 5).Value = EmployeeExportToExcelQry01("TimeIn") 'This the Time In Data into the cell
ExcelBook.worksheetS(1).cells(I, 6).Value = EmployeeExportToExcelQry01("TimeOut") 'This the Time Out Data into the cell
ExcelBook.worksheetS(1).cells(I, 7).Value = EmployeeExportToExcelQry01("TotalHours") 'This the Total Hours Data into the cell
ExcelBook.worksheetS(1).cells(I, 8).Value = EmployeeExportToExcelQry01("Notes") 'This the Notes Data into the cell
I = I + 1
J = J + 1
EmployeeExportToExcelQry01.MoveNext 'This moves to the next cell for data insertion
Loop
ExcelBook.worksheetS(1).range("E2:l" & I - 1).NumberFormat = "m/d/yyyy h:mm:ss AM/PM" 'This checks the formatting for TimeIn field in each worksheet
ExcelBook.worksheetS(1).range("F2:l" & J - 1).NumberFormat = "m/d/yyyy h:mm:ss AM/PM" 'This checks the formatting for TimeOut field in each worksheet
ExcelBook.worksheetS(1).range("G2:l" & J - 1).NumberFormat = "##.#0" 'This creates the formatting for the TotalHours Data
ExcelBook.worksheetS(1).Columns("A:H").AutoFit 'This sets the autofit for each worksheet
On Error GoTo Save_err
ExcelBook.SaveAs "Employee Records Book 1" 'This saves the new Excel Book to the Local Documents Folder as "Employee Records Book 1"
ExcelBook.Application.Visible = True 'This makes the Excel Book Visible
Save_err:
If Err.Number = "1004" Then
MsgBox "Workbook Closed", vbOKOnly, "Error Check"
Else
Resume SaveError_Exit:
SaveError_Exit:
End If
End Function

 

[ajetrumpet]

please format your code. use the tags. it is absolutely impossible to read...

 

[tpickrel]

please format your code. use the tags. it is absolutely impossible to read...

I hope that helps unless there is another way to format
I tried to indent but it doesnt stay.