Hello folks. Newbie here with a problem.

I have a problem with some code not doing what I expected it to, and I know why, but am not sure how to fix it.

I have a table of data from many suppliers, each supplier having many records. Each supplier has their own SupplierId.

I have an SQL query that selects the DISTINCT SupplierIds.
A Do Until... Loop needs to look at the DISTINCT SupplierIds, select matching data, and create an Excel spreadsheet.
The spreadsheet naming convention is a concatenation of SupplierId & Date & ".xls"

How do I reference the current value of the SupplierId to use in the concatenated spreadsheet name?

The piece of code I have is below. The offending variable in italics and underlined;

'Repeat the following process until the last record is reached
Do Until SupplierIdrst.EOF

'Open a new Excel workbook
Set objApp = New Excel.Application
Set objBook = objApp.Workbooks.Add

'SQL Statement to pull the data to be exported
strSql2 = "SELECT * FROM tblREPORTDATA"

'Create a recordset consisting of data in the sql query
Set rst = CurrentDb.OpenRecordset(strSql2, dbOpenSnapshot, dbReadOnly)

'copy the recordset data into an Excel worksheet starting at cell A1
With objBook.Sheets("Sheet1")
.Range("A1").CopyFromRecordset rst
End With

'close the recordset
rst.Close

'clear the strTempPath variable
strTempPath = ""

'Set the strTempPath to D:\Databases etc
strTempPath = "J:\Databases\Output\" SupplierId & strReportDate & strFileExtension

strTempPath = "J:\Databases\Output\" & SupplierIdrst.SupplierId & strReportDate & strFileExtension

.

That was quick.

Thanks for that - makes sense.

But I'm getting 'Compile Error: Method or data member not found' and it's the SupplierId that is highlighted.

What have I not done?

Presuming that's a field in the recordset's data, use bang not dot:

SupplierIdrst!SupplierId

Wahaay - that's better.

I have another problem with the Loop and the spreadsheet creation now which I need to look at.

Many thanks for the swift reply.

DOH :o

Sorry bout that Jemmo and thanx Paul.

.

No problemo; my keyboard does that to me sometimes too. :p