Dear Gurus
I am trying to export some data to excel from a form, but I seem to run into
difficulties when the user has filtered for particular data that is from a
dropdown (linking to another table in a one to many relationship).
The W (filter for the form) that caused the error was
WHERE ((([Lookup_M-CIT-ID].DD="New York"))) AND (([Lookup_M-CLI-ID].DD="X"))
so from the form the user had filtered for a lookup ("M-CIT-ID") that should
be equal to New York and another field ("M-CLI-ID") that should be equal to
X
in my excel output I want to also have all the records from the form where
the M-CIT-ID field is equal to the ID for New York and the M-CLI-ID is equal
to the ID for X.
Here is my code:
Public Function s151()
'on error GoTo ERR
'---
Dim FilN As String 'file name
Dim MSG As String
Dim SQLs As String
Const TempTbl = "ZOUTPUTtbl"
Dim RST As dao.Recordset
Dim W As String
'---
Set RST = FRM.RecordsetClone
If FRM.FilterOn = True Then
W = " WHERE " & FRM.FILTER
If W = " WHERE " Then W = ""
End If
SQLs = "SELECT [" & RST.Name & "].* INTO [" & TempTbl & "] FROM [" &
RST.Name & "] " & W & ";"
FRUNSQL (SQLs)
FilN = Format(FRM.Caption & Format(TIME() * 24 * 60 * 60, "0000") &
".xls", "<")
MSG = FINTXT(240) & FilN
'---
Call FMB(-10, MSG)
DoCmd.OutputTo acOutputTable, TempTbl, acFormatXLS, FilN, True, ""
xit:
Exit Function
ERR:
MsgBox ERROR$ & " {PROCEDURE # " & oPt + ModEro & "}"
Call Ferr