Need to count the number of lines produced by WriteLines function in a module (1 Viewer)

[Ingeneeus]

I think that subject line needs a little explanation.

I've inherited (i.e. I didn't create it but I need to use it) an Access module that essentially creates a text file that my customers can import into their accounting systems as an electronic (EDI) invoice.

It looks like this, when opened in a text-viewer:

UNA:+.? '
UNB+UNOC:3+6319807:31B+63:91+150826:1043+i22727'
UNH++INVOIC'
BGM+380+22727+43'
DTM+137:20150826:102'
LIN+000001'
IMD+L+050+::retty Slick'
QTY+47:1'
MOA+203:250.00'
RFF+LI:.o15175911'
LIN+000002'
IMD+L+050+:::Fargo?: Season One'
QTY+47:1'
MOA+203:39.99'
RFF+LI:.o15175959'
UNS+S'
CNT+2:2'
MOA+86:296.65'
ALC+C++++TX'
MOA+8:0'
UNT+56+00000000055181'
UNZ:15000000022732'
​

The 3-letter designators at the start of each line alert the accounting system as to the meaning of the rest of the line. The apostrophe serves to designate the end of the line. The lines are created by ts.WriteLine statements in the module's code [ ts.WriteLine "UNA:+.? '", ts.WriteLine "UNB+UNOC:3+6319807:31B+" & rsOrders("CustomerID") . . ., etc. ].

