Recordset No current record

[Gina Maylone]

If you've been playing along, I am trying to use a recordset with a loop to post payments to the oldest assessments (invoices) first. I did not start out this way, but have managed to cobble together something that

nearly

works. I am getting a "No current record" error. (My monitor is on the fritz, so I have to do without colors right now). I'm also attaching a copy of the db.



The process is, you enter a payment,date paid and check number on the form AccountsandPayments...in the unbound text boxes. And click PROCESS. It should go to the oldest assessment first and enter the cramount, calculate the balance left and the balance due and move to the next assessment and do the same for as long as there is money left. The next time a member makes a payment, the oldest balance due should be paid off, rinse and repeat. If there is money left over, a new record will get created with that amount in the cramount field. In addition I will be adding the payment to the AsmtPayments table (not currently in this module).

I am not adept at recordsets and am unsure of where to use ".movenext". TIA!

Private Sub Command57_Click()
    Dim dbs As Database
    Dim rst As Recordset
    Dim strSQL As String
    Dim BD As Variant
    Dim BL As Variant
    Dim CR As Variant
    Dim db As Variant
    Dim tb As Variant
    Dim P As Variant
    Dim mbrid As Integer
    Dim dp As Date
    Dim RC As Long, i As Long

    dp = DatePaid
    mbrid = Me.MemberID
    BD = Me.baldue
    BL = Me.balleft
    CR = Me.CRAmount
    db = Me.DBAmount
    tb = Me.totalbal
    P = Me.payment


strSQL = "SELECT Accounts.MemberID, Accounts.DBAmount, Accounts.CRAmount, Accounts.DateAssessed, Accounts.AsmtType, Accounts.Details, Accounts.LotNumber, Accounts.EnteredBy, Accounts.DatePaid, Accounts.CheckNumber, Accounts.Notes, [dbamount]-[cramount] AS baldue " & vbCrLf & _
"FROM Accounts " & vbCrLf & _
"WHERE (((Accounts.MemberID)=" & [Forms]![AccountsandPayments]![MemberID] & ")) "

Debug.Print strSQL

Set rst = CurrentDb.OpenRecordset(strSQL)
Do While (BL > BD)
            i = i + 1

            If BL > BD Then   'edit the record set - add a payment

                rst.Edit ************HERE IS WHERE IT ERRORS*************************
                rst!DatePaid = Forms![AccountsandPayments]![Accounts].Form![DatePaid]
                rst!CRAmount = BD
                rst.Update

                'calc how much is left to pay
                BL = BL - CRAmount
                Debug.Print "BL " & BL

                If BL < BD And BL > 0 Then
                    'post remaining to next asmt.
                    rst.MoveNext
                    rst.Edit
                    rst!CRAmount = rst!CRAmount + BL
                    Debug.Print rst!CRAmount + BL
                    rst!DatePaid = Forms![AccountsandPayments]![Accounts].Form![DatePaid]
                    rst.Update
                    BL = BL - CRAmount
                    Debug.Print BL

                End If
            End If

            rst.MoveNext
            If RC = i And (BL > 0) Then
                'save remaining amt to additional principal in last records of record set
                rst.Edit
                rst!CRAmount = BD
                rst.Update
                BL = 0
            End If

        Loop


    Me.Requery
    '        addtopaymenttable

    rst.Close
    Set rst = Nothing
MsgBox "End of process"
End Sub

NEWHAHDBforum.zip

[orange]

I'm not certain what you are trying to do. I see a lot of comments within the code that suggests that this is more a trial and error approach than programming from some tested logic/flow diagram.
You are adding values to tbl AsmtPayments, but no other activity( balance adjustment etc) that I can see.

Have you worked out on paper the logic to apply a payment against a Balance?
This may not be what you wanted to hear, but I think you have to clarify what the business process is in order to design an test a program/logic to do it with a database.

I would also suggest you use more meaningful names for your controls (eg Command52) and variables (BD, BL). Also DoCmd.RunSql is an older construct --you should review currentdb.execute using the dbFailOnError parameter. Is there a reason you Dim all variables a Variants?


Another debugging approach is to use Debug.print (or msgbox) to ensure parts of your code are doing what you think.
You can always comment out such lines later.

Good luck

[Gicu]

In the db you uploaded the subform's source object is "copy of account2" which has totally different code than the one you posted. You have to test to see if there are any records first by checking the recordcount property of the recordset. If none you would use a rst.AddNew instead of rst.edit. Similarly, you mentioned you want to add a record if there is balance left is > 0 at the end and yet you use rst.edit instead of rst.addnew.

Cheers,
Vlad

[ssanfu]

Hi Gina,

I've made many changes to your dB.
Look at the relationship window. I renamed the PK/FK fields, changed the compound PKs to compound indexes, changed a couple of relationships and modified some code.
I added a "CheckNumber" field to the table "AsmtPayments".
I added a new button to initiate the Pmt Processing on the form "AssementsAndPayments"...

You should look through all Integer/Long Integer fields and remove the default value of 0 (zero).

The code handles payments for amounts less than or equal to the bal due. I am not sure how to handle payments that are more than the amount due.

I see that member Baker has a $100 amount (as a credit?) but no entries in the CRamount field, so the bal due is $394.07. Should the credit record be added to a payment, then applied to the oldest debit records until all are paid, then the excess (if any) is put back into the credit record?

[Gina Maylone]

Hi Gina,

I've made many changes to your dB. Am always eternally grateful.
Look at the relationship window. I renamed the PK/FK fields, changed the compound PKs to compound indexes, changed a couple of relationships and modified some code.Why did you rename the PK/FK fields? And what do you mean by changing them to compound indexes? What is the benefit?
I added a "CheckNumber" field to the table "AsmtPayments". I thought I had check num in the table already.
I added a new button to initiate the Pmt Processing on the form "AssementsAndPayments".I am going to have the event fire after the check number is entered.

