Do...While Loop Question

[PJFry]

I work with a group of selling sites in my company. My team maintains a
database that tracks errors made by these sites. Every day we send a list of
outstanding errors to the site administrator. In our current process we run a
query that brings up, say, site 10. We then copy that data into an excel
sheet and send it to the administrator for site 10.

What I want to do is to create a recordset with all of the site information
and use a do…while function to loop through the record set, create the excel
files and e-mail them to the proper administrators. For the administrators I
have a table with the site number and names for each person. (There can be
multiple administrators at the sites). I have seen this kind of loop function
used but I don’t know where to start.

I am just guessing that the loop I suggested is the proper one. Any
suggestions are welcome.

PJ

 

[Ken Snell \(MVP\)]

This sample code may get you started. I wrote it to help another newsgroup
poster, so you'd need to make changes in the SQL statement and the filtering
strings to fit your needs. Post back with questions.


Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query
----------------------------------------------------------

'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' Get list of manager IDs -- note: replace my generic table and field
names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code

 

[PJFry]

Ken,

Thanks for the response. I have taken some time to get familiar with the
code and I have an initial question:

Can you explain what the below code does. The TableDefs is not something I
have ever seen before. I am guessing it feeds to the WHERE clause.
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"

Thanks
PJ

This sample code may get you started. I wrote it to help another newsgroup
poster, so you'd need to make changes in the SQL statement and the filtering
strings to fit your needs. Post back with questions.


Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query
----------------------------------------------------------

'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' Get list of manager IDs -- note: replace my generic table and field
names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code



--

Ken Snell
<MS ACCESS MVP>

I work with a group of selling sites in my company. My team maintains a
database that tracks errors made by these sites. Every day we send a list
of
outstanding errors to the site administrator. In our current process we
run a
query that brings up, say, site 10. We then copy that data into an excel
sheet and send it to the administrator for site 10.

What I want to do is to create a recordset with all of the site
information
and use a do.while function to loop through the record set, create the
excel
files and e-mail them to the proper administrators. For the administrators
I
have a table with the site number and names for each person. (There can be
multiple administrators at the sites). I have seen this kind of loop
function
used but I don't know where to start.

I am just guessing that the loop I suggested is the proper one. Any
suggestions are welcome.

PJ

 

[Ken Snell \(MVP\)]

I use these two lines of code just to establish an initial query in the
database so that the code can then assign different SQL strings to it and
use it for the individual exports. So the
"SELECT * FROM [" & strTemp & "] WHERE 1=0;"
line is just a generic query SQL statement that I use as the "dummy" SQL
string for this initial query. The code is just selecting the first table in
the TableDefs collection and using that name in the SQL statement; the use
of the "WHERE 1=0" clause is just to ensure that no records would be
returned by the query if it were to be run using this dummy SQL statement.

QueryDef is a collection of stored queries in the ACCESS database file. You
can use this collection to gain access to any of the stored queries in the
database, and to change the properties of a query. In order to export a
query to an EXCEL file, the query must be saved; it cannot be generated
dynamically and exported. Hence, the code creates a query, assigns a "dummy"
SQL statement to it, and then generates new SQL statements for each
"administrator" that will be the filter of an exported query and saves the
SQL statement to this query so that you can export it.

--

Ken Snell
<MS ACCESS MVP>

Ken,

Thanks for the response. I have taken some time to get familiar with the
code and I have an initial question:

Can you explain what the below code does. The TableDefs is not something I
have ever seen before. I am guessing it feeds to the WHERE clause.
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"

Thanks
PJ

This sample code may get you started. I wrote it to help another
newsgroup
poster, so you'd need to make changes in the SQL statement and the
filtering
strings to fit your needs. Post back with questions.


Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query
----------------------------------------------------------

'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' Get list of manager IDs -- note: replace my generic table and field
names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code



--

Ken Snell
<MS ACCESS MVP>

I work with a group of selling sites in my company. My team maintains a
database that tracks errors made by these sites. Every day we send a
list
of
outstanding errors to the site administrator. In our current process we
run a
query that brings up, say, site 10. We then copy that data into an
excel
sheet and send it to the administrator for site 10.