The reason I'm posting here today is that we've just added a customer whose system demands a line at the end of the invoice which indicates the total number of lines in the invoice. This will vary, depending on the number of items in the order (there's a loop in the code which writes specific info like price and productcode for each line-item in the order).

LinNum = 0
                        If rsDetails.RecordCount > 0 Then
                            Do While Not rsDetails.EOF
                                LinNum = LinNum + 1
                                ts.WriteLine "LIN+" & Format(LinNum, "000000") & "'"
                                ts.WriteLine "IMD+L+050+:::" + EscapeInput(Left(rsDetails("PRODUCT"), 35)) & "'"
                                ts.WriteLine "QTY+47:" & rsDetails("QuantityShipped") & "'"
                                ts.WriteLine "MOA+203:" & Format(((rsDetails("QuantityShipped")) * (rsDetails("PricePerUnit"))), "0.00") & "'"
                                If (IsNull(rsDetails("Option02")) Or (rsDetails("Option02") = "")) Then
                                    ts.WriteLine "RFF+LI:.o" & rsDetails("OrderNumber") & rsDetails("ItemNumber") & "'"
                                Else
                                    ts.WriteLine "RFF+LI:.o" & Left$(rsDetails("Option02"), 33) & "'"
                                End If
                                rsDetails.MoveNext
                            Loop

So . . . I need some way to count the number of ts.WriteLines that have been executed in any given invoice, and then include another ts.WriteLine statement which puts that number in the invoice :banghead: Actually, that number less two because it doesn't need to count the UNA and UNB lines.

I've been googling for hours, but no real luck. All of the counting functions I've turned up all seem to deal with tallying up the total number of records in a table or counting occurrences of specific characters in strings, etc; nothing that seem to apply to this particular situation.

I have this kind of vague idea that I could count the number of apostrophes that have been written (although trying to get Access to recognize that I'm not using them as Comment marks seems like a nightmare), but I'm at a dead end as to how to accomplish that.

Any help you can offer would be much appreciated!

 

[jdraw]

Is the code you showed the only code to write the lines? I ask because I see some outputs (line prefixes UN*) that don't appear in the code.

It seems that the LinNum variable is keeping track of the count, but maybe there is more code.

 

[arnelgp]

just declare another integer in your code for the count of WriteLine

LinNum = 0
[I]dim numWriteLine as Integer[/I]
                        If rsDetails.RecordCount > 0 Then
                            Do While Not rsDetails.EOF
                                [I]numWriteLine = numWriteLine + 5[/I]
                                LinNum = LinNum + 1
                                ts.WriteLine "LIN+" & Format(LinNum, "000000") & "'"
                                ts.WriteLine "IMD+L+050+:::" + EscapeInput(Left(rsDetails("PRODUCT"), 35)) & "'"
                                ts.WriteLine "QTY+47:" & rsDetails("QuantityShipped") & "'"
                                ts.WriteLine "MOA+203:" & Format(((rsDetails("QuantityShipped")) * (rsDetails("PricePerUnit"))), "0.00") & "'"
                                If (IsNull(rsDetails("Option02")) Or (rsDetails("Option02") = "")) Then
                                    ts.WriteLine "RFF+LI:.o" & rsDetails("OrderNumber") & rsDetails("ItemNumber") & "'"
                                Else
                                    ts.WriteLine "RFF+LI:.o" & Left$(rsDetails("Option02"), 33) & "'"
                                End If
                                rsDetails.MoveNext
                            Loop

 

[gemma-the-husky]

the variable linnum is counting the number of lines. [edit. no it's not - it's counting documents. Just have another variable called tslines, say, and increment that each time you do a ts.writeline.]

incidentally, if you need more info, I think that EDI format is a standard format called tradacomms. You can probably find a lot of info on line about it.

 

[Gasman]

You could just subtract 2 at the end of the loop if UNA and UNB are always in the lines written, else you would need to test the prefix of each line and only increment the count when not one of those?

Also you would need to test for this particular customer after the loop and only write the total line for them?

 

[Ingeneeus]

Thank you all for your replies. This has been very helpful.

jdraw, your are correct in your assumption that there is more code to the module than I showed. I just copied a section. Sorry about that. Here's the full code:

[/SIZE][SIZE=2]Dim x, y[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    Dim LinNum As Long[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    Dim rs As Recordset[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    Dim rsDetails As Recordset[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    'New code: adds Orders table as recordset[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    Dim rsOrders As Recordset[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    'End New Code[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    Dim lngFormHwnd As Long[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    Dim lngAppInstance As Long[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    Dim strInitDir As String[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    Dim strFileFilter As String[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    Dim lngResult As Long[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    Dim strFileName As String[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    Dim fso[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    Dim ts[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]       [/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    'Formvar = "OrderNumber=" & Me.txtStart[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    On Error GoTo Err_OkClick[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    If IsNumeric(Me.txtStart) Then[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]        Set rs = CurrentDb.OpenRecordset("Select * from Orders WHERE OrderNumber = " & Me.txtStart & ";")[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]        If rs.RecordCount > 0 Then[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]            x = "SELECT * FROM [Order Details] WHERE OrderNumber=" & txtStart & " AND (Option02 is null or Option02 = '') and Adjustment=FALSE"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]            Set rsDetails = CurrentDb.OpenRecordset(x)[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]            If rsDetails.RecordCount > 0 Then[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                MsgBox "Order detail lines are missing purchase order information."[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]            Else[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                x = "SELECT * FROM [Order Details] WHERE OrderNumber=" & txtStart & " AND Adjustment=FALSE AND quantityshipped > 0 ORDER BY ITEMNUMBER"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                Set rsDetails = CurrentDb.OpenRecordset(x)[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                'New Code - adds an additional selection: Orders table[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                y = "SELECT * FROM [Orders] WHERE OrderNumber=" & txtStart[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                Set rsOrders = CurrentDb.OpenRecordset(y)[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                'End new code[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                If rsDetails.RecordCount > 0 Then[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                    lngFormHwnd = Me.hWnd[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                    lngAppInstance = Application.hWndAccessApp[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                    strFileFilter = "EDI Files (*.edi)" & Chr(0) & "*.edi" & Chr(0) & "Text Files (*.csv, *.txt)" & Chr(0) & "*.csv; *.txt" & Chr(0)[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                    lngResult = SaveFileDialog(lngFormHwnd, lngAppInstance, strInitDir, strFileFilter, "Inv" & txtStart & ".edi")[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                [/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                    If mblnStatus = True Then[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        strFileName = mstrFileName[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        Set fso = CreateObject("Scripting.FileSystemObject")[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        Set ts = fso.CreateTextFile(strFileName, True)[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        'MsgBox "You selected file: " & mstrFileName[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        ts.WriteLine "UNA:+.? '"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        'ts.WriteLine "UNB+:+:+:+:+++INVOIC'"   Replacement code follows immediately below this line[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        ts.WriteLine "UNB+UNOC:3+6319807:31B+" & rsOrders("CustomerID") & ":91+" & Format(Now(), "YYMMDD") & ":" & Format(Now(), "HHMM") & "+i" & txtStart & "'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        ts.WriteLine "UNH++INVOIC'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        'ts.WriteLine "BGM+380:::+" & txtStart & "+43'" Replacement code follows immediately below this line[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        ts.WriteLine "BGM+380+" & txtStart & "+43'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        ts.WriteLine "DTM+137:" & Format(Now(), "YYYYMMDD") & ":102'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                                        [/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        ' Read Details[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        x = "SELECT * FROM [Order Details] WHERE OrderNumber=" & txtStart & " AND Adjustment=FALSE AND quantityshipped > 0 ORDER BY ITEMNUMBER"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        Set rsDetails = CurrentDb.OpenRecordset(x)[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        [/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        LinNum = 0[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        Dim VariableLineCount As Integer[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        If rsDetails.RecordCount > 0 Then[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                            Do While Not rsDetails.EOF[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                            VariableLineCount = VariableLineCount + 5[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                                LinNum = LinNum + 1[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                                ts.WriteLine "LIN+" & Format(LinNum, "000000") & "'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                                ts.WriteLine "IMD+L+050+:::" + EscapeInput(Left(rsDetails("PRODUCT"), 35)) & "'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                                ts.WriteLine "QTY+47:" & rsDetails("QuantityShipped") & "'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                                ts.WriteLine "MOA+203:" & Format(((rsDetails("QuantityShipped")) * (rsDetails("PricePerUnit"))), "0.00") & "'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                                If (IsNull(rsDetails("Option02")) Or (rsDetails("Option02") = "")) Then[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                                    ts.WriteLine "RFF+LI:.o" & rsDetails("OrderNumber") & rsDetails("ItemNumber") & "'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                                Else[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                                    ts.WriteLine "RFF+LI:.o" & Left$(rsDetails("Option02"), 33) & "'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                                End If[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                                rsDetails.MoveNext[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                            Loop[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        End If[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        [/SIZE]

[SIZE=2][/SIZE][SIZE=2] [/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        ts.WriteLine "UNS+S'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        ts.WriteLine "CNT+2:" & LinNum & "'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        ts.WriteLine "MOA+86:" & rs("FinalGrandTotal") & "'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        ts.WriteLine "ALC+C++++TX'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        ts.WriteLine "MOA+8:" & rs("FinalTaxTotal") & "'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        'ts.WriteLine "UNT+56+00000000055181'"  
[/SIZE]
[SIZE=2][/SIZE][SIZE=2]                        ts.WriteLine "UNZ:15000000022732'"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        ts.Close[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        Set ts = Nothing[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        Set fso = Nothing[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        MsgBox "Order " & txtStart & " has been export to " & strFileName[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                    Else[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                        MsgBox "No file selected."[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                    End If[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                Else[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                    MsgBox "Order contains all backordered items.  No export performed."[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]                End If[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]            End If[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]        Else[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]            MsgBox "Order not found.", vbOKOnly, "Order Not Found"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]        End If[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    Else[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]        MsgBox "Order Number must be numeric.", vbOKOnly, "Order Number Invalid"[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    End If[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    Exit Sub[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    [/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]Err_OkClick:[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]    MsgBox "Error Occurred: " & Err.Description, vbInformation[/SIZE]
[SIZE=2]  [/SIZE][SIZE=2]End Sub[/SIZE]
[SIZE=2]

arnelgp, thanks for the suggestion and, more importantly, the example. Your fix was elegant and simple. Unfortunately, since I didn't specify that there were other ts.WriteLine lines that I failed to show, it counted only the variable line in the loop, not the fixed lines in the Header and Summary sections.
Because of your example, however, I was able to come up with a work-around in which I declared another variable integer (FixedLineCount) and set it to the number of static lines in the invoice, and then declared a third variable integer (TotalLines) which I set to be FixedLineCount + numWriteLine. I was then able to add a ts.WriteLine statement which inserts TotalLines into the invoice as a line item.

gemma-the-husky, thanks for the reply. It certainly backed up what I got from arnelgp. Fortunately, I don't think I need to know any more about EDI. I very much hope that this is about as deep as my involvement gets. This isn't really part of my job; it's just something that landed on my desk because I work with our Access-based invoicing system more than anybody else around here

Gasman, fortunately for me, the UNA and UNB lines are always present, so your solution works perfectly!

Thanks again to all!