Export multiple versions of a query to separate Excel files

[Erick C]

Hello again.
I think that I may have finally tried to make something that may not
be possible. I want to create multiple excel spreadsheets for each
unique identifier (Subinventory) in one of my tables. I tried to
modify the code provided by Ken Snell on his very helpful website, but
I tried to get a bit too inventive.
I also tried to combine the code for browsing to a single folder to
export the data. So, essentially I was trying to create a button that
would open a dialog box where the user can select the correct folder
and then the individual spreadsheets are created in that folder.
I also had a bit of trouble matching up information for the code since
the example uses manager and associate names along with an ID number.
I do not have an actual name, I only have a number for a location. I
made a separate table with only the store numbers (Subinventories)
that are being exported. The other table has all of the data to be
exported.
I don't know if anybody has ever done anything like this, or if this
is even possible. Any help would be appreciated.
Here is my code. I also left the example information for reference
purposes.

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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for
each ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM Subinv Subtotal By Transaction
Type WHERE " & _
"Subinventory = " & rstMgr!Subinventory.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & strMgr & ".xls"
strPathFile = strPath & "\" & strFile
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport,
acSpreadsheetTypeExcel9, _
strTemp, strPathFile
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

 

[Erick C]

One addition to my previous post:
If I try to run the code I can select the folder that I want to create
the files in, but I then receive a runtime error #3131. When I open
the debudder it highlights the following line in the code:
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

Hello again.
I think that I may have finally tried to make something that may not
be possible.  I want to create multiple excel spreadsheets for each
unique identifier (Subinventory) in one of my tables.  I tried to
modify the code provided by Ken Snell on his very helpful website, but
I tried to get a bit too inventive.
I also tried to combine the code for browsing to a single folder to
export the data.  So, essentially I was trying to create a button that
would open a dialog box where the user can select the correct folder
and then the individual spreadsheets are created in that folder.
I also had a bit of trouble matching up information for the code since
the example uses manager and associate names along with an ID number.
I do not have an actual name, I only have a number for a location.  I
made a separate table with only the store numbers (Subinventories)
that are being exported.  The other table has all of the data to be
exported.
I don't know if anybody has ever done anything like this, or if this
is even possible.  Any help would be appreciated.
Here is my code. I also left the example information for reference
purposes.

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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
      MsgBox "No folder was selected.", vbOK, "No Selection"
      Exit Sub
End If

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for
each ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
            strMgr = DLookup("Subinventory", "Stores for Export", _
                  "Subinventory = " & rstMgr!Subinventory.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
            strSQL = "SELECT * FROM Subinv Subtotal By Transaction
Type WHERE " & _
                  "Subinventory = " & rstMgr!Subinventory.Value & ";"
            Set qdf = dbs.QueryDefs(strTemp)
            qdf.Name = "q_" & strMgr
            strTemp = qdf.Name
            qdf.SQL = strSQL
            qdf.Close
            Set qdf = Nothing
          strFile = Format$(Date, "Medium Date") & strMgr & "..xls"
          strPathFile = strPath & "\" & strFile
' Replace C:\FolderName\ with actual path
            DoCmd.TranferSpreadsheet acExport,
acSpreadsheetTypeExcel9, _
                  strTemp, strPathFile
            rstMgr.MoveNext
      Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

 

[Ken Snell [MVP]]

I don't have time tonite to look at your code, but I will do this as soon as
my work schedule permits. I'll post back to this thread.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello again.
I think that I may have finally tried to make something that may not
be possible. I want to create multiple excel spreadsheets for each
unique identifier (Subinventory) in one of my tables. I tried to
modify the code provided by Ken Snell on his very helpful website, but
I tried to get a bit too inventive.
I also tried to combine the code for browsing to a single folder to
export the data. So, essentially I was trying to create a button that
would open a dialog box where the user can select the correct folder
and then the individual spreadsheets are created in that folder.
I also had a bit of trouble matching up information for the code since
the example uses manager and associate names along with an ID number.
I do not have an actual name, I only have a number for a location. I
made a separate table with only the store numbers (Subinventories)
that are being exported. The other table has all of the data to be
exported.
I don't know if anybody has ever done anything like this, or if this
is even possible. Any help would be appreciated.
Here is my code. I also left the example information for reference
purposes.

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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for
each ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM Subinv Subtotal By Transaction
Type WHERE " & _
"Subinventory = " & rstMgr!Subinventory.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & strMgr & ".xls"
strPathFile = strPath & "\" & strFile
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport,
acSpreadsheetTypeExcel9, _
strTemp, strPathFile
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

 

[Ken Snell [MVP]]

Your SQL statement has incorrect syntax. Change these two code lines:

strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)



to these:

strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)


I haven't had a chance to work on an answer to your questions in your first
post, but I will do so soon.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


One addition to my previous post:
If I try to run the code I can select the folder that I want to create
the files in, but I then receive a runtime error #3131. When I open
the debudder it highlights the following line in the code:
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

Hello again.
I think that I may have finally tried to make something that may not
be possible. I want to create multiple excel spreadsheets for each
unique identifier (Subinventory) in one of my tables. I tried to
modify the code provided by Ken Snell on his very helpful website, but
I tried to get a bit too inventive.
I also tried to combine the code for browsing to a single folder to
export the data. So, essentially I was trying to create a button that
would open a dialog box where the user can select the correct folder
and then the individual spreadsheets are created in that folder.
I also had a bit of trouble matching up information for the code since
the example uses manager and associate names along with an ID number.
I do not have an actual name, I only have a number for a location. I
made a separate table with only the store numbers (Subinventories)
that are being exported. The other table has all of the data to be
exported.
I don't know if anybody has ever done anything like this, or if this
is even possible. Any help would be appreciated.
Here is my code. I also left the example information for reference
purposes.

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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for
each ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM Subinv Subtotal By Transaction
Type WHERE " & _
"Subinventory = " & rstMgr!Subinventory.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & strMgr & ".xls"
strPathFile = strPath & "\" & strFile
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport,
acSpreadsheetTypeExcel9, _
strTemp, strPathFile
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

 

[Erick C]

Hi Ken -
Thank you for the update. Do not worry about the timeliness of your
response, it is no problem at all. I am just happy to have someone
that can answer my questions! Unfortunately, I cannot make these
additions to my database at this time. I came in this morning and I
tried to open the database only to receive an error message saying the
"database cannot be opened because the VBA project contained in it
cannot be read." It only gives an option to make a copy of the
database or delete all of the VBA from all modules, forms, and
reports. So essentially I think I just lost all of my stuff. I had a
backup, but it unfortunately is not current, there is some code but
not everything.
I am going to start making another database now, it should not take
too long for me to get back up and running since I did not really have
a lot of code, and most of it is saved in emails and in message posts
on here.
Once I get your new change suggestions in I will let you know the
outcome.
Thanks.

Erick

Your SQL statement has incorrect syntax. Change these two code lines:

strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

to these:

strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

I haven't had a chance to work on an answer to your questions in your first
post, but I will do so soon.
--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


