Need to add line-item purchase order numbers to an existing invoice report

I

Ingeneeus

Registered User.
Local time
Today, 06:40
Joined
Jul 29, 2011
Messages
89
Hello again; as usual, I will begin with a bit of explanation. Sorry if this gets long-winded:

I have a report – not written by me – that my company uses as an invoice. The report was originally designed to list Product Options directly under the product name on the invoice. It allows for 10 options, each of which is in a separate field in the Order Details table (Option01, Option02, Option03, etc.). I’m attaching a screencap of what the Orders screen looks like for reference purposes. Note that on the Orders screen, these are referenced as Option1, Option2, etc., rather than Option01, Option 02, etc.

This is the original code. If there is something in the Product Options field for a particular line item, the code inserts a hard return after the product name, inserts a “ – “, and then inserts whatever is in the appropriate Product Option field.

Code: 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim x$, y$, i%
    
    x = ""
    For i = 1 To 10
        y = Me("txtOp" & i) & ""
        If y > "" Then
            If x > "" Then x = x & cr
            x = x & " - " & y
        End If
    Next
    If x > "" Then x = cr & x
    Me.txtProduct = Me.txtItem & "" & x
        
    If Me.Adjustment Then
        Me.txtShowSKU = ""
    Else
        Me.txtShowSKU = Me.txtSKU
    End If
    
End Sub
Some time ago, we decided to, uh, hijack one of the Product Option fields so that we could accommodate line-item purchase orders from our customers (we don’t have any actual Product Options).

We decided that we would use Option02 as the field for line item purchase orders. I was able to modify the original code – I suspect with help from someone on this very board – so that, if there was something in the Option02 field, it would insert “PO – “ instead of the usual “ – “ before putting in the content of the Option02 field.

Code: 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim x$, y$, i%
    
    x = ""
    For i = 1 To 10
        y = Me("txtOp" & i) & ""
        If y > "" Then
'If there is anything in Option02, this inserts "PO - " in front of it
            If y = Me("txtOp2") & "" Then
                If x > "" Then x = x & cr
            x = x & " PO - " & y
            ElseIf y <> Me("txtOp2") & "" Then
'End inserted code
            If x > "" Then x = x & cr
            x = x & " - " & y
        End If
        End If
        
    Next
    If x > "" Then x = cr & x
    Me.txtProduct = Me.txtItem & "" & x
        
    If Me.Adjustment Then
        Me.txtShowSKU = ""
    Else
        Me.txtShowSKU = Me.txtSKU
    End If
    
End Sub
It has been working like a charm. I have attached a screencap of the report generated with this modification.

So now we get to the next thorny issue. We have decided that, in addition to line-item purchase orders, we need to support line item Fund Codes. We selected the Option01 field for Fund Codes. I initially thought that this would be a simple tweak. I thought wrong.
I’ve tried a number of different bits of code so that we could add the “Fund Code” label to the report, all of which have failed to one degree or another. In the worst cases, it blew up the report completely and I had to restore from my backup copy (I always make a backup copy before I start). Errors galore, I’m afraid. The code manages to not crash the report, and it does add the “Fund Code” label, but it also puts the content of the Option01 field into the report again, this time preceded by the “ – “ (see third screencap).
Code: 
  Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim x$, y$, i%
    
    x = ""
    For i = 1 To 10
        y = Me("txtOp" & i) & ""
        If y > "" Then
'New Code to insert "fund code"
            If y = Me("txtOp1") & "" Then
                If x > "" Then x = x & cr
            x = x & " Fund Code: " & y
             End If
'Original code
'If there is anything in Option02, this inserts "PO - " in front of it
            If y = Me("txtOp2") & "" Then
                If x > "" Then x = x & cr
            x = x & " PO - " & y
            ElseIf y <> Me("txtOp2") & "" Then
'End inserted code
            If x > "" Then x = x & cr
            x = x & " - " & y
        
        End If
        End If
        
    Next
    If x > "" Then x = cr & x
    Me.txtProduct = Me.txtItem & "" & x
        
    If Me.Adjustment Then
        Me.txtShowSKU = ""
    Else
        Me.txtShowSKU = Me.txtSKU
    End If
    