You should look through all Integer/Long Integer fields and remove the default value of 0 (zero). OK...why? (worse than a little kid huh?)

The code handles payments for amounts less than or equal to the bal due. I am not sure how to handle payments that are more than the amount due Initially I had it adding a new record with a credit amount. But I'm rethinking that.

I see that member Baker has a $100 amount (as a credit?) but no entries in the CRamount field, so the bal due is $394.07. Should the credit record be added to a payment, then applied to the oldest debit records until all are paid, then the excess (if any) is put back into the credit record? See above.

It works slick Steve (of course) I'm comparing it to what I had so I can see where I was going wrong. The older I get, the less I know. THANK YOU!

[Gina Maylone]

Thanks Vlad!!!

[Gina Maylone]

I'm not certain what you are trying to do. I see a lot of comments within the code that suggests that this is more a trial and error approach than programming from some tested logic/flow diagram.

I thought I was pretty specific on what I was trying to do "The process is, you enter a payment,date paid and check number on the form AccountsandPayments...in the unbound text boxes. And click PROCESS. It should go to the oldest assessment first and enter the cramount, calculate the balance left and the balance due and move to the next assessment and do the same for as long as there is money left. The next time a member makes a payment, the oldest balance due should be paid off, rinse and repeat. If there is money left over, a new record will get created with that amount in the cramount field. In addition I will be adding the payment to the AsmtPayments table (not currently in this module). If there is a better way I should explain things, please let me know.

You are adding values to tbl AsmtPayments, but no other activity( balance adjustment etc) that I can see. Actually the code I posted last was not adding anything to the AsmtPayments table just to the Accounts table.

Have you worked out on paper the logic to apply a payment against a Balance? Yes, I have a diagram of the process drawn out, I just have a short circuit between my paper and my keyboard sometimes.

This may not be what you wanted to hear, but I think you have to clarify what the business process is in order to design an test a program/logic to do it with a database I thought it was pretty logical to want to apply payments to the oldest invoices first. Seems like SOP to me.

I would also suggest you use more meaningful names for your controls (eg Command52) and variables (BD, BL). Also DoCmd.RunSql is an older construct --you should review currentdb.execute using the dbFailOnError parameter.Is there a reason you Dim all variables a Variants? this was some advice I had received earlier, to use instead of currency.


Another debugging approach is to use Debug.print (or msgbox) to ensure parts of your code are doing what you think.
You can always comment out such lines later. I have 5 "debug.prints" in the code I posted. Prior to that I had one on nearly every line. I knew where things were failing, I just couldn't figure out why or how to fix it. That's why I posted here.

Good luck

I thank you for taking the time to look at this and for your advice.

[orange]

Gina,

You are adding values to tbl AsmtPayments, but no other activity( balance adjustment etc) that I can see. Actually the code I posted last was not adding anything to the AsmtPayments table just to the Accounts table.

This is the code in the click event of command52 (Process pmt.) on form AccountsAndPayments in the database:

'Add to payments
             DoCmd.RunSQL "INSERT INTO AsmtPayments ( PaymentAmount, MemberID, PaymentDate ) " & vbCrLf & _
            "SELECT [payment] AS Expr1, Accounts.MemberID, [Forms]![AccountsandPayments]![Accountssubform].[Form]![datepaid] AS Expr2 " & vbCrLf & _
            "FROM Accounts " & vbCrLf & _
            "WHERE (((Accounts.MemberID)=[Forms]![AccountsandPayments]![MemberID]));

"

Here is the updated table showing a $100.00 I just added as test with date 4Apr2018

MemberID	PaymentID	PaymentAmount	PaymentDate
1	7	$94.07	03-Apr-18
1	4	$94.07	05-Apr-18
1	8	$100.00	04-Apr-18
1	1	$400.00	01-Apr-18
		$688.14

Here is tbl Accounts (I don't see the $100.00 RECORD HERE)???

AccountID	MemberID	DBAmount	CRAmount	DateAssessed	Type	Details	LotNumber	EnteredBy	DatePaid	CheckNumber	Note
12384	1	$164.69	$164.69	01-Jan-18	Monthly		0373				Monthly Jan 18 Lot-0373
12385	6	$164.69	$0.00	01-Jan-18	Monthly		0206	Auto			Monthly Jan 18 Lot-0206
12386	9	$164.69	$0.00	01-Jan-18	Monthly		0342	Auto			Monthly Jan 18 Lot-0342
12389	11	$164.69	$0.00	01-Jan-18	Monthly		0262	Auto			Monthly Jan 18 Lot-0262
13249	1	$164.69	$164.69	18-Feb-18	Monthly		0373				Monthly Feb 18 Lot-0373
13250	6	$164.69	$0.00	18-Feb-18	Monthly		0206	Auto			Monthly Feb 18 Lot-0206
13251	9	$164.69	$0.00	18-Feb-18	Monthly		0342	Auto			Monthly Feb 18 Lot-0342
13254	11	$164.69	$0.00	18-Feb-18	Monthly		0262	Auto			Monthly Feb 18 Lot-0262
14114	1	$164.69	$70.62	18-Mar-18	Monthly		0373				Monthly Mar 18 Lot-0373
14115	6	$164.69	$0.00	18-Mar-18	Monthly		0206	Auto			Monthly Mar 18 Lot-0206
14116	9	$164.69	$0.00	18-Mar-18	Monthly		0342	Auto			Monthly Mar 18 Lot-0342
14119	11	$164.69	$0.00	18-Mar-18	Monthly		0262	Auto			Monthly Mar 18 Lot-0262
		$1,976.28	$400.00