One addition to my previous post:
If I try to run the code I can select the folder that I want to create
the files in, but I then receive a runtime error #3131.  When I open
the debudder it highlights the following line in the code:
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

Hello again.
I think that I may have finally tried to make something that may not
be possible. I want to create multiple excel spreadsheets for each
unique identifier (Subinventory) in one of my tables. I tried to
modify the code provided by Ken Snell on his very helpful website, but
I tried to get a bit too inventive.
I also tried to combine the code for browsing to a single folder to
export the data. So, essentially I was trying to create a button that
would open a dialog box where the user can select the correct folder
and then the individual spreadsheets are created in that folder.
I also had a bit of trouble matching up information for the code since
the example uses manager and associate names along with an ID number.
I do not have an actual name, I only have a number for a location. I
made a separate table with only the store numbers (Subinventories)
that are being exported. The other table has all of the data to be
exported.
I don't know if anybody has ever done anything like this, or if this
is even possible. Any help would be appreciated.
Here is my code. I also left the example information for reference
purposes.

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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for
each ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM Subinv Subtotal By Transaction
Type WHERE " & _
"Subinventory = " & rstMgr!Subinventory.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & strMgr & ".xls"
strPathFile = strPath & "\" & strFile
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport,
acSpreadsheetTypeExcel9, _
strTemp, strPathFile
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing- Hide quoted text -

- Show quoted text -

 

[Erick C]

Ken -
I made the changes that you suggested, but I think I have some other
problems now. I press the button, but I receive a Compile error
asying "User-defined type not defined." When the debug opens it
highlights "qdf As DAO.QueryDef" at the top of the code. I made sure
that I added the modules again, I do not know why I am getting this
error now and I did not get it before.

Hi Ken -
Thank you for the update.  Do not worry about the timeliness of your
response, it is no problem at all.  I am just happy to have someone
that can answer my questions!  Unfortunately, I cannot make these
additions to my database at this time.  I came in this morning and I
tried to open the database only to receive an error message saying the
"database cannot be opened because the VBA project contained in it
cannot be read."  It only gives an option to make a copy of the
database or delete all of the VBA from all modules, forms, and
reports.  So essentially I think I just lost all of my stuff.  I had a
backup, but it unfortunately is not current, there is some code but
not everything.
I am going to start making another database now, it should not take
too long for me to get back up and running since I did not really have
a lot of code, and most of it is saved in emails and in message posts
on here.
Once I get your new change suggestions in I will let you know the
outcome.
Thanks.

Erick

Your SQL statement has incorrect syntax. Change these two code lines:

strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

to these:

strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

I haven't had a chance to work on an answer to your questions in your first
post, but I will do so soon.
--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

"Erick C" <[email protected]> wrote in message

One addition to my previous post:
If I try to run the code I can select the folder that I want to create
the files in, but I then receive a runtime error #3131.  When I open
the debudder it highlights the following line in the code:
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

Hello again.
I think that I may have finally tried to make something that may not
be possible. I want to create multiple excel spreadsheets for each
unique identifier (Subinventory) in one of my tables. I tried to
modify the code provided by Ken Snell on his very helpful website, but
I tried to get a bit too inventive.
I also tried to combine the code for browsing to a single folder to
export the data. So, essentially I was trying to create a button that
would open a dialog box where the user can select the correct folder
and then the individual spreadsheets are created in that folder.
I also had a bit of trouble matching up information for the code since
the example uses manager and associate names along with an ID number.
I do not have an actual name, I only have a number for a location. I
made a separate table with only the store numbers (Subinventories)
that are being exported. The other table has all of the data to be
exported.
I don't know if anybody has ever done anything like this, or if this
is even possible. Any help would be appreciated.
Here is my code. I also left the example information for reference
purposes.
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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query for
each ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM Subinv Subtotal By Transaction
Type WHERE " & _
"Subinventory = " & rstMgr!Subinventory.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & strMgr & ".xls"
strPathFile = strPath & "\" & strFile
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport,
acSpreadsheetTypeExcel9, _
strTemp, strPathFile
rstMgr.MoveNext
Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Erick C]

Please disregard my last message. I found out that the DAO object
library was unchecked in the references. I have added it again. Now
I receive an error for something different.
I am getting a Runtime Error #3075
Syntax error (missing operator) in query expression 'Subinventory =
WIN 0191'.
The debugger goes back to this:
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.value)

The directions above this part of code say:
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names

I do not have a Manager Name Field. I made a table of all stores that
should be exported and the only field in the table is "Subinventory",
there are no other fields.

Ken -
I made the changes that you suggested, but I think I have some other
problems now.  I press the button, but I receive a Compile error
asying "User-defined type not defined."  When the debug opens it
highlights "qdf As DAO.QueryDef" at the top of the code.  I made sure
that I added the modules again, I do not know why I am getting this
error now and I did not get it before.

Hi Ken -
Thank you for the update.  Do not worry about the timeliness of your
response, it is no problem at all.  I am just happy to have someone
that can answer my questions!  Unfortunately, I cannot make these
additions to my database at this time.  I came in this morning and I
tried to open the database only to receive an error message saying the
"database cannot be opened because the VBA project contained in it
cannot be read."  It only gives an option to make a copy of the
database or delete all of the VBA from all modules, forms, and
reports.  So essentially I think I just lost all of my stuff.  I had a
backup, but it unfortunately is not current, there is some code but
not everything.
I am going to start making another database now, it should not take
too long for me to get back up and running since I did not really have
a lot of code, and most of it is saved in emails and in message posts
on here.
Once I get your new change suggestions in I will let you know the
outcome.
Thanks.

Erick

Your SQL statement has incorrect syntax. Change these two code lines:
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
to these:
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
I haven't had a chance to work on an answer to your questions in yourfirst
post, but I will do so soon.
--
        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
One addition to my previous post:
If I try to run the code I can select the folder that I want to create
the files in, but I then receive a runtime error #3131.  When I open
the debudder it highlights the following line in the code:
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
Hello again.
I think that I may have finally tried to make something that may not
be possible. I want to create multiple excel spreadsheets for each
unique identifier (Subinventory) in one of my tables. I tried to
modify the code provided by Ken Snell on his very helpful website, but
I tried to get a bit too inventive.
I also tried to combine the code for browsing to a single folder to
export the data. So, essentially I was trying to create a button that
would open a dialog box where the user can select the correct folder
and then the individual spreadsheets are created in that folder.
I also had a bit of trouble matching up information for the code since
the example uses manager and associate names along with an ID number.
I do not have an actual name, I only have a number for a location. I
made a separate table with only the store numbers (Subinventories)
that are being exported. The other table has all of the data to be
exported.
I don't know if anybody has ever done anything like this, or if this
is even possible. Any help would be appreciated.
Here is my code. I also left the example information for reference
purposes.
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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query for
each ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM Subinv Subtotal By Transaction
Type WHERE " & _
"Subinventory = " & rstMgr!Subinventory.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & strMgr & ".xls"
strPathFile = strPath & "\" & strFile
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport,
acSpreadsheetTypeExcel9, _
strTemp, strPathFile
rstMgr.MoveNext
Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Ken Snell [MVP]]

Change this code line:

strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.value)


to this:

strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value & "'")

Because Subinventory is a text field, not a numeric field, you must delimit
the value on the right side of the = sign with ' characters to show that
it's a text value.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Please disregard my last message. I found out that the DAO object
library was unchecked in the references. I have added it again. Now
I receive an error for something different.
I am getting a Runtime Error #3075
Syntax error (missing operator) in query expression 'Subinventory =
WIN 0191'.
The debugger goes back to this:
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.value)

The directions above this part of code say:
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names

I do not have a Manager Name Field. I made a table of all stores that
should be exported and the only field in the table is "Subinventory",
there are no other fields.

Ken -
I made the changes that you suggested, but I think I have some other
problems now. I press the button, but I receive a Compile error
asying "User-defined type not defined." When the debug opens it
highlights "qdf As DAO.QueryDef" at the top of the code. I made sure
that I added the modules again, I do not know why I am getting this
error now and I did not get it before.

Hi Ken -
Thank you for the update. Do not worry about the timeliness of your
response, it is no problem at all. I am just happy to have someone
that can answer my questions! Unfortunately, I cannot make these
additions to my database at this time. I came in this morning and I
tried to open the database only to receive an error message saying the
"database cannot be opened because the VBA project contained in it
cannot be read." It only gives an option to make a copy of the
database or delete all of the VBA from all modules, forms, and
reports. So essentially I think I just lost all of my stuff. I had a
backup, but it unfortunately is not current, there is some code but
not everything.
I am going to start making another database now, it should not take
too long for me to get back up and running since I did not really have
a lot of code, and most of it is saved in emails and in message posts
on here.
Once I get your new change suggestions in I will let you know the
outcome.
Thanks.

Erick

Your SQL statement has incorrect syntax. Change these two code lines:
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
to these:
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
I haven't had a chance to work on an answer to your questions in your
first
post, but I will do so soon.
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
One addition to my previous post:
If I try to run the code I can select the folder that I want to create
the files in, but I then receive a runtime error #3131. When I open
the debudder it highlights the following line in the code:
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
Hello again.
I think that I may have finally tried to make something that may not
be possible. I want to create multiple excel spreadsheets for each
unique identifier (Subinventory) in one of my tables. I tried to
modify the code provided by Ken Snell on his very helpful website,
but
I tried to get a bit too inventive.
I also tried to combine the code for browsing to a single folder to
export the data. So, essentially I was trying to create a button
that
would open a dialog box where the user can select the correct folder
and then the individual spreadsheets are created in that folder.
I also had a bit of trouble matching up information for the code
since
the example uses manager and associate names along with an ID
number.
I do not have an actual name, I only have a number for a location. I
made a separate table with only the store numbers (Subinventories)
that are being exported. The other table has all of the data to be
exported.
I don't know if anybody has ever done anything like this, or if this
is even possible. Any help would be appreciated.
Here is my code. I also left the example information for reference
purposes.
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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's
identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query for
each ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM Subinv Subtotal By Transaction
Type WHERE " & _
"Subinventory = " & rstMgr!Subinventory.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & strMgr & ".xls"
strPathFile = strPath & "\" & strFile
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport,
acSpreadsheetTypeExcel9, _
strTemp, strPathFile
rstMgr.MoveNext
Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Erick C]

Ken -
Thank you for the update. I have made the changes that you
specified. I get a new error a few rows down from the previous one.
It is a runtime error 3131, Syntax error in FROM clause. The debugger
highlights the following:
qdf.SQL = strSQL
I also looked at the temporary slect queries that were created, q_WIN
191 and zExportQuery. When I view either query they both contain all
of the headers from a table that is not identified anywhere in the
code. I am tring to pull information from the [Subinv Subtotal By
Transaction Type] table as specified in the code, but the two queries
are being built from the [All Transactions Crosstab] table which is
not specified anywhere in the code. I don't know why it is picking
this table to create the queries from.

Change this code line:

strMgr = DLookup("Subinventory", "Stores for Export", _
                  "Subinventory = " & rstMgr!Subinventory.value)

to this:

strMgr = DLookup("Subinventory", "Stores for Export", _
                  "Subinventory = '" & rstMgr!Subinventory.value & "'")

Because Subinventory is a text field, not a numeric field, you must delimit
the value on the right side of the = sign with ' characters to show that
it's a text value.

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Please disregard my last message.  I found out that the DAO object
library was unchecked in the references.  I have added it again.  Now
I receive an error for something different.
I am getting a Runtime Error #3075
Syntax error (missing operator) in query expression 'Subinventory =
WIN 0191'.
The debugger goes back to this:
strMgr = DLookup("Subinventory", "Stores for Export", _
                  "Subinventory = " & rstMgr!Subinventory.value)

The directions above this part of code say:
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names

I do not have a Manager Name Field.  I made a table of all stores that
should be exported and the only field in the table is "Subinventory",
there are no other fields.

Ken -
I made the changes that you suggested, but I think I have some other
problems now. I press the button, but I receive a Compile error
asying "User-defined type not defined." When the debug opens it
highlights "qdf As DAO.QueryDef" at the top of the code. I made sure
that I added the modules again, I do not know why I am getting this
error now and I did not get it before.

Hi Ken -
Thank you for the update. Do not worry about the timeliness of your
response, it is no problem at all. I am just happy to have someone
that can answer my questions! Unfortunately, I cannot make these
additions to my database at this time. I came in this morning and I
tried to open the database only to receive an error message saying the
"database cannot be opened because the VBA project contained in it
cannot be read." It only gives an option to make a copy of the
database or delete all of the VBA from all modules, forms, and
reports. So essentially I think I just lost all of my stuff. I had a
backup, but it unfortunately is not current, there is some code but
not everything.
I am going to start making another database now, it should not take
too long for me to get back up and running since I did not really have
a lot of code, and most of it is saved in emails and in message posts
on here.
Once I get your new change suggestions in I will let you know the
outcome.
Thanks.
Erick
On Jul 23, 5:53 am, "Ken Snell [MVP]"
Your SQL statement has incorrect syntax. Change these two code lines:
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
to these:
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
I haven't had a chance to work on an answer to your questions in your
first
post, but I will do so soon.
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
One addition to my previous post:
If I try to run the code I can select the folder that I want to create
the files in, but I then receive a runtime error #3131. When I open
the debudder it highlights the following line in the code:
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
Hello again.
I think that I may have finally tried to make something that may not
be possible. I want to create multiple excel spreadsheets for each
unique identifier (Subinventory) in one of my tables. I tried to
modify the code provided by Ken Snell on his very helpful website,
but
I tried to get a bit too inventive.
I also tried to combine the code for browsing to a single folder to
export the data. So, essentially I was trying to create a button
that
would open a dialog box where the user can select the correct folder
and then the individual spreadsheets are created in that folder.
I also had a bit of trouble matching up information for the code
since
the example uses manager and associate names along with an ID
number.
I do not have an actual name, I only have a number for a location.. I
made a separate table with only the store numbers (Subinventories)
that are being exported. The other table has all of the data to be
exported.
I don't know if anybody has ever done anything like this, or if this
is even possible. Any help would be appreciated.
Here is my code. I also left the example information for reference
purposes.
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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's
identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query for
each ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM Subinv Subtotal By Transaction
Type WHERE " & _
"Subinventory = " & rstMgr!Subinventory.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & strMgr & ".xls"
strPathFile = strPath & "\" & strFile
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport,
acSpreadsheetTypeExcel9, _
strTemp, strPathFile
rstMgr.MoveNext
Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Ken Snell [MVP]]

This code line:

strSQL = "SELECT * FROM Subinv Subtotal By Transaction Type WHERE " & _
"Subinventory = " & rstMgr!Subinventory.Value & ";"


needs to be changed to this:

strSQL = "SELECT * FROM [Subinv Subtotal By Transaction Type] WHERE " & _
"Subinventory = " & rstMgr!Subinventory.Value & ";"


Whenever you have characters other than letters, numbers, or underscore in a
field, table, or query name, you must enclose the name with [ ] brackets.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Ken -
Thank you for the update. I have made the changes that you
specified. I get a new error a few rows down from the previous one.
It is a runtime error 3131, Syntax error in FROM clause. The debugger
highlights the following:
qdf.SQL = strSQL
I also looked at the temporary slect queries that were created, q_WIN
191 and zExportQuery. When I view either query they both contain all
of the headers from a table that is not identified anywhere in the
code. I am tring to pull information from the [Subinv Subtotal By
Transaction Type] table as specified in the code, but the two queries
are being built from the [All Transactions Crosstab] table which is
not specified anywhere in the code. I don't know why it is picking
this table to create the queries from.

Change this code line:

strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.value)

to this:

strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value & "'")

Because Subinventory is a text field, not a numeric field, you must
delimit
the value on the right side of the = sign with ' characters to show that
it's a text value.

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Please disregard my last message. I found out that the DAO object
library was unchecked in the references. I have added it again. Now
I receive an error for something different.
I am getting a Runtime Error #3075
Syntax error (missing operator) in query expression 'Subinventory =
WIN 0191'.
The debugger goes back to this:
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.value)

The directions above this part of code say:
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names

I do not have a Manager Name Field. I made a table of all stores that
should be exported and the only field in the table is "Subinventory",
there are no other fields.

Ken -
I made the changes that you suggested, but I think I have some other
problems now. I press the button, but I receive a Compile error
asying "User-defined type not defined." When the debug opens it
highlights "qdf As DAO.QueryDef" at the top of the code. I made sure
that I added the modules again, I do not know why I am getting this
error now and I did not get it before.

Hi Ken -
Thank you for the update. Do not worry about the timeliness of your
response, it is no problem at all. I am just happy to have someone
that can answer my questions! Unfortunately, I cannot make these
additions to my database at this time. I came in this morning and I
tried to open the database only to receive an error message saying the
"database cannot be opened because the VBA project contained in it
cannot be read." It only gives an option to make a copy of the
database or delete all of the VBA from all modules, forms, and
reports. So essentially I think I just lost all of my stuff. I had a
backup, but it unfortunately is not current, there is some code but
not everything.
I am going to start making another database now, it should not take
too long for me to get back up and running since I did not really have
a lot of code, and most of it is saved in emails and in message posts
on here.
Once I get your new change suggestions in I will let you know the
outcome.
Thanks.
Erick
On Jul 23, 5:53 am, "Ken Snell [MVP]"
Your SQL statement has incorrect syntax. Change these two code
lines:
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
to these:
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
I haven't had a chance to work on an answer to your questions in
your
first
post, but I will do so soon.
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
One addition to my previous post:
If I try to run the code I can select the folder that I want to
create
the files in, but I then receive a runtime error #3131. When I open
the debudder it highlights the following line in the code:
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
Hello again.
I think that I may have finally tried to make something that may
not
be possible. I want to create multiple excel spreadsheets for each
unique identifier (Subinventory) in one of my tables. I tried to
modify the code provided by Ken Snell on his very helpful website,
but
I tried to get a bit too inventive.
I also tried to combine the code for browsing to a single folder
to
export the data. So, essentially I was trying to create a button
that
would open a dialog box where the user can select the correct
folder
and then the individual spreadsheets are created in that folder.
I also had a bit of trouble matching up information for the code
since
the example uses manager and associate names along with an ID
number.
I do not have an actual name, I only have a number for a location.
I
made a separate table with only the store numbers (Subinventories)
that are being exported. The other table has all of the data to be
exported.
I don't know if anybody has ever done anything like this, or if
this
is even possible. Any help would be appreciated.
Here is my code. I also left the example information for reference
purposes.
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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's
identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table
and
field names
' with the real names of the EmployeesTable table and the
ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query for
each ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM Subinv Subtotal By Transaction
Type WHERE " & _
"Subinventory = " & rstMgr!Subinventory.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & strMgr & ".xls"
strPathFile = strPath & "\" & strFile
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport,
acSpreadsheetTypeExcel9, _
strTemp, strPathFile
rstMgr.MoveNext
Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Erick C]

Hi Ken -
I made the change that you suggested, but I am getting a new Syntax
error (missing operator) in query expression 'Subinventory = WIN 0191'
for the same strSQL.
Here is my code:
strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
"Subinventory = " & rstMgr!Subinventory.value & ";"

This code line:

strSQL = "SELECT * FROM Subinv Subtotal By Transaction Type WHERE " & _
"Subinventory = " & rstMgr!Subinventory.Value & ";"

needs to be changed to this:

strSQL = "SELECT * FROM [Subinv Subtotal By Transaction Type] WHERE " &_
"Subinventory = " & rstMgr!Subinventory.Value & ";"

Whenever you have characters other than letters, numbers, or underscore in a
field, table, or query name, you must enclose the name with [ ] brackets.

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Ken -
Thank you for the update.  I have made the changes that you
specified.  I get a new error a few rows down from the previous one.
It is a runtime error 3131, Syntax error in FROM clause.  The debugger
highlights the following:
qdf.SQL = strSQL
I also looked at the temporary slect queries that were created, q_WIN
191 and zExportQuery.  When I view either query they both contain all
of the headers from a table that is not identified anywhere in the
code.  I am tring to pull information from the [Subinv Subtotal By
Transaction Type] table as specified in the code, but the two queries
are being built from the [All Transactions Crosstab] table which is
not specified anywhere in the code.  I don't know why it is picking
this table to create the queries from.

Change this code line:

strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.value)

to this:

strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value & "'")

Because Subinventory is a text field, not a numeric field, you must
delimit
the value on the right side of the = sign with ' characters to show that
it's a text value.

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

"Erick C" <[email protected]> wrote in message

Please disregard my last message. I found out that the DAO object
library was unchecked in the references. I have added it again. Now
I receive an error for something different.
I am getting a Runtime Error #3075
Syntax error (missing operator) in query expression 'Subinventory =
WIN 0191'.
The debugger goes back to this:
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.value)

The directions above this part of code say:
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names

I do not have a Manager Name Field. I made a table of all stores that
should be exported and the only field in the table is "Subinventory",
there are no other fields.

