Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Nov 2005
    Posts
    26

    Unanswered: Same field value in all records

    Hi, is it possible to make so that whenever i fill in one number in one record (for particular field) in table or form, access would automaticaly fill the same number in the rest records (for the same field)?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Like an update statement?
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Nov 2005
    Posts
    26
    Quote Originally Posted by Teddy
    Like an update statement?

    In Northwind DB, in "Orders" Form there are Discount fields.For each product you must type seperate discount number.

    I want to do so, that there would be only one discount field for whole open form (for all products , all records).

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Fprot2
    In Northwind DB, in "Orders" Form there are Discount fields.For each product you must type seperate discount number.

    I want to do so, that there would be only one discount field for whole open form (for all products , all records).
    Do you mean, then, discounts by Client rather than discounts by Order?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2005
    Posts
    26
    Quote Originally Posted by pootle flump
    Do you mean, then, discounts by Client rather than discounts by Order?
    Yes you can say like that, in other words for the whole order (but only this one, not all other client's orders).

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you can find a suitable event...

    dim strSQL as string
    strSQL = "UPDATE myTable SET myDiscount = " & formDiscountBox & " WHERE myID = " & formOrderIDbox & ";"
    currentdb.execute strSQL

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Nov 2005
    Posts
    26
    Quote Originally Posted by izyrider
    if you can find a suitable event...

    dim strSQL as string
    strSQL = "UPDATE myTable SET myDiscount = " & formDiscountBox & " WHERE myID = " & formOrderIDbox & ";"
    currentdb.execute strSQL

    izy

    It will update my whole table? Or my current form?

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    neither!

    check the WHERE.
    the SQL is going to update a table WHERE the orderID in the table = the orderID on your form.
    i.e. the current order.

    more...

    if your form is going to apply the same discount to each line of the order currently being displayed, there is no purpose at all to displaying the discount on each line of a list or on each detail record. stick the discount in the form header or footer. if you are using my stupid-example names, stick it in a textbox called formDiscountBox.

    which records are being displayed and how did they get there? something on your form "knows" the ID of the current "order". let's pretend that the ID is in a textbox called formOrderIDbox in the header.

    now the SQL is going to update all the records of the current order (from the form) with the current discount (from the form). what we need next is something to cause this to happen.

    it really depends on how your application is designed.
    i live in unbound-forms, so i have very obvious bits of code that bring in an editor-form and sometime later save whatever got edited on the editor-form. in my case i would use this lump of save code to handle the SQL... but in my case the SQL would have to handle all the edits, not just the discount, and i would do it as each order-line was saved.

    if you are in bound forms, maybe i can offer a couple of pointers.

    now you have the discount somewhere up in the header you could use that famous "appropriate event"
    perhaps whatever creates a new record
    perhaps whatever saves an edited record
    perhaps whatever selects a partnumber
    anyhow, find any event that happens to EVERY line on the order at least once in it's life and insert:
    myNewRecordDiscountBox = formDiscountBox
    these are still my silly-example names - replace with something suitable.

    i hope you have agreed with me that it is visually annoying and logically pointless to display this repeating discount data in each detail record, so even in bound-world, once you have it all working, set myNewRecordDiscountBox.visible = false

    the fact that discount is not somehow hard-wired in the system suggests that it can change. maybe you change the discount for order 1234 from 10% to 15% for some reason.
    if yes, you will need to use formDiscountBox_AfterUpdate() to run
    dim strSQL as string
    strSQL = "UPDATE myTable SET myDiscount = " & formDiscountBox & " WHERE myID = " & formOrderIDbox & ";"
    currentdb.execute strSQL
    which takes us back to the beginning with a bulk update of all records in the current order.

    there are probably too many words here: say where you are stuck and someone will help.

    izy

    EDIT typo: 'any' not 'and'
    Last edited by izyrider; 11-19-05 at 11:57.
    currently using SS 2008R2

  9. #9
    Join Date
    Nov 2005
    Posts
    26
    Thanks for reply, i'll give it a try later...

    You see i have such tricky situation, where i need to have invoice form (similar to Northwind.db) and I want to have one Dicount field for whole order.This could be easy done moving it to header or footer, BUT there are exceptions..
    Sometimes for certain products dicount number can be different, while for all others - the same "general". So i want to have general discount number set to all products (records) at first, and then if needed, to change specific product's discount number.

    Thats why i'm thinking day and night how to do it

    Thanks again.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    tblCustomer has the standard discount - stick it in the header

    tblOrder needs two fields
    dblDiscPercent 'the discount field you already have in the table
    ynDiscOverride 'an additional boolean to note when the standard % is overridden.

    you still have the standard discount in the header
    you still copy it to each new record when it is created
    you
    .visible = False
    the detail record's discount percent box by default

    if the user checks the Override checkbox for a given record you
    .visible = True
    that record's discount percent box.

    if the user unchecks the Override checkbox, you reset the discount percent to the standard discount in the header and
    .visible = False
    the percent box

    (((maybe you also .enabled = True/False as well for safety)))

    now everything works as before, except the bulk update SQL which needs:
    WHERE ((ynDiscOverride = False) AND (orderID = " & formOrderID & "));
    i.e. the UPDATE does not hit records where the DiscountOverride checkbox is checked.

    izy


    LATER EDIT - replaced absolute gibberish
    WHERE ((dblDiscPercent = " & formDiscountBox & ") AND (ynDiscOverride = False) AND (orderID = " & formOrderID & "));
    with
    WHERE ((ynDiscOverride = False) AND (orderID = " & formOrderID & "));
    Last edited by izyrider; 11-19-05 at 14:28.
    currently using SS 2008R2

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sounds like you need to consider another table - follwoing on from IZYRider's naming suggestions called, lets say tblDiscount, with a RI link to your order line.

    Despite your earlier comments you now have at least 3 discount rates: "none", "standard" & "other"

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Healdem:

    it is best to ignore normalisation for this situation.

    discountPercent needs to be kept in tblOrderLines (thanks for that critical correction to the table name which hopefully hints to a decent design). referring to a master discount table wreaks havoc when old orders unintentionally reflect a new discount schedule.

    keeping normalisation would require maintaining a discount history and looking up the standard discount applicable at the time of the order... so much hassle.

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Agree, if the discount is negotiated at the point of sale, in which case I'd suggest that the discount entry is 'variable'. It all depends on the business rules being deployed. Some business have a discount applied at customer level (ie a customer attracts a discount of x%), some as indiviudal lines, some as annual rebates, some at point of sale, some a mix of all 3. I have seen multiple discounts applying to the same line item.

    In essence its always a B!!!D, it depends on the business.

  14. #14
    Join Date
    Nov 2005
    Posts
    26
    izyrider,

    I have problems...tried sql but get some errors...And i still can't figure out exactly what to do..

    I have:

    "tblOrders" - My invoices table
    "frmOrders" - the same, only form (continuous form)
    "tblCustomers" - customers table
    "txtOrderID" - Order ID field (bound textbox)
    "txtGeneralDiscount" - General discount field (bound textbox) where i fill only one number for whole form.
    "txtDiscount" - discount field for each record in "frmOrders". It should be invisible, as you suggested.

    Now i can write event procedure (in vba) for txtGeneralDiscount_Afterupdate. Because i want discounts to be calculated after i fill some value...

    So i write:
    "dim strSQL as string
    strSQL = "UPDATE tblOrders SET txtGeneralDiscount = " & txtDiscount & " WHERE txtOrderID = " & txtOrderID & ";"
    currentdb.execute strSQL

    ??

    But this is wrong...i don't get it... what do you mean "mytable" or "mydiscount"?


    And another question, in order to have discounts, i need one field for entering numbers (precents) and another field for calculating values, right? So i need two fields for each row discounts, and two fields for generaldiscount.


    Thnx for your help

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    ideas anyone?

    do you have txtDiscount & txtGeneralDiscount swapped round??

    try
    strSQL = "UPDATE tblOrders SET txtDiscount = " & txtGeneralDiscount & " WHERE txtOrderID = " & txtOrderID & ";"

    assuming:
    txtDiscount is a field in the table tblOrders
    ...maybe worth repeating something i said earlier in the thread: the UPDATE is not going to do anything directly to your form - it is trying to update a table. if your form gets its data from the table you are updating, .requery the form and then it gets the new data.

    also assuming:
    txtGeneralDiscount
    txtOrderID
    txtDiscount
    are all three numeric.
    if not numeric, e.g. if txtOrderID is text as it's name suggests, replace
    = " & txtOrderID & ";"
    with
    = '" & txtOrderID & "';"

    you could code your way through updating all the form textboxes, and (in a bound form) let the form update the table. it just seems the backwards way to do it.

    maybe you need some fresh blood on this.
    ideas anyone?

    izy
    currently using SS 2008R2

Posting Permissions

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