Skip to content
Advertisement

SQL statement not appending to temporary table

To export displayed user data from a form to excel I create a temporary table and export that table. Normally this works however, in the case where I need to Query two different tables and export them together it will output nothing, only the table’s structure nothing else.

I have tried manually querying the tables in addition to taking straight from the forms record source, manually doing the queries in the code, and using the standard output.

The code for all other forms is listed like this

sheetName = [Forms]![frmOutputPickering]![TabCtl3].Pages(page).Name
lenSQL = InStr(1, selectedPageName(page).Form.RecordSource, "FROM") - 2
strSQL = Left(selectedPageName(page).Form.RecordSource, lenSQL) & "INTO [" & sheetName & "] " _
& Mid(selectedPageName(page).Form.RecordSource, lenSQL)
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

The code with two parts looks like this:

sheetName = [Forms]![frmOutputPickering]![TabCtl3].Pages(page).Name
lenSQL = InStr(1, selectedPageName(page).Form.RecordSource, "FROM") - 2
strSQL = Left(selectedPageName(page).Form.RecordSource, lenSQL) & " INTO [" & sheetName & "] " & Mid(selectedPageName(page).Form.RecordSource, _
lenSQL, InStr(1, selectedPageName(page).Form.RecordSource, "UNION") - lenSQL - 2) & ");"
strSQL = "SELECT qryOUTAGE_NEW_WOs_PA.* INTO [" & sheetName & "] FROM [qryOUTAGE_NEW_WOs_PA];"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
lenSQL = InStr(lenSQL, selectedPageName(page).Form.RecordSource, "UNION") + 5
strSQL = "INSERT INTO [" & sheetName & "] SELECT [qryOUTAGE_NEW_WOs_PB].*;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

It should export both the queries to the temporary table however it only outputs the table structure.

Edit: As requested the original record source for the Form output

