Using FSO to write a text file using a variable

[MrChips]

Whilst the 4th line from the bottom writes to the file OK.
The fifth line up whilst containing the email address I expect, does not get written to the text file.
Am I correct in thinking that FSO is unable to write a variable?

Dim db As DAO.Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("t_email_list", dbOpenDynaset)
Dim Path, EmailType, TextLine As String
EmailType = DLookup("[Lvl2 Group Name] &[,]", "t_email_list")
Set fs = CreateObject("Scripting.FileSystemObject")
Path = "C:/PGCO/Email_List_" & Format(Date, "yyyy-mm-dd") & "_" & EmailType & ".txt" '"c:\TestDB\testfile.txt"
Set a = fs.CreateTextFile(Path, True)
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
a.WriteLinetofile = rs.[Primary Email]
'.a.WriteLine ("This is a test.") '... This works, but the above line doesn't
rs.MoveNext
Loop



Set fs = Nothing

[Welshgasman]

No.
I would expect you need double quotes around the fieldname if you are going to use that syntax.

[davegri]

Try a bang instead of the dot (upsized for clarity).

a.WriteLinetofile = rs![Primary Email]

[MrChips]

Thanks Welshman
I have tries a number of variations of quote marks, but either it doesn't like the syntax, or it returns the following eror.
"Object doesn't support the property or method"

The use of quotes in access is truly a black art known only to a few.

[MrChips]

Thanks Davegri,
That was a monumentally stupid error of mine,
For some reason, however, correcting it didn't solve the problem!

[Welshgasman]

You need to tidy up your code.
Had to clean a lot just to try it out.

Sub TestFSO()




Dim db As DAO.Database, rs As DAO.Recordset, fs As Object, a As Object
Set db = CurrentDb()
Set rs = db.OpenRecordset("Transactions", dbOpenDynaset)
Dim Path, EmailType, TextLine As String
'EmailType = DLookup("[Lvl2 Group Name] &[,]", "t_email_list")
Set fs = CreateObject("Scripting.FileSystemObject")
Path = "F:\Temp\TestFSO.txt"
Set a = fs.CreateTextFile(Path, True)
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
a.WriteLine rs.Fields("Description")
'.a.WriteLine ("This is a test.") '... This works, but the above line doesn't
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set fs = Nothing


End Sub

Do not make stuff up, go look for the syntax.

https://wellsr.com/vba/2018/excel/vb...file-with-fso/

Dave's option will works as well providing you get the rest of the code correct.

[MrChips]

Thank you all,
With your joint efforts, this now works, and I appreciate your time spent on this for me.
I found WelshGasMan's link most useful, explaining a lot of what I hadn't previously understood.

I still need a good tutorial on the use of quotes, double quotes and treble quotes though.
Any suggestions please, I can't find anything understandable on the net so far.
I really do want to understand this stuff, rather than copy what others do, where I can.

[Welshgasman]

No 1 tip. Make sure you have Option Explicit at the top of every module, form, code etc.

In the VBA window. Tools/Options/Require variable declaration and tick that option.

That will put Option Explicit in all code modules from then on. ​ You will need to put it in manually for all existing modules.

[MrChips]

Hi WelshGasMan
Done it . . . A great tip.
It'll save me trying to remember for each new module.

Ta again for your help.

[Welshgasman]

Don't forget to compile. That will show you all undeclared variables.

[MrChips]

Thanks WelshGasMan,
This is all infiltrating my grey matter.