Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: Urgent Code Help Pls....

    Hi,

    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:

    SalesInvNum
    Amount
    PAmount
    Balance

    The sub form SF2_SalesPayment based on T_SalesInvoice has following field. Here I am generating records thru other procedures.

    SalesInvNum
    ReceiptNum
    PDate
    PAmount

    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
    Dim PaidAmt
    Dim X

    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......
    ................
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    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.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks.

    This is ok. But why then it works after second entry onwards with no problem. I just want to know why not working with first entry.

    The sub form SF2_SalesPayment I even conected to table directly and tried with keeping query as record source too.
    With kind regards,
    Ashfaque

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    But why then it works after second entry onwards with no problem. I just want to know why not working with first entry.
    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.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •