I have a table (tblMain with 20 fields), which I created a query (qrySPM) to choose 5 fields from tblMain), from which I created a form (frmSPM-in split form view) for the purpose of data entry into tblMain. Finally, I would like to filter the various fields in frmSPM and export the filtered data to excel at the click of a button.
I have struggled for a week due to being new to vba. Below is the code is copied (from Rey Obrero- link omitted as I am not sure if I can post it here) and did some modification as below. I am still very much confused about the "querydef" part. Appreciate any help to point me to the right direction. TIA.
Code:
Private Sub CommandSPM_Click()
Dim sWhere As String, qd As DAO.QueryDef, nSql As String
If Me.Filter = "" Then
sWhere = ""
Else
sWhere = Me.Filter
End If
Set qd = CurrentDb.QueryDefs("qrySPM")
qd.SQL = "select * from tblMain"
nSql = qd.SQL & " Where " & sWhere
If Len(sWhere) > 0 Then
qd.SQL = nSql
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qrySPM3", "Z:\aSOPO\SPM.xlsx", True
End Sub