Export to txt with two line body and footer

P

pcbozz

New member
Local time
Yesterday, 22:19
Joined
Jun 16, 2010
Messages
6
Hi, I need to export to a text file, something like this:

Row1--Client A Details
Row2--Client A Payments Totals
Row3--Client B Details
Row4--Client B Payments Totals
.....
Footer - Sum of all payments

I almost have it working but have problems wit the "body" (Row) of the file, how can alternate the client datails (rsCustomer ) an total payments (rsPaylinkGP_VOI)...?

Here is my code:
__________________________________________-
Private Sub Command1_Click()
DoCmd.SetWarnings False
'DoCmd.RunSQL "DELETE * FROM PaylinkGP"
DoCmd.SetWarnings True
'end deleting
Dim FileName As String
FileName = "C:\PayLink\DigicelPaylink.txt"
Dim FileNumber As Long
Dim qryCurrent As QueryDef
Dim rsCustomer As Recordset
Dim rsInvoice As Recordset
Dim CustIDas As Long
Dim CustNameas As String
Dim InvID As String
Dim InvAmt As String 'pretending all are integers
Dim InvLine As String
Dim contador As Integer
Dim HeaderLine As String

'open new file
FileNumber = FreeFile
Open FileName For Output As FileNumber
'loop through invoice records nested inside vendor record loop
Set rsCustomer = CurrentDb.OpenRecordset("PaylinkGP", dbOpenSnapshot)
rsCustomer.MoveFirst
Do While Not rsCustomer.EOF 'write each vendor header
'inicia carga de datos
RecordType = rsCustomer.Fields("RecordType").Value
CustomerCountryCode = rsCustomer.Fields("CustomerCountryCode").Value
CustomerAccountNumber = rsCustomer.Fields("CustomerAccountNumber").Value
Fechadeldocumento = rsCustomer.Fields("Fechadeldocumento").Value
TransactionCode = rsCustomer.Fields("TransactionCode").Value
Numerodedocumento = rsCustomer.Fields("Numerodedocumento").Value
TransactionSequenceNumber = rsCustomer.Fields("TransactionSequenceNumber").Value
ThirdPartyTaxID = rsCustomer.Fields("ThirdPartyTaxID").Value
CurrencyCode = rsCustomer.Fields("CurrencyCode").Value
Iddeproveedor = rsCustomer.Fields("Iddeproveedor").Value
Montodeldocumento = rsCustomer.Fields("Montodeldocumento").Value
MaturityDate = rsCustomer.Fields("MaturityDate").Value
TransactionDetail1 = rsCustomer.Fields("TransactionDetail1").Value
TransactionDetail2 = rsCustomer.Fields("TransactionDetail2").Value
TransactionDetail3 = rsCustomer.Fields("TransactionDetail3").Value
TransactionDetail4 = rsCustomer.Fields("TransactionDetail4").Value
LocalTranCode = rsCustomer.Fields("LocalTranCode").Value
CustomerAccType = rsCustomer.Fields("CustomerAccType").Value
Nombreproveedor = rsCustomer.Fields("Nombreproveedor").Value
ThirdPartyAddr1 = rsCustomer.Fields("ThirdPartyAddr1").Value
ThirdPartyAddr1B = rsCustomer.Fields("ThirdPartyAddr1B").Value
ThirdPartyBankNumber = rsCustomer.Fields("ThirdPartyBankNumber").Value
ThirdPartyBankAgency = rsCustomer.Fields("ThirdPartyBankAgency").Value
ThirdPartyAcctNumber = rsCustomer.Fields("ThirdPartyAcctNumber").Value
AccType = rsCustomer.Fields("AccType").Value
ThirdPartyBF = rsCustomer.Fields("ThirdPartyBF").Value
TransactionDeliveryMethod = rsCustomer.Fields("TransactionDeliveryMethod").Value
CollActivityCode = rsCustomer.Fields("CollActivityCode").Value
ThirdPartyEmailAddr = rsCustomer.Fields("ThirdPartyEmailAddr").Value
MaximumPayment = rsCustomer.Fields("MaximumPayment").Value
'Trimidproveedor = Trim(Iddeproveedor)
UpdateType = rsCustomer.Fields("UpdateType")
'fin carga de datos
HeaderLine = RecordType & "" & CustomerCountryCode & "" & CustomerAccountNumber & "" & Fechadeldocumento & "" & TransactionCode & "" & Numerodedocumento & "" & TransactionSequenceNumber & "" & ThirdPartyTaxID & "" & CurrencyCode & "" & Iddeproveedor & "" & Montodeldocumento & "" & MaturityDate & "" & TransactionDetail1 & "" & TransactionDetail2 & "" & TransactionDetail3 & "" & TransactionDetail4 & "" & LocalTranCode & "" & CustomerAccType & "" & Nombreproveedor & "" & ThirdPartyAddr1 & "" & ThirdPartyAddr1B & "" & ThirdPartyBankNumber & "" & ThirdPartyBankAgency & "" & ThirdPartyAcctNumber & "" & AccType & "" & ThirdPartyBF & "" & TransactionDeliveryMethod & "" & CollActivityCode & "" & ThirdPartyEmailAddr & "" & MaximumPayment & "" & UpdateType
'Print #FileNumber, HeaderLine 'writes vendor name to file
rsCustomer.MoveNext
'loop through invoice records nested inside vendor record loop
Set rsPaylinkGP_VOI = CurrentDb.OpenRecordset("PaylinkGP_VOI", dbOpenSnapshot)
rsPaylinkGP_VOI.MoveFirst
Do While Not rsPaylinkGP_VOI.EOF 'write each vendor header
'Do While rsCustomer.Fields("Numerodedocumento") = rsPaylinkGP_VOI.Fields("TransactionReference")
' old CustID = rsCustomer.Fields("iddeproveedor").Value
' old CustName = rsCustomer.Fields("NombreProveedor").Value
'inicia carga de datos
vRecordType = rsPaylinkGP_VOI.Fields("RecordType").Value
vCustomerCountryCode = rsPaylinkGP_VOI.Fields("CustomerCountryCode").Value
vCustomerAccountNumber = rsPaylinkGP_VOI.Fields("CustomerAccountNumber").Value
vTransactionReference = rsPaylinkGP_VOI.Fields("TransactionReference").Value
vTransactionSequenceNumber = rsPaylinkGP_VOI.Fields("TransactionSequenceNumber").Value
vSubSequence = rsPaylinkGP_VOI.Fields("SubSequence").Value
vzInvoiceDetailDescription = rsPaylinkGP_VOI.Fields("zInvoiceDetailDescription").Value
vzBlank = rsPaylinkGP_VOI.Fields("zBlank").Value
'Trimidproveedor = Trim(Iddeproveedor)
'UpdateType = rsPaylinkGP_VOI.Fields("UpdateType")
'fin carga de datos
VOILine = vRecordType & "" & vCustomerCountryCode & "" & vCustomerAccountNumber & "" & vTransactionReference & "" & vTransactionSequenceNumber & "" & vSubSequence & "" & vzInvoiceDetailDescription & "" & vzBlank
'Print #FileNumber, HeaderLine 'writes vendor name to file
' Print #FileNumber, VOILine 'writes vendor name to file
'rsPaylinkGP_VOI.MoveNext
'Loop 'goes to next VOI RECORD
'---- TERMINA VOI -----------------------------------------------------------------------------
' rsPaylinkGP_VOI.MoveNext
' Loop 'goes to next VOI RECORD
'rsPaylinkGP_VOI.MoveNext
HeaderLine = RecordType & "" & CustomerCountryCode & "" & CustomerAccountNumber & "" & Fechadeldocumento & "" & TransactionCode & "" & Numerodedocumento & "" & TransactionSequenceNumber & "" & ThirdPartyTaxID & "" & CurrencyCode & "" & Iddeproveedor & "" & Montodeldocumento & "" & MaturityDate & "" & TransactionDetail1 & "" & TransactionDetail2 & "" & TransactionDetail3 & "" & TransactionDetail4 & "" & LocalTranCode & "" & CustomerAccType & "" & Nombreproveedor & "" & ThirdPartyAddr1 & "" & ThirdPartyAddr1B & "" & ThirdPartyBankNumber & "" & ThirdPartyBankAgency & "" & ThirdPartyAcctNumber & "" & AccType & "" & ThirdPartyBF & "" & TransactionDeliveryMethod & "" & CollActivityCode & "" & ThirdPartyEmailAddr & "" & MaximumPayment & "" & UpdateType
'Print #FileNumber, HeaderLine 'writes vendor name to file
Print #FileNumber, HeaderLine
Print #FileNumber, VOILine 'writes vendor name to file
rsPaylinkGP_VOI.MoveNext
Loop 'goes to next vendor
Loop 'goes to next VOI RECORD
'rsCustomer.MoveNext

