for all that is good help!

[hamrthroer]

I have this module to export a spreadsheet. The issue is with the SQL statement. I have tried both the stored query and using it in code. When I take out the reference to forms!frmMain!cboRptShift the export is successfull. I need this reference to correctly create the export as well as another reference that I haven't even tried yet because I've spent over 18 hours on this issue literally. I've read about this issue but still cannot find a solution. Here is the module:

Public Function ExportOvertime(strTQName As String, strSheetName As String, strFilePath As String)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
 
' strFilePath is the name and path of the file you want to send this data into.
 
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As Field
Dim DB As Database
Dim MyQDef As QueryDef
Dim strSQL As String
 
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
'On Error GoTo err_handler
 
strSQL = "SELECT tblStaff.StateEntryDate" & vbNewLine
strSQL = strSQL & " , tblStaff.LastName" & vbNewLine
strSQL = strSQL & " , tblStaff.FirstName" & vbNewLine
strSQL = strSQL & " , tblStaff.MI" & vbNewLine
strSQL = strSQL & " , tblStaff.PriPhone" & vbNewLine
strSQL = strSQL & " FROM tblStaff" & vbNewLine
strSQL = strSQL & " WHERE (((tblStaff.Shift)=[Forms]![frmMain]![cboRptShift]) " & vbNewLine
strSQL = strSQL & " AND ((tblStaff.Rank)=""COI"" " & vbNewLine
strSQL = strSQL & " OR (tblStaff.Rank)=""COII"" " & vbNewLine
strSQL = strSQL & " OR (tblStaff.Rank)=""COS"") " & vbNewLine
strSQL = strSQL & " AND ((tblStaff.WorkOT)=Yes))" & vbNewLine
strSQL = strSQL & " ORDER BY tblStaff.StateEntryDate" & vbNewLine
strSQL = strSQL & " , tblStaff.Random;"
 
 
strPath = "X:\Overtime Spreadsheet\OvertimeCallDown"
Set rst = CurrentDb.OpenRecordset(strSQL)
 
 
 
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True
Set xlWSh = xlWBk.Worksheets("OvertimeLocalShift")
xlWSh.Range("A2").Select
 
 
rst.MoveFirst
xlWSh.Select
xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select
 
xlWSh.Range("A2").Select
rst.Close
Set rst = Nothing
Exit Function
'err_handler:
'DoCmd.SetWarnings True
'MsgBox Err.Description, vbExclamation, Err.Number
'Exit Function
End Function

 

[jzwp22]

Since the form is supplying a variable, it cannot be included within the string


You'll need to add a double quote and the & as shown in red; you also need to remove the ending double quote mark

strSQL = strSQL & " WHERE (((tblStaff.Shift)=" & [Forms]![frmMain]![cboRptShift] & vbNewLine

I would recommend using a debug.print strSQL statement after the query is built. This will put the actual SQL text to the immediate window and you can see exactly how the query is created.

If this function is executed from frmMain, you can use the shorthand notation to reference the control

strSQL = strSQL & " WHERE (((tblStaff.Shift)=" & me.[cboRptShift] & vbNewLine

 

[boblarson]

And when you post code like that use CODE TAGS...

See here how to do it:

 

[hamrthroer]

You are a life saver! I had to add a ) in there and voila! My next torture oportunity is to add another variable on the same form titled weekday but I think this lesson should guide me through it. I'll post an update when all is complete.

thanks again!

PS: I will use the code tags as suggested. Newbies!

 

[hamrthroer]

False alarm. The export executes but does not recognize the variable supplied by the form. Any ideas? BTW I am calling this using:

Call ExportOvertime("strSQL", "OvertimeLocalShift", "X:\Overtime Spreadsheet\OvertimeCallDown")

 

[JANR]

I think you must take the form refrence out of the string expression.

....
strSQL = strSQL & " FROM tblStaff" & vbNewLine
strSQL = strSQL & " WHERE (((tblStaff.Shift)= " [COLOR=red]& [Forms]![frmMain]![cboRptShift][/COLOR]
strSQL = strSQL & " AND ((tblStaff.Rank)=""COI"" " & vbNewLine
...

JR

Edit:

Try and use Debug.Print strSQL after the sqlstring is set and you will often spot troublesom syntax.

ps. I assume that cboRptShift is a number and not text.

 

[hamrthroer]

I'm not understanding (big suprise)...