Ken -
I made the changes that you suggested, but I think I have some other
problems now. I press the button, but I receive a Compile error
asying "User-defined type not defined." When the debug opens it
highlights "qdf As DAO.QueryDef" at the top of the code. I made sure
that I added the modules again, I do not know why I am getting this
error now and I did not get it before.
Hi Ken -
Thank you for the update. Do not worry about the timeliness of your
response, it is no problem at all. I am just happy to have someone
that can answer my questions! Unfortunately, I cannot make these
additions to my database at this time. I came in this morning and I
tried to open the database only to receive an error message saying the
"database cannot be opened because the VBA project contained in it
cannot be read." It only gives an option to make a copy of the
database or delete all of the VBA from all modules, forms, and
reports. So essentially I think I just lost all of my stuff. I had a
backup, but it unfortunately is not current, there is some code but
not everything.
I am going to start making another database now, it should not take
too long for me to get back up and running since I did not really have
a lot of code, and most of it is saved in emails and in message posts
on here.
Once I get your new change suggestions in I will let you know the
outcome.
Thanks.
Erick
On Jul 23, 5:53 am, "Ken Snell [MVP]"
Your SQL statement has incorrect syntax. Change these two code
lines:
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
to these:
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
I haven't had a chance to work on an answer to your questions in
your
first
post, but I will do so soon.
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
One addition to my previous post:
If I try to run the code I can select the folder that I want to
create
the files in, but I then receive a runtime error #3131. When I open
the debudder it highlights the following line in the code:
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
Hello again.
I think that I may have finally tried to make something that may
not
be possible. I want to create multiple excel spreadsheets for each
unique identifier (Subinventory) in one of my tables. I tried to
modify the code provided by Ken Snell on his very helpful website,
but
I tried to get a bit too inventive.
I also tried to combine the code for browsing to a single folder
to
export the data. So, essentially I was trying to create a button
that
would open a dialog box where the user can select the correct
folder
and then the individual spreadsheets are created in that folder..
I also had a bit of trouble matching up information for the code
since
the example uses manager and associate names along with an ID
number.
I do not have an actual name, I only have a number for a location.
I
made a separate table with only the store numbers (Subinventories)
that are being exported. The other table has all of the data tobe
exported.
I don't know if anybody has ever done anything like this, or if
this
is even possible. Any help would be appreciated.
Here is my code. I also left the example information for reference
purposes.
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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files willbe
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's
identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table
and
field names
' with the real names of the EmployeesTable table and the
ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query for
each ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to

...

read more »- Hide quoted text -

- Show quoted text -

 

[Ken Snell [MVP]]

Because Subinventory is a text field, not a numeric field, you must delimit
the value on the right side of the = sign with ' characters to show that
it's a text value.

strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.value & "';"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Hi Ken -
I made the change that you suggested, but I am getting a new Syntax
error (missing operator) in query expression 'Subinventory = WIN 0191'
for the same strSQL.
Here is my code:
strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
"Subinventory = " & rstMgr!Subinventory.value & ";"

This code line:

strSQL = "SELECT * FROM Subinv Subtotal By Transaction Type WHERE " & _
"Subinventory = " & rstMgr!Subinventory.Value & ";"

needs to be changed to this:

strSQL = "SELECT * FROM [Subinv Subtotal By Transaction Type] WHERE " & _
"Subinventory = " & rstMgr!Subinventory.Value & ";"

Whenever you have characters other than letters, numbers, or underscore in
a
field, table, or query name, you must enclose the name with [ ] brackets.

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Ken -
Thank you for the update. I have made the changes that you
specified. I get a new error a few rows down from the previous one.
It is a runtime error 3131, Syntax error in FROM clause. The debugger
highlights the following:
qdf.SQL = strSQL
I also looked at the temporary slect queries that were created, q_WIN
191 and zExportQuery. When I view either query they both contain all
of the headers from a table that is not identified anywhere in the
code. I am tring to pull information from the [Subinv Subtotal By
Transaction Type] table as specified in the code, but the two queries
are being built from the [All Transactions Crosstab] table which is
not specified anywhere in the code. I don't know why it is picking
this table to create the queries from.

Change this code line:

strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.value)

to this:

strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value & "'")

Because Subinventory is a text field, not a numeric field, you must
delimit
the value on the right side of the = sign with ' characters to show that
it's a text value.

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

"Erick C" <[email protected]> wrote in message

Please disregard my last message. I found out that the DAO object
library was unchecked in the references. I have added it again. Now
I receive an error for something different.
I am getting a Runtime Error #3075
Syntax error (missing operator) in query expression 'Subinventory =
WIN 0191'.
The debugger goes back to this:
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.value)

The directions above this part of code say:
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names

I do not have a Manager Name Field. I made a table of all stores that
should be exported and the only field in the table is "Subinventory",
there are no other fields.

Ken -
I made the changes that you suggested, but I think I have some other
problems now. I press the button, but I receive a Compile error
asying "User-defined type not defined." When the debug opens it
highlights "qdf As DAO.QueryDef" at the top of the code. I made sure
that I added the modules again, I do not know why I am getting this
error now and I did not get it before.
Hi Ken -
Thank you for the update. Do not worry about the timeliness of your
response, it is no problem at all. I am just happy to have someone
that can answer my questions! Unfortunately, I cannot make these
additions to my database at this time. I came in this morning and I
tried to open the database only to receive an error message saying
the
"database cannot be opened because the VBA project contained in it
cannot be read." It only gives an option to make a copy of the
database or delete all of the VBA from all modules, forms, and
reports. So essentially I think I just lost all of my stuff. I had a
backup, but it unfortunately is not current, there is some code but
not everything.
I am going to start making another database now, it should not take
too long for me to get back up and running since I did not really
have
a lot of code, and most of it is saved in emails and in message
posts
on here.
Once I get your new change suggestions in I will let you know the
outcome.
Thanks.
Erick
On Jul 23, 5:53 am, "Ken Snell [MVP]"
Your SQL statement has incorrect syntax. Change these two code
lines:
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
to these:
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
I haven't had a chance to work on an answer to your questions in
your
first
post, but I will do so soon.
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
One addition to my previous post:
If I try to run the code I can select the folder that I want to
create
the files in, but I then receive a runtime error #3131. When I
open
the debudder it highlights the following line in the code:
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
Hello again.
I think that I may have finally tried to make something that may
not
be possible. I want to create multiple excel spreadsheets for
each
unique identifier (Subinventory) in one of my tables. I tried to
modify the code provided by Ken Snell on his very helpful
website,
but
I tried to get a bit too inventive.
I also tried to combine the code for browsing to a single folder
to
export the data. So, essentially I was trying to create a button
that
would open a dialog box where the user can select the correct
folder
and then the individual spreadsheets are created in that folder.
I also had a bit of trouble matching up information for the code
since
the example uses manager and associate names along with an ID
number.
I do not have an actual name, I only have a number for a
location.
I
made a separate table with only the store numbers
(Subinventories)
that are being exported. The other table has all of the data to
be
exported.
I don't know if anybody has ever done anything like this, or if
this
is even possible. Any help would be appreciated.
Here is my code. I also left the example information for
reference
purposes.
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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's
identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table
and
field names
' with the real names of the EmployeesTable table and the
ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset,
dbReadOnly)
' Now loop through list of ManagerID values and create a query
for
each ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to