' Final Footer
Set rsFooter = CurrentDb.OpenRecordset("Footer", dbOpenSnapshot)
rsFooter.MoveFirst
Do While Not rsFooter.EOF 'write each vendor header
' old CustID = rsCustomer.Fields("iddeproveedor").Value
RecordType = rsFooter.Fields("RecordType").Value
NumberofTransactions = rsFooter.Fields("NumberofTransactions").Value
TotalTransacAmount = rsFooter.Fields("TotalTransacAmount").Value
ThirdPartyRecords = rsFooter.Fields("ThirdPartyRecords").Value
RecordsSent = rsFooter.Fields("RecordsSent").Value
FooterLine = RecordType & "" & NumberofTransactions & "" & TotalTransacAmount & "" & ThirdPartyRecords & "" & RecordsSent
'RecordType & "" & CustomerCountryCode & "" & CustomerAccountNumber & "" & Fechadeldocumento & "" & TransactionCode & "" & Numerodedocumento & "" & TransactionSequenceNumber & "" & ThirdPartyTaxID & "" & CurrencyCode & "" & Iddeproveedor & "" & Montodeldocumento & "" & MaturityDate & "" & TransactionDetail1 & "" & TransactionDetail2 & "" & TransactionDetail3 & "" & TransactionDetail4 & "" & LocalTranCode & "" & CustomerAccType & "" & Nombreproveedor & "" & ThirdPartyAddr1 & "" & ThirdPartyAddr1B & "" & ThirdPartyBankNumber & "" & ThirdPartyBankAgency & "" & ThirdPartyAcctNumber & "" & AccType & "" & ThirdPartyBF & "" & TransactionDeliveryMethod & "" & CollActivityCode & "" & ThirdPartyEmailAddr & "" & MaximumPayment & "" & UpdateType
Print #FileNumber, FooterLine 'writes vendor name to file
rsFooter.MoveNext
Loop
rsFooter.Close
' End Final Footer
rsCustomer.Close 'when all vendor are done
'rsFooter.Close 'when all vendor are done
rsPaylinkGP_VOI.Close
Close #FileNumber 'close the text file
MsgBox "Finalizada la Exportacion"
End Sub
_____________________________________________

