Results 1 to 11 of 11
  1. #1
    Join Date
    May 2004
    Posts
    16

    Exclamation Unanswered: urgent assistance requird, can anyone help?

    i am using a form based on [invoice customer details] with a subform [invoice stock] for inputting stock from a list linked to [stock file] table.

    once the stock is selected dlookup is used on update to complete the [retail], [cost] and [vat rate] fields in the [invoice stock] table.

    the subform is used to add as many items as required to each invoice. a textbox with sum([retail] is used to calculate invoice totals.

    i need to add another text box which will then be used to recalculate [retail] to equal a prefered sum.

    for example the total invoice (sum([retail])) might be 198.90 but the user might want to round the invoice to total (sum([retail])) 200.00.

    the calcutaions on retail would need to be percentages for example the 200.00 is a 0.553% increase on the origional sum, i need to make each [retail] increase or decrease by the desired percentage.

    any suggestions???????????????

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What do you have so far?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004
    Posts
    16

    Urgent help required

    this is what i have so far,

    some of the on events mought not work becaue invoices1 form is extracted from the fully working database.

    the red text box is the input if a new invoice total is required.

    what i need to happen is the [retail] values on the sub form are reduced or increased by the correct percentage to make payment total = new sum retail

    any ideas
    Attached Files Attached Files

  4. #4
    Join Date
    May 2004
    Posts
    16

    Exclamation can anyone helo with this

    no replies yet, can ayone help, please!!!!!!!!!!!

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I am sure I am missing something here. Those attachments don't work for me.

    Is it a case that (sum([retail])) is the field in your invoice and in your example that results in 198.90 but you want 200.00 and the user decides on 200.00 or do you have some pre determined amount of rounding "up" or "down"

    Mike

  6. #6
    Join Date
    May 2004
    Posts
    16

    Exclamation payment amount = sum([retail])

    thats right, invoice total is set to = sum([retail]) (the sum of the retail value items in the subform).

    say for instance the sum = 198.50 but the user wants to round the invoice to 200. then i need the [retail] field value to increase or decrease by the correct amount for each record in the subform so that [payment totoal] = sum([retail]) is 200.00


    try opening the form [invoices1] on the attachment, you might need winzip to unzip the access db file attachment.

    regards

    andy

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    So is like this

    Record 1 in Subform is $37
    2................is $10
    3................is $87
    4................is $31
    5................is $32

    Total ....................is $197

    Rounded up to $200 which is 1.0152 increase and you want each of the 5 records increased by 1.0152.

    The attachment is not working and probably because Zip is not on this machine, but is the above the correct picture?

    Mike

  8. #8
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    If I am reading the problem correctly this is what I just did and this is with the main form open and subform displaying. By the way, this is for an insurance client and the subform is displaying policy benefits and amounts etc.

    I put an unbound text box on the main form which defaulted to Text1515 and then made an update query on the table that holds all the policy benefits and with

    citeria being [Forms]![Masterform]![NameNumber] where NameNumber is the ID for the person and that person's policy benefits.

    Update to was [Forms]![MasterForm]![Text1515]

    I put $550 in Text1515 and opened the query and each premium changed to $550 and of course the total premiums reflected this.

    So the next stage would be in your case to enter the $200 in Text1515 and then another text box will be =[Text1515]/[SumRetailTextbox] and use that text box for the "Update to"

    Is this sounding right or way off from what you want?

    Mike

  9. #9
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    In the Update Query the Update would be the textbox with a result of say 1.02

    [Forms]![MainForm]![TextBox with 1.02 type result]*[Retail]

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Wait... so this is for an insurance underwriter? You may want to double check with him on exactly how they would like this modifier applied.

    Chances are pretty good they'll want something along the lines of a "schedule credit", which would be a modifier applied AFTER everything is calculated, which is displayed a bit differently then what you're suggesting.

    Wait... so this is for an insurance underwriter? You may want to double check with him on exactly how they would like this modifier applied.

    Chances are pretty good they'll want something along the lines of a "schedule credit", which would be a modifier applied AFTER everything is calculated, which is displayed a bit differently then what you're suggesting.

    For example, with the figures given, the schedule credit (in this case debit) would be 1.015

    So they may want it to read more like this:

    1................ $37
    2................ $10
    3................ $87
    4................ $31
    5................ $32

    Total .......... $197
    Sched. Cred. 1.015

    Adj. Total..... $200

    You may want to double check on that. Also ask them how they round quotes... that can be messy..
    Last edited by Teddy; 05-14-04 at 10:20.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Teddy,

    No, just some confusion caused by myself. I am in insurance and went to a form and subform which I think is similar in principle to what Andy was doing. I mentioned it was from insurance so he would know I just did the example on something that was real.

    Mike

Posting Permissions

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