Export recordset to excel unbound column issues

[munkee]

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.

[nico5038]

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)

[munkee]

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.

[nico5038]

You can now use the "straight forward" docmd.TransferS preadsheet 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)

[munkee]

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..

[nico5038]

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)

[munkee]

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

[nico5038]

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)

[munkee]

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

[nico5038]

Glad I could help and success with your application Chris !

Nic;o)