Thanks in advanced...
 
Perhaps if you format your code?
Code: 
Private Sub Command1_Click()
    DoCmd.SetWarnings False
    'DoCmd.RunSQL "DELETE * FROM PaylinkGP"
    DoCmd.SetWarnings True
    'end deleting
    Dim FileName As String
    FileName = "C:\PayLink\DigicelPaylink.txt"
    Dim FileNumber As Long
    Dim qryCurrent As QueryDef
    Dim rsCustomer As Recordset
    Dim rsInvoice As Recordset
    Dim CustIDas As Long
    Dim CustNameas As String
    Dim InvID As String
    Dim InvAmt As String 'pretending all are integers
    Dim InvLine As String
    Dim contador As Integer
    Dim HeaderLine As String
    
    'open new file
    FileNumber = FreeFile
    Open FileName For Output As FileNumber
    'loop through invoice records nested inside vendor record loop
    Set rsCustomer = CurrentDb.OpenRecordset("PaylinkGP", dbOpenSnapshot)
    rsCustomer.MoveFirst
    Do While Not rsCustomer.EOF 'write each vendor header
    'inicia carga de datos
        RecordType = rsCustomer.Fields("RecordType").Value
        CustomerCountryCode = rsCustomer.Fields("CustomerCountryCode").Value
        CustomerAccountNumber = rsCustomer.Fields("CustomerAccountNumber").Value
        Fechadeldocumento = rsCustomer.Fields("Fechadeldocumento").Value
        TransactionCode = rsCustomer.Fields("TransactionCode").Value
        Numerodedocumento = rsCustomer.Fields("Numerodedocumento").Value
        TransactionSequenceNumber = rsCustomer.Fields("TransactionSequenceNumber").Value
        ThirdPartyTaxID = rsCustomer.Fields("ThirdPartyTaxID").Value
        CurrencyCode = rsCustomer.Fields("CurrencyCode").Value
        Iddeproveedor = rsCustomer.Fields("Iddeproveedor").Value
        Montodeldocumento = rsCustomer.Fields("Montodeldocumento").Value
        MaturityDate = rsCustomer.Fields("MaturityDate").Value
        TransactionDetail1 = rsCustomer.Fields("TransactionDetail1").Value
        TransactionDetail2 = rsCustomer.Fields("TransactionDetail2").Value
        TransactionDetail3 = rsCustomer.Fields("TransactionDetail3").Value
        TransactionDetail4 = rsCustomer.Fields("TransactionDetail4").Value
        LocalTranCode = rsCustomer.Fields("LocalTranCode").Value
        CustomerAccType = rsCustomer.Fields("CustomerAccType").Value
        Nombreproveedor = rsCustomer.Fields("Nombreproveedor").Value
        ThirdPartyAddr1 = rsCustomer.Fields("ThirdPartyAddr1").Value
        ThirdPartyAddr1B = rsCustomer.Fields("ThirdPartyAddr1B").Value
        ThirdPartyBankNumber = rsCustomer.Fields("ThirdPartyBankNumber").Value
        ThirdPartyBankAgency = rsCustomer.Fields("ThirdPartyBankAgency").Value
        ThirdPartyAcctNumber = rsCustomer.Fields("ThirdPartyAcctNumber").Value
        AccType = rsCustomer.Fields("AccType").Value
        ThirdPartyBF = rsCustomer.Fields("ThirdPartyBF").Value
        TransactionDeliveryMethod = rsCustomer.Fields("TransactionDeliveryMethod").Value
        CollActivityCode = rsCustomer.Fields("CollActivityCode").Value
        ThirdPartyEmailAddr = rsCustomer.Fields("ThirdPartyEmailAddr").Value
        MaximumPayment = rsCustomer.Fields("MaximumPayment").Value
        'Trimidproveedor = Trim(Iddeproveedor)
        UpdateType = rsCustomer.Fields("UpdateType")
        'fin carga de datos
        HeaderLine = RecordType & "" & CustomerCountryCode & "" & CustomerAccountNumber & "" & Fechadeldocumento & "" & TransactionCode & "" & Numerodedocumento & "" & TransactionSequenceNumber & "" & ThirdPartyTaxID & "" & CurrencyCode & "" & Iddeproveedor & "" & Montodeldocumento & "" & MaturityDate & "" & TransactionDetail1 & "" & TransactionDetail2 & "" & TransactionDetail3 & "" & TransactionDetail4 & "" & LocalTranCode & "" & CustomerAccType & "" & Nombreproveedor & "" & ThirdPartyAddr1 & "" & ThirdPartyAddr1B & "" & ThirdPartyBankNumber & "" & ThirdPartyBankAgency & "" & ThirdPartyAcctNumber & "" & AccType & "" & ThirdPartyBF & "" & TransactionDeliveryMethod & "" & CollActivityCode & "" & ThirdPartyEmailAddr & "" & MaximumPayment & "" & UpdateType
         'Print #FileNumber, HeaderLine  'writes vendor name to file
        rsCustomer.MoveNext
        'loop through invoice records nested inside vendor record loop
        Set rsPaylinkGP_VOI = CurrentDb.OpenRecordset("PaylinkGP_VOI", dbOpenSnapshot)
        rsPaylinkGP_VOI.MoveFirst
        Do While Not rsPaylinkGP_VOI.EOF 'write each vendor header
            'Do While rsCustomer.Fields("Numerodedocumento") = rsPaylinkGP_VOI.Fields("TransactionReference")
            ' old CustID = rsCustomer.Fields("iddeproveedor").Value
            ' old CustName = rsCustomer.Fields("NombreProveedor").Value
            'inicia carga de datos
            vRecordType = rsPaylinkGP_VOI.Fields("RecordType").Value
            vCustomerCountryCode = rsPaylinkGP_VOI.Fields("CustomerCountryCode").Value
            vCustomerAccountNumber = rsPaylinkGP_VOI.Fields("CustomerAccountNumber").Value
            vTransactionReference = rsPaylinkGP_VOI.Fields("TransactionReference").Value
            vTransactionSequenceNumber = rsPaylinkGP_VOI.Fields("TransactionSequenceNumber").Value
            vSubSequence = rsPaylinkGP_VOI.Fields("SubSequence").Value
            vzInvoiceDetailDescription = rsPaylinkGP_VOI.Fields("zInvoiceDetailDescription").Value
            vzBlank = rsPaylinkGP_VOI.Fields("zBlank").Value
            'Trimidproveedor = Trim(Iddeproveedor)
            'UpdateType = rsPaylinkGP_VOI.Fields("UpdateType")
            'fin carga de datos
            VOILine = vRecordType & "" & vCustomerCountryCode & "" & vCustomerAccountNumber & "" & vTransactionReference & "" & vTransactionSequenceNumber & "" & vSubSequence & "" & vzInvoiceDetailDescription & "" & vzBlank
            'Print #FileNumber, HeaderLine  'writes vendor name to file
            ' Print #FileNumber, VOILine  'writes vendor name to file
             'rsPaylinkGP_VOI.MoveNext
             'Loop 'goes to next VOI RECORD
            '---- TERMINA VOI -----------------------------------------------------------------------------
            ' rsPaylinkGP_VOI.MoveNext
            ' Loop 'goes to next VOI RECORD
            'rsPaylinkGP_VOI.MoveNext
            HeaderLine = RecordType & "" & CustomerCountryCode & "" & CustomerAccountNumber & "" & Fechadeldocumento & "" & TransactionCode & "" & Numerodedocumento & "" & TransactionSequenceNumber & "" & ThirdPartyTaxID & "" & CurrencyCode & "" & Iddeproveedor & "" & Montodeldocumento & "" & MaturityDate & "" & TransactionDetail1 & "" & TransactionDetail2 & "" & TransactionDetail3 & "" & TransactionDetail4 & "" & LocalTranCode & "" & CustomerAccType & "" & Nombreproveedor & "" & ThirdPartyAddr1 & "" & ThirdPartyAddr1B & "" & ThirdPartyBankNumber & "" & ThirdPartyBankAgency & "" & ThirdPartyAcctNumber & "" & AccType & "" & ThirdPartyBF & "" & TransactionDeliveryMethod & "" & CollActivityCode & "" & ThirdPartyEmailAddr & "" & MaximumPayment & "" & UpdateType
             'Print #FileNumber, HeaderLine  'writes vendor name to file
            Print #FileNumber, HeaderLine
            Print #FileNumber, VOILine  'writes vendor name to file
            rsPaylinkGP_VOI.MoveNext
         Loop 'goes to next vendor
    Loop 'goes to next VOI RECORD
          'rsCustomer.MoveNext
        
        ' Final Footer
    Set rsFooter = CurrentDb.OpenRecordset("Footer", dbOpenSnapshot)
    rsFooter.MoveFirst
    Do While Not rsFooter.EOF 'write each vendor header
        ' old CustID = rsCustomer.Fields("iddeproveedor").Value
        RecordType = rsFooter.Fields("RecordType").Value
        NumberofTransactions = rsFooter.Fields("NumberofTransactions").Value
        TotalTransacAmount = rsFooter.Fields("TotalTransacAmount").Value
        ThirdPartyRecords = rsFooter.Fields("ThirdPartyRecords").Value
        RecordsSent = rsFooter.Fields("RecordsSent").Value
        FooterLine = RecordType & "" & NumberofTransactions & "" & TotalTransacAmount & "" & ThirdPartyRecords & "" & RecordsSent
        'RecordType & "" & CustomerCountryCode & "" & CustomerAccountNumber & "" & Fechadeldocumento & "" & TransactionCode & "" & Numerodedocumento & "" & TransactionSequenceNumber & "" & ThirdPartyTaxID & "" & CurrencyCode & "" & Iddeproveedor & "" & Montodeldocumento & "" & MaturityDate & "" & TransactionDetail1 & "" & TransactionDetail2 & "" & TransactionDetail3 & "" & TransactionDetail4 & "" & LocalTranCode & "" & CustomerAccType & "" & Nombreproveedor & "" & ThirdPartyAddr1 & "" & ThirdPartyAddr1B & "" & ThirdPartyBankNumber & "" & ThirdPartyBankAgency & "" & ThirdPartyAcctNumber & "" & AccType & "" & ThirdPartyBF & "" & TransactionDeliveryMethod & "" & CollActivityCode & "" & ThirdPartyEmailAddr & "" & MaximumPayment & "" & UpdateType
        Print #FileNumber, FooterLine  'writes vendor name to file
        rsFooter.MoveNext
    Loop
    rsFooter.Close
    ' End Final Footer
    rsCustomer.Close 'when all vendor are done
    'rsFooter.Close 'when all vendor are done
    rsPaylinkGP_VOI.Close
    Close #FileNumber 'close the text file
    MsgBox "Finalizada la Exportacion"