If I take out the form reference then there is no way to offer the user an option for differenciating which shift they want to export.

I also tried using

 Debug.Print strSQL

and I get nothing. Should I place it after I set strSQL?

 

[JANR]

...
StrSQL = " SELECT......."
StrSQL = StrSQL & " FROM....."
StrSQL = StrSQL & " WHERE....."
Debug.Print StrSQL

This prints the sqlstring to the Immediate Window where you can see if the string looks ok. You can also use

MsgBox StrSQL

which prompts you with a messagebox with your stringexpression as you execute your sub/function

JR

 

[jzwp22]

You will have to bring in the value from the form using the function call. I assume that you are calling the function from the form:

Call ExportOvertime(me.cboRptShift,"strSQL","OvertimeLocalShift", "X:\Overtime Spreadsheet\OvertimeCallDown")


You will then have to modify the code of your function

Public Function ExportOvertime([COLOR="Red"]myID as long,[/COLOR] strTQName As String, strSheetName As String, strFilePath As String)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
 
' strFilePath is the name and path of the file you want to send this data into.
 
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As Field
Dim DB As Database
Dim MyQDef As QueryDef
Dim strSQL As String
 
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
'On Error GoTo err_handler
 
strSQL = "SELECT tblStaff.StateEntryDate" & vbNewLine
strSQL = strSQL & " , tblStaff.LastName" & vbNewLine
strSQL = strSQL & " , tblStaff.FirstName" & vbNewLine
strSQL = strSQL & " , tblStaff.MI" & vbNewLine
strSQL = strSQL & " , tblStaff.PriPhone" & vbNewLine
strSQL = strSQL & " FROM tblStaff" & vbNewLine
strSQL = strSQL & " WHERE (((tblStaff.Shift)="[COLOR="Red"]& myID[/COLOR] & ") " & vbNewLine
strSQL = strSQL & " AND ((tblStaff.Rank)=""COI"" " & vbNewLine
strSQL = strSQL & " OR (tblStaff.Rank)=""COII"" " & vbNewLine
strSQL = strSQL & " OR (tblStaff.Rank)=""COS"") " & vbNewLine
strSQL = strSQL & " AND ((tblStaff.WorkOT)=Yes))" & vbNewLine
strSQL = strSQL & " ORDER BY tblStaff.StateEntryDate" & vbNewLine
strSQL = strSQL & " , tblStaff.Random;"
 
 
strPath = "X:\Overtime Spreadsheet\OvertimeCallDown"
Set rst = CurrentDb.OpenRecordset(strSQL)
 
 
 
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True
Set xlWSh = xlWBk.Worksheets("OvertimeLocalShift")
xlWSh.Range("A2").Select
 
 
rst.MoveFirst
xlWSh.Select
xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select
 
xlWSh.Range("A2").Select
rst.Close
Set rst = Nothing
Exit Function
'err_handler:
'DoCmd.SetWarnings True
'MsgBox Err.Description, vbExclamation, Err.Number
'Exit Function
End Function

 

[JANR]

I see that jzwp2 gave you the same answer as mine, but anyhow here is the StrSQL my Immediate window gave me:

SELECT tblStaff.StateEntryDate
 , tblStaff.LastName
 , tblStaff.FirstName
 , tblStaff.MI
 , tblStaff.PriPhone
 FROM tblStaff
 WHERE (((tblStaff.Shift)=[Forms]![frmMain]![cboRptShift]) 
 AND ((tblStaff.Rank)="COI" 
 OR (tblStaff.Rank)="COII" 
 OR (tblStaff.Rank)="COS") 
 AND ((tblStaff.WorkOT)=Yes))
 ORDER BY tblStaff.StateEntryDate
 , tblStaff.Random;

Do you spot any problem, also if you paste this string in a blank querySQL does it run as expected?

JR

 

[jzwp22]

Out of curiosity, in your function call you have strSQL, but you are constructing the query in the body of the function, so do you need it?

Call ExportOvertime("strSQL", "OvertimeLocalShift", "X:\Overtime Spreadsheet\OvertimeCallDown")

 

[hamrthroer]

Ok. I modified the code as jzwp22 advised and I get a type mismatch on the call :

Call ExportOvertime(Me.cboRptShift, "strSQL", "OvertimeLocalShift", "X:\Overtime Spreadsheet\OvertimeCallDown")

JR I pasted the query into the blank SQL it runs but when I put it into code I get the original issue, too few parameters.