...

read more »- Hide quoted text -

- Show quoted text -

 

[Erick C]

Hello Ken -
Again you have saved the day! I made the changes that you outlined
and the export is now working.
I do have one more question regarding this code. I really do not know
if it is possible, but there was a code that you helped me with
previously that exported data to particular worksheets in a workbook.
Is there a way to combine these two codes so as the individual files
are being created other worksheets are being exported as well.
So with the code that is now working, can I add another DoCmd at the
end before it loops that will export information to a particular
worksheet?
I was thinking about something like this code at the bottom

strTable = "Subinventory Item Crosstab"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, ,"Subinv Quantities"

Also, in the current code that was just completed there is a portion
at the end of DoCmd that says rstMgr.MoveNext. Does this cause the
quert to go to the next store and export? Would this have to be moved
to the last DoCmd in the list?

I apologize for all of my questions, and I greatly appreciate all of
the help that you have given to me and it is greatly appreciated. If
I start asking too many questions feel free to let me know and I will
stop.

Thanks.

Because Subinventory is a text field, not a numeric field, you must delimit
the value on the right side of the = sign with ' characters to show that
it's a text value.

            strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
                "Subinventory = '" & rstMgr!Subinventory.value & "';"

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Hi Ken -
I made the change that you suggested, but I am getting a new Syntax
error (missing operator) in query expression 'Subinventory = WIN 0191'
for the same strSQL.
Here is my code:
            strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
                "Subinventory = " & rstMgr!Subinventory..value & ";"

This code line:

strSQL = "SELECT * FROM Subinv Subtotal By Transaction Type WHERE " &_
"Subinventory = " & rstMgr!Subinventory.Value & ";"

needs to be changed to this:

strSQL = "SELECT * FROM [Subinv Subtotal By Transaction Type] WHERE "& _
"Subinventory = " & rstMgr!Subinventory.Value & ";"

Whenever you have characters other than letters, numbers, or underscorein
a
field, table, or query name, you must enclose the name with [ ] brackets.

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

"Erick C" <[email protected]> wrote in message

Ken -
Thank you for the update. I have made the changes that you
specified. I get a new error a few rows down from the previous one.
It is a runtime error 3131, Syntax error in FROM clause. The debugger
highlights the following:
qdf.SQL = strSQL
I also looked at the temporary slect queries that were created, q_WIN
191 and zExportQuery. When I view either query they both contain all
of the headers from a table that is not identified anywhere in the
code. I am tring to pull information from the [Subinv Subtotal By
Transaction Type] table as specified in the code, but the two queries
are being built from the [All Transactions Crosstab] table which is
not specified anywhere in the code. I don't know why it is picking
this table to create the queries from.

Change this code line:
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.value)
to this:
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value & "'")
Because Subinventory is a text field, not a numeric field, you must
delimit
the value on the right side of the = sign with ' characters to showthat
it's a text value.
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
Please disregard my last message. I found out that the DAO object
library was unchecked in the references. I have added it again. Now
I receive an error for something different.
I am getting a Runtime Error #3075
Syntax error (missing operator) in query expression 'Subinventory =
WIN 0191'.
The debugger goes back to this:
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = " & rstMgr!Subinventory.value)
The directions above this part of code say:
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
I do not have a Manager Name Field. I made a table of all stores that
should be exported and the only field in the table is "Subinventory",
there are no other fields.
Ken -
I made the changes that you suggested, but I think I have some other
problems now. I press the button, but I receive a Compile error
asying "User-defined type not defined." When the debug opens it
highlights "qdf As DAO.QueryDef" at the top of the code. I made sure
that I added the modules again, I do not know why I am getting this
error now and I did not get it before.
Hi Ken -
Thank you for the update. Do not worry about the timeliness of your
response, it is no problem at all. I am just happy to have someone
that can answer my questions! Unfortunately, I cannot make these
additions to my database at this time. I came in this morning andI
tried to open the database only to receive an error message saying
the
"database cannot be opened because the VBA project contained in it
cannot be read." It only gives an option to make a copy of the
database or delete all of the VBA from all modules, forms, and
reports. So essentially I think I just lost all of my stuff. I had a
backup, but it unfortunately is not current, there is some code but
not everything.
I am going to start making another database now, it should not take
too long for me to get back up and running since I did not really
have
a lot of code, and most of it is saved in emails and in message
posts
on here.
Once I get your new change suggestions in I will let you know the
outcome.
Thanks.
Erick
On Jul 23, 5:53 am, "Ken Snell [MVP]"
Your SQL statement has incorrect syntax. Change these two code
lines:
strSQL = "SELECT DISTINCT Subinventory FROM Subinv Subtotal By
Transaction Type;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
to these:
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
I haven't had a chance to work on an answer to your questions in
your
first
post, but I will do so soon.
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
One addition to my previous post:
If I try to run the code I can select the folder that I want to
create
the files in, but I then receive a runtime error #3131. When I
open
the debudder it highlights the following line in the code:
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
Hello again.
I think that I may have finally tried to make something that may
not
be possible. I want to create multiple excel spreadsheets for
each
unique identifier (Subinventory) in one of my tables. I triedto
modify the code provided by Ken Snell on his very helpful
website,
but
I tried to get a bit too inventive.
I also tried to combine the code for browsing to a single folder
to
export the data. So, essentially I was trying to create a button
that
would open a dialog box where the user can select the correct
folder
and then the individual spreadsheets are created in that folder.
I also had a bit of trouble matching up information for the code
since
the example uses manager and associate names along with an ID
number.
I do not have an actual name, I only have a number for a
location.
I
made a separate table with only the store numbers
(Subinventories)
that are being exported. The other table has all of the data to
be
exported.
I don't know if anybody has ever done anything like this, or if
this
is even possible. Any help would be appreciated.
Here is my code. I also left the example information for
reference
purposes.
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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's
identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf =

...

read more »- Hide quoted text -

- Show quoted text -

 

[Ken Snell [MVP]]

The easiest way to do the export to a specific worksheet is to export a
query that is named the same as the name you want to have on the worksheet.

The original code that you posted in this thread was doing that in the part
where it set the properties of the qdf object:

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM Subinv Subtotal By Transaction
Type WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL


The qdf.Name step is the one that names the query so that you could export
it in the TransferSpreadsheet action. You'd use strTemp instead of strTable.

To give you more specific assistance, I'd need to have some specific
examples, along with the "final" code that you now are using, to show me
what you want to do. And there is no problem with more questions.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Hello Ken -
Again you have saved the day! I made the changes that you outlined
and the export is now working.
I do have one more question regarding this code. I really do not know
if it is possible, but there was a code that you helped me with
previously that exported data to particular worksheets in a workbook.
Is there a way to combine these two codes so as the individual files
are being created other worksheets are being exported as well.
So with the code that is now working, can I add another DoCmd at the
end before it loops that will export information to a particular
worksheet?
I was thinking about something like this code at the bottom

