How to Use RecordSets and dump to a text file

[Maturi]

Hi

I have a form (frmMain) which has
-a subForm (called ResultsBox -has a list of ‘Name’ and ‘Address’ and other fields)
-a button – when clicked reads the list in ResultsBox and outputs to a file on my C Drive. I want to dump the contents of ResultsBox to a text file

So I need to do the steps
-open a file on the C Drive (with “Open FileName For Output As MyFile” ??)
-open a RecordSet of ResultsBox – ( with Set ResSet = Me![ReAssignDocs].OpenRecordset - but can’t get this to work)
-Go to first record in ResultBox (or ResSet)
-Loop through the list in ResultsBox
-For each record output to the file on the C Drive


The problem I have is I can’t get the syntax right for opening and manipulating the ResultsBox (ie OpenRecordSet etc). I get a type mismatch when I do
Set ResSet = Me![ResultsBox].OpenRecordset

And I don’t know how to dump data into a text file

Any help appreciated, Thanks

 

[Remou]

You can use the RecordsetClone:
[tt]Set rs=Me.SubformControlName.Form.RecordsetClone[/tt]

To output to a file, use either Write or Print and the file number. Be sure to close the file:
[tt]Open "C:\Docs\xyz.txt" For Output As #1
Print #1, "abc"
Close #1[/tt]

However, the subform must be based in some way on a query, so it may be easier to use TransferText with that query.

 

[JerryKlmns]

For an ADODB.Recorset you could use
...
While Not rs.EOF
Print #1, rs.GetString(adClipString, 1, "|")
Wend
...

To get the recordset's one :1 record separating:
fields with |
lines with the default value of CARRIAGE RETURN
with Null values as empty strings.

FYI
Using a TextStrem object: Looping and writing one record per time is faster than using rs.GetString(adClipString, , "|") and dump all the recorset at once.

 

[Maturi]

Remou

Thanks. Still got a problem

The name of the subform is ResultsBox (in datasheet view).

I've tried: Set rs=Me.ResultsBox.Form.RecordsetClone

and I get Error message 'Runtime Error 13 -Type Mismatch'

Any idea why? How do I fix it?

Alternatively - ResultsBox is based on a table called MyTable. Can I open the tabel as Record Set??

Thanks

 

[Remou]

Did you Dim rs as as DAO.Recordset?

Yes, you can base your recordset on Mytable:
Set rs=CurrentDB.OpenRecordset("MyTable")
Or
Set rs=CurrentDB.OpenRecordset("Select Field1, Field2 From MyTable Where ID=" & Me.ID)

But consider:
[tt]DoCmd.TransferText acExportDelim, , "MyTable", "C:\Docs\Text.txt"[/tt]

 

[Maturi]

Remou

Thanks for the help.

I will use the TransferText method..... but first I will need to format the table in a particular way (which I would have done in VB using my original approach). Here's the scenario:

If I have 3 fields in the table Name, Group, and NewText.

NewText needs to be a text concatination as follows
[Name] & "Part of" & [Group]

How do I create the NewText field in the table such that it automatically updates itself if Name or Group are changed?

Thanks

 

[Remou]

How about

strSQL = "Select CustID, '" _
        & Me.[Name] & " Part of " & Me.[Group] & "' As NewField From Table1"
If IsNull(DLookup("[Name]", "MSysObjects", "[Name]='qryQuery'")) Then
    Set qdf = CurrentDb.CreateQueryDef("qryQuery", strSQL)
Else
   Set qdf = CurrentDb.QueryDefs("qryQuery")
   qdf.SQL = strSQL
End If
        
DoCmd.TransferText acExportDelim, , "qryQuery", "Text.txt"

PS both Name and Group as field names are likely to bite at some stage as they are reserved words.