TransferText with specification?

[Gasman]

Hi all,
After playing around with export specification for the RBS Bankline import today, I decided to change the code that I had created to show refund splits back to providers.

I originally wrote to a text file and then opened that, but as I discovered how to remove the double quotes on an export spec, I decide to change it to a spec and use TransferText.

qryClientRefunds

SELECT Emails.CMS, Emails.Client, Emails.ThirdParty AS Donor, Sum(Emails.Amount) AS Donated, Calcrefund([CMS],[ThirdParty]) AS Refund
FROM Emails
WHERE (((Emails.Amount)>0))
GROUP BY Emails.CMS, Emails.Client, Emails.ThirdParty
HAVING (((Emails.CMS)=[Forms]![frmClientRefunds]![txtCMS]));

Query runs fine

I created an export spec called "ExportRefund" by following the instructions here
https://www.experts-exchange.com/questions/27982786/MS-Access-VBA-exporting-using-Specification.html particularly following the last instruction NOT to save the spec as I was doing previously and wondering why Access could not find it when it was staring me in the face.:banghead:

I get the error message in the attached pic but do not understand 'too few parameters, expected 0 ?'

Error occurs on the DoCmd.TransferText line

This is my old code

Sub ExportRefunds(lngCMS As Long)

Dim db As DAO.Database, rst As DAO.Recordset, qdf As DAO.QueryDef
Dim strQuery As String, strTextFile As String, strSql As String, strText As String, strFormat As String
Dim curBalance As Currency, curDonated As Currency, curBalanceCheck As Currency
Dim blnFile As Boolean

strQuery = "qryClientRefunds"
strTextFile = "c:\temp\CMS_Client_Refunds.txt"

Set db = CurrentDb()
strSql = "SELECT Emails.CMS, Emails.Client, Emails.ThirdParty, Sum(Emails.Amount) AS Donated, Calcrefund([CMS],[ThirdParty]) AS Refund FROM Emails"
strSql = strSql & " WHERE (((Emails.Amount)>0))"
strSql = strSql & " GROUP BY Emails.CMS, Emails.Client, Emails.ThirdParty"
strSql = strSql & " HAVING (((Emails.CMS)= " & lngCMS & "))"
'Debug.Print strSQL

Set rst = db.OpenRecordset(strSql)
' Open file to write to
Open strTextFile For Output As #1
Write #1, rst!Client
Write #1, "Donor" & vbTab & vbTab & "Donated" & vbTab & vbTab & "Refund"
Do While Not rst.EOF
    blnFile = True
    Write #1, rst!ThirdParty & vbTab & vbTab & Format(rst!Donated, "Currency") & vbTab & vbTab & Format(rst!Refund, "Currency")
    curBalance = curBalance + rst!Refund
    curDonated = curDonated + rst!Donated
    rst.MoveNext
Loop
If blnFile Then
    curBalanceCheck = DSum("Amount", "Emails", "CMS = " & lngCMS)
    Write #1, "Total" & vbTab & vbTab & Format(curDonated, "Currency") & vbTab & vbTab & Format(curBalance, "Currency")
End If
If curBalanceCheck <> curBalance Then
    MsgBox "Balance check has failed"
End If
Close #1
rst.Close
' Now open text file to copy & paste if we wrote anything
If blnFile Then
    Shell ("Notepad.exe " & strTextFile)
End If

Set rst = Nothing
Set db = Nothing

End Sub

This is my new code in the form

Private Sub cmdRefund_Click()
Dim strFileName As String

If Len(Me.txtCMS) & "" = 0 Then
    MsgBox "CMS reference is mandatory"
    Exit Sub
End If
' We have a CMS number, so use it
strFileName = "C:\Temp\CMS_Client_Refund.txt"

'Call ExportRefunds(Me.txtCMS)
'TempVars("CMS").Value = Me.txtCMS.Value
DoCmd.TransferText , "ExportRefund", "qryClientRefunds", strFileName
DoCmd.Close acForm, Me.Name
End Sub

CalcRefund is

Public Function CalcRefund(plngCMS As Long, strDonor As String) As Currency
' Calculate % refund to donor
Dim curDonorDonated As Currency, curTotalDonated As Currency, curRemainder As Currency

curTotalDonated = DSum("Amount", "Emails", "Amount > 0 AND CMS = " & plngCMS)
curDonorDonated = DSum("Amount", "Emails", "Amount > 0 AND CMS = " & plngCMS & " AND ThirdParty = '" & strDonor & "'")
curRemainder = DSum("Amount", "Emails", "CMS = " & plngCMS)
'Debug.Print "Total " & curTotalDonated
'Debug.Print "Donor " & curDonorDonated
'Debug.Print "Remain " & curRemainder

' Now calc amount to refund
CalcRefund = Round(curDonorDonated / curTotalDonated * curRemainder, 2)
End Function

What am I missing please.?

 

[Pat Hartman]

what happens when you run qryClientRefunds by itself?