strTable = "Subinventory Item Crosstab"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, ,"Subinv Quantities"

Also, in the current code that was just completed there is a portion
at the end of DoCmd that says rstMgr.MoveNext. Does this cause the
quert to go to the next store and export? Would this have to be moved
to the last DoCmd in the list?

I apologize for all of my questions, and I greatly appreciate all of
the help that you have given to me and it is greatly appreciated. If
I start asking too many questions feel free to let me know and I will
stop.

Thanks.

 

[Erick C]

Hi Ken -
Thank you for your reply. I apologize, I should have worded my
previous post a bit differently. Currently the code is taking one
table and splitting it up by subinventory and creating an individual
file. What I am attempting to do is once the file is created from the
temp query for "Subinventory #1" then the process repeats itself for
another table, finds the same subinventory number that was used to
create the file for a new temp query, and export that table
information into a new worksheet in the file that was just created.
Once the new tab has been added to the file then the process loops and
moves to "Subinventory #2" and creates a new file. I tried to modify
the existing code, basically repeating the process from the first
strSQL point. I think you will understand what I tried to do once you
see the code below.
I tried to use the code as it is written below but I get a compile
error that says "Do without loop."

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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value &
"'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

strFile = Format$(Date, "Medium Date") & " " & strMgr &
".xls"
strPathFile = strPath & "\" & strFile

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, strTemp, strPathFile

' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM [CC Adj Totals];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value &
"'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [CC Adj Totals] WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
_
strTemp, strPathFile, ,"CC Adj Totals"
rstMgr.MoveNext

Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

MsgBox "Individual Analysis Files Created.", vbOK, "Status"

 

[Ken Snell [MVP]]

OK - This code should do what you seek. It uses two separate Do loops for
the two different tables.

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By Transaction
Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [Subinv Subtotal By Transaction Type]
WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

strFile = Format$(Date, "Medium Date") & " " & strMgr & ".xls"
strPathFile = strPath & "\" & strFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, strPathFile

rstMgr.MoveNext
Loop
End If



' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [CC Adj Totals];"
Set rstMgr1 = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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 rstMgr1.EOF = False And rstMgr1.BOF = False Then
If rstMgr1.EOF = False Then
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [CC Adj Totals] WHERE " & _
"Subinventory = '" & rstMgr1!Subinventory.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

strFile = Format$(Date, "Medium Date") & " " & strMgr & ".xls"
strPathFile = strPath & "\" & strFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, strPathFile, , "CC Adj Totals"

rstMgr1.MoveNext

Loop
End If
rstMgr.Close
rstMgr1.Close
Set rstMgr = Nothing
Set rstMgr1 = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

MsgBox "Individual Analysis Files Created.", vbOK, "Status"


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hi Ken -
Thank you for your reply. I apologize, I should have worded my
previous post a bit differently. Currently the code is taking one
table and splitting it up by subinventory and creating an individual
file. What I am attempting to do is once the file is created from the
temp query for "Subinventory #1" then the process repeats itself for
another table, finds the same subinventory number that was used to
create the file for a new temp query, and export that table
information into a new worksheet in the file that was just created.
Once the new tab has been added to the file then the process loops and
moves to "Subinventory #2" and creates a new file. I tried to modify
the existing code, basically repeating the process from the first
strSQL point. I think you will understand what I tried to do once you
see the code below.
I tried to use the code as it is written below but I get a compile
error that says "Do without loop."

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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value &
"'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

strFile = Format$(Date, "Medium Date") & " " & strMgr &
".xls"
strPathFile = strPath & "\" & strFile

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, strTemp, strPathFile

' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM [CC Adj Totals];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value &
"'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [CC Adj Totals] WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
_
strTemp, strPathFile, ,"CC Adj Totals"
rstMgr.MoveNext

Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

MsgBox "Individual Analysis Files Created.", vbOK, "Status"

 

[Erick C]

Ken -
Thank you for the update. As always, you are the greatest!
I have added the code that you modified and I am getting an error:
Compile Error: Loop Without Do
The debugger highlights the loop at the end, right before the
rstMgr.Close and rstMgr1.Close.
How can I get rid of this error?

OK - This code should do what you seek. It uses two separate Do loops for
the two different tables.

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
      MsgBox "No folder was selected.", vbOK, "No Selection"
      Exit Sub