End Sub
and use code tags...

I think this code already does write 2 types of lines?
Code: 
            VOILine = vRecordType & "" & vCustomerCountryCode & "" & vCustomerAccountNumber & "" & vTransactionReference & "" & vTransactionSequenceNumber & "" & vSubSequence & "" & vzInvoiceDetailDescription & "" & vzBlank
...
            HeaderLine = RecordType & "" & CustomerCountryCode & "" & CustomerAccountNumber & "" & Fechadeldocumento & "" & TransactionCode & "" & Numerodedocumento & "" & TransactionSequenceNumber & "" & ThirdPartyTaxID & "" & CurrencyCode & "" & Iddeproveedor & "" & Montodeldocumento & "" & MaturityDate & "" & TransactionDetail1 & "" & TransactionDetail2 & "" & TransactionDetail3 & "" & TransactionDetail4 & "" & LocalTranCode & "" & CustomerAccType & "" & Nombreproveedor & "" & ThirdPartyAddr1 & "" & ThirdPartyAddr1B & "" & ThirdPartyBankNumber & "" & ThirdPartyBankAgency & "" & ThirdPartyAcctNumber & "" & AccType & "" & ThirdPartyBF & "" & TransactionDeliveryMethod & "" & CollActivityCode & "" & ThirdPartyEmailAddr & "" & MaximumPayment & "" & UpdateType
..
            Print #FileNumber, HeaderLine
            Print #FileNumber, VOILine  'writes vendor name to file
 
OK.. This is the problem, the first query show the Customer data (7 columns), the second query shows the total ammount he buyed (3 columns) so the file should be like this:
Customer1 data
Customer1 ammount
Customer2 data
Customer2 ammount
...and so on
Footer
I need help with the "Customer" lines... rigth now the file looks like this:

Customer1 data
Customer1 ammount
Customer1 data
Customer2 ammount
Customer1 data
Customer3 ammount
Customer2 data
Customer1 ammount
Customer2 data
Customer2 ammount
Customer2 data
Customer3 ammount
....

The "Customer data" row is repeated as many "customer ammount" rows I have in my query.
 
lets simplyfy your code:
Code: 
    Set rsCustomer = CurrentDb.OpenRecordset("PaylinkGP", dbOpenSnapshot)
    rsCustomer.MoveFirst
    Do While Not rsCustomer.EOF 'write each vendor header
        rsCustomer.MoveNext
        'loop through invoice records nested inside vendor record loop
        Set rsPaylinkGP_VOI = CurrentDb.OpenRecordset("PaylinkGP_VOI", dbOpenSnapshot)
        rsPaylinkGP_VOI.MoveFirst
        Do While Not rsPaylinkGP_VOI.EOF 'write each vendor header
            VOILine = vRecordType & "" & vCustomerCountryCode & "" & vCustomerAccountNumber & "" & vTransactionReference & "" & vTransactionSequenceNumber & "" & vSubSequence & "" & vzInvoiceDetailDescription & "" & vzBlank
            HeaderLine = RecordType & "" & CustomerCountryCode & "" & CustomerAccountNumber & "" & Fechadeldocumento & "" & TransactionCode & "" & Numerodedocumento & "" & TransactionSequenceNumber & "" & ThirdPartyTaxID & "" & CurrencyCode & "" & Iddeproveedor & "" & Montodeldocumento & "" & MaturityDate & "" & TransactionDetail1 & "" & TransactionDetail2 & "" & TransactionDetail3 & "" & TransactionDetail4 & "" & LocalTranCode & "" & CustomerAccType & "" & Nombreproveedor & "" & ThirdPartyAddr1 & "" & ThirdPartyAddr1B & "" & ThirdPartyBankNumber & "" & ThirdPartyBankAgency & "" & ThirdPartyAcctNumber & "" & AccType & "" & ThirdPartyBF & "" & TransactionDeliveryMethod & "" & CollActivityCode & "" & ThirdPartyEmailAddr & "" & MaximumPayment & "" & UpdateType
            Print #FileNumber, HeaderLine
            Print #FileNumber, VOILine  'writes vendor name to file
            rsPaylinkGP_VOI.MoveNext
         Loop 'goes to next vendor
    Loop 'goes to next VOI RECORD
          'rsCustomer.MoveNext

I think your looping the PaylinkGP table for each and every customer, prinign a customer line each time, so the code seems to be doing ath your telling it to do..

If you dont want the customer to repeat then take it out of the loop and into a place where it is only printed when needed.
 
Exactly,... I now what to do, but I need help with how to do it...
can you help me with the code, I was thinking in a For... then statement with 2 cycles, but how can I code this?
 
Might it be enough to take the customer line to the customer level?
Code: 
        Do While Not rsPaylinkGP_VOI.EOF 'write each vendor header
            VOILine = vRecordType & "" & vCustomerCountryCode & "" & vCustomerAccountNumber & "" & vTransactionReference & "" & vTransactionSequenceNumber & "" & vSubSequence & "" & vzInvoiceDetailDescription & "" & vzBlank
to
Code: 
        VOILine = vRecordType & "" & vCustomerCountryCode & "" & vCustomerAccountNumber & "" & vTransactionReference & "" & vTransactionSequenceNumber & "" & vSubSequence & "" & vzInvoiceDetailDescription & "" & vzBlank
        Do While Not rsPaylinkGP_VOI.EOF 'write each vendor header

And... it is 'more common' to have your move next just before the loop
Code: 
    Do While Not rsCustomer.EOF 'write each vendor header
        rsCustomer.MoveNext
...
    loop

to
Code: 
    Do While Not rsCustomer.EOF 'write each vendor header
...
        rsCustomer.MoveNext
    loop
 
All VOI lines are printed for each PAY line.. This is the result:

PAY5900500564016100614071PAY00012113 00000444ANEGON001 USDANEGON001 000000000300000 0101ANEL GONZALEZ AIZPURUA
VOI5900500564016PAY00022339 000000010001
VOI5900500564016PAY00022340 000000010001
VOI5900500564016PAY00022341 000000010001
VOI5900500564016PAY00022342 000000010001
VOI5900500564016PAY00022343 000000010001
VOI5900500564016PAY00022344 000000010001
VOI5900500564016PAY00022348 000000010001
VOI5900500564016PAY00022349 000000010001
VOI5900500564016PAY00022350 000000010001
VOI5900500564016PAY00022351 000000010001
VOI5900500564016PAY00022354 000000010001
VOI5900500564016PAY00022355 000000010001
VOI5900500564016PAY00022357 000000010001
VOI5900500564016PAY00022358 000000010001
VOI5900500564016PAY00022359 000000010001
VOI5900500564016PAY00022360 000000010001
VOI5900500564016PAY00022361 000000010001
VOI5900500564016PAY00022362 000000010001
VOI5900500564016PAY00022364 000000010001
VOI5900500564016PAY00022366 000000010001
VOI5900500564016PAY00022367 000000010001
VOI5900500564016PAY00022368 000000010001
VOI5900500564016PAY00022369 000000010001
VOI5900500564016PAY00022370 000000010001
VOI5900500564016PAY00022371 000000010001
VOI5900500564016PAY00022372 000000010001
PAY5900500564016100614071PAY00012129 000004452-106-1737 USDDALVAR001 000000000031500 0102DALIS VARGAS
VOI5900500564016PAY00022339 000000010001
VOI5900500564016PAY00022340 000000010001
VOI5900500564016PAY00022341 000000010001
VOI5900500564016PAY00022342 000000010001
VOI5900500564016PAY00022343 000000010001
VOI5900500564016PAY00022344 000000010001
VOI5900500564016PAY00022348 000000010001
 
Well thats because your getting your whole table, not just the VOI for that customer, try changing:
Set rsPaylinkGP_VOI = CurrentDb.OpenRecordset("PaylinkGP_VOI", dbOpenSnapshot)

Into something that is limited to that customer only, something like:
Set rsPaylinkGP_VOI = CurrentDb.OpenRecordset("Select * from PaylinkGP_VOI where customerID = " & CustomerAccountNumber , dbOpenSnapshot)
 
Thanks a lot, that makes sense... I changed my code to this:
strSQL = "Select * from PaylinkGP_VOI where TransactionReference =" & Numerodedocumento

Set rsPaylinkGP_VOI = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

_____________________________________________________
but it sends this error: Too few parameters. Expected 1.
If I choose DEBUG and pass the cursor over strSQL sentence I can see the value for "Numerodedocumento", but if I pass the cursor over "Set rsPaylinkGP_VOI" it displays "rsPaylinkGP_VOI = Empty"

any clue?
 
I must say that the value for "Numerodedocumento" is correct and it exist in PaylinkGP_VOI table.
 
pcbozz said:
Thanks a lot, that makes sense... I changed my code to this:
strSQL = "Select * from PaylinkGP_VOI where TransactionReference =" & Numerodedocumento

Set rsPaylinkGP_VOI = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

_____________________________________________________
but it sends this error: Too few parameters. Expected 1.
If I choose DEBUG and pass the cursor over strSQL sentence I can see the value for "Numerodedocumento", but if I pass the cursor over "Set rsPaylinkGP_VOI" it displays "rsPaylinkGP_VOI = Empty"

any clue?
Click to expand...

That probably means that your TransactionReference is a text field not a number field.
To look for text you must use quotes to identify that as text:
strSQL = "Select * from PaylinkGP_VOI where TransactionReference = '" & Numerodedocumento &"'"

For dates:
strSQL = "Select * from PaylinkGP_VOI where TransactionReference = #" & Numerodedocumento &"#"

And lastly the original for numbers:
strSQL = "Select * from PaylinkGP_VOI where TransactionReference =" & Numerodedocumento
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom