Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Unanswered: Append Query Problem

    I've created a user form that's sourced to a table within my database and the control source for each text box is populated with the respective data columns from the table. The user may update any field (text box) on the form and then the event click deletes that record on the table and populates with what's currently on the form.

    This works like a charm when the user manipulates the data in any single field (text box), however, if no changes are made, the data record on the table is blank. All the data is set to Null! When a change is made on the open form and the table record is initially deleted, all the data remains on the open form until it gets appended. However, when no change is made, all the data on the form actually shows "deleted" in the text boxes. It's as if a single change causes the data to stay in the form's memory.

    I can't simply use an update statememt rather then deleting and re-appending the record because there are too many fileds.

    Hopefully, a user would just close the form if no changes were made rather than clicking save and triggering the event, however, I can't take that chance becuase the data is extremely important.

    The form has two pages (two tabs), so I believe the dirty event will be triggered when they browse between the two tabs but don't make any changes.

    Any suggestions are appreciated!

    P.S........using MS Access 2003 SP3
    Last edited by JamesB; 11-19-08 at 22:22.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The dirty property won't be modified just by changing tabs in a tab control.

    Can't really help much because I can't see how exactly you are doing the delete and then append. Making no changes should not result in the deletion of the record, so there must be something askew with how you are doing this. Perhaps you should delete after your append?

    If you use VBA, you would be able to just do an update to the record rather than kill a record and re-create one... which is far more typical.
    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

  3. #3
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Thanks for looking at this. I agree, updating the record on the table makes more since, but my update query produced an error stating that I was attempting to update too many fields (over 255). There's not nearly that many, but it simply didn't work. I'm not quite certin how to update the table using VBA. Would I have to individually reference each of the 150 data columns in my code?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I would recommend two approaches then.

    My first recommendation would be to use VBA to update the record. Yes, you would have to reference all fields you want to update.

    Second option is to get the update query working.

    Both will be impossible if you are using a bound form and the record you are trying to update is the same as the one on the form and the record is dirty.
    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

Posting Permissions

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