SELECT tbl_OMSPA_v_SCOPED_WORK.WORK_ORDER_NBR AS [WO #], tbl_OMSPA_v_SCOPED_WORK.WO_REQ_NUMBER AS [WR #], tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE AS System, tbl_OMSPA_v_SCOPED_WORK.ADD_DATE AS [Add Date], tbl_OMSPA_v_SCOPED_WORK.SW_TITLE AS Description, tbl_OMSPA_v_SCOPED_WORK.SW_UNIT AS Unit, [tbl_OMSPA_v_SCOPED_WORK].[SW_UNIT]+"-"+[tbl_OMSPA_v_SCOPED_WORK].[SW_SYSTEM_CODE]+"-"+[tbl_OMSPA_v_SCOPED_WORK].[SW_EQUIPMENT_NUMBER] AS Equipment_Tag, tbl_OMSPA_v_SCOPED_WORK.SW_CRITICAL_EQUIPMENT AS Criticality, tbl_OMSPA_v_SCOPED_WORK.SCOPE_STATUS AS [Scope Status], tbl_OMSPA_v_SCOPED_WORK.SW_SHUTDOWN_NBR AS [Shutdown Number], tbl_OMSPA_v_SCOPED_WORK.I2P_Score AS [I2P Score]
FROM tbl_OMSPA_v_SCOPED_WORK
WHERE (((tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='42100' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='42110' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='42111' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='42120' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='42121' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='42122' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='42123' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='42130' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='42140' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='43100' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='43110' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='43120' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='43130' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='43140' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='43150' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='43200' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='43210' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='43220' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='43230' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='43240' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='45100' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='45110' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='45200' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='45210' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='45220' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='45230' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='45300' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='45310' OR (tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)='42000') AND ((tbl_OMSPA_v_SCOPED_WORK.ADD_DATE)>=[Forms]![frmOutputPickering]![TextStartDate] And (tbl_OMSPA_v_SCOPED_WORK.ADD_DATE)<=([Forms]![frmOutputPickering]![TextEndDate]+1))) UNION SELECT tbl_OMSPB_v_SCOPED_WORK.WORK_ORDER_NBR AS [WO #], tbl_OMSPB_v_SCOPED_WORK.WO_REQ_NUMBER AS [WR #], tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE AS System, tbl_OMSPB_v_SCOPED_WORK.ADD_DATE AS [Add Date], tbl_OMSPB_v_SCOPED_WORK.SW_TITLE AS Description, tbl_OMSPB_v_SCOPED_WORK.SW_UNIT AS Unit, [tbl_OMSPB_v_SCOPED_WORK].[SW_UNIT]+"-"+[tbl_OMSPB_v_SCOPED_WORK].[SW_SYSTEM_CODE]+"-"+[tbl_OMSPB_v_SCOPED_WORK].[SW_EQUIPMENT_NUMBER] AS Equipment_Tag, tbl_OMSPB_v_SCOPED_WORK.SW_CRITICAL_EQUIPMENT AS Criticality, tbl_OMSPB_v_SCOPED_WORK.SCOPE_STATUS AS [Scope Status], tbl_OMSPB_v_SCOPED_WORK.SW_SHUTDOWN_NBR AS [Shutdown Number], tbl_OMSPB_v_SCOPED_WORK.I2P_Score AS [I2P Score]
FROM tbl_OMSPB_v_SCOPED_WORK
WHERE (((tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='42100' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='42110' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='42111' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='42120' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='42121' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='42122' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='42123' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='42130' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='42140' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='43100' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='43110' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='43120' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='43130' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='43140' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='43150' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='43200' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='43210' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='43220' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='43230' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='43240' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='45100' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='45110' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='45200' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='45210' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='45220' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='45230' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='45300' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='45310' OR (tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)='42000') AND ((tbl_OMSPB_v_SCOPED_WORK.ADD_DATE)>=[Forms]![frmOutputPickering]![TextStartDate] And (tbl_OMSPB_v_SCOPED_WORK.ADD_DATE)<=([Forms]![frmOutputPickering]![TextEndDate]+1)));

Edit 2: The queries that are called by the code

qryOUTAGE_NEW_WOs_PA:

SELECT tbl_OMSPA_v_SCOPED_WORK.WORK_ORDER_NBR AS [WO #], tbl_OMSPA_v_SCOPED_WORK.WO_REQ_NUMBER AS [WR #], tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE AS System, tbl_OMSPA_v_SCOPED_WORK.ADD_DATE AS [Add Date], tbl_OMSPA_v_SCOPED_WORK.SW_TITLE AS Description, tbl_OMSPA_v_SCOPED_WORK.SW_UNIT AS Unit, [tbl_OMSPA_v_SCOPED_WORK].[SW_UNIT]+"-"+[tbl_OMSPA_v_SCOPED_WORK].[SW_SYSTEM_CODE]+"-"+[tbl_OMSPA_v_SCOPED_WORK].[SW_EQUIPMENT_NUMBER] AS Equipment_Tag, tbl_OMSPA_v_SCOPED_WORK.SW_CRITICAL_EQUIPMENT AS Criticality, tbl_OMSPA_v_SCOPED_WORK.SCOPE_STATUS AS [Scope Status], tbl_OMSPA_v_SCOPED_WORK.SW_SHUTDOWN_NBR AS [Shutdown Number], tbl_OMSPA_v_SCOPED_WORK.I2P_Score AS [I2P Score]
FROM tbl_OMSPA_v_SCOPED_WORK
WHERE (((tbl_OMSPA_v_SCOPED_WORK.SW_SYSTEM_CODE)=[Forms]![frmOutputPickering]![ListSelectedSystem]) AND ((tbl_OMSPA_v_SCOPED_WORK.ADD_DATE)>=[Forms]![frmOutputPickering]![TextStartDate] And (tbl_OMSPA_v_SCOPED_WORK.ADD_DATE)<=([Forms]![frmOutputPickering]![TextEndDate]+1)));

qryOUTAGE_NEW_WOs_PB:

SELECT tbl_OMSPB_v_SCOPED_WORK.WORK_ORDER_NBR AS [WO #], tbl_OMSPB_v_SCOPED_WORK.WO_REQ_NUMBER AS [WR #], tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE AS System, tbl_OMSPB_v_SCOPED_WORK.ADD_DATE AS [Add Date], tbl_OMSPB_v_SCOPED_WORK.SW_TITLE AS Description, tbl_OMSPB_v_SCOPED_WORK.SW_UNIT AS Unit, [tbl_OMSPB_v_SCOPED_WORK].[SW_UNIT]+"-"+[tbl_OMSPB_v_SCOPED_WORK].[SW_SYSTEM_CODE]+"-"+[tbl_OMSPB_v_SCOPED_WORK].[SW_EQUIPMENT_NUMBER] AS Equipment_Tag, tbl_OMSPB_v_SCOPED_WORK.SW_CRITICAL_EQUIPMENT AS Criticality, tbl_OMSPB_v_SCOPED_WORK.SCOPE_STATUS AS [Scope Status], tbl_OMSPB_v_SCOPED_WORK.SW_SHUTDOWN_NBR AS [Shutdown Number], tbl_OMSPB_v_SCOPED_WORK.I2P_Score AS [I2P Score]
FROM tbl_OMSPB_v_SCOPED_WORK
WHERE (((tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)=[Forms]![frmOutputPickering]![ListSelectedSystem]) AND ((tbl_OMSPB_v_SCOPED_WORK.ADD_DATE)>=[Forms]![frmOutputPickering]![TextStartDate] And (tbl_OMSPB_v_SCOPED_WORK.ADD_DATE)<=([Forms]![frmOutputPickering]![TextEndDate]+1)));

Advertisement

Answer

Essentially, you are attempting to run an action query (make-table) based on parameterized recordset select query. However, when running VBA SQL queries with DoCmd.RunSQL, your parameters are never evaluated. As a result, your WHERE clause:

WHERE (((tbl_OMSPB_v_SCOPED_WORK.SW_SYSTEM_CODE)=[Forms]![frmOutputPickering]![ListSelectedSystem])

receives a NULL parameter value and does not evaluate to TRUE and hence no records are returned.

There are several solutions you can implement to evaluate parameters in a parameterized query.

  • Access’ DoCmd.OpenQuery

    Save your make-table query as its own stored query and call the action. Note: there is no need to close action queries:

    DoCmd.SetWarnings False
DoCmd.OpenQuery "myMakeTableQuery"
DoCmd.SetWarnings True

    This GUI run method will locate the control [Forms]![frmOutputPickering]![ListSelectedSystem] across open forms and bind its value accordingly. If form is not opened, query returns no records.

  • Access’ QueryDefs

    Save your make-table query at its own stored query and in code, initialize a querydef and evaluate the parameter literally.

    Dim qdef As QueryDef, prm As Parameter

' INITIALIZE QUERYDEF
Set qdef = CurrentDb.QueryDefs("myMakeTableQuery")

' EVALUATE EACH FORM PARAMETER
For Each prm In qdef.Parameters
    prm.value = Eval(prm.name)
Next prm

' RUN ACTION 
qdef.Execute dbFailOnError

  • Excel’s CopyFromRecordset

    Even better, bypass any need of temp tables and use querydefs and recordsets to output data to open workbook using an Excel application object. Below shows how to export one parameterized query.

    Public Sub ExportQueryToExcel()
On Error GoTo ErrHandle
    Dim qdef As QueryDef, rs As Recordset, prm As Parameter
    Dim xlApp As Object, xlWb As Object
    Dim i As Long

    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add

    Set qdef = CurrentDb.QueryDefs("mySelectQuery")            
    For Each prm In qdef.Parameters
        prm.value = Eval(prm.name)
    Next prm    
    Set rs = qdef.OpenRecordset()

    xlWb.Sheets(1).Range("A2").CopyFromRecordset rs            ' DATA ROWS

    For i = 1 To rs.Fields.Count
        xlWb.Sheets(1).Cells(1, i) = rs.Fields(i - 1).name     ' HEADER ROW
    Next i

    xlApp.Visible = True

ExitHandle:
    rs.Close: qdef.Close
    Set rs = Nothing: Set qdef = Nothing
    Set xlWb = Nothing: Set xlApp = Nothing
    Exit Sub

ErrHandle:
    MsgBox Err.Number & ": " & Err.Description, vbCritical, "RUNTIME ERROR"
    Resume ExitHandle
End Sub
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement