Checkbox If statement

[soco3594]

Hi guys,

I have a form called PurchaseOrders which displays information about purchase orders raised against a contract. The form is based on PurchaseOrders query, which displays the following:

- OrderNumber
- Vendor
- OrderDescription
- OrderValue
- Invoice1
- Invoice2
- Invoice3
- Invoice4
- Invoice5


- OutstandingInvoices (calculated field)
- OrderComplete (checkbox)

Each time you enter a value against "Invoice1", "Invoice2" etc. in the form, the query recalculates the value of "OutstandingInvoices". What I want to do is add a click event to the "OrderComplete" checkbox which contains the following IF statement:

Private Sub OrderComplete_click()

      If OrderComplete.Value = True Then
      OrderValue = Invoice1 + Invoice2 + Invoice3 + Invoice4 + Invoice5
     
      End If

End Sub

So basically I want the value of "OrderValue" to be overridden (to equal the sum of the invoices posted) if the checkbox is ticked. This would then make the value of "OutstandingInvoices" equal to zero, since [OutstandingInvoices] = [OrderValue] - [Invoice1] - [Invoice2] - [Invoice3] - [Invoice4] - [Invoice5] in the query.

Let me know if that doesn't make any sense (it does in my head but I've been staring at it for the last week).

Thanks!

[June7]

Saving calculated date (data dependent on other data) is usually a bad idea. There really is no reason to save this calculated data to table. Calculate when needed.

Multiple Invoice fields is not a normalized structure. Will there always be 5 invoices? What if you need 6 or more?

Did you try your code? What happens?

[soco3594]

There are usually 5 invoices or less. I understand that multiple invoice fields is probably not the right way to go about it, but I set the database up a while ago when I was just learning Access and am working with what I've got for now.

If I substitute the calculation with a MsgBox e.g.

Private Sub OrderComplete_click()

      If OrderComplete.Value = True Then
      MsgBox ("Order Complete")
     
      End If

End Sub

It works fine. Can you suggest a better way of going about it? All of my cost reports etc. track cost based on purchase order value, so if there is value left on a purchase order once all invoices have been submitted, my cost forecast won't be accurate.

[June7]

I don't understand that. What do you mean by 'value left' - how is this calculated?

[soco3594]

Value left = order value - invoiced value. Never mind, I've come up with a manual work around which prompts the user to amend the Order Value if the total invoices exceeds the Order Value (and vice versa):

Private Sub OrderComplete_Click()

    If OrderComplete.Value = True And OutstandingInvoices > 0 Then
    MsgBox ("Total invoices exceed Order Value" & vbCrLf & vbCrLf & "Amend Order Value")
    
    ElseIf OrderComplete.Value = True And OutstandingInvoices < 0 Then
    MsgBox ("Order Value exceeds total invoices" & vbCrLf & vbCrLf & "Amend Order Value")
    
    End If
    
End Sub

That achieves what I'm after and doesn't require saving of calculated data

[June7]

Okay, think I understand the process better:

1. order is created and OrderValue is manually entered

2. invoices get processed - up to five and their sum might or might not agree with the original OrderValue

3. modify the OrderValue with the final sum of the invoices

If you change the original OrderValue then you lose information. You can no longer compare the original OrderValue with the final actual. What you can do is keep the original order value and calculate the final by summing the 5 invoices when needed. Then if you want to do forecasting based on the actual final, use the calculated total of invoices.

So if you don't care about retaining the original order value, your initial code to change it should work. And don't have to harass user or depend on them to follow through.

Are you aware that arithmetic with Null will return Null? This means if any of the Invoice fields is Null, the calculation will return Null.

[soco3594]

I've covered the Null case in my query using Nz, but that may be what was causing the zero values in my form... I have thought about using the sum of invoices in my cost forecast for orders marked as complete, but in essence the original Order Value is of no value to me (excuse the pun). Once an order is closed, only the invoiced value is important.

Thanks for your help

[soco3594]

You were right, the null values were causing the issue. I've settled with the following code:

Private Sub OrderComplete_Click()

' prompt user to amend Order Value if total invoices <> Order Value

    Dim Response As String

    If OrderComplete.Value = True And OutstandingInvoices < 0 Then
    Response = MsgBox("Total invoices exceed Order Value" & vbCrLf & vbCrLf & "Amend Order Value and close order?", VbMsgBoxStyle.vbOKCancel)
    
        If Response = vbOK Then
        OrderValue = Nz(Invoice1) + Nz(Invoice2) + Nz(Invoice3) + Nz(Invoice4) + Nz(Invoice5)
      
        ElseIf Response = vbCancel Then
        Undo
        
        End If
    
    ElseIf OrderComplete.Value = True And OutstandingInvoices > 0 Then
    Response = MsgBox("Total invoices less than Order Value" & vbCrLf & vbCrLf & "Amend Order Value and close order?", VbMsgBoxStyle.vbOKCancel)
    
        If Response = vbOK Then
        OrderValue = Nz(Invoice1) + Nz(Invoice2) + Nz(Invoice3) + Nz(Invoice4) + Nz(Invoice5)
    
        ElseIf Response = vbCancel Then
        Undo
      
        End If
     
    End If
    
End Sub

The multiple invoice fields are still not ideal and the code does save calculated data into the table but it doesn't seem to be causing any issues. I'll try and tidy it up more when I have a bit more time to spend on it.

Cheers