Check reconciliation macro 3

[JoeAtWork]

Sage Accpac 500 ERP (Version 5.3B)

I'm a programmer who occasionally does work for an AccPac consultant. Usually he provides me with a recorded macro to use as a template, but I don't have one this time.

Basically, I need to reconcile checks that have been cleared by the bank. He told me I need to update certain fields in the BKCHK table. I find when I try to do so I get errors saying this fields are read-only.

This is my essential code:

    Dim dbCompany As AccpacCOMAPI.AccpacDBLink
    Set dbCompany = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
    
    Dim dbSystem As AccpacCOMAPI.AccpacDBLink
    Set dbSystem = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)

    'Open the AccPac view to the Checks table
    Dim vwChecks As AccpacCOMAPI.AccpacView
    dbCompany.OpenView "BK0004", vwChecks

    '...... Skip code where data is read from bank file....

    'Look for a matching check number in the AccPac View
    vwChecks.Init
    vwChecks.Browse "BANK=""" & strBankCode & """ AND CHECK = """ & _
         strCheckNum & """", True

    'Update the Reconciliation Status, Date, etc.
    vwChecks.Fields("RECCLEARED").Value = curChequeAmount
    vwChecks.Fields("RECDATE").Value = datDatePaid
    vwChecks.Fields("RECSTATUS").Value = 5
    vwChecks.Fields("RECSTATCHG").Value = datDatePaid
    vwChecks.Fields("RECYEAR").Value = strYear
    vwChecks.Fields("RECPERIOD").Value = intPeriod
    vwChecks.Fields("POSTDATE").Value = datDatePaid

    vwChecks.Update

I assume what I am actually supposed to do is update some other set of fields, which will automatically change the "read-only" ones, but I don't know which ones I should.

Can anyone provide an example?

 

[ettienne]

That is not a simple task, the bank reconciliation does not record the required views, there are other views you need to use. You need to look at RVSpy logs to determine what needs to be done and I think trying to explain how to read RVSpy is beyond the scope of a forum like this.

 

[Jay Converse]

You need:

Dim vwBank As AccpacCOMAPI.AccpacView
dbCompany.OpenView "BK0004", vwBank


vwBank.Compose Array(Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)
vwChecks.Compose Array(vwBank, Nothing, Nothing, Nothing, Nothing)
vwCheck.SystemAccess = VIEW_SYSACCS_IMPORT
vwBank.SystemAccess = VIEW_SYSACCS_IMPORT

 

[JoeAtWork]

tuba2007:

I think you meant:

dbCompany.OpenView "BK0001", vwBank

If that's not the right number, please confirm what I should put in there.

I now can assign values to the fields, but I get two errors when I attempt to do the "vwChecks.Update", as below:

Error #1
Check Stock.

Attempt to modify a different record than was retrieved.

Error #2
Incorrect procedure: Bank Check. Bank Code has a different value than the header does.

I am putting these values in:
vwChecks.Fields("RECCLEARED").Value = 2687.36
vwChecks.Fields("RECDATE").Value = cdate("June 30, 2010")
vwChecks.Fields("RECSTATUS").Value = 5
vwChecks.Fields("RECSTATCHG").Value = cdate("June 30, 2010")

vwChecks.Fields("RECYEAR").Value = "2010"
vwChecks.Fields("RECPERIOD").Value = 6
vwChecks.Fields("POSTDATE").Value = cdate("June 30, 2010")


Here is my current code:

    Dim dbCompany As AccpacCOMAPI.AccpacDBLink
    Set dbCompany = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
    
    Dim dbSystem As AccpacCOMAPI.AccpacDBLink
    Set dbSystem = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)

    'Open the AccPac view to the Checks table
    Dim vwChecks As AccpacCOMAPI.AccpacView
    Dim vwBank As AccpacCOMAPI.AccpacView

    dbCompany.OpenView "BK0004", vwChecks
    dbCompany.OpenView "BK0001", vwBank

    vwBank.Compose Array(Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)
    vwChecks.Compose Array(vwBank, Nothing, Nothing, Nothing, Nothing)

    vwChecks.SystemAccess = VIEW_SYSACCS_IMPORT
    vwBank.SystemAccess = VIEW_SYSACCS_IMPORT

    '...... Skip code where data is read from bank file....

'        'Look for a matching check number in the AccPac View
        vwChecks.Init
        vwChecks.Order = 0
        vwChecks.Browse "BANK=""" & strBankCode & """ AND CHECK = """ & _
         strCheckNum & """", True

        '-- DEBUG - try to fix the "Bank Code has a different value than the header does" error
        vwBank.Init
        vwBank.Order = 0
        vwBank.Browse "BANK=""" & strBankCode & """", True

        If vwChecks.Fetch Then
            'Cheque found in AccPac
            If IsNumeric(vwChecks.Fields("SISSUED").Value) Then
                curIssued = vwChecks.Fields("SISSUED").Value
            End If
            
            'Now see if the amounts match, and that it is
            'currently outstanding
            If vwChecks.Fields("RECSTATUS").Value = 3 And curIssued = curChequeAmount Then
                'Attempt to change record
                
                vwChecks.Fields("RECCLEARED").Value = curChequeAmount
                vwChecks.Fields("RECSTATUS").Value = 5
                vwChecks.Fields("RECSTATCHG").Value = datDatePaid
                vwChecks.Fields("RECYEAR").Value = strYear
                vwChecks.Fields("RECPERIOD").Value = intPeriod
                vwChecks.Fields("POSTED").Value = datDatePaid
                vwChecks.Fields("RECDATE").Value = datDatePaid
                
                vwChecks.Update
                
                importStatus = "CLEARED"
            ElseIf vwChecks.Fields("RECSTATUS").Value <> 3 And curIssued = curChequeAmount Then
                importStatus = "CHEQUE STATUS NOT OUTSTANDING"
            Else
                importStatus = "WITHOUT MATCH"
            End If
        Else
            importStatus = "WITHOUT MATCH"
        End If

 

[Jay Converse]

Here's a snip from a macro I've had running for years:

With BKCheck
.Cancel
.Fields("BANK") = frmPickBank.Bankpick
.Fields("CHECK") = BKDetail.sCheckNum
.Init
.Cancel
.Browse sBrowse, 1
If .Fetch Then
.Read
If .Fields("STATUS") = 3 And .Fields("RECSTATUS") = 3 Then ' Only outstanding checks
.Fields("RECSTATUS") = "5"
.Fields("RECCLEARED") = BKDetail.nCrAmount
.Fields("RECSTATCHG") = BKDetail.dRecDate
.Fields("RECDATE").PutWithoutVerification (Me.dtRecDate)
.Fields("RECCOMMENT") = sInputName
.Update
.Post
BKBank.Update
BKBank.Post
iCheckCount = iCheckCount + 1
lCheckSum = lCheckSum + BKDetail.nCrAmount
If .Fields("ISSUED") <> BKDetail.nCrAmount Then
LogWrite "Check: " & BKDetail.sCheckNum & " cleared with bank error, amount: " & Format(Val(BKDetail.nCrAmount), "###,###,##0.00") & ", original amount was " & Format(Val(.Fields("ISSUED")), "###,###,##0.00"), False
Else
LogWrite "Check: " & BKDetail.sCheckNum & " cleared, amount: " & Format(Val(BKDetail.nCrAmount), "###,###,##0.00"), False
End If
Else
LogWrite "Check: " & BKDetail.sCheckNum & " not cleared, status: " & Presentation_String(BKCheck.Fields("RECSTATUS")), False
End If
Else
LogWrite "Check: " & BKDetail.sCheckNum & " not found.", False
End If
End With

 

[DjangMan]

I just stumbed on AR0072 the other day. It is easy to work with - use rvSPy to record it's processes. So far I've only used it to reverse cleared receipts but that's working great.

 

[JoeAtWork]

Tuba2007 - you came through for me!

After matching your code, I was still getting the error "Incorrect procedure: Bank Check. Bank Code has a different value than the header does.".

However, I figured out how to fix this with the following additional code:

            vwBank.Cancel
            vwBank.Fields("BANK") = strBankCode
            vwBank.Init
            vwBank.Cancel
            vwBank.Browse "BANK=""" & strBankCode & """", 1
            If vwBank.Fetch Then
                vwBank.Read
            End If