End Sub
I’m convinced this is tied to the [FONT=&quot]ElseIf y <> Me("txtOp2") & "" Then [/FONT][FONT=&quot]line, but don’t know how to solve it. [/FONT] I initially thought I could just change the line to [FONT=&quot]ElseIf y <> Me("txtOp1") & "" OR y <> Me("txtOp2") & "" Then[/FONT][FONT=&quot] but that got me one of Microsoft’ s illegal procedure warnings. From what I’ve gleaned from googling, I now think you can’t put an OR in an ElseIf statement, but I don’t know how to craft a line which does something like “check to see if there’s something in Option01, if so, add ‘Fund Code’ in front of it, and then move on to see if there is something in Option02.” [/FONT]

[FONT=&quot]I’m hoping that by the time I get back into the office on Monday, someone here will have told me where I went wrong.[/FONT]
[FONT=&quot]Thanks![/FONT]
 

Attachments

  • 1-Orders - Options.jpg
    1-Orders - Options.jpg
    99.4 KB · Views: 171
  • 2-Invoice - PO.jpg
    2-Invoice - PO.jpg
    51 KB · Views: 168
  • 3-Invoice - FundCode.jpg
    3-Invoice - FundCode.jpg
    59.4 KB · Views: 163
I think it's one of those cases where if you keep adding and adding you end up with a bit of spaghetti code. Try this

Code: 
  Dim x$, y$, i%
    Dim prefix As String


    x = Me.txtItem
    
    For i = 1 To 10

        Select Case i
            Case 1: prefix = " Fund Code: "
            Case 2: prefix = " PO "
            Case Else: prefix = ""
        End Select
        If Nz(Me("txtOp" & i), "") <> "" Then
        
            x = x & cr & prefix & " - " & Me("txtOp" & i)
            
        End If
    Next
    
    Me.txtProduct = x
        
    If Me.Adjustment Then
        Me.txtShowSKU = ""
    Else
        Me.txtShowSKU = Me.txtSKU
    End If

Hopefully easier to follow/read
 
please, if this can help
Code: 
  Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim x$, y$, i%
    
    x = ""
    For i = 1 To 10
        y = Me("txtOp" & i) & ""
        If y <> "" Then
    'New Code to insert "fund code"
            If y = Me("txtOp1") & "" Then
                If x <> "" Then 
            x = x & cr
                    x = x & " Fund Code: " & y
        end if
             End If
    'Original code
    'If there is anything in Option02, this inserts "PO - " in front of it
            If y = Me("txtOp2") & "" Then
                If x <> "" Then x = x & cr
                x = x & " PO - " & y
            ElseIf y <> Me("txtOp2") & "" Then
        'End inserted code
        End If
            'If x <> "" Then x = x & cr
            'x = x & " - " & y
        
        End If
        
    Next
    If x <> "" Then x = cr & x
    Me.txtProduct = Me.txtItem & "" & x
        
    If Me.Adjustment Then
        Me.txtShowSKU = ""
    Else
        Me.txtShowSKU = Me.txtSKU
    End If
    
End Sub
 
arnelgp said:
please, if this can help
Code: 
  Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim x$, y$, i%
    
    x = ""
    For i = 1 To 10
        y = Me("txtOp" & i) & ""
        If y <> "" Then
    'New Code to insert "fund code"
            If y = Me("txtOp1") & "" Then
                If x <> "" Then 
            x = x & cr
                    x = x & " Fund Code: " & y
        end if
             End If
    'Original code
    'If there is anything in Option02, this inserts "PO - " in front of it
            If y = Me("txtOp2") & "" Then
                If x <> "" Then x = x & cr
                x = x & " PO - " & y
            ElseIf y <> Me("txtOp2") & "" Then
        'End inserted code
        End If
            'If x <> "" Then x = x & cr
            'x = x & " - " & y
        
        End If
        
    Next
    If x <> "" Then x = cr & x
    Me.txtProduct = Me.txtItem & "" & x
        
    If Me.Adjustment Then
        Me.txtShowSKU = ""
    Else
        Me.txtShowSKU = Me.txtSKU
    End If
    
End Sub
Click to expand...