What I want to do is to create a recordset with all of the site
information
and use a do.while function to loop through the record set, create the
excel
files and e-mail them to the proper administrators. For the
administrators
I
have a table with the site number and names for each person. (There can
be
multiple administrators at the sites). I have seen this kind of loop
function
used but I don't know where to start.

I am just guessing that the loop I suggested is the proper one. Any
suggestions are welcome.

PJ

 

[PJFry]

That makes sense now. I was thinking that the WHERE 1=0 was somehow related
to the '0' in the 'strTemp = dbs.TableDefs(0).Name' code.

The, as it is, works great. I was playing around with e-mailing them to
certain people and I replaced the transferspreadsheet with a sendobject, as
seen below:

DoCmd.SendObject acSendQuery, strTemp, acFormatXLS, "(e-mail address removed)", ,
, _
"Daily Report for Site " & rs!lngSiteNum.Value, "Please find attached the
site report"

The code sends out the query in an excel sheet well enough, but it sends one
copy of the complete site report for each line in the report. So if, say,
site three had five items, it e-mails the five item report five time. The
spreadsheet method works fine.

Thoughts?

Thanks again for the help.
PJ

I use these two lines of code just to establish an initial query in the
database so that the code can then assign different SQL strings to it and
use it for the individual exports. So the
"SELECT * FROM [" & strTemp & "] WHERE 1=0;"
line is just a generic query SQL statement that I use as the "dummy" SQL
string for this initial query. The code is just selecting the first table in
the TableDefs collection and using that name in the SQL statement; the use
of the "WHERE 1=0" clause is just to ensure that no records would be
returned by the query if it were to be run using this dummy SQL statement.

QueryDef is a collection of stored queries in the ACCESS database file. You
can use this collection to gain access to any of the stored queries in the
database, and to change the properties of a query. In order to export a
query to an EXCEL file, the query must be saved; it cannot be generated
dynamically and exported. Hence, the code creates a query, assigns a "dummy"
SQL statement to it, and then generates new SQL statements for each
"administrator" that will be the filter of an exported query and saves the
SQL statement to this query so that you can export it.

--

Ken Snell
<MS ACCESS MVP>

Ken,

Thanks for the response. I have taken some time to get familiar with the
code and I have an initial question:

Can you explain what the below code does. The TableDefs is not something I
have ever seen before. I am guessing it feeds to the WHERE clause.
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"

Thanks
PJ

This sample code may get you started. I wrote it to help another
newsgroup
poster, so you'd need to make changes in the SQL statement and the
filtering
strings to fit your needs. Post back with questions.


Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query
----------------------------------------------------------

'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' Get list of manager IDs -- note: replace my generic table and field
names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code



--

Ken Snell
<MS ACCESS MVP>

I work with a group of selling sites in my company. My team maintains a
database that tracks errors made by these sites. Every day we send a
list
of
outstanding errors to the site administrator. In our current process we
run a
query that brings up, say, site 10. We then copy that data into an
excel
sheet and send it to the administrator for site 10.

What I want to do is to create a recordset with all of the site
information
and use a do.while function to loop through the record set, create the
excel
files and e-mail them to the proper administrators. For the
administrators
I
have a table with the site number and names for each person. (There can
be
multiple administrators at the sites). I have seen this kind of loop
function
used but I don't know where to start.

I am just guessing that the loop I suggested is the proper one. Any
suggestions are welcome.

PJ

 

[Ken Snell \(MVP\)]

Post the actual code that you're using (with the SendObject method)...let's
see if it all looks correct.

 

[PJFry]

Ken,
I was able to figure out the problem. I had the statment looping through
unique contract numbers rather than sites. I was confused because there only
appeared to one Excel file per site, but multiple e-mails. I changed the
format on the Excel file name to include seconds and all of the duplicates
started show up. I changed the recordset so the code looped through the
sites and everything worked perfectly.

Thanks again for the help!
PJ

 

[Ken Snell \(MVP\)]

Glad you found the solution!