[QUOTE=ssanfu;393354]Note: "
State", "
Description" and "
Type" are all
RESERVED words
in Access and shouldn't be used for object names.
Thanks, I'll change them.
I commented out the lines
Code:
DoCmd.SetWarnings False
Code:
DoCmd.SetWarnings True
Then single stepped through the code. The "DoCmd.RunSQL "INSERT INTO....." line errored out. It did not append a record to table "AsmtPayments".
I rewrote it using the Single-record append query version:
Code:
P = Me.payment
dtPD = Me.DatePaid
Code:
'<<-- I added this variable
MbrID = Me.MemberID
'<<-- I added this variable
'Add to payments
strsql = "
INSERT INTO AsmtPayments (PaymentAmount, MemberID, PaymentDate )
"
strsql = strsql & "
VALUES (" & P & ", " & MbrID & ", #" & dtPD & "#)
;"
' Debug.Print strsql
CurrentDb.Execute strsql, dbFailOnError
' <snip>'
The record was added to the table "AsmtPayments".
But, nowhere in the loop code do I see anyway to add a record for a payment or changes the balance...??
Would you explain what is supposed to happen?
Code:
Option Compare DatabaseOption ExplicitPrivate Sub Command52_Click() DoCmd.SetWarnings False 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 expr2 As Variant BD = Me.baldue BL = Me.balleft CR = Me.CRAmount db = Me.DBAmount tb = Me.totalbal P = Me.payment 'Add to payments DoCmd.RunSQL "INSERTINTO AsmtPayments ( PaymentAmount, MemberID, PaymentDate ) " & vbCrLf& _ "SELECT[payment] AS Expr1, Accounts.MemberID,[Forms]![AccountsandPayments]![Accountssubform].[Form]![datepaid] AS Expr2" & vbCrLf & _ "FROMAccounts " & vbCrLf & _ "WHERE(((Accounts.MemberID)=[Forms]![AccountsandPayments]![MemberID]));" DoCmd.SetWarnings True Requery‘*****Go to the account subform and go to the first record (which willbe the oldest assessment) Forms![accountsandpayments]![Accountssubform].SetFocus Forms![accountsandpayments]![Accountssubform].Form![DateAssessed].SetFocus Do While BL > 0 DoCmd.GoToRecord , ,acFirst
'**********EXACTPAYMENT**** While BL >= tb And tb> 0 and CR=0 (just added this final parameter) If CR <> dbThen CR = CR + BL End If BL = 0 DoCmd.GoToRecord , ,acNext Wend If BL = 0 Then Exit Sub End If '**********FIRSTLOOP************‘*****there is money to pay assessment(s) and there is a balance due. While BL >= BD And BD> 0 Debug.Print"meets first loop criteria" Debug.Print BL Debug.Print BD Debug.Print CR Debug.Print db Debug.Print BDCR = CR + BD ‘ (note, these lines were missing, don’tknow where they went) BL = BL - BD DoCmd.GoToRecord , ,acNext Wend '******************* '**********SECOND LOOP*** ‘***WHOOPS, thisjust duplicates the first loop. My bad While BL > 0 And BL>= BD And BD > 0 Debug.Print"meets the second loop criteria" CR = CR + BD BL = BL - BD 'DatePaid1 = DatePaid 'PostedBy =[Forms]![AccountsandPayments]!user DoCmd.GoToRecord , ,acNext Wend '************************ '***********THIRD LOOP*** While BL > 0 And BD> 0 And CR < db and CR>0 ‘I was missing the final parameter again‘***This looks for a previous partial pay of an assessment *** Debug.Print"meets the third loop criteria" Debug.Print CR <db CR = CR + BL BL = BL - CR DoCmd.GoToRecord , , acNext Wend '************************ '*****Overpayment createsa new credit record not related to a debit. ' If BL > 0 And totalbal = 0 Then ' ' DoCmd.GoToRecord , , acNewRec ' DatePaid1 = DatePaid ' PostedBy =[Forms]![AccountsandPayments]!user ' CR = BL ' BL = BL - CR ' BL = 0 ' IfNote = "" Then ''add a note to a new record only ' Note = "Over Pmt" & "" & DatePaid1 & " " & CheckNum ' DoCmd.GoToRecord , , acNext ' Wend Exit Do LoopEnd Sub
A record can be added on the "members" form in the accounts sub form, a record can be added in the "AccountsandPayments" form and one or more records could be created using the "Process Pmt." button.
This "CR = CR + BL" sets the credit amount which updates the baldue (calculated control on subform). In the subform "Baldue=(Nz([dbamount],0)-Nz([cramount],0)"
I re-read your posts and I am unsure what you are trying to do. I don't see how a payment is being split to pay the oldest charge first.
And I am confused as to why you have 4 tables for charges and payments.
sure
Not sure what 4 tables you're referring to. Accounts covers the assessments and payments. AsmtPayments is a permanent record of all payments, for accounting purposes. I use the AccountsQuery to sort by oldest to newest date. Please clarify and I will explain.
--------------------------------------------------------------------------
I would also advise against displaying autonumber fields on a form/report.
Yes, the membered is an autonumber, no good reason to have it be something else. So it's necessary to have it be visible.
Windows 7 32bit 
Access 2010 32bit
Reply With Quote
Originally Posted by Gina Maylone