Hi, arnelgp --
Thanks for responding!
It looked like your code should have worked, but unfortunately, there seems to be a hitch in it. It adds the "PO-" from the Option02 field ("txtOp2), but it seems to ignore the "Fund Code:" from Option01 ("txtOp1") somehow. I'm attaching a screencap.
 

Attachments

  • 5 - Invoice arnelgp.jpg
    5 - Invoice arnelgp.jpg
    64.3 KB · Views: 153
stopher said:
I think it's one of those cases where if you keep adding and adding you end up with a bit of spaghetti code. Try this

Code: 
  Dim x$, y$, i%
    Dim prefix As String


    x = Me.txtItem
    
    For i = 1 To 10

        Select Case i
            Case 1: prefix = " Fund Code: "
            Case 2: prefix = " PO "
            Case Else: prefix = ""
        End Select
        If Nz(Me("txtOp" & i), "") <> "" Then
        
            x = x & cr & prefix & " - " & Me("txtOp" & i)
            
        End If
    Next
    
    Me.txtProduct = x
        
    If Me.Adjustment Then
        Me.txtShowSKU = ""
    Else
        Me.txtShowSKU = Me.txtSKU
    End If
Hopefully easier to follow/read
Click to expand...

Hi, Stopher -- Thank you for responding. I absolutely agree that my normal approach -- which is to kind of cobble in bits of code that mimic the original to the best of my ability :o -- leads to mounds of tangled metaphorical pasta ;) . I just didn't know how to start from scratch!
Your solution is much more elegant, and best of all, works like a charm!
Thank you again. I will file this away for future use!

Best,
~Gene
 

Attachments

  • 4 - Invoice Stopher.jpg
    4 - Invoice Stopher.jpg
    84.9 KB · Views: 151
hardcoded:
Code: 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim x$
    
    x = ""
    ' hardcode
    x = IIf(Trim(Me.txtOp1 & "") = "", "", " Fund Code: " & Me.txtOp1 & vbCr)
    x = x & IIf(Trim(Me.txtOp2 & "") = "", "", " PO - " & Me.txtOp2 & vbCr)
    x = x & IIf(Trim(Me.txtOp3 & "") = "", "", Me.txtOp2 & vbCr)
    x = x & IIf(Trim(Me.txtOp4 & "") = "", "", Me.txtOp2 & vbCr)
    x = x & IIf(Trim(Me.txtOp5 & "") = "", "", Me.txtOp2 & vbCr)
    x = x & IIf(Trim(Me.txtOp6 & "") = "", "", Me.txtOp2 & vbCr)
    x = x & IIf(Trim(Me.txtOp7 & "") = "", "", Me.txtOp2 & vbCr)
    x = x & IIf(Trim(Me.txtOp8 & "") = "", "", Me.txtOp2 & vbCr)
    x = x & IIf(Trim(Me.txtOp9 & "") = "", "", Me.txtOp2 & vbCr)
    x = x & IIf(Trim(Me.txtOp10 & "") = "", "", Me.txtOp2 & vbCr)
    ' if the last char is vbCr, remove it from text
    If InStr(x, vbCr) = Len(x) Then x = Left(x, Len(x) - 1)
    ' append to x txtproduct
    Me.txtProduct = Me.txtItem & _
                    IIf(Len(x) <> 0, x, "")
    
    If Me.Adjustment Then
        Me.txtShowSKU = ""
    Else
        Me.txtShowSKU = Me.txtSKU
    End If

End Sub
 
Hi again, arnelgp --
Thanks for taking a second go at my problem! This solution appears to work as well, although I had to go in and make a few adjustments to this section:

x = IIf(Trim(Me.txtOp1 & "") = "", "", " Fund Code: " & Me.txtOp1 & vbCr)
x = x & IIf(Trim(Me.txtOp2 & "") = "", "", " PO - " & Me.txtOp2 & vbCr)
x = x & IIf(Trim(Me.txtOp3 & "") = "", "", Me.txtOp3 & vbCr)
x = x & IIf(Trim(Me.txtOp4 & "") = "", "", Me.txtOp4 & vbCr)
x = x & IIf(Trim(Me.txtOp5 & "") = "", "", Me.txtOp5 & vbCr)

etc., from your original. I will assume it was a quick copy/paste thing :)
 
You must log in or register to reply here.

Similar threads

G
CATIA Macro that inserts the biggest possible sphere inside of a model
Replies
11
Views
1,939
goncalo
G
D
Solved Creating transaction records via OnClick - (Sales Order to Invoice conversion)
Replies
10
Views
785
pbaldy
pbaldy
A
Solved Copying data from Quote-line-items Subform to Sales-Order-line-items Table : Error 3022
Replies
6
Views
761
Pat Hartman
P
D
Allocating payments to line items
2
Replies
20
Views
1,748
Pat Hartman
P
N
How to populate label controls on a form to act as a restaurant menu
Replies
16
Views
2,192
Pat Hartman
P

Users who are viewing this thread

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