Ho Do I Do Loop Query Export - Help loosing sleep over this one.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jonno - 24 Jul 2007 21:54 GMT

Hi, VBA newbie needs rescuing. I am trying to export the same query with a
changing parameter (manager name) to the same destination and then run a
macro to do some formatting in excel for each sequence.

I have progressed to the following but its not picking up the Manager
reference, please could somebody help! or suggest alternate approach.

Function ExportMgr()

Dim mydb As DAO.Database
Dim myset As DAO.Recordset
Dim Manager as String

Set mydb = CurrentDb
Set myset = mydb.OpenRecordSet("Managers List")

Do Until myset.EOF

Manager = myset![Manager name]

CurrentDb.QueryDefs("ManagerQuery2").SQL = "SELECT* FROM ManagerQuery1 WHERE
Manager name = Manager"

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel7,"ManagerQuery2","C:\Temp\Details.xls"

DoCmd.Run Macro Format

myset.MoveNext

Loop

End Function

Reply to this Message

Douglas J. Steele - 24 Jul 2007 22:06 GMT

CurrentDb.QueryDefs("ManagerQuery2").SQL = "SELECT* FROM ManagerQuery1 WHERE
Manager name = """ & Manager & """"

The variable needs to be outside of the SQL string: the Jet Engine doesn't
know anything about VBA variables. And since you're dealing with a text
value, you need all those extra quotes to ensure that quotes are put around
the name. If you knew for certain that you'd never have any names with
apostrophes in them (O'Riley), you could use

CurrentDb.QueryDefs("ManagerQuery2").SQL = "SELECT* FROM ManagerQuery1 WHERE
Manager name = '" & Manager & "'"

but I wouldn't recommend that.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi, VBA newbie needs rescuing. I am trying to export the same query with
> a
[quoted text clipped - 31 lines]
>
> End Function

Reply to this Message

Jonno - 25 Jul 2007 07:10 GMT

Many Thanks Doug for your assistance, I shall give this a try.

> CurrentDb.QueryDefs("ManagerQuery2").SQL = "SELECT* FROM ManagerQuery1 WHERE
> Manager name = """ & Manager & """"
[quoted text clipped - 45 lines]
> >
> > End Function

Reply to this Message