Set rst = CurrentDb.OpenRecordset("Select * From T_SalesInvoice where SalesInvNum=" & rst1!SalesInvNum)
I have a main form with 2 sub forms in it for the sales payment. These 2 sub forms are based on query that based on table and connected with Link Master / Child field on CustomerCode and SalesInvNum etc.
The sub form SF_SalesPmtFoot based on T_SalesPayment table has following field:
The sub form SF2_SalesPayment based on T_SalesInvoice has following field. Here I am generating records thru other procedures.
Sub form SF_SalesPmtFoot displays only those invoices whose balance are greater than Zero.
When customer pays some amounts, user makes entry in SF_SalesPmtFoot and I wrote the below codes after update of field PAmount. My idea is to sum the amount paid and place the figure into field PAmount of sub form SF_SalesPmtFoot .
Example : If a customer has due invoice # 3011 which has amount of 20,000. The customer will pay partial amount at different dates. Let us say first time he paid 1200 and at next date he paid 500.
What I tried to do is once customer paid 1200 it should be deducted from the actual invoice value and the paid amount should be entered in PAmount field of T_SalesPayment.
When again second time customer pays 500, it should sum previous 1200 and currently entered 500 and deduct from 20,000 and enter 1700 into PAmount field.
My code doesn’t take action at first entry but works fine from second entry. I mean when use makes first entry…nothing happens but when makes second entry then it calculates sum of first and second entery which means sum 1700 and shows correct result.
My rst1 show nothing after entering first record. When enter second record, rst1 shows as 1 record in table. I don’t understand why the field PaidAmt doestnt show the value.
I want this to be also at first entery. I placed code here…I hope some one can help me among you gents.
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Set rst1 = CurrentDb.OpenRecordset("Select SalesInvNum, Sum(PAmount) As PaidAmt from T_SalesPayment Where SalesInvNum=" & (SalesInvNum) & " Group by SalesInvNum")
If rst1.RecordCount >= 1 Then
PaidAmt = rst1!PaidAmt
further code lines......
I think you're going about this the wrong way. You don't need to record running sums in table data imo; you just record the invoice data and you record the payment data. Whenever you want to see a balance, you can sum the payments and subtract from the invoice's sum. You can also use running sums by using the Running Sum property in reports.
Other than that, I'd say your code isn't being executed because it's inside an IF test to see if there's any payment data to work with. Since the first time you do it, there isn't any payment data, the code doesn't execute.
Get rid of the if test and just use something like PaidAmt = Nz(rst1!PaidAmt,0) in subsequent lines.