Calculate sql recordset using access adp vba 3

[9milla]

I've been using the following section of code using access adp in vba with sql 2005 as the current connection. Am wanting to post the values to the current db based on certain events within the application.


Private Sub BtnUpdate1_Click()
'On Error GoTo Err_BtnUpdate1_Click

Dim aradb As Connection
Dim comrst As ADODB.Recordset
Dim rst As ADODB.Recordset
Dim DbRst As ADODB.Recordset
Dim SqlRst As ADODB.Recordset
Dim strDbRst As String
Dim StrYear As String
Dim StrMonth As String
Dim StrDate As String
Dim strCurDatePart As String

Dim strSQL As String
Dim strSQLrst As String
Dim strRstDate As String

Set aradb = CurrentProject.Connection
Set comrst = New ADODB.Recordset
Set rst = New ADODB.Recordset
Set DbRst = New ADODB.Recordset
Set SqlRst = New ADODB.Recordset

If IsNull(Me![Comment]) Then
MsgBox ("Please enter a valid Comment!")
Me![Comment].SetFocus
GoTo Exit_BtnUpdate1_Click
End If

If Me![Comment] = "PTPH" Or Me![Comment] = "PTPC" Or Me![Comment] = "PTPW" Then
If IsNull(Me![PTP DATE]) Then
MsgBox ("PTP Date must have a valid date in It. Please Correct!")
Me![PTPDate].SetFocus
GoTo Exit_BtnUpdate1_Click
End If

If IsNull(Me![PTP AMOUNT]) Or Me![PTP AMOUNT] < 1 Then
Me!ptpamt.SetFocus
MsgBox ("PTP Amount must have a value. Please Correct! ")
GoTo Exit_BtnUpdate1_Click
End If
End If

If (Comment = "ADM") Or (Comment = "HLC") Or (Comment = "PTPH") Or (Comment = "PTPW") Or (Comment = "QRY") Or (Comment = "RTP") Or (Comment = "PTPC") Or (Comment = "PTPH-NS") Or (Comment = "PTPW-NS") Or (Comment = "PTPC-NS") Or (Comment = "PTP-SUP") Then
Me.txtLetterCharge = "12.60"
Else: Me.txtLetterCharge = "0.00"
End If

'
'¯¯¯¯¯»» Checks if Date is valid and within the same ««¯¯¯¯¯'
'_____»» month and adds exp charges to new debtors ««_____'

strSQL = "SELECT ClaimNo, MAX(Date) AS Date, MAX(Comment) AS comment FROM dbo.[Comments Table] WHERE (Comment IN (N'qry', N'ptph', N'rtp', N'adm', N'ptpw', N'ptpc', N'ptph-ns', N'ptpw-ns', N'ptpc-ns', N'ptp-sup')) GROUP BY ClaimNo HAVING (ClaimNo = " & Me![ClaimNo] & ")"
rst.Open strSQL, aradb, adOpenDynamic, adLockOptimistic

If (rst.RecordCount <= 0) Then
strSQLrst = "Select * from [Payments Table] where 1 = 2"
SqlRst.Open strSQLrst, aradb, adOpenDynamic, adLockOptimistic
SqlRst.AddNew
SqlRst!ClaimNo = Me.ClaimNo
SqlRst!ClientCode = Me.ClientCode
SqlRst!FirstAccNo = Me.FirstAccNo
SqlRst!PayRefDesc = "EXPSUB " & Me.Comment
SqlRst!Value = Me.txtLetterCharge
SqlRst!Date = Format(Now(), "dd/mm/yyyy")
SqlRst!TransactionDate = Format(Now(), "dd/mm/yyyy")
SqlRst.Update
SqlRst.Close

strDbRst = "SELECT * FROM dbo.Debtors WHERE (ClaimNo = " & Me![ClaimNo] & ")"
DbRst.Open strDbRst, aradb, adOpenDynamic, adLockOptimistic
DbRst.AddNew
DbRst![Expenses] = Me.[Expenses]
DbRst![Total Expenses] = Me.[Total Expenses] + (Me.[Expenses] * 0.14)
DbRst.Update
DbRst.Close
'Me.[Total Expenses] = "(12.60 * 0.14)"
Else:

strCurDatePart = Right((Format(Now(), "dd MM yyyy")), 7)

