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

[sakurako97]

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

[ADezii]

@sakurako97
You can write code to process a Recordset, then dynamically write the Values for all Fields to a CSV File.

[sakurako97]

@ADezii
thanks...shall look further into it!

[ADezii]

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.

Public Sub CreateCSVFromRecordset(rstToProcess As DAO.Recordset)
Dim rst As DAO.Recordset
Dim intNumOfFiedls As Integer
Dim intFldCtr As Integer
Dim strBuild As String
 
Open "C:\Dezii\Recordset.txt" For Append As #1
 
Set rst = rstToProcess
intNumOfFields = rst.Fields.Count
 
rst.MoveFirst
 
With rst
  Do While Not .EOF
    For intFldCtr = 0 To intNumOfFields - 1
      strBuild = strBuild & Chr$(34) & .Fields(intFldCtr) & Chr$(34) & ","
    Next
      Print #1, Left$(strBuild, Len(strBuild) - 1)
      strBuild = ""
      .MoveNext
  Loop
End With
 
rst.Close
Set rst = Nothing
 
Close #1
End Sub

Sample Call to Routine from Click() Event of Command Button:

Call CreateCSVFromRecordset(Me.Recordset)

Sample of Partial Contents from Recordset.txt

"1","Chai","1","1","10 boxes x 20 bags","18","39","0","10","False"
"2","Chang","1","1","24 - 12 oz bottles","19","17","40","25","False"
"3","Aniseed Syrup","1","2","12 - 550 ml bottles","10","13","70","25","False"
"4","Chef Anton's Cajun Seasoning","2","2","48 - 6 oz jars","22","53","0","0","False"
"5","Chef Anton's Gumbo Mix","2","2","36 boxes","21.35","0","0","0","True"
"6","Grandma's Boysenberry Spread","3","2","12 - 8 oz jars","25","120","0","25","False"
"7","Uncle Bob's Organic Dried Pears","3","7","12 - 1 lb pkgs.","30","15","0","10","False"
"8","Northwoods Cranberry Sauce","3","2","12 - 12 oz jars","40","6","0","0","False"
"9","Mishi Kobe Niku","4","6","18 - 500 g pkgs.","97","29","0","0","True"
"10","Ikura","4","8","12 - 200 ml jars","31","31","0","0","False"
"11","Queso Cabrales","5","4","1 kg pkg.","21","22","30","30","False"
"12","Queso Manchego La Pastora","5","4","10 - 500 g pkgs.","38","86","0","0","False"
"13","Konbu","6","8","2 kg box","6","24","0","5","False"
"14","Tofu","6","7","40 - 100 g pkgs.","23.25","35","0","0","False"
"15","Genen Shouyu","6","2","24 - 250 ml bottles","15.5","39","0","5","False"
"16","Pavlova","7","3","32 - 500 g boxes","17.45","29","0","10","False"
"17","Alice Mutton","7","6","20 - 1 kg tins","39","0","0","0","True"
"18","Carnarvon Tigers","7","8","16 kg pkg.","62.5","42","0","0","False"
"19","Teatime Chocolate Biscuits","8","3","10 boxes x 12 pieces","9.2","25","0","5","False"
"20","Sir Rodney's Marmalade","8","3","30 gift boxes","81","40","0","0","False"
"21","Sir Rodney's Scones","8","3","24 pkgs. x 4 pieces","10","3","40","5","False"
"22","Gustaf's Knäckebröd","9","5","24 - 500 g pkgs.","21","104","0","25","False"
"23","Tunnbröd","9","5","12 - 250 g pkgs.","9","61","0","25","False"
"24","Guaraná Fantástica","10","1","12 - 355 ml cans","4.5","20","0","0","True"
"25","NuNuCa Nuß-Nougat-Creme","11","3","20 - 450 g glasses","14","76","0","30","False"
"26","Gumbär Gummibärchen","11","3","100 - 250 g bags","31.23","15","0","0","False"
"27","Schoggi Schokolade","11","3","100 - 100 g pieces","43.9","49","0","30","False"
"28","Rössle Sauerkraut","12","7","25 - 825 g cans","45.6","26","0","0","True"
"29","Thüringer Rostbratwurst","12","6","50 bags x 30 sausgs.","123.79","0","0","0","True"
"30","Nord-Ost Matjeshering","13","8","10 - 200 g glasses","25.89","10","0","15","False"
"31","Gorgonzola Telino","14","4","12 - 100 g pkgs","12.5","0","70","20","False"

[sakurako97]

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

[ADezii]

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

[sakurako97]

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.

Private Sub cmdExportAllLRs_Click()
 
 If fsubSampleLoginLRSGeneratedHeader.Form.Recordset.RecordCount > 0 Then
 
    Dim rstHeader As DAO.Recordset
    Dim rstDetails As DAO.Recordset
    Dim lr As String
 
    Set rstHeader = Me!fsubSampleLoginLRSGeneratedHeader.Form.RecordsetClone
 
    rstHeader.MoveFirst
 
    With rstHeader
        Do While Not .EOF
 
            Set rstDetails = Me!fsubSampleLoginLRSGeneratedHeader.Form.fsubSampleLoginLRSGeneratedDetails.Form.RecordsetClone
            lr = !LRNumber
            Call CreateCSVFromRecordset(rstDetails, lr)
 
        .MoveNext
        Loop
    End With
 
    rstHeader.Close
    Set rstHeader = Nothing
 
    MsgBox "All Done!", vbInformation, "Information"
 
 Else
  MsgBox "Sorry, but there are no LRs to export!", vbInformation, "Information"
 End If
 
End Sub
 
Sub CreateCSVFromRecordset(rstToProcess As DAO.Recordset, lr As String)
 
    Dim intNumOfFields As Integer
    Dim intFldCtr As Integer
    Dim strBuild As String
    Dim strFilePath As String
 
    strFilePath = "c:\$A1OUT\" & lr & ".csv"
 
    Open strFilePath For Append As #1
 
        intNumOfFields = rstToProcess.Fields.Count
 
        rstToProcess.MoveFirst
 
        With rstToProcess
          Do While Not .EOF
            For intFldCtr = 0 To intNumOfFields - 1
              strBuild = strBuild & Chr$(34) & .Fields(intFldCtr) & Chr$(34) & ","
            Next
              Print #1, Left$(strBuild, Len(strBuild) - 1)
              strBuild = ""
              .MoveNext
          Loop
        End With
 
        rstToProcess.Close
        Set rstToProcess = Nothing
 
    Close #1
 
End Sub

To get it to do what I needed, I changed:

Set rstDetails = Me!fsubSampleLoginLRSGeneratedHeader.Form.fsubSampleLoginLRSGeneratedDetails.Form.RecordsetClone

To:

sqlstr = "Select * From tblSampleLoginLRSGeneratedDetails Where tblSampleLoginLRSGeneratedDetails.LRNumber = '" & lr & "';"
 
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!

[ADezii]

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.

Dim rst As DAO.Recordset
 
Set rst = Forms![Orders]![Orders Subform2].Form.Recordset

[sakurako97]

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:

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:

sqlstr = "Select * From tblSampleLoginLRSGeneratedDetails Where tblSampleLoginLRSGeneratedDetails.LRNumber = '" & lr & "';"
 
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.

[ADezii]

If everything is structured correctly, try accessing the Sub-Sub-Form's Recordset directly as in:

Set rst2 = Forms![Orders]![Orders Subform2].Form![<Level 2 Sub-Form>].Form.Recordset 

[sakurako97]

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