Results 1 to 12 of 12
  1. #1
    Athar Khan is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    63

    Calculation based on checkbox

    I want to calculate the balance amount based on the clearance of 3 received amounts.

    Please view the attachment picture.
    Please notice that you can view the attachment only in full site mode, not on mobile version.



    By the way, after many years, still we don't have an Android app for this forum but a limited mobile version website only
    Attached Thumbnails Attached Thumbnails IMG_20210608_234237.jpg  
    Last edited by Athar Khan; 06-08-2021 at 09:56 PM. Reason: Limitations in mobile-version website

  2. #2
    Athar Khan is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    63
    I need a code to calculate the balance

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Code:
    Sub SumBoxes()
    dim vTot
    
    vTot =0
    If chk1  then vTot= vTot+ txtBox1
    If chk2  then vTot= vTot+ txtBox2
    If chk3  then vTot= vTot+ txtBox3
    
    txtTotal= vTot+ txtAmt
    end sub
    then add SumBoxes to every chkBox Afterupdate event:

    Code:
    Sub chk1_afterupdate()
      SumBoxes
    end sub

  4. #4
    Athar Khan is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    63
    vTot will be equal to a single value only which is checked.
    What if chk1 and chk3 are both checked

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    it totals all boxes with check box, if checked,
    then adds the top BILL AMT box too.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by ranman256 View Post
    it totals all boxes with check box, if checked,
    then adds the top BILL AMT box too.
    I *think* the idea is to sum the cleared cheques and deduct that sum from Bill Amount ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Athar Khan is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    63
    Yes, you are right

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Simplest might be to have an invisible calculated control txtSum and update it with AfterUpdate for each checkbox: txtSum = txt1+txt2+txt3 while Balance = Amount-txtSum.
    However, the design seems wrong anyway. If you had to add a 4th cheque you have much re-design to do that should not be necessary. Even if you will never have a 4th cheque, the litmus test is "if you did would a redesign be necessary" and since the answer is yes, you're on the wrong path.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    checkboxes are booleans, true/false, -1,0

    so you can multiply.

    Not tested but as a calculated control, perhaps something like this

    =billamt-sum(chqval*-chkbox)

    I'm assuming this is a continuous form (it should be) but from your labels it would appear not

  10. #10
    SamL is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2021
    Location
    NJ
    Posts
    20

    Some details

    Hopefully, you have some better customers who order at least once a month, and at any time they have multiple orders on which to make payments. To try to have all the important information in one table would make your database unwieldy. In addition, you don't know how many payments the customer will need to make against a given order, so normalization rules dictate that you break up the data into multiple tables.

    I would have the Customer table include the customer's outstanding balance. The Orders table (or Invoice table) would include the customer number, order number, original invoice amount for that order, and the balance due for that order. The Accounts Receivable table would include the customer number, payment date, payment amount, and a checkbox to flag the payment as having cleared the bank. These will not be the entire structures for any of the tables, of course. You will need additional fields as well.

    You have to decide if you want to deduct the payment from the Invoice balance due, and the Customer table's outstanding balance, upon receipt of payment or when it clears the bank. With large checks, the bank's clearing cycle could take two weeks.

    In any event, when the payment is entered, or it clears (see above), you will locate the appropriate payment and check the 'cleared' checkbox. You should do this on a form, and the underlying VBA should do the rest.

    Good luck,
    Sam

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would have the Customer table include the customer's outstanding balance
    The generally accepted approach is to not store that which you can calculate in a form, report or query. That presumes that you have the data that would be used in a calculation in tables. If not, you probably should. There are exceptions to the 'rule' but they are rare when you get right down to it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    SamL is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2021
    Location
    NJ
    Posts
    20
    Customer outstanding balance is a number that can accumulate over years, and can include data on records that were archived. Without this number in the table, it cannot be calculated so easily, I don't think.

    Sam

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-14-2017, 03:38 PM
  2. Generate report based on checkbox value
    By accessn00blet in forum Reports
    Replies: 11
    Last Post: 06-02-2015, 10:59 AM
  3. Checkbox based on Combo Box
    By roarcrm in forum Forms
    Replies: 2
    Last Post: 04-07-2015, 10:23 AM
  4. Query Based on Form Checkbox
    By tylerpickering in forum Queries
    Replies: 4
    Last Post: 11-11-2014, 09:32 AM
  5. Replies: 7
    Last Post: 11-26-2010, 07:54 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums