By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,027 Members | 1,111 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,027 IT Pros & Developers. It's quick & easy.

exporting data to excel

P: n/a
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
Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.