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)));

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