I am calling it from on click event of a command button on frmMain

Maybe I can get a job as a janitor.

Thanks for your efforts thus far.

 

[jzwp22]

Can you post your entire code for the function now that you have modified it as well as the function call itself?

 

[hamrthroer]

Call code:

Call ExportOvertime(Me.cboRptShift, "strSQL", "OvertimeLocalShift", "X:\Overtime Spreadsheet\OvertimeCallDown")

Module Code:

Public Function ExportOvertime(myID As Long, strTQName As String, strSheetName As String, strFilePath As String)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to

' strFilePath is the name and path of the file you want to send this data into.

    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As Field
    Dim DB As Database
    Dim MyQDef As QueryDef
    Dim strSQL As String
    
    'Set frm = Forms!frmMain
    'Set cboShift = frm!cboRptShift
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    'On Error GoTo err_handler
'sample code
   
'DB.OpenRecordset (strSQl)
'end sample code
    strSQL = "SELECT tblStaff.StateEntryDate" & vbNewLine
        strSQL = strSQL & "           , tblStaff.LastName" & vbNewLine
        strSQL = strSQL & "           , tblStaff.FirstName" & vbNewLine
        strSQL = strSQL & "           , tblStaff.MI" & vbNewLine
        strSQL = strSQL & "           , tblStaff.PriPhone" & vbNewLine
        strSQL = strSQL & "        FROM tblStaff" & vbNewLine
        'strSQL = strSQL & "       WHERE (((tblStaff.Shift)=[Forms]![frmMain]![cboRptShift]) " & vbNewLine
       ' strSQL = strSQL & " WHERE (((tblStaff.Shift)=" & [Forms]![frmMain]![cboRptShift] & vbNewLine
        strSQL = strSQL & " WHERE (((tblStaff.Shift)=" & myID & ") " & vbNewLine
        strSQL = strSQL & "         AND ((tblStaff.Rank)=""COI"" " & vbNewLine
        strSQL = strSQL & "          OR (tblStaff.Rank)=""COII"" " & vbNewLine
        strSQL = strSQL & "          OR (tblStaff.Rank)=""COS"") " & vbNewLine
        strSQL = strSQL & "         AND ((tblStaff.WorkOT)=Yes)))" & vbNewLine
        strSQL = strSQL & "    ORDER BY tblStaff.StateEntryDate" & vbNewLine
        strSQL = strSQL & "           , tblStaff.Random;"
        
        
    strPath = "X:\Overtime Spreadsheet\OvertimeCallDown"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
    
        
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Open(strPath)
    ApXL.Visible = True
    Set xlWSh = xlWBk.Worksheets("OvertimeLocalShift")
    xlWSh.Range("A2").Select
    'For Each fld In rst.Fields
        'ApXL.ActiveCell = fld.Name
        'ApXL.ActiveCell.Offset(0, 1).Select
    'Next
    
    'rst.MoveFirst
    xlWSh.Select
    xlWSh.Range("A2").CopyFromRecordset rst
    xlWSh.Range("1:1").Select
    ' This is included to show some of what you can do about formatting.  You can comment out or delete
    ' any of this that you don't want to use in your own export.
    'With ApXL.Selection.Font
        '.Name = "Arial"
        '.Size = 12
        '.Strikethrough = False
        '.Superscript = False
       ' .Subscript = False
        '.OutlineFont = False
        '.Shadow = False
    'End With
   ' ApXL.Selection.Font.Bold = True
    'With ApXL.Selection
        '.HorizontalAlignment = xlCenter
        '.VerticalAlignment = xlBottom
        '.WrapText = False
        '.Orientation = 0
        '.AddIndent = False
        '.IndentLevel = 0
       ' .ShrinkToFit = False
        '.MergeCells = False
   'End With
    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select
    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
    ' selects the first cell to unselect all cells
    xlWSh.Range("A2").Select
    rst.Close
    Set rst = Nothing
    Exit Function
'err_handler:
    'DoCmd.SetWarnings True
    'MsgBox Err.Description, vbExclamation, Err.Number
    'Exit Function
End Function

 

[hamrthroer]

I changed the datatype for MyID from long to string and removed an extra ) and it opens the spreadsheet but doesnt insert the info. It is so strange that if I take out the form reference the export is successfull but I need the reference.

 

[jzwp22]

Ok, just taking the section of code to get the data into the spreadsheet, I made several changes as shown below. I made several changes to your query as well.

Public Function ExportOvertime(myID As Long, strSheetName As String, strFilePath As String)
' strSheetName is the name of the sheet you want to send it to

' strFilePath is the name and path of the file you want to send this data into.

    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As Field
    Dim DB As Database
    Dim MyQDef As QueryDef
    Dim strSQL As String
    
 
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    'On Error GoTo err_handler

    strSQL = "SELECT tblStaff.StateEntryDate"
    strSQL = strSQL & ", tblStaff.LastName"
    strSQL = strSQL & ", tblStaff.FirstName"
    strSQL = strSQL & ", tblStaff.MI"
    strSQL = strSQL & ", tblStaff.PriPhone"
    strSQL = strSQL & " FROM tblStaff"
    strSQL = strSQL & " WHERE tblStaff.Shift=" & myID
    strSQL = strSQL & " AND tblStaff.Rank IN ('COI','COII', 'COS')"
    strSQL = strSQL & " AND tblStaff.WorkOT=Yes"
    strSQL = strSQL & " ORDER BY tblStaff.StateEntryDate"
    strSQL = strSQL & ", tblStaff.Random"
        
    Debug.Print strSQL
        
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
    
        
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Open(strFilePath)
    ApXL.Visible = True
    Set xlWSh = xlWBk.Worksheets(strSheetName)

    xlWSh.Range("A2").CopyFromRecordset rst

Your original call did not include the .xls for the file name, so to call this function:
Call ExportOvertime(Me.cboRptShift, "OvertimeLocalShift", "X:\Overtime Spreadsheet\OvertimeCallDown.xls")

This will only work if the cboRptShift is on the same form where your button is that calls the function.

 

[jzwp22]

The datatype of the ID must be the same as that of tblStaff.Shift

 

[boblarson]

Get rid of the vbNewLine in your SQL strings and you have double quotes where you need single quotes and you don't have the spacing right.

It should be:

strSQL = "SELECT tblStaff.StateEntryDate, "
        strSQL = strSQL & "tblStaff.LastName, "
        strSQL = strSQL & "tblStaff.FirstName, "
        strSQL = strSQL & "tblStaff.MI, " 
        strSQL = strSQL & "tblStaff.PriPhone, " 
        strSQL = strSQL & "FROM tblStaff " 
        strSQL = strSQL & "WHERE (((tblStaff.Shift)=" & Forms!frmMain.cboRptShift) "
        strSQL = strSQL & "AND ((tblStaff.Rank)='COI' " 
        strSQL = strSQL & "OR (tblStaff.Rank)='COII'" 
        strSQL = strSQL & "OR (tblStaff.Rank)='COS')" 
        strSQL = strSQL & "AND ((tblStaff.WorkOT)=True))) " 
        strSQL = strSQL & "ORDER BY tblStaff.StateEntryDate, "
        strSQL = strSQL & "tblStaff.Random;"

If SHIFT is text then you would need to also change this line:

strSQL = strSQL & "WHERE (((tblStaff.Shift)=" & Forms!frmMain.cboRptShift) "

to this:

strSQL = strSQL & "WHERE (((tblStaff.Shift)=" [COLOR=red][B]& Chr(34)[/B][/COLOR] & Forms!frmMain.cboRptShift [COLOR=red][B]& Chr(34)[/B])[/COLOR] "

 

[hamrthroer]

I've got errors with both methods. Stupid question but how do I define myID as text? Also on this line of the SQL I get error end of statement expected:

strSQL = strSQL & "WHERE (((tblStaff.Shift)=" [COLOR=red][B]& Chr(34)[/B][/COLOR] & Forms!frmMain.cboRptShift [COLOR=red][B]& Chr(34)[/B])[/COLOR] "

 

[boblarson]

Well, we have to get your SQL formatted right first to avoid some errors.

The line:
strSQL = strSQL & "WHERE (((tblStaff.Shift)=" & Chr(34) & Forms!frmMain.cboRptShift & Chr(34)) "

I had an error on. It should be:

strSQL = strSQL & "WHERE (((tblStaff.Shift)=" [COLOR=red][B]& Chr(34)[/B][/COLOR] & Forms!frmMain.cboRptShift [COLOR=red][B]& Chr(34)[/B] [COLOR=seagreen][B]& "[/B][/COLOR][/COLOR][COLOR=seagreen][B]) "[/B][/COLOR]