DSum and values in different records

[MikeLeBen]

I have a table that holds the information for the "Ricette" (literally prescriptions, practically checks provided to some of our customers by the public health system).

I think an example will come in handy to explain the situation.

A customer places an order for a total worth of 95€.

She has 2 checks each worth 49.50 for a total of 99.

I am building a form where a button makes the necessary calculations and saves the result in the "residue" field of said table.

Private Sub cmdAggiornaResiduo_Click()

Dim disp As Currency                   'total availability, see Dsum below
Dim totPagamenti As Currency      'result of Dsum for all due payments
Dim sforo As Currency                  '<- this, keeps track of excess in payments
Dim risp As Integer                        

disp = (DSum("Residuo", "Clienti_Ricette", "IDCliente = " & Me.IDCliente))

If IsNull(DLookup("Importo", "Ordini", "IDRicetta = " & Me.IDRicetta)) Then
   Me.txtResiduo = Me.ValoreRicetta
    
    Else
    
    totPagamenti = DSum("[Importo]", "Ordini", _
                   "[IDRicetta]= " & Me.IDRicetta.Value)
End If

With Me![txtResiduo]
    If totPagamenti > disp Then
            
        risp = MsgBox("Residuo insufficiente al pagamento!", 0, "Attenzione")
                                  
        Else
        
        sforo = Me.ValoreRicetta - totPagamenti
        'Me.txtResiduo = Me.ValoreRicetta - totPagamenti
        
    End If
    
Call .Requery
    
End With

End Sub

Basically i'd like to be able to add "sforo" (that holds a negative value) to the residue field in another check, if available.

 

[SmallTime]

Maybe it's a little too late for me (gone midnight) but I think might have over complicated you question. Try to simplify things a little.

Also, just as a side issue when you're doing calculations learn to love the Nz.

Take Care
SmallTime

 

[MikeLeBen]

I wish I could make this simpler, but that would require a coding prowess I undoubtedly lack.

Regardless, I think so far the routine might still be valid; I now need to modify so it runs a query and subtract the excess value if another check is available.

Something like
"SELECT IDRicetta FROM Clienti_Ricette WHERE " & _
"[Residuo] >= " & sforo

and then use the "residue" field for that id to subtract.

That is if a customer pays 70€ and she has two checks for 40€ each, she'd end up with a check with 0€ and the other with 10€ left.

 

[MikeLeBen]

Ok this is now complete, though still at a prototype state.
Should anyone need the routine for such a messy calculation, here goes:

Private Sub cmdAggiornaResiduo_Click()

'variables for availability calculations
Dim disp As Single
Dim totPagamenti As Single
Dim sforo As Single

'variable for querying the database
Dim mySQL As String

disp = (DSum("Residuo", "Clienti_Ricette", "IDCliente = " & Me.IDCliente))

If IsNull(DLookup("Importo", "Ordini", "IDRicetta = " & Me.IDRicetta)) Then
   Me.txtResiduo = Me.ValoreRicetta
    
    Else
    
    If IsNull(DSum("[Importo]", "Ordini", _
                   "[IDRicetta]= " & Me.IDRicetta & _
                   " AND [PagatoConRic] = -1")) Then
    Else
    totPagamenti = DSum("[Importo]", "Ordini", _
                   "[IDRicetta]= " & Me.IDRicetta & _
                   " AND [PagatoConRic] = -1")
    End If
End If

With Me![txtResiduo]
    If totPagamenti > disp Then
            
        MsgBox "Residuo insufficiente al pagamento!", 0, "Attenzione"
                                  
        Else
        
        sforo = totPagamenti - Me.ValoreRicetta
        If sforo <= 0 Then
        Me.txtResiduo = Me.ValoreRicetta - totPagamenti
        Else
        Me.txtResiduo = 0
        RunCommand acCmdSaveRecord
        
        mySQL = "UPDATE Clienti_Ricette " & _
                "SET Residuo = Residuo - " & Replace(sforo, ",", ".") & _
                " WHERE [Residuo] >= " & Replace(sforo, ",", ".") & _
                " AND [IDCliente] = " & Me.IDCliente
                Debug.Print mySQL
        DoCmd.RunSQL mySQL
        DoCmd.RunSQL ("UPDATE Ordini SET PagatoConRic = -1 " & _
                     " WHERE IDRicetta = " & Me.IDRicetta)
        End If
    End If
    
Call .Requery
End With

Me.lstRicetteClienti.Requery

End Sub