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

    Unanswered: Updation Code help Again

    Hi,

    I have 2 table mentioned below along with their fields.

    T_PurInvoice

    InvNum (Num)
    InvDate (Date)
    Amount (Num)
    PAmount (Num)
    Balance (Num)
    SuppCode (Num)
    CrDr (Text) – storing Debit or Credit texts

    T_PurPayment

    SuppCode (Num)
    VoucherNum (Num)
    PDate (Date)
    Amount (Num)

    These 2 tables holds few records which I displayed as sample.

    T_PurInvoice

    InvNum InvDate Amount PAmount Balance SuppCode CrDr
    1 19/04/2007 737.50 0.00 737.50 1 Credit
    2 31/12/2007 117.00 0.00 117.00 1 Credit
    3 31/12/2007 220.00 0.00 220.00 2 Credit
    4 03/01/2008 550.00 0.00 550.00 1 Cash

    Table T_PurInvoice gets update thru a Save command button on purchase invoice form called “T_PurInvHead” that sets Amount = 737.50, PAmount=0 and Balance = 737.50 while saving it first time which is normal.

    T_PurPayment

    SuppCode VoucherNum PDate Amount
    1 2501 25/05/2007 172.00
    1 2502 29/06/2007 390.00
    3 2518 05/01/2008 150.00
    1 3140 05/01/2008 600.00

    At payment form, I only displayed all Credit invoices for respective supplier and stored paid amount in T_PurPayment. There is no question for Cash invoices

    Behind one command button, I wrote some code that updates record T_PurInvoice once the data is updated in T_PurPayment. Let us take a practical example.

    T_PurInvoice

    InvNum InvDate Amount PAmount Balance SuppCode CrDr
    1 19/04/2007 737.50 737.50 0.00 1 Credit
    2 31/12/2007 117.00 117.00 0.00 1 Credit
    3 31/12/2007 220.00 0.00 220.00 2 Credit
    4 03/01/2008 550.00 307.50 242.50 1 Credit


    Here, the total Credit invoices for supplier 1 is 1404.50 where as we paid him sum of 1162 at different times. Whenever we make payment, the PAmount field updates figure along with Balance field in table T_PurInvoice.

    My question:

    Due to some reason sooner or later, if the Amount of invoice number 1 has altered / changes at purchase invoice form “T_PurInvHead” and let us say it becomes now 700.00 instead of 737.50 for which my below query should respond in following manner and update the below table table. (alteration may increase or decrease invoice value)

    . T_PurInvoice

    InvNum InvDate Amount PAmount Balance SuppCode CrDr
    1 19/04/2007 700.00 700.00 0.00 1 Credit
    2 31/12/2007 117.00 117.00 0.00 1 Credit
    3 31/12/2007 220.00 0.00 220.00 2 Credit
    4 03/01/2008 550.00 345.00 205.00 1 Credit

    To do this what I am doing is as follows but it is mess up.

    Private Sub CmdPRetUpdate_Click()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Dim OldPay: OldPay = 0
    Dim OldInv: OldInv = 0
    Dim TotPaid: TotPaid = 0

    Set rst = CurrentDb.OpenRecordset("Select * From T_PurInvoice where Suppcode= " & (SuppCode)) - I believe here criteria should be for both Credit Invoices & SuppCode as well..How ?
    (summing total payment for that suppliers.)

    TotPaid = Nz(DSum("Pamount", "T_Purpayment", "SuppCode= " & SuppCode), 0)

    If rst.RecordCount >= 1 Then

    rst.Edit

    Dim X As Integer
    For X = 1 To rst.RecordCount
    OldInv = rst!Amount

    Mess up here

    If TotPaid <= rst!Amount Then

    'rst!Balance = rst!Amount - TotPaid
    rst!PAmount = rst!Amount - TotPaid

    'TotPaid = TotPaid - rst!PAmount
    'rst!PAmount = TotPaid
    'TotPaid = TotPaid - rst!PAmount
    Else
    'rst!Balance = TotPaid - rst!Amount

    'rst!Amount = Val(rst!Balance)
    End If
    Next

    OldPay = rst!PAmount

    rst!Amount = Me.TxtNetAmount
    rst!PAmount = OldPay
    rst!Balance = rst!Amount - OldPay

    rst.Update
    MsgBox "Record Updated", vbInformation, "Inf"
    rst.Close
    Set rst = Nothing
    End If

    End Sub

    Could someone help me please?
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    not a direct answer to your question, i'm afraid, but instead of editing the existing invoice value, why not add a credit note to reverse the original invoice and then add the new invoice. this is arguably a more "correct" approach than arbitrarily editing invoice values.

    ...and it gets you out of the hole you created with your stored calculated values.
    currently using SS 2008R2

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

    That way could be more easy but but my client do not want any Credit Note or Debit Note. He just want to re-edit the invoice and thats all.
    With kind regards,
    Ashfaque

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Ashfaque
    Thanks Izy,

    That way could be more easy but but my client do not want any Credit Note or Debit Note. He just want to re-edit the invoice and thats all.

    im guessing your customer hasn't talked to his accountants then...... what you are being asked to do breaks one of the cardinal rules in accounting, that of traceability and accountability. Mind you some would argue creating any accounting db in JET leaves you open to that. Personally If I were you as an ass covering exercise I'd point out to the customer that what they are doing doesn't stack up accounting wise, and you will do what they want but point out that it is their responsibility. The last thing you need is someone saying the fault was your software rather than the requirements of the customer.

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

    My client is one-man-show only..I mean he owns a small shop and does't hae any accountant. Moreover, he just want to go invoice by invoice amount. Hence this need.

    From accounting point you are absoultely correct. But I have no choice to go with him. I already explained him last week about the wrong way. But useless as he is not that much qualified gentlman.
    With kind regards,
    Ashfaque

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Who's designing the application, you or him?
    Who knows about this stuff, you or him?

    As long as you can produce the answers out of it then who cares? It makes both your lives easier.

    Plus, give it afew months and you can bet he'll be asking you for some changes
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Let me try to convince him another time. If works, will add Credit Note / Debit Note otherwise need re-coding that part.
    With kind regards,
    Ashfaque

Posting Permissions

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