If IsDate(rst!Date) Then
strRstDate = Format(rst!Date, "MM yyyy")
If (strCurDatePart = strRstDate) Then
MsgBox "The debtor has already been charged", vbInformation + vbExclamation + vbOKOnly
Me.txtLetterCharge = ""
ElseIf (strCurDatePart <> strRstDate) Then
strSQLrst = "Select * from [Payments Table] where 1 = 2"
SqlRst.Open strSQLrst, aradb, adOpenDynamic, adLockOptimistic
SqlRst.AddNew
SqlRst!ClaimNo = Me.ClaimNo
SqlRst!ClientCode = Me.ClientCode
SqlRst!FirstAccNo = Me.FirstAccNo
SqlRst!PayRefDesc = "EXPSUB " & Me.Comment
SqlRst!Value = Me.txtLetterCharge
SqlRst!Date = Format(Now(), "dd/mm/yyyy")
SqlRst!TransactionDate = Format(Now(), "dd/mm/yyyy")
SqlRst.Update
SqlRst.Close
End If
Else:
MsgBox "TEST"
End If
End If
'Me.[Total Expenses] = Me.[Total Expenses] + ([Expenses] + [Expenses] * 0.14)


comrst.Open "Select * from [Comments Table] where 1 = 2", aradb, adOpenDynamic, adLockOptimistic

'_________________________
'Update Comments Table
'-------------------------

comrst.AddNew
comrst![ClaimNo] = Me![ClaimNo]
comrst![Date] = Date
comrst![RefNo] = Me![CollectorNo]
comrst![Comment] = Me![Comment]
comrst![Remark1] = Me![Remarks]
comrst![PTPDate] = Me![PTPDate]
comrst![PTPAmount] = Me![ptpamt]

comrst.Update

If IsNull(Me![ReviewDate]) = False Then
If IsNull(Me![ARAPTPCount]) Then
Me![ARAPTPCount] = 1
Else
Me![ARAPTPCount] = Me![ARAPTPCount] + 1
End If
End If

If Me![PTPDate] >= Date Then
Me![ReviewDate] = Me![PTPDate] + 2
End If
Me!DateWorked = Now

DoCmd.Close

Exit_BtnUpdate1_Click:
Exit Sub

Err_BtnUpdate1_Click:
MsgBox Err.Description
Resume Exit_BtnUpdate1_Click

End Sub
This results in an error whereby erorr trapping cannot determine whether the recordset [Total Expenses] is being calculated. Has this someting to do with the fact that there is a space in the original table of the header colunm (Total Expenses)? If so, how would I overcome this problem. This was set up by a previous Administrator and the colunm contains millions of records. Changing the name is also not an option as other applications use this field as well as stored procedures and various queries.

Any help would be appreciated.

Thanks

 

[PHV]

This results in an error
Which error message ?
Which line of code highlighted when in debug mode ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[9milla]

Okay......after much deliberation on trying to figure out why I wasn't able to calculculate the the [Total Expenses] column, I stumbled accross a forum with regards to follwing 'Rules'/'Standards'

thread669-1355446



In relation to my code snippet below with which I'm struggling with. I've changed the code somewhat from this...

strDbRst = "SELECT * FROM dbo.Debtors WHERE (ClaimNo = " & Me![ClaimNo] & ")"
        DbRst.Open strDbRst, aradb, adOpenDynamic, adLockOptimistic
        DbRst.AddNew
        DbRst![Expenses] = Me.[Expenses]
        DbRst![Total Expenses] = Me.[Total Expenses] + (Me.[Expenses] * 0.14)
        DbRst.Update
        DbRst.Close
        'Me.[Total Expenses] = "(12.60 * 0.14)"

to this...

strDbRst = "SELECT * FROM dbo.Debtors WHERE (ClaimNo = " & Me![ClaimNo] & ")"
        DbRst.Open strDbRst, aradb, adOpenDynamic, adLockOptimistic
        
        DbRst.AddNew
        DbRst![Expenses] = Me.[Expenses]
        DbRst.Resync
        Me![Total Expenses] = 0.01
        DbRst.Update 
     [b]DbRst![Total Expenses] = ((Me![Expenses] + Me![Expenses] * 0.14) + Me![Total Expenses] - 0.01)[/b]
        DbRst.Update
        DbRst.Close

So let me explain this a bit better. The bolded code is the actual calculation that 'should' be done once the recorset is updated. I don't get an error as per say, but for some reason the code doesn't break and just steps through this process. When checking the recordsource, only the parsed value of '0.01' appears in the [Total Expenses] colunm and calculation.

Is this due to there being a 'space' in the 'colunm name'? If so, how would I overcome this problem? As stated in my previous post, I cannot simply change the name of the column within the db.

'This was designed and implemented by the previous Database Administrator'

Here is a list of columns affected by this calculation and their datatypes:


Balance money(8) 'runs fine'

[Total Expenses] money(8)'problematic'

Expenses money(8)'runs fine'

VAT numeric(13)'runs fine'

ExpSub numeric(13)'runs fine as it results in the calculation of ([expenses] + [expenses] * 0.14)'


Once again, 'Many Thanks...!'


"And so it begins!"
LOTR (Battle for Helms Deep)

 

[JerryKlmns]

9milla

just few suggestions

You don't need 3 recordset objects. One is enough.

Use the With ... End With block when refering many times to the same object, like the recordset.

If you are only to add a record use Forward-only cursor as it is faster to open than anyother type.

Do not check the Recordcount property for empty recordset but rather .EOF and .BOF to be TRUE at the same time

What is the reason to do this

strCurDatePart = Right((Format(Now(), "dd MM yyyy")), 7)
If IsDate(rst!Date) Then
   strRstDate = Format(rst!Date, "MM yyyy")
   If (strCurDatePart = strRstDate) Then

If rst!Date & "" <> "" Then
   If CDate(Year(rst!Date),Month(rst!Date),01)= CDate(Year(Date()),Month(Date()),01) Then

I believe 0.14 is the value for the public currency variable for VAT but you simplified that fonly for posting.

BTW
You didn't answer PHV's question which is critical for us to lockate the problem.

 

[Andrzejek]

In your code, you ask for all columns (*) from your table dbo.Debtors, but you use only 2 fields:

strDbRst = "SELECT * FROM dbo.Debtors WHERE (ClaimNo = " & Me![ClaimNo] & ")"
        DbRst.Open strDbRst, aradb, adOpenDynamic, adLockOptimistic
        
        DbRst.AddNew
        [b]DbRst![Expenses][/b] = Me.[Expenses]
        DbRst.Resync
        Me![Total Expenses] = 0.01
        DbRst.Update 
     [b]DbRst![Total Expenses][/b] = ((Me![Expenses] + Me![Expenses] * 0.14) + Me![Total Expenses] - 0.01)
        DbRst.Update
        DbRst.Close

If you have a problem with a space in the column name, try alias for the name:

strDbRst = "SELECT Expenses, [Total Expenses] [blue]As MyTotExp[/blue]  FROM dbo.Debtors WHERE (ClaimNo = " & Me![ClaimNo] & ")"

And use MyTotExp instead - no spaces in the name.

Have fun.

---- Andy

 

[9milla]

Thank you all for your input. It was most welcomed. I apologise for not replying earlier to the responses.

As for the problem I posted, I am pleased to say that I have found a solution.

As for PHV's response, the error message I was getting was a 'Multistep calculation error'
which I narrowed down to the code which was performing
the calculation as well as the columns within the db.

As for JerryKlmns' response, you are correct with regards to the VAT.
The reason for all the date formatting was to determine the exact month and
year for calculating the expenses as a 'debtor' can only be charged once every
month for expenses. However, the 'debtor's account' can be worked more than
once a month and each time it's worked, it gets updated through the use of an
'Update Button' which runs the code and thus determines whether or not to
process the expenses.
Using 'RecordCount' instead of EOF/BOF was just for me to get to better grips
with the logic flow. Hope that answer's your question!

As for Andrzejek's response. Thank you very much as this is what I was
looking for. Although by the time of your posting, I had already come up
with an alternative solution which works 100%.
This will come in handy in future as it will limit tedious lines of coding which I
unfortunately put myself through for this project.

Thanks once again Gentlemen and Ladie's if any. This has been one of my better
experiences using a forum to get solutions. 'Thumbs Up' for Tek-Tips and
it's forum members.

Below is the completed code. 'Constructive Criticism' is most welcomed!
It's a lot I know!
Apologies if the posting is too big

