All,
I am using the following adapted code to export a filtered recordset from a search form in to excel: - Option Explicit
-
-
Private Sub btnexcelexport_Click()
-
'revised for late binding
-
'define variables
-
Dim xlApp As Object
-
Dim xlWorkbook As Object
-
-
-
'create the excel application object
-
Set xlApp = CreateObject("Excel.Application")
-
xlApp.Visible = True
-
-
-
'create a new workbook
-
Set xlWorkbook = xlApp.Workbooks.Add
-
-
-
'define variables
-
Dim objRST As Recordset
-
Dim strSheetname As String
-
-
-
'create the recordset
-
Set objRST = Screen.ActiveForm.RecordsetClone
-
-
-
-
'create a sheet name - must be 30 characters or less
-
strSheetname = "NCC Export - " & Format(Date, "dd.mm.yyyy")
-
-
-
'copy data from the recordset to the cells
-
Dim xlsheet As Object
-
-
-
Set xlsheet = xlWorkbook.Sheets(1)
-
With xlsheet
-
.Cells.CopyFromRecordset objRST
-
.Name = strSheetname
-
End With
-
-
-
'clean up all variables
-
Set objRST = Nothing
-
Set xlsheet = Nothing
-
Set xlWorkbook = Nothing
-
Set xlApp = Nothing
-
-
-
-
End Sub
This does everything as I want however, whenever it comes to a value that is depicted in a combo box it exports the bound column value which is an ID value within my sub tables. The unbound column (text I wish to see) is what is displayed in the actual search form.
How can I edit the above code to export the unbound column? which would be column 1 where the bound is column 0.
The form is filtering a recordset produced from a query just so you know. - 521 40364 40361 9 9 12 Aerofoil milling 1 14 1260
-
Output is shown above. Ignore all the dates being shown as serials this is because I have yet to set any cell formats but as you can see, the textbox field shows up correctly as "aerofoil milling" the other boxes are on the whole combo and show bound columns.
You need to "connect" (JOIN) each remaining ID field in the query with the table holding the Description. So you get as many JOINs as there are so-called Foreign keys.
Instead of the ID you need to place the Description of the Joined field in the query to show.
Clearer ?
Nic;o)
9 3864
Access will "translate" the foreign keys for 1:N relations into a combobox showing the related value from the linked table.
When using the form's recordset you'll find however the "hidden" foreignkey value :-(
The simplest solution I see is to create a query with a JOIN to the external tables to translate the foreign keys and use that for the export.
Nic;o)
Thank you for the response nico. I am not too great with queries just used to using the wizard can you give an example of how I would create such a join?
I basically have a main table which has within it two particular fields relating to departments in the business. These are: DeptRaisedBy and DeptResp these basically are used to record issues raised and who was responsible but since it is all internal to the company the list for both can be the same hence the single table.
Using the wizard in access I have produced a query to select relevant fields I want to display within a form. But it does have the inner joins there to relate the ID values in the main table down to the text within the sub tables. - SELECT DISTINCTROW tbllog.NCC_ID, tbllog.DteReport, tbllog.DteOccur, tbllog.DeptRaisedBy, tbllog.DeptResp, tbllog.NCType, tbllog.NCLocation, tbllog.NCStatus, tbllog.PNumOrRef, tbllog.NCImpact, Sum(tblCosts.CostFig) AS [Sum Of CostFig]
-
FROM (tbldept INNER JOIN tbllog ON tbldept.DeptID = tbllog.DeptRaisedBy) INNER JOIN tblCosts ON tbllog.NCC_ID = tblCosts.NCC_ID
-
GROUP BY tbllog.NCC_ID, tbllog.DteReport, tbllog.DteOccur, tbllog.DeptRaisedBy, tbllog.DeptResp, tbllog.NCType, tbllog.NCLocation, tbllog.NCStatus, tbllog.PNumOrRef, tbllog.NCImpact
-
ORDER BY Sum(tblCosts.CostFig) DESC;
-
As you can see it is quite long but I then use allen brownes form filter script to display and select records from this query.
You can now use the "straight forward" docmd.TransferSpreadsheet statement and use the queryname as the table parameter.
It's just a VBA one liner having this query :-)
When you want the current form filter appended use two queries(see code) and this code behind btnExport: -
Private Sub btnExport_Click()
-
' Define queries
-
Dim qd1 As QueryDef
-
Dim qd2 As QueryDef
-
-
' Set them to saved queries:
-
Set qd1 = CurrentDb.QueryDefs("qryBasic")
-
Set qd2 = CurrentDb.QueryDefs("qryDummy")
-
-
' Test a filter has been set
-
If Me.FilterOn Then
-
' Remove trailing ";" and the "[formname]."
-
qd2.SQL = Replace(qd1.SQL, ";", "") & " WHERE " & Replace(Me.Filter, "[" & Me.Form.Name & "].", "")
-
Else
-
' No filter, so show all
-
qd2.SQL = qd1.SQL
-
End If
-
-
' Export the result
-
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qryDummy", "C:\temp\test.xls", True
-
-
End Sub
-
Getting the idea ?
Nic;o)
I have been messing around with my code and decided to just see what the recordset was showing when I run the search/filter.
It produced the followingin my immediate window after a debug.print: - 521 05/07/2010 02/07/2010 9 9 12 Aerofoil milling 1 Null 14 1260
-
472 03/06/2010 20/05/2010 12 12 10 Lifting Components 1 Null 6 700
-
524 06/07/2010 09/06/2010 3 6 5 Cost Centres 1 Null 3 400
-
522 05/07/2010 25/06/2010 8 6 3 Armstrong meeting room 1 Null 12 125
-
525 06/07/2010 05/06/2010 3 2 2 Expenses 1 Null 3 30
So ofcourse the export will not be showing the departments since the recordset is just the ID numbers no matter how much I think I can mess around with it.
I have taken a look at your code and managed to confuse myself (probably been looking at this too long now) so I will just explain a bit further since I think ive got everything in a mess.
I use Allen brownes form filter script to filter the recordset which is set to a query which I used to select values I wanted from specific tables instead of bringing in everything. I had to then edit this as I wanted to allow for a top N records to be shown producing the following: - Me.RecordSource = "Select " & topinsert & " * FROM logforfiltering WHERE " & strWhere2
Where topinsert is just creating the TOPN and the strWhere2 is the sql string produced from the selection made from combo boxes/text boxes.
So my new record source for the form is a query of a query.
Am I right in assuming in your code I would set qryBasic to be = to my logforfiltering query? and I just create the dummy query blank?
Sorry for all of this I feel like im in to deep but its one of those things I dont want to let go. However I'm liking the more straight forward Docmd method of exporting to excel!
I have been messing a bit more with some SQL to see exactly whats going on to see if i can get the department names to display in the excel export instead of the department IDs. Eventually I have had some success running a small test: - SELECT tbldept.Department
-
FROM tbldept INNER JOIN tbllog ON tbldept.DeptID=tbllog.DeptRaisedBy;
This code working suggests all my joins are actually backwards. I need to go through my original SQL I "think" and reverse the joins. I need to be joining the department from the sub table up to the main table. Since the main table contains the ID and the sub table contains the ID. I have no clue how this is going to break the rest of the code or how hard it will be to implement though..
Am I right in assuming in your code I would set qryBasic to be = to my logforfiltering query? and I just create the dummy query blank?
That's correct. The qryDummy will be overwritten each time.
When you have a strWhere created for the form, then you can use that instead of my "Replace Me.Filter" construction in the procedure.
Personally I prefer to instruct the user to use the right-click popup form to filter (or sort) the form.
Saved me a lot of additional coding :-)
Nic;o)
I created the qryDummy and run the code and it works good to transfer over to excel however it produced the following output: - NCC_ID DteReport DteOccur DeptRaisedBy DeptResp NCType NCLocation NCStatus PNumOrRef NCImpact Sum Of CostFig
-
521 05/07/2010 02/07/2010 9 9 12 Aerofoil milling 1 14 £1,260.00
-
472 03/06/2010 20/05/2010 12 12 10 Lifting Components 1 6 £700.00
-
524 06/07/2010 09/06/2010 3 6 5 Cost Centres 1 3 £400.00
-
522 05/07/2010 25/06/2010 8 6 3 Armstrong meeting room 1 12 £125.00
-
525 06/07/2010 05/06/2010 3 2 2 Expenses 1 3 £30.00
-
The formatting of the cells is spot on but the same issue is still there it seems with the ID number only being referenced and not the actual department value (and other values hence the other 1.2's etc in other columns).
I think this must be something to do with the way I have the joins referencing? as if they are looking up backwards? This is really annoying as I can open the query and select the records and copy and paste them in to excel perfectly. But it doesnt work any other way.
Thanks for all the input so far (and a lot of patience im sure) I feel that I'm slowly getting somewhere
You need to "connect" (JOIN) each remaining ID field in the query with the table holding the Description. So you get as many JOINs as there are so-called Foreign keys.
Instead of the ID you need to place the Description of the Joined field in the query to show.
Clearer ?
Nic;o)
Thanks for the reply nic.
I have managed to break down the sql slowly to atleast getting the correct information going in for department responsible and department raised by.
Using your JOIN hint (which to be honest seems so obvious now but I think i need a break) I just too the two departments and realised I needed to create two references using the following code: - SELECT d1.Department AS DepartmentResp, d2.Department AS DepartmentRaised
-
FROM (tbllog AS t1 INNER JOIN tbldept AS d1 ON t1.DeptResp=d1.DeptID) INNER JOIN tbldept AS d2 ON t1.DeptRaisedBy=d2.DeptID;
Adding this in to my original sql to create the query which gets filtered I produce the following: - SELECT d1.Department AS DepartmentResp, d2.Department AS DepartmentRaised, t1.NCC_ID, t1.DteReport, t1.DteOccur, t1.NCType, t1.NCLocation, t1.NCStatus, t1.PNumOrRef, t1.NCImpact, Sum(tblCosts.CostFig) AS [Sum of cost]
-
FROM ((tbllog AS t1 INNER JOIN tbldept AS d1 ON t1.DeptResp = d1.DeptID) INNER JOIN tbldept AS d2 ON t1.DeptRaisedBy = d2.DeptID) INNER JOIN tblCosts ON t1.NCC_ID = tblCosts.NCC_ID
-
GROUP BY d1.Department, d2.Department, t1.NCC_ID, t1.DteReport, t1.DteOccur, t1.NCType, t1.NCLocation, t1.NCStatus, t1.PNumOrRef, t1.NCImpact
-
ORDER BY Sum(tblCosts.CostFig) DESC;
-
Using the dummy query create to export to excel I now produce the following output: - DepartmentResp DepartmentRaised NCC_ID DteReport DteOccur NCType NCLocation NCStatus PNumOrRef NCImpact Sum of cost
-
Service Facility - Blading Service Facility - Blading 521 05/07/2010 02/07/2010 12 Aerofoil milling 1 14 £1,260.00
-
Service Facility - Heavy Machining Service Facility - Heavy Machining 472 03/06/2010 20/05/2010 10 Lifting Components 1 6 £700.00
-
HS&E Finance & Commercial 524 06/07/2010 09/06/2010 5 Cost Centres 1 3 £400.00
-
HS&E Business Excellence 522 05/07/2010 25/06/2010 3 Armstrong meeting room 1 12 £125.00
-
Field Service Finance & Commercial 525 06/07/2010 05/06/2010 2 Expenses 1 3 £30.00
-
I still need to finish the sql code for the other JOINS but as just a test for the departments everything seems to be running fine :D
Thanks for everything,
Chris
Glad I could help and success with your application Chris !
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Matt |
last post by:
I have an ASP page that calls ASP routines that I created that execute
a database query and return the results to a recordset. I then iterate
through the recordset and display the data in a table....
|
by: Robin Cushman |
last post by:
Hi all,
I need some help -- I'm working with an A2K database, using DAO, and
am trying to read records into a Crystal Report and then export it to
a folder on our network as an Excel...
|
by: ghanley |
last post by:
I have searched the web all day for a lead on this.
I have found how to control the Graph object mut not the embedded excel
unbound object frame.
I am trying to chart the data below on one...
|
by: DC Gringo |
last post by:
I have a simple button that should open another window and export a datagrid
to an Excel file. I'm getting: "Name 'window' is not declared."
What do I need to declare or import?
<INPUT...
|
by: JB |
last post by:
Hi All,
We have an ASP .Net application that has export to Excel functionality.
We are using contenttype="application/vnd.ms-excel". The functionality
works fine on Windows XP on the customer's...
|
by: Hemant Sipahimalani |
last post by:
The following piece of code is being used to export HTML to excel.
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"...
|
by: Agnes |
last post by:
dim strPeriod as string = dtTransdate.ToString("dd-MMM-yyyy",
System.Globalization.DateTimeFormatInfo.InvariantInfo)
'--excel code
..Range("H2").Value = strPeriod
When I read the excel, the...
|
by: yovation |
last post by:
Hi,
I have a 3 table database.
1 parent
1 child
1 child of child
I would like to get the data into excel as 1 sheet (similar to a
grouped report).
|
by: Grey |
last post by:
i need to export the data set to excel in asp.net, but my requirement is
need to use the template, i.e. some column should be lock as read-only and
some column should be highlighted. the format...
|
by: bmcgeoch |
last post by:
To preface this post, I personally did not create the database in question.
It was created by someone working for our technical support provider. That
person has since left the company and is not...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |