I am using Access 2003 vba to create a spreadsheet with variable tab names. Below is the code I am using. I am using the same code on another button with different data and it works just fine; the only variance between the 2 code sets is that the one that works has an integer value instead of text (where highlighted red below). When I run the code below - I get a pop up asking me for a value for "General" (which is one of the categories). If I look at the temporary query that was created in Access, it has the criteria as [General] instead of "General" - which explains the parameter request; but I don't understand why it is doing that.
Any help would be much appreciated!
Private Sub Command0_Click()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstDisc As DAO.Recordset
Dim strSQL As String, strTemp As String, strDisc As String
Const strFileName As String = "Name of File"
Const strQName As String = "zExportQueryDis"
Stop
Set dbs = CurrentDb
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
Stop
strSQL = "SELECT DISTINCT CategoryFROM DescType;"
Set rstDisc = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
Stop
If rstDisc.EOF = False And rstDisc.BOF = False Then
rstDisc.MoveFirst
Do While rstDisc.EOF = False
strDisc = rstDisc!Category.Value
strSQL = "SELECT * FROM qryMemberData WHERE [Date_Submitted] between #" & Me.txtBeginDate & "# AND #" & Me.txtEndDate & "# AND Category = " & rstDisc!Category.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = strDisc
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTemp, strFileName & ".xls"
rstDisc.MoveNext
Loop
End If
rstDisc.Close
Set rstDisc = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
MsgBox "Description Form Created", vbInformation
Cancel = True
End Sub