By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,715 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,715 IT Pros & Developers. It's quick & easy.

Is it possible to pass the current form's Recordset to DoCmd.TransferText

P: 7
hi,
i am trying to find out if it is possible to pass the current recordset of a form to DoCmd.TransferText so i can export it as a .csv

I know I can use querydefs etc, but it would be a much nicer solution if i could just loop through my subform1 and get the nested subform2 recordsets and pass them to the routine.

has anyone had experience with this ?
any ideas would be overwhelmingly appreciated!!
Jul 7 '10 #1
Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,669
@sakurako97
You can write code to process a Recordset, then dynamically write the Values for all Fields to a CSV File.
Jul 8 '10 #2

P: 7
@ADezii
thanks...shall look further into it!
Jul 8 '10 #3

ADezii
Expert 5K+
P: 8,669
I created a Generic Sub-Routine for you to illustrate the point that I was making. Simply pass to this Routine any DAO Recordset, and it will generate a CSV File for you from its contents. For the sake of simplicity and brevity, I've eliminated any Error Checking and treated all Fields in the Recordset as Text. This Routine is called from the Click() Event of a Command Button on a Form whose Record Source happens to be the Products Table from the Northwind Sample Database.
Expand|Select|Wrap|Line Numbers
  1. Public Sub CreateCSVFromRecordset(rstToProcess As DAO.Recordset)
  2. Dim rst As DAO.Recordset
  3. Dim intNumOfFiedls As Integer
  4. Dim intFldCtr As Integer
  5. Dim strBuild As String
  6.  
  7. Open "C:\Dezii\Recordset.txt" For Append As #1
  8.  
  9. Set rst = rstToProcess
  10. intNumOfFields = rst.Fields.Count
  11.  
  12. rst.MoveFirst
  13.  
  14. With rst
  15.   Do While Not .EOF
  16.     For intFldCtr = 0 To intNumOfFields - 1
  17.       strBuild = strBuild & Chr$(34) & .Fields(intFldCtr) & Chr$(34) & ","
  18.     Next
  19.       Print #1, Left$(strBuild, Len(strBuild) - 1)
  20.       strBuild = ""
  21.       .MoveNext
  22.   Loop
  23. End With
  24.  
  25. rst.Close
  26. Set rst = Nothing
  27.  
  28. Close #1
  29. End Sub
Sample Call to Routine from Click() Event of Command Button:
Expand|Select|Wrap|Line Numbers
  1. Call CreateCSVFromRecordset(Me.Recordset)
Sample of Partial Contents from Recordset.txt
Expand|Select|Wrap|Line Numbers
  1. "1","Chai","1","1","10 boxes x 20 bags","18","39","0","10","False"
  2. "2","Chang","1","1","24 - 12 oz bottles","19","17","40","25","False"
  3. "3","Aniseed Syrup","1","2","12 - 550 ml bottles","10","13","70","25","False"
  4. "4","Chef Anton's Cajun Seasoning","2","2","48 - 6 oz jars","22","53","0","0","False"
  5. "5","Chef Anton's Gumbo Mix","2","2","36 boxes","21.35","0","0","0","True"
  6. "6","Grandma's Boysenberry Spread","3","2","12 - 8 oz jars","25","120","0","25","False"
  7. "7","Uncle Bob's Organic Dried Pears","3","7","12 - 1 lb pkgs.","30","15","0","10","False"
  8. "8","Northwoods Cranberry Sauce","3","2","12 - 12 oz jars","40","6","0","0","False"
  9. "9","Mishi Kobe Niku","4","6","18 - 500 g pkgs.","97","29","0","0","True"
  10. "10","Ikura","4","8","12 - 200 ml jars","31","31","0","0","False"
  11. "11","Queso Cabrales","5","4","1 kg pkg.","21","22","30","30","False"
  12. "12","Queso Manchego La Pastora","5","4","10 - 500 g pkgs.","38","86","0","0","False"
  13. "13","Konbu","6","8","2 kg box","6","24","0","5","False"
  14. "14","Tofu","6","7","40 - 100 g pkgs.","23.25","35","0","0","False"
  15. "15","Genen Shouyu","6","2","24 - 250 ml bottles","15.5","39","0","5","False"
  16. "16","Pavlova","7","3","32 - 500 g boxes","17.45","29","0","10","False"
  17. "17","Alice Mutton","7","6","20 - 1 kg tins","39","0","0","0","True"
  18. "18","Carnarvon Tigers","7","8","16 kg pkg.","62.5","42","0","0","False"
  19. "19","Teatime Chocolate Biscuits","8","3","10 boxes x 12 pieces","9.2","25","0","5","False"
  20. "20","Sir Rodney's Marmalade","8","3","30 gift boxes","81","40","0","0","False"
  21. "21","Sir Rodney's Scones","8","3","24 pkgs. x 4 pieces","10","3","40","5","False"
  22. "22","Gustaf's Knäckebröd","9","5","24 - 500 g pkgs.","21","104","0","25","False"
  23. "23","Tunnbröd","9","5","12 - 250 g pkgs.","9","61","0","25","False"
  24. "24","Guaraná Fantástica","10","1","12 - 355 ml cans","4.5","20","0","0","True"
  25. "25","NuNuCa Nuß-Nougat-Creme","11","3","20 - 450 g glasses","14","76","0","30","False"
  26. "26","Gumbär Gummibärchen","11","3","100 - 250 g bags","31.23","15","0","0","False"
  27. "27","Schoggi Schokolade","11","3","100 - 100 g pieces","43.9","49","0","30","False"
  28. "28","Rössle Sauerkraut","12","7","25 - 825 g cans","45.6","26","0","0","True"
  29. "29","Thüringer Rostbratwurst","12","6","50 bags x 30 sausgs.","123.79","0","0","0","True"
  30. "30","Nord-Ost Matjeshering","13","8","10 - 200 g glasses","25.89","10","0","15","False"
  31. "31","Gorgonzola Telino","14","4","12 - 100 g pkgs","12.5","0","70","20","False"