Does the query prompt for data or does it refer to form fields? If it refers to form fields (best method), that form MUST be open when you run the query.

 

[Gasman]

Hi Pat,
frmClientRefunds is modal.
I inserted a DoCmd.Openquery before TransferText line and commented out the TransferText line and the query opens fine?

However if I then try and export that query to create a spec I get 'Too few parameters, expected 1' ?

If I run it without the form being open, then of course it prompts for the value, which I key in to the input prompt.

 

[GinaWhipp]

That parameter in the query must be filled in before you call to transfer or do anything. What I do to avoid that is I run my filtered report and save to a temp folder to eMail from and then delete the report from the temp folder.

 

[Gasman]

OK, starting again.

Testing the steps....

Open the form frmClientRefunds from the navigation pane and enter a CMS number
Then I run the query from the navigation pane, which produces the data as I would expect
Then with query open, I click on Text File option to create the spec
Window opens up with suggested file name.
I click OK, and then it gives me the error 'Too few parameters, expected 1'

Yet if I minimize the query, the form is still open with a valid CMS number.

My first mistake was not realising that a spec created manually is not available to VBA and you have to create those specs in a different way, as shown in that link I posted.

 

[GinaWhipp]

Right, won't work that way. So, you would need to create your query on the fly with the parameter set then send your report out and then you can delete that query. You will need to delete it because it will not overwrite without asking you first and that defeats the purpose.

 

[Gasman]

Sorry all.

Seems I misinterpreted the syntax?
I had

DoCmd.TransferText , "ExportRefund", "qryClientRefunds", strFileName

when it should be

DoCmd.TransferText acExportDelim, "ExportRefund", "qryClientRefunds", strFileName

However something screwy is going on as now I cannot create new specs.?
Getting that error only when I select that query or a new query where I copied and pasted the sql?

However I have found that if I manage to output without the text delimiters, I cannot get the headings at the same time.? If I leave the text delimiters in, I get the headings.:banghead:

So I'll have to leave it as a textfile output.

 

[GinaWhipp]

Maybe if you use Excel output and save as .CSV?

 

[Gasman]

Hi Gina,

I'm going to leave it as it is.
I was just trying to save myself replacing " with nothing after the output.

Now I have remember there was a reason I did it that way in the first place and it was to get the client name as a heading and then the amounts below it.

Then I could easily copy and paste into a note on the CMS system.

Trying to use a query would just repeat the client name and make more work for myself.

The whole point of this was to make less work for myself, and I got blinded by the fact I could omit the text qualifier.:banghead:

Fortunately refunds are not that frequent, we generally spend everything we get for a client.

 

[Gasman]

Right, won't work that way. So, you would need to create your query on the fly with the parameter set then send your report out and then you can delete that query. You will need to delete it because it will not overwrite without asking you first and that defeats the purpose.

Missed this in my posts. sorry Gina.

I'm not creating a report. I just wanted a simple text file formatted like below to copy and paste into a note on our computer system called CMS, but without the text qualifiers.

I also have edited that below as when amounts with a , are involved the line up is off, but again, I just edit that, and as mentioned this happens infrequently.

Thank you all for your replies.

"Mr J.L.H Williams:150524"
"Donor		Donated		Refund"
"ABF		£1,000.00	£86.27"
"RBL		£1,000.00	£86.27"
"RBLWS		£417.99		£36.06"
"RLC		£1,500.00	£129.40"
"SSAFA		£300.00		£25.88"
"Total		£4,217.99	£363.88"

 

[GinaWhipp]

No problem. Didn't realize you did not want a report.

Hmm, no code to remove the text qualifiers from the file once saved? Wish I had some spare time for that. Could be fun.

 

[GinaWhipp]

Google is a wonderful thing!

Found this (untested and might need some tweaking)...

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("FullPathAndFileName", ForReading)
strContents = objFile.ReadAll
objFile.Close

strContents = Replace(strContents, Chr(34), "")

Set objFile = objFSO.OpenTextFile("FullPathAndFileName", ForWriting)
objFile.WriteLine strContents
objFile.Close

 

[Gasman]

That did the trick!

Thank you Gina.

For others
I had to add Microsoft Scripting Runtime reference to get the FileSystem object. I initially had objFile as a FileSystem object, but it balked at

strContents = objFile.ReadAll

Just added

Dim strContents as String
Dim objFSO As FileSystemObject, objFile As Object

Google is a wonderful thing!

Found this (untested and might need some tweaking)...

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("FullPathAndFileName", ForReading)
strContents = objFile.ReadAll
objFile.Close

strContents = Replace(strContents, Chr(34), "")

Set objFile = objFSO.OpenTextFile("FullPathAndFileName", ForWriting)
objFile.WriteLine strContents
objFile.Close

 

[GinaWhipp]

Glad that worked!

 

[Gasman]

Glad that worked!

Going to keep that as a function now.

 

[GinaWhipp]

I know I am!