Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2007
    Posts
    16

    Unanswered: Modify multiple records from a form?

    Hi there,

    I'd like to create a form on which I have grouped data. I want to make modification to multiple records then (to all which are in the group).
    Eg. I have a table "Payment due" with the following fields/data:
    Name1, Date1, Amount ($10), paid (Yes/no)
    Name2, Date1, Amount ($10), paid (Yes/no)
    Name3, Date1, Amount ($10), paid (Yes/no)
    Name1, Date2, Amount ($10), paid (Yes/no)

    The form would display the following summary:
    Name1 - $20 - Payed (checkbox)
    Name2 - $10 - Payed (checkbox)
    Name3 - $10 - Payed (checkbox)

    If I click on first checkbox (next to Name1), it should modify both records in the table for Name1 (Set both records to paid or unpaid). How can I do this?

    Thanks a lot!
    Axel

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    UPDATE yourTable
    SET    paid = -1
    WHERE  name = 'Name1'
    ??
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    That... or do it with VBA recordsets.
    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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well, you can execute that query via VBA; which would be my chosen method. Set based ftw
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    All comes down to whether you want record level error trapping or not
    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

  6. #6
    Join Date
    Feb 2007
    Posts
    16
    Hi,

    Thank you for your kind help.
    I'm facing a problem when creating the form.
    If the Paid checkbox is bound to my query which calculates the SUM, then it cannot be modified. If the checkbox is unbound, then all checkboxes in displayed row gets modified. I am using tabular form. How can I modify just one checkbox at a time?

    Axel

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If the Paid checkbox is bound to my query which calculates the SUM, then it cannot be modified.
    Fact. You cannot modify the contents of any expression.

    If the checkbox is unbound, then nothing will be modified without using code.

    I am using tabular form. How can I modify just one checkbox at a time?
    Bind it to ONE normal field, or use VBA to do the modifying.
    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

  8. #8
    Join Date
    Feb 2007
    Posts
    16
    Helo StarTrekker,

    I'm afraid I was not clear enough. You are right, nothing changes in the table. I was talking about the form display.
    When I have an unbound checkbox and multiple rows are displayed in a form's tabular view, then if I click one checkbox, all others are changing value as well (all becomes checked or unchecked). How can I change the value of just one checkbox?

    BR,
    Axel

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    To do that it MUST be bound to a writeable field. Since you are grouping the data for the form, you've reached in impass.

    You might want to ungroup your form data and just sort it by name instead. This will achieve two things; it will allow you to tick the checkbox for those items that are paid and it will also allow you tick one payment out of many for one name.

    You could run some VBA after checking a box that offers to tick all of the payments for the one name (if there are more). This way you can tick many payments with one action, but still gives you "granular" control over which payments have actually been paid.

    Hope that helps
    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

  10. #10
    Join Date
    Feb 2007
    Posts
    16
    Hi,

    Thanks for your answer.
    Meanwhile I found the ideal solution for my needs:
    First I'll create a make table query, which creates a temporary table with the sums like this:
    NameID, Name, Sum of amount, Paid (default no)
    Then based on the temp table I can create a continuous form, on which I can select/deselect individual paid checkboxes, since they are now bound to a table's field.
    From here I can catch any event I want and update the records in my original table based on the NameID with an update query.
    The only problem is if I want to modify my data more than once, because then I have to check line by line if the paid column is set to yes or no.

    Any idea how to accomplish this?
    Axel

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The only problem is if I want to modify my data more than once, because then I have to check line by line if the paid column is set to yes or no.
    Not sure I understand exactly what the problem is here, but if you mean that repeating the process won't show those already set to paid, then you'll need to modify your make table query to set its paid column to true if ALL of the group items paid fields are set to true.

    Personally I don't like copying data around the way you are doing there, but as long as you're happy with it...
    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

  12. #12
    Join Date
    Feb 2007
    Posts
    16

    Lightbulb

    Hello StarTrekker,

    Thanks for sharing your thoughts. I like your idea, but I might have 100+ rows and it is difficult to overview this if I list all the lines.

    My other approach would be to expand and collapse data fields in a similar way, than one can expand and collapse directories in explorer. (with clicking the + or - sign).

    Unfortunately I could not find a solution how to do it in Access. Maybe I should use datagrid?

    Regards,
    Axel

Posting Permissions

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