MS Access vba export form results to excel with where condition

Question

I have a table that has a lot of fields and then a form that takes only a few of those fields. I have a search button on the form where you can select certain records. Is there a way in VBA to export the results from the form but include all the fields from the table.

Here is my attempt from some code that I found:

Private Sub Command49_Click()
     Dim strWhere As String
     Dim strFile As String
     Const strcStub = "SELECT * FROM tblMaster " & vbCrLf
     Const strcTail = "ORDER BY ID;"
     Const strcExportQuery = "Query1"    'Name of the query for exports.

'Keyword
If Nz(Me.tKW, "") <> "" Then
    strWhere = strWhere & "[iavmtitle] Like '*" & Replace(Me.tKW, " '", "''") & "*' AND "
End If

'Release Date From
If Nz(Me.tRF, "") <> "" Then
   strWhere = strWhere & "[releaseDate] between " & "#" & Me.tRF & "# AND #" & Me.tRT & "#" & " AND "
End If

'Expliots
If Nz(Me.cmbExploits, "") <> "" Then
    strWhere = strWhere & "[knownExploits] = '" & Me.cmbExploits & "' AND "
End If

'Incidents
If Nz(Me.cmdIncidents, "") <> "" Then
    strWhere = strWhere & "[knownDodIncidents] = '" & Me.cmdIncidents & "' AND "
End If

'Release Date From
If Nz(Me.txtSaveSend, "") <> "" Then
   strWhere = strWhere & "[lastSaved] > " & "#" & Me.txtSaveSend & "#" & " AND "
End If

If strWhere <> "" Then
    strWhere = Left(strWhere, Len(strWhere) - 5) 'Remove the extra AND
    Me.Filter = strWhere
    Me.FilterOn = True
Else
    Me.Filter = ""
    Me.FilterOn = False
End If
     If Me.FilterOn Then
         strWhere = "WHERE " & Me.Filter & vbCrLf
     End If
     CurrentDb.QueryDefs(strcExportQuery).SQL = strcStub & strWhere & strcTail

     strFile = "C:\Data\MyExport.xls"
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
         strcExportQuery, strFile
End Sub

`DoCmd.TransferSpreadsheet`is an MS Access command. Why do you not mention or tag MS Access? The MS Office Object Libraries differ in methods and properties and are not shared. — Parfait, Feb 15 '17 at 00:56
@A.S.H right now it only exports the fields in the form. Is it possible to export everything from the table? — 1122335, Feb 15 '17 at 15:59
@Parfait My mistake, I thought I added it in the title. I'll fix it now. — 1122335, Feb 15 '17 at 16:00
@1122335 That surprised me, because of `strcStub = SELECT * FROM tblMaster`... — A.S.H, Feb 15 '17 at 16:39
@A.S.H alright I did some tinkering, now it just exports a blank excel sheet. But it has all the fields now. — 1122335, Feb 15 '17 at 18:28
[How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) — Andre, Feb 15 '17 at 18:56
Thanks everyone! Got it figured out, it had to do with the data type in the form versus the data type in the table — 1122335, Feb 15 '17 at 21:12
@1122335 -- is this question resolved? Perhaps the solution could be posted as an answer? — ashleedawg, Nov 19 '17 at 12:04

MS Access vba export form results to excel with where condition

0 Answers0