Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mass Updates (A2K)

    Long piece of code moved to attachment by HansV; also edited to provide link to post

    Before panic sets in, the code at the end of this post works perfectly and is based on the help and guidance received from Hans in <post:=688,770>post 688,770</post:> back in January, but decided to repeat it here for the record and to save you folks the trouble of bouncing back and forth between posts.

    Scenario #1, using the aforementioned code, is as follows:

    There are 200 records:

    Using
    [F00_Function] code = 11
    and
    [F01_RA_No] = 905

    100 records are selected.

    Within those 100 records, find records with [F11_Invoice_No] = 231749 ( the number varies, therefore cannot be considered a constant )

    and then

    Mass update the newly selected records so that
    [F00_Function] code = 20
    [F20_Waybill_No] = WB193771
    [F20_Shipped_Date] = 03/21/08

    I realize and know how to create an Update Query based on the above scenario, but this must be set up so the user can just enter data into a preset field on a form.

    If that's possible.

    I'd be much obliged for any help on this and would assume that this situation would be helpful to others once solved.

    Thanks in advance for any help,

    ps. I did get the "long message warning" but feel that having all the code involved in front of you will better enable you to understand what's happening.
    My apologies for my presumption, but I'm big on clarity when dealing with any problem.
    Attached Files Attached Files
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mass Updates (A2K)

    Do you always want to set the values of F00_Function, F20_Waybill_No and F20_Shipped_Date, or should the user also be able to indicate which fields are to be updated?

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mass Updates (A2K)

    Good Sunshiny Morning Hans! Sorry for the trouble with respect to my posting's length.

    With respect to your question, the ideal situation would be to have the three aforementioned fields available at all times ( or their permanent equivalents when the situation arises ) and the capability to throw any field on the form to be updated.
    Cheers,
    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mass Updates (A2K)

    Let's tackle the "fixed" fields first.

    Place text boxes on the form in which the user can specify the new values, let's say

    txtNewFunction
    txtNewWaybill
    txtNewShippedDate

    Place a command button butUpdate on the form, with "Update" or something similar as caption.
    The attached text file contains the On Click event procedure for this button. I didn't repeat the code to assemble strWhere, it is the same as the code you posted.
    The code is obviously air code, so check thoroughly.
    If you study the code, you'll see how a number field, a text field and a date/time field are handled.
    I put in the code to suppress warnings such as "You are about to update 37 records", but I commented it out. I also put in a line to display the SQL string to be executed.
    I'd leave it that way during testing, so that you get an idea of what the code does.
    When it works to your satisfaction, you can uncomment the lines to suppress warnings, and comment out the line that displays the SQL.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mass Updates (A2K)

    Here is a simpler version that doesn't require you to copy the filter code. It simply uses the filter set on the form.
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mass Updates (A2K)

    Hans,

    Had to step out for a bit and just got back.

    In the words of Yoda, 'Never failed to be amazed, am I".

    I'll get back to you as soon as I've digested your coding nuggets,
    Cheers,
    Andy

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mass Updates (A2K)

    Hans, being a coward, I've tried working on the 2nd simpler version. See the attached for the gruesome details.
    Attached Files Attached Files
    Cheers,
    Andy

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mass Updates (A2K)

    See if the attached version works better. I added code to save the current record if necessary, and to requery the form.
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mass Updates (A2K)

    Hans,

    Your attached worked perfectly. Thanks very much. I'll have a go at my other problems with respect to date lookups, etc. before I come begging again.
    Cheers,
    Andy

Posting Permissions

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