You need to tell us which table and field holds the value that you want
to
use as the name of the spreadsheet tab. I cannot tell from your
description
which one you want to use.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Hi Ken
Thank you for taking the time to respond.
There is a table (TBL_BUSRULES). The fields available in this field are
(1)
TXT_BUSRULEID [Auto Number, Primary Key] (2) TXT_SUNCODE [This is a
lookup
field] The query for this look up is...... SELECT
TBL_COADEPARTMENTS.TXT_DEPTID, TBL_COADEPARTMENTS.TXT_SUNCODE,
TBL_COADEPARTMENTS.TXT_DESCRIPTION FROM TBL_COADEPARTMENTS ORDER BY
[TXT_SUNCODE];
The next table is (TBL_BUSRULESMAPPING) There is a lookup field in this
table, which is linked to (TBL_BUSRULES). The fields available in this
table
are: (1) TXT_BUSRULESMAPID [Auto Number, Primary Key] (2)
TXT_ACCOUNTCODE
[Lookup field - but not applicable for the department code I am
seeking]
(3)
TXT_BUSRULEID (This is not a lookup field. The value stored in this
field
is
the TXT_BUSRULEID value from TBL_BUSRULES) (4) TXT_KEYCODE (This is not
a
lookup field)
Thank you in advance for your help
Jason
:
This line of code is "fixing" the name of the query that then is used
as
the
name on the spreadsheet tab:
strMAP = DLookup("TXT_BUSRULESMAPID", "TBL_BUSRULESMAPPING", _
"TXT_BUSRULEID = " & rstMAP!TXT_BUSRULEID.Value)
I'm guessing that TXT_BUSRULESMAPID is a lookup field in your
TBL_BUSRULESMAPPING table? As such, although you see the desired value
in
the table's field, the real value being stored in the field is the
primary
key value corresponding to that value.
You need to change the above DLookup expression to get the real value
from
the correct table. Open up your TBL_BUSRULESMAPPING table in design
view,
click on the TXT_BUSRULESMAPID field, click on the Lookup tab, and get
the
query that is there (assuming you are using a Lookup field). That is
the
info that we need to assist you in changing the DLookup expression.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
HI there Ken,
Thank you very much for the link. I managed to get it working with
one
minor
challenge.
The temp query, is using the primary key value which results in the
spreadsheet tabs named as the primary key. I really need these tabs
to
be
named the value of the field. Have spent many hours trying to work
out
how
to
make this happen, with no success.
Below is the code that I have:
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMAP As DAO.Recordset
Dim strSQL As String, strTemp As String, strMAP As String
Const strFileName As String = "TestingChartMapping"
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT TBL_BUSRULES.TXT_SUNCODE FROM [" & strTemp & "]
WHERE
1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
strSQL = "SELECT DISTINCT TXT_BUSRULEID FROM TBL_BUSRULES;"
Set rstMAP = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
If rstMAP.EOF = False And rstMAP.BOF = False Then
rstMAP.MoveFirst
Do While rstMAP.EOF = False
strMAP = DLookup("TXT_BUSRULESMAPID", "TBL_BUSRULESMAPPING", _
"TXT_BUSRULEID = " & rstMAP!TXT_BUSRULEID.Value)
strSQL = "SELECT TBL_COADEPARTMENTS.TXT_SUNCODE AS DEPT,
TBL_COACHART.TXT_ACCOUNTCODE AS ACCOUNT,
TBL_COACHART.TXT_ACCOUNTNAME
AS
[ACCOUNT DESCRIPTION], TBL_COACHART.TXT_ACTYPE AS TYPE,
TBL_COAA6.TXT_SUNCODE
AS A6, TBL_COACHART.TXT_T0 AS T0, TBL_COACHART.TXT_T1 AS T1,
TBL_COACHART.TXT_T2 AS T2, TBL_COACHART.TXT_T3 AS T3,
TBL_COACHART.TXT_T4
AS
T4, TBL_COACHART.TXT_T5 AS T5, TBL_COACHART.TXT_T6 AS T6,
TBL_COACHART.TXT_T7
AS T7, TBL_COACHART.TXT_T8 AS T8, TBL_COACHART.TXT_T9 AS T9 FROM
(TBL_COAA6
INNER JOIN (TBL_COACHART INNER JOIN TBL_BUSRULESMAPPING ON
TBL_COACHART.TXT_COAID = TBL_BUSRULESMAPPING.TXT_ACCOUNTCODE) ON
TBL_COAA6.TXT_A6ID = TBL_COACHART.TXT_A6) INNER JOIN
(TBL_COADEPARTMENTS
INNER JOIN TBL_BUSRULES ON TBL_COADEPARTMENTS.TXT_DEPTID =
TBL_BUSRULES.TXT_SUNCODE) ON TBL_BUSRULESMAPPING.TXT_BUSRULEID =
TBL_BUSRULES.TXT_BUSRULEID WHERE " & _
"TBL_BUSRULESMAPPING!txt_BUSRULEID = " &
rstMAP!TXT_BUSRULEID.Value
& ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = strMAP
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "D:\My Documents\!!!Projects\Financials\Global Back
Office\" &
strFileName & ".xls"
rstMAP.MoveNext
Loop
End If
rstMAP.Close
Set rstMAP = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
End Sub
Is there any chance you can help me identify where in the code, I
call
for
the dept code?
Thanks in advance
Jason
:
See
Create a Query and Export multiple "filtered" versions of a Query
(based
on
data in another table) to separate Worksheets within one EXCEL file
via
TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#FilterExportSameFile
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Hi all
I have a query that I would like to export (transfer) to Excel.
However,
there is a requirment for the transfer to recognise changes in a
particular
field, and place the results in a separate tab.
The query returns 24,500 records, of which there can be more than
50
one
to
many combinations. So potentially, the export to excel will have
50+
tabs
Is this possible ?