End If

Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By Transaction
Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
           strMgr = DLookup("Subinventory", "Stores for Export", _
                  "Subinventory = '" & rstMgr!Subinventory.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
            strSQL = "SELECT * FROM [Subinv Subtotal By Transaction Type]
WHERE " & _
                "Subinventory = '" & rstMgr!Subinventory.Value & "';"
            Set qdf = dbs.QueryDefs(strTemp)
            qdf.Name = "q_" & strMgr
            strTemp = qdf.Name
            qdf.SQL = strSQL
            qdf.Close
            Set qdf = Nothing

          strFile = Format$(Date, "Medium Date") & " " & strMgr & ".xls"
          strPathFile = strPath & "\" & strFile

            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, strPathFile

          rstMgr.MoveNext
     Loop
End If

' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [CC Adj Totals];"
Set rstMgr1 = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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 rstMgr1.EOF = False And rstMgr1.BOF = False Then
      If rstMgr1.EOF = False Then
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
           strMgr = DLookup("Subinventory", "Stores for Export", _
                  "Subinventory = '" & rstMgr!Subinventory.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
            strSQL = "SELECT * FROM [CC Adj Totals] WHERE "& _
                "Subinventory = '" & rstMgr1!Subinventory.Value & "';"
            Set qdf = dbs.QueryDefs(strTemp)
            qdf.Name = "q_" & strMgr
            strTemp = qdf.Name
            qdf.SQL = strSQL
            qdf.Close
            Set qdf = Nothing

          strFile = Format$(Date, "Medium Date") & " " & strMgr & ".xls"
          strPathFile = strPath & "\" & strFile

          DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
          strTemp, strPathFile, , "CC Adj Totals"

          rstMgr1.MoveNext

      Loop
End If
rstMgr.Close
rstMgr1.Close
Set rstMgr = Nothing
Set rstMgr1 = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

MsgBox "Individual Analysis Files Created.", vbOK, "Status"

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Hi Ken -
Thank you for your reply.  I apologize, I should have worded my
previous post a bit differently.  Currently the code is taking one
table and splitting it up by subinventory and creating an individual
file.  What I am attempting to do is once the file is created from the
temp query for "Subinventory #1" then the process repeats itself for
another table, finds the same subinventory number that was used to
create the file for a new temp query, and export that table
information into a new worksheet in the file that was just created.
Once the new tab has been added to the file then the process loops and
moves to "Subinventory #2" and creates a new file.  I tried to modify
the existing code, basically repeating the process from the first
strSQL point.  I think you will understand what I tried to do once you
see the code below.
I tried to use the code as it is written below but I get a compile
error that says "Do without loop."

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
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
      MsgBox "No folder was selected.", vbOK, "No Selection"
      Exit Sub
End If

Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
           strMgr = DLookup("Subinventory", "Stores for Export", _
                  "Subinventory = '" & rstMgr!Subinventory.value &
"'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
            strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
                "Subinventory = '" & rstMgr!Subinventory.value & "';"
            Set qdf = dbs.QueryDefs(strTemp)
            qdf.Name = "q_" & strMgr
            strTemp = qdf.Name
            qdf.SQL = strSQL
            qdf.Close
            Set qdf = Nothing

          strFile = Format$(Date, "Medium Date") & " " & strMgr &
".xls"
          strPathFile = strPath & "\" & strFile

            DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, strTemp, strPathFile

' Get list of ManagerID values -- note: replace my generic table and
field names
' with the real names of the EmployeesTable table and the ManagerID
field
strSQL = "SELECT DISTINCT Subinventory FROM [CC Adj Totals];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
...

read more »

 

[Ken Snell [MVP]]

Sorry - copy/paste error on my part:

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By Transaction
Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [Subinv Subtotal By Transaction Type]
WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

strFile = Format$(Date, "Medium Date") & " " & strMgr & ".xls"
strPathFile = strPath & "\" & strFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, strPathFile

rstMgr.MoveNext
Loop
End If



' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [CC Adj Totals];"
Set rstMgr1 = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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 rstMgr1.EOF = False And rstMgr1.BOF = False Then
Do While rstMgr1.EOF = False Then
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [CC Adj Totals] WHERE " & _
"Subinventory = '" & rstMgr1!Subinventory.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

strFile = Format$(Date, "Medium Date") & " " & strMgr & ".xls"
strPathFile = strPath & "\" & strFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, strPathFile, , "CC Adj Totals"

rstMgr1.MoveNext

Loop
End If
rstMgr.Close
rstMgr1.Close
Set rstMgr = Nothing
Set rstMgr1 = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

MsgBox "Individual Analysis Files Created.", vbOK, "Status"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Ken -
Thank you for the update. As always, you are the greatest!
I have added the code that you modified and I am getting an error:
Compile Error: Loop Without Do
The debugger highlights the loop at the end, right before the
rstMgr.Close and rstMgr1.Close.
How can I get rid of this error?

 

[Erick C]

Hi Ken -
I made the update from your last response.
When I tried to run I received a syntax error and the debugger went to
the rstMgr1.EOF portion. The debugger highlighted the Do While
rstMgr1.EOF portion:

If rstMgr1.EOF = False And rstMgr1.BOF = False Then
Do While rstMgr1.EOF = False Then

I looked at the code above this spot and saw that the previous "Do
While" did not have the Then at the end, so I removed this one and
tried to run again.
On the second one, I received a runtime error 3021 saying No Current
Record.
This time the debugger highlighted the Dlookup code directly below the
rstMgr1.EOF from above.

' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.Value &
"'")

Sorry - copy/paste error on my part:

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
      MsgBox "No folder was selected.", vbOK, "No Selection"
      Exit Sub
End If

Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By Transaction
Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
           strMgr = DLookup("Subinventory", "Stores for Export", _
                  "Subinventory = '" & rstMgr!Subinventory.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
            strSQL = "SELECT * FROM [Subinv Subtotal By Transaction Type]
WHERE " & _
                "Subinventory = '" & rstMgr!Subinventory.Value & "';"
            Set qdf = dbs.QueryDefs(strTemp)
            qdf.Name = "q_" & strMgr
            strTemp = qdf.Name
            qdf.SQL = strSQL
            qdf.Close
            Set qdf = Nothing

          strFile = Format$(Date, "Medium Date") & " " & strMgr & ".xls"
          strPathFile = strPath & "\" & strFile

            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, strPathFile

          rstMgr.MoveNext
     Loop
End If

' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [CC Adj Totals];"
Set rstMgr1 = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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 rstMgr1.EOF = False And rstMgr1.BOF = False Then
      Do While rstMgr1.EOF = False Then
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
           strMgr = DLookup("Subinventory", "Stores for Export", _
                  "Subinventory = '" & rstMgr!Subinventory.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
            strSQL = "SELECT * FROM [CC Adj Totals] WHERE "& _
                "Subinventory = '" & rstMgr1!Subinventory.Value & "';"
            Set qdf = dbs.QueryDefs(strTemp)
            qdf.Name = "q_" & strMgr
            strTemp = qdf.Name
            qdf.SQL = strSQL
            qdf.Close
            Set qdf = Nothing

          strFile = Format$(Date, "Medium Date") & " " & strMgr & ".xls"
          strPathFile = strPath & "\" & strFile

          DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
          strTemp, strPathFile, , "CC Adj Totals"

          rstMgr1.MoveNext

      Loop
End If
rstMgr.Close
rstMgr1.Close
Set rstMgr = Nothing
Set rstMgr1 = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

MsgBox "Individual Analysis Files Created.", vbOK, "Status"

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Ken -
Thank you for the update.  As always, you are the greatest!
I have added the code that you modified and I am getting an error:
Compile Error: Loop Without Do
The debugger highlights the loop at the end, right before the
rstMgr.Close and rstMgr1.Close.
How can I get rid of this error?

 

[Ken Snell [MVP]]

We'll get there. This line that you posted:

strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.Value &
"'")

should be this:

strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr1!Subinventory.Value &
"'")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hi Ken -
I made the update from your last response.
When I tried to run I received a syntax error and the debugger went to
the rstMgr1.EOF portion. The debugger highlighted the Do While
rstMgr1.EOF portion:

If rstMgr1.EOF = False And rstMgr1.BOF = False Then
Do While rstMgr1.EOF = False Then

I looked at the code above this spot and saw that the previous "Do
While" did not have the Then at the end, so I removed this one and
tried to run again.
On the second one, I received a runtime error 3021 saying No Current
Record.
This time the debugger highlighted the Dlookup code directly below the
rstMgr1.EOF from above.

' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.Value &
"'")

Sorry - copy/paste error on my part:

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the detail files will be created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction
Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [Subinv Subtotal By Transaction Type]
WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

strFile = Format$(Date, "Medium Date") & " " & strMgr & ".xls"
strPathFile = strPath & "\" & strFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, strPathFile

rstMgr.MoveNext
Loop
End If

' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [CC Adj Totals];"
Set rstMgr1 = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' 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 rstMgr1.EOF = False And rstMgr1.BOF = False Then
Do While rstMgr1.EOF = False Then
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [CC Adj Totals] WHERE " & _
"Subinventory = '" & rstMgr1!Subinventory.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

strFile = Format$(Date, "Medium Date") & " " & strMgr & ".xls"
strPathFile = strPath & "\" & strFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, strPathFile, , "CC Adj Totals"

rstMgr1.MoveNext

Loop
End If
rstMgr.Close
rstMgr1.Close
Set rstMgr = Nothing
Set rstMgr1 = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

MsgBox "Individual Analysis Files Created.", vbOK, "Status"

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Ken -
Thank you for the update. As always, you are the greatest!
I have added the code that you modified and I am getting an error:
Compile Error: Loop Without Do
The debugger highlights the loop at the end, right before the
rstMgr.Close and rstMgr1.Close.
How can I get rid of this error?