Private Sub BtnUpdate1_Click()
'On Error GoTo Err_BtnUpdate1_Click

    Dim aradb As Connection
    Dim comrst As ADODB.Recordset
    Dim rst As ADODB.Recordset
    Dim DbRst As ADODB.Recordset
    Dim SqlRst As ADODB.Recordset
    Dim rsClient As ADODB.Recordset
    
    Dim strDbRst As String
    Dim StrYear As String
    Dim StrMonth As String
    Dim StrDate As String
    Dim strCurDatePart As String
    Dim strClient As String
    Dim strSQL As String
    Dim strSQLrst As String
    Dim strRstDate As String
    
    Set aradb = CurrentProject.Connection
    Set comrst = New ADODB.Recordset
    Set rst = New ADODB.Recordset
    Set DbRst = New ADODB.Recordset
    Set SqlRst = New ADODB.Recordset
    Set rsClient = New ADODB.Recordset
    
    If IsNull(Me![Comment]) Then
        MsgBox ("Please enter a valid Comment!")
        Me![Comment].SetFocus
        GoTo Exit_BtnUpdate1_Click
    End If
            
    If Me![Comment] = "PTPH" Or Me![Comment] = "PTPC" Or Me![Comment] = "PTPW" Then
        If IsNull(Me![PTP DATE]) Then
            MsgBox ("PTP Date must have a valid date in It. Please Correct!")
            Me![PTPDate].SetFocus
            GoTo Exit_BtnUpdate1_Click
        End If
        
        If IsNull(Me![PTP AMOUNT]) Or Me![PTP AMOUNT] < 1 Then
            Me!ptpamt.SetFocus
            MsgBox ("PTP Amount must have a value. Please Correct! ")
            GoTo Exit_BtnUpdate1_Click
        End If
    End If
    '___________________________________________________________________________________'
    '¯¯¯¯¯¯¯¯¯¯““___________________________________________________________””¯¯¯¯¯¯¯¯¯¯'
                '¯¯¯¯¯»»Compares 'ClientCode' to current account and ««¯¯¯¯¯'
                '_____»»determines whether charges will be calculated««_____'
    '__________””¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯““__________'
    '¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯'
    
    strClient = "SELECT * FROM Clients" & _
                " WHERE Clients.Expenses <> 0 AND ClientCode = " & Me!ClientCode & ""
    
    rsClient.Open strClient, aradb, adOpenDynamic, adLockOptimistic
                        
    If rsClient.RecordCount = 0 Then
        GoTo comrst
    Else
    'Dim c1 As Variant
    If (Comment = "ADM") Or (Comment = "HLC") Or (Comment = "PTPH") Or (Comment = "PTPW") Or 
       (Comment = "QRY") Or (Comment = "RTP") Or (Comment = "PTPC") Or (Comment = "PTPH-NS") Or 
       (Comment = "PTPW-NS") Or (Comment = "PTPC-NS") Or (Comment = "PTP-SUP") Then
        
       If Me.Expen > 603.3 Or Me.Expen = 630# Then
            Me.txtLetterCharge = 0#
            Me.txtCallCharge = 0#
       GoTo comrst
    '___________________________________________________________________________________'
    '¯¯¯¯¯¯¯¯¯¯““___________________________________________________________””¯¯¯¯¯¯¯¯¯¯'
                '¯¯¯¯¯»» Uncomment code segment if charges are to be ««¯¯¯¯¯'
                '_____»»  be calculated to the max limit of 630.00   ««_____'
    '__________””¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯““__________'
    '¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯'
            'ElseIf Me.Expen >= 601.27 Then
                'c1 = 630# - Me.Expen
                'Me.txtLetterCharge = (c1 / 2) / 1.14
                'Me.txtCallCharge = (c1 / 2) / 1.14
            Else:
                Me.txtLetterCharge = 12.6
                Me.txtCallCharge = 12.6
            End If
        End If
    End If
    'End If
    
    '___________________________________________________________________________________'
    '¯¯¯¯¯¯¯¯¯¯““___________________________________________________________””¯¯¯¯¯¯¯¯¯¯'
                '¯¯¯¯¯»» Checks if Date is valid and within the same ««¯¯¯¯¯'
                '_____»» month and adds exp charges to new debtors   ««_____'
    '__________””¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯““__________'
    '¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯'
    
    strSQL = "SELECT ClaimNo, MAX(Date) AS Date, MAX(Comment) AS comment FROM dbo.[Comments Table]
             WHERE (Comment IN (N'qry', N'ptph', N'rtp', N'adm', N'ptpw', N'ptpc', N'ptph-ns',
             N'ptpw-ns', N'ptpc-ns', N'ptp-sup')) GROUP BY ClaimNo 
             HAVING (ClaimNo = " & Me![ClaimNo] & ")"
    
    rst.Open strSQL, aradb, adOpenDynamic, adLockOptimistic

    If (rst.RecordCount <= 0) Then

        strSQLrst = "Select * from [Payments Table] where 1 = 2"
        SqlRst.Open strSQLrst, aradb, adOpenDynamic, adLockOptimistic
        
        SqlRst.AddNew
        SqlRst!ClaimNo = Me.ClaimNo
        SqlRst!ClientCode = Me.ClientCode
        SqlRst!FirstAccNo = Me.FirstAccNo
        SqlRst!PayRefDesc = "EXPSUB LTS"
        SqlRst!Value = Me.txtLetterCharge
        SqlRst!Date = Format(Now(), "dd/mm/yyyy")
        SqlRst!TransactionDate = Format(Now(), "dd/mm/yyyy")
        SqlRst.Update
        
        SqlRst.AddNew
        SqlRst!ClaimNo = Me.ClaimNo
        SqlRst!ClientCode = Me.ClientCode
        SqlRst!FirstAccNo = Me.FirstAccNo
        SqlRst!PayRefDesc = "EXPSUB " & Me.Comment
        SqlRst!Value = Me.txtCallCharge
        SqlRst!Date = Format(Now(), "dd/mm/yyyy")
        SqlRst!TransactionDate = Format(Now(), "dd/mm/yyyy")
        SqlRst.Update
        SqlRst.Close
    '|                _________________________________________________
    '|_______________/Retrieve, calculate and store value in recordset \___________________________
    '                     for new debtor with no previous history                                  \
    '|¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯\_________________________________________________/¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯||
    '|                ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯                           ||
                                                                                                 '||
        strDbRst = "SELECT * FROM dbo.Debtors WHERE (ClaimNo = " & Me![ClaimNo] & ")"            '||
        DbRst.Open strDbRst, aradb, adOpenDynamic, adLockOptimistic                              '||
                                                                                                 '||
        Dim x As Variant                                                                         '||
        Dim y As Variant                                                                         '||
        Dim TempVar As Variant                                                                   '||
        Dim MyVar As Variant                                                                     '||
        Dim ExpVar As Variant                                                                    '||
        Dim UpdateBalVar As Variant                                                              '||
                                                                                                 '||
            x = Me![txtLetterCharge]                                                             '||
            y = Me![txtCallCharge]                                                               '||
                                                                                                 '||
            TempVar = x + y                                                                      '||
                                                                                                 '||
        DbRst.AddNew                                                                             '||
        DbRst![Expenses] = TempVar                                                               '||
        DbRst.Resync adAffectCurrent                                                             '||
                                                                                                 '||
            ExpVar = DbRst![ExpSub]                                                              '||
            MyVar = Me.[Total Expenses]                                                          '||
            MyVar = MyVar + ExpVar                                                               '||
            Me.[Total Expenses] = MyVar                                                          '||
                                                                                                 '||
            Me.Bal = Me.Bal + DbRst![ExpSub]                                                     '||
                                                                                                 '||
        DbRst.Update '  __________________________________________________________________________||
        DbRst.Close '__/¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
        
    Else:

    strCurDatePart = Right((Format(Now(), "dd MM yyyy")), 7)
        
        If IsDate(rst!Date) Then
            strRstDate = Format(rst!Date, "MM yyyy")
            If (strCurDatePart = strRstDate) Then
                'MsgBox "The debtor has already been charged expenses for the current month", 
		vbInformation + vbExclamation + vbOKOnly
                Me.txtLetterCharge = ""
                Me.txtCallCharge = ""                  '                             ______________
            ElseIf (strCurDatePart <> strRstDate) Then '\_Check if account is closed/¯¯¯¯¯¯¯¯¯¯¯¯¯||
                                                       ' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯              ||
                If Me.CLOSED_FLAG = "C" Then '____________________________________________________||
                    GoTo comrst '__/¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

                Else
                strSQLrst = "Select * from [Payments Table] where 1 = 2"
                SqlRst.Open strSQLrst, aradb, adOpenDynamic, adLockOptimistic
                   
                SqlRst.AddNew
                SqlRst!ClaimNo = Me.ClaimNo
                SqlRst!ClientCode = Me.ClientCode
                SqlRst!FirstAccNo = Me.FirstAccNo
                SqlRst!PayRefDesc = "EXPSUB LTS"
                SqlRst!Value = Me.txtLetterCharge
                SqlRst!Date = Format(Now(), "dd/mm/yyyy")
                SqlRst!TransactionDate = Format(Now(), "dd/mm/yyyy")
                SqlRst.Update
                   
                SqlRst.AddNew
                SqlRst!ClaimNo = Me.ClaimNo
                SqlRst!ClientCode = Me.ClientCode
                SqlRst!FirstAccNo = Me.FirstAccNo
                SqlRst!PayRefDesc = "EXPSUB " & Me.Comment
                SqlRst!Value = Me.txtCallCharge
                SqlRst!Date = Format(Now(), "dd/mm/yyyy")
                SqlRst!TransactionDate = Format(Now(), "dd/mm/yyyy")
                SqlRst.Update
                SqlRst.Close
                        '|    _________________________________________________
                        '|___/Retrieve, calculate and store value in recordset \___________________
                        '         for existing debtor with previous history                        \
                        '|¯¯¯\_________________________________________________/¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯||
                        '|    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯                   ||
                                                                                                 '||
                strDbRst = "SELECT * FROM dbo.Debtors WHERE (ClaimNo = " & Me![ClaimNo] & ")"    '||
                DbRst.Open strDbRst, aradb, adOpenDynamic, adLockOptimistic                      '||
                                                                                                 '||
                Dim Avar As Variant                                                              '||
                Dim Bvar As Variant                                                              '||
                Dim Var As Variant                                                               '||
                                                                                                 '||
                    Avar = Me![txtLetterCharge]                                                  '||
                    Bvar = Me![txtCallCharge]                                                    '||
                                                                                                 '||
                    Var = Avar + Bvar                                                            '||
                                                                                                 '||
                DbRst![Expenses] = Var                                                           '||
                DbRst.Resync adAffectCurrent                                                     '||
                                                                                                 '||
                Dim A As Variant                                                                 '||
                Dim B As Variant                                                                 '||
                                                                                                 '||
                    B = DbRst![ExpSub]                                                           '||
                    A = Me.[Total Expenses]                                                      '||
                    A = A + B                                                                    '||
                    Me.[Total Expenses] = A                                                      '||
                                                                                                 '||
                DbRst!Balance = Me.Balance + B                                                   '||
                DbRst.Update                                                                     '||
                DbRst.Close                                                                      '||
            End If                                                                               '||
            End If                                                                               '||
        End If ' _________________________________________________________________________________||
      End If '__/¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
   
   rsClient.Update
   rsClient.Close

    '|                _________________________________________________
    '|_______________/              Update Comment's Table             \__________________
    '|¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯\_________________________________________________/¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯||
    '|                ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯                  ||
                                                                                        '||