Jul 8 '10 #4

P: 7
That's fantastic ADezii!
Just the type of approach I was looking for.
Thank you.
I shall get to work on it tonight.
cheers
sakurako
Jul 9 '10 #5

ADezii
Expert 5K+
P: 8,669
@sakurako97
Let me kow how you make out. I also tested the Importing of the CSV File should this be required, and it can easily, and without Errors, be imported into an Access DB.
Jul 9 '10 #6

P: 7
Just tried the code (much appreciated) and it worked fine if I was executing it from the subform itself, but could not get it to work properly from the main form. I found a workaround to get it to do what I want, but I don;t think it's the best way to do it.

I have listed the original code (which essentially creates the files etc, but just sticks the content of the first rowset in every file). Following this, I have included the small change I made to get it to do what I need.

I am sure this is a pretty common scenario (maybe not just for exports etc, but for processing in general). I have a main form (where my export files button lives), and nested subforms within the main form (S1 - Header subform, S2 - Detail subform).

What I am trying to do, is loop through the Header subform (code works fine for this), and for each record in the Header subform, get all related records in the Details subform, and export them. The loops work fine in both the Header and Details recordsets, so I know it is down to that one line of code where I set the Details recordset.

When I move next in the Header loop, and set the Recordset for the details, I was expecting access to create the Details recordset based on my position in the Header recordset, but it just keeps recreating the initial one.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdExportAllLRs_Click()
  2.  
  3.  If fsubSampleLoginLRSGeneratedHeader.Form.Recordset.RecordCount > 0 Then
  4.  
  5.     Dim rstHeader As DAO.Recordset
  6.     Dim rstDetails As DAO.Recordset
  7.     Dim lr As String
  8.  
  9.     Set rstHeader = Me!fsubSampleLoginLRSGeneratedHeader.Form.RecordsetClone
  10.  
  11.     rstHeader.MoveFirst
  12.  
  13.     With rstHeader
  14.         Do While Not .EOF
  15.  
  16.             Set rstDetails = Me!fsubSampleLoginLRSGeneratedHeader.Form.fsubSampleLoginLRSGeneratedDetails.Form.RecordsetClone
  17.             lr = !LRNumber
  18.             Call CreateCSVFromRecordset(rstDetails, lr)
  19.  
  20.         .MoveNext
  21.         Loop
  22.     End With
  23.  
  24.     rstHeader.Close
  25.     Set rstHeader = Nothing
  26.  
  27.     MsgBox "All Done!", vbInformation, "Information"
  28.  
  29.  Else
  30.   MsgBox "Sorry, but there are no LRs to export!", vbInformation, "Information"
  31.  End If
  32.  
  33. End Sub
  34.  
  35. Sub CreateCSVFromRecordset(rstToProcess As DAO.Recordset, lr As String)
  36.  
  37.     Dim intNumOfFields As Integer
  38.     Dim intFldCtr As Integer
  39.     Dim strBuild As String
  40.     Dim strFilePath As String
  41.  
  42.     strFilePath = "c:\$A1OUT\" & lr & ".csv"
  43.  
  44.     Open strFilePath For Append As #1
  45.  
  46.         intNumOfFields = rstToProcess.Fields.Count
  47.  
  48.         rstToProcess.MoveFirst
  49.  
  50.         With rstToProcess
  51.           Do While Not .EOF
  52.             For intFldCtr = 0 To intNumOfFields - 1
  53.               strBuild = strBuild & Chr$(34) & .Fields(intFldCtr) & Chr$(34) & ","
  54.             Next
  55.               Print #1, Left$(strBuild, Len(strBuild) - 1)
  56.               strBuild = ""
  57.               .MoveNext
  58.           Loop
  59.         End With
  60.  
  61.         rstToProcess.Close
  62.         Set rstToProcess = Nothing
  63.  
  64.     Close #1
  65.  
  66. End Sub