comrst:                                                                                 '||
    comrst.Open "Select * from [Comments Table] where 1 = 2", aradb, adOpenDynamic,     '||
		adLockOptimistic 							                         '||
                                                                                        '||
    comrst.AddNew                                                                       '||
        comrst![ClaimNo] = Me![ClaimNo]                                                 '||
        comrst![Date] = Date                                                            '||
        comrst![RefNo] = Me![CollectorNo]                                               '||
        comrst![Comment] = Me![Comment]                                                 '||
        comrst![Remark1] = Me![Remarks]                                                 '||
        comrst![PTPDate] = Me![PTPDate]                                                 '||
        comrst![PTPAmount] = Me![ptpamt]                                                '||
                  '   ___________________________________________________________________||
    comrst.Update '__/¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
      
    If IsNull(Me![ReviewDate]) = False Then
        If IsNull(Me![ARAPTPCount]) Then
            Me![ARAPTPCount] = 1
        Else
            Me![ARAPTPCount] = Me![ARAPTPCount] + 1
        End If
    End If
    
    If Me![PTPDate] >= Date Then
        Me![ReviewDate] = Me![PTPDate] + 2
    End If
    Me!DateWorked = Now
    
    DoCmd.Close
    
Exit_BtnUpdate1_Click:
    Exit Sub

Err_BtnUpdate1_Click:
    MsgBox Err.Description
    Resume Exit_BtnUpdate1_Click
    
End Sub

"And so it begins!"
LOTR (Battle for Helms Deep)

 

[9milla]

Ps. To get a better viewing of the code, try setting 'Zoom Options' to 90%.

Doesn't seem to be Horizontal scrolling

Cheers!

"And so it begins!"
LOTR (Battle for Helms Deep)

 

[JerryKlmns]

'Constructive Criticism' is most welcomed!

My 1st, 2nd, 3rd & 5th are still on!

Thnx for the stars.