To get it to do what I needed, I changed:

Expand|Select|Wrap|Line Numbers
  1. Set rstDetails = Me!fsubSampleLoginLRSGeneratedHeader.Form.fsubSampleLoginLRSGeneratedDetails.Form.RecordsetClone
To:

Expand|Select|Wrap|Line Numbers
  1. sqlstr = "Select * From tblSampleLoginLRSGeneratedDetails Where tblSampleLoginLRSGeneratedDetails.LRNumber = '" & lr & "';"
  2.  
  3. Set rstDetails = CurrentDb.OpenRecordset(sqlstr)

This second one works fine, but I would really like to find out how to get the hierarchy and loop through it all.

Once again, thanks for you help!
Jul 13 '10 #7

ADezii
Expert 5K+
P: 8,669
I'll post below the syntax for setting an Object Variable to the Recordset of a Sub-Form from the Main/Parent Form. For this Demo I am using the Parent Form (Orders) and the Child Form (Orders Subform2) scenario of the Northwind Sample database.
Expand|Select|Wrap|Line Numbers
  1. Dim rst As DAO.Recordset
  2.  
  3. Set rst = Forms![Orders]![Orders Subform2].Form.Recordset
Jul 14 '10 #8

P: 7
thanks ADezii, but the subform actually works fine - it's the nested subform which is not getting the correct rowset.

The hierarchy is:

main form
header subform
detail subform (which is nested within the header)

All the parent child relationships are setup correctly.

From the main form, I can get the header subform recordset and loop through it and pick up values fine. For each record in the header recordset, I am then trying to pick up the corresponding detail recordset. This is where I keep getting the same values. I am using the syntax you posted (except I am using .RecordsetClone).

To give a practical example, let's say my header subform has 3 records:

Record 1 has 5 associated detail subform records
Record 2 has 10 associated detail subform records
Record 3 has 20 associated detail subform records

My outer loop handles the header recordset. For each record returned, I go to my inner loop the get the detail recordset associated with the header e.g. when I am on record 1 of the header, my detail recordset should have 5 records, when on record 2, it should have 10 records and so on.

The problem is my inner loop which sets the detail recordset keeps returning the same 5 records (record 1's accociated detail recordset) for all 3 header records.

I am setting the inner loop detail recordset with:
Expand|Select|Wrap|Line Numbers
  1. Set rstDetails = Me!fsubSampleLoginLRSGeneratedHeader.Form.fsubSampleLoginLRSGeneratedDetails.Form.RecordsetClone
which just gives me the same recordset each time.
If I change it to base the recordset on an sql statement using parameters retrieved from the header recordset such as:

Expand|Select|Wrap|Line Numbers
  1. sqlstr = "Select * From tblSampleLoginLRSGeneratedDetails Where tblSampleLoginLRSGeneratedDetails.LRNumber = '" & lr & "';"
  2.  
  3. Set rstDetails = CurrentDb.OpenRecordset(sqlstr)
then it works fine.
I was just hoping to find a way to do this using recordset syntax rather than an sql based recordset.
I hope this makes sense
Many thanks.
Jul 14 '10 #9

ADezii
Expert 5K+
P: 8,669
If everything is structured correctly, try accessing the Sub-Sub-Form's Recordset directly as in:
Expand|Select|Wrap|Line Numbers
  1. Set rst2 = Forms![Orders]![Orders Subform2].Form![<Level 2 Sub-Form>].Form.Recordset 
Jul 14 '10 #10

P: 7
Thanks ADezii...tried it using the recordset instead of the clone, but still no luck. I read some info about using Shape and ADO, which would work fine, but it's really just doing what I already did using the recordset based on an sql string (which also works fine).
Jul 16 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.