Results 1 to 12 of 12

Thread: Im Confused !

  1. #1
    Join Date
    Sep 2004
    Location
    London
    Posts
    64

    Unanswered: Im Confused !

    Good afternoon everyone.

    As is normally the case when i post, i have a problem. I have built an input system for our marketing department where they can enter their budgeting and spend details in a safe and happy environment. So far so good.
    Within this system there is an amend form that is causing me to doubt my sanity right now.

    (im aware that this whole way of doing things is probably really long winded but hey ho)

    The user enters an order number on ther main form and then goes into the amend form. The amend form in recordsourced from a select query using the order number as criteria. The user can then add details to the record and submit the amendment (which would then update the original record int he main table). The problem that i have is that if the user wants to come out of the form without submitting the change, then the data is being updated in the main table anyway.
    I have followed the little code and events etc attached to the form and there is nothing on there that can, to my knowledge, affect this.

    After tracing through the problem, i have found where this is happenbing. It is updating the main table when the form closes (regardless of whether i use the close button, my close button or CTRL+F4) it still does this.

    Has anyone else ever come across this or have any clue how closing a form can update a completely unrelated table.

    I eagerly await your replies.

    Many thanks in advance for time taken to look into this.

    Eddiesvoicebox

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sounds like your amend form/textboxes have a control sources - meaning when you make a change to a field, it updates the table manually.

    One way round this would be to run an "On Dirty" evens behind each control asking the user to confirm the change.

    Or you could use unbound textboxes on the form that you populate on load using some snazzy SQL M'larky. Then when the user hits "Save" you can run an update query!

    Finally: Lol @
    Quote Originally Posted by eddiesvoicebox
    where they can enter their budgeting and spend details in a safe and happy environment
    It just made me giggle

    Hope this helps!
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Eddiesvoicebox
    What you have described is the way Access works. Access WILL update the data keyed unless maybe you lose power. That is just what Access does. George's suggestion about using the On Dirty event really only needs to be done at the record level (if my memory is still working). In the OnDirty event for the record level (actually the form) you can ask the user "do you want to update this record". What just went through my head which is a nicer solution, depending on what you want, would be to put a button on the form that says something like "Undo Changes". The code behind this button would issue a DoCmd.RunCommand acCmdUndo. This command will undo any changes the user has done to the current record on the form. Somethings else that can be done is to press the Esc key twice to undo all changes to the current record.
    HTH,

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    good point Vic about the RunCommand - completely forogt that existed!
    And I never knew about Esc x2!
    Clever chap.

    My approach to these situations is generally to use lookup textboxes and update queries - rather than using control sources.

    Just a different style of doing things I guess!
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Do you mean you are using Unbound forms? Because I have just inherited an application using unbound forms and it is a huge pain to have to do anything. I have been asked to add about 15 controls to the main input form that uses 4 tabs of a tab control. To add these fields, I have to move others around, between tabs. Controls CAN NOT be copied between tabs. When you try that, the copied control is put on the FORM, not the tab page. So, I can not just add new fields from the field list, because there are no fields, but I have to add each field as a text box, then go through the Tag control of each field to be sure the program knows which table and the "real" field name is, and the type of data the field is. Etc., etc., etc. Trust me, a REAL PAIN. I highly reccommend NOT using unbound controls unless it is the ONLY way to do something.
    OK, I'll get off my soap box.

  6. #6
    Join Date
    Sep 2004
    Location
    London
    Posts
    64
    Guys,

    Thanks for the responses so far. What you are saying makes sense actually, i just didnt want to code the form with lookups for the existing record details. However i do just want to clarify something on this before i go back to the drawing board. The fields on the form are controlsourced, but to fields in the select query. does this mean that even though i am not linking directly from the table (ie using a Select query) that it will still update the record in the table ? I didnt realise such things were possible.

    Again thanks for your help so far.

    EV

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yep - unbound forms all the way for me in some projects!
    It was just the way I was taught and so far I've never hit a snag.
    However, I can completely see your logic.
    But also I have NEVER used tabs in any of my Access projects - partly because I hate tabs from a users perspective!
    Yes using bound forms is a better idea in theory, but I tend to use unbounds purely so you can have greater control over what is and what isn't saved by users (IMHO).
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    doCmd.RunSQL "UPDATE <table1> SET <fieldname1> = <textbox1value> WHERE <table1 identifier> = <whicheverrecordyouwantupdating>
    ?
    George
    Home | Blog

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by eddiesvoicebox
    Guys,

    Thanks for the responses so far. What you are saying makes sense actually, i just didnt want to code the form with lookups for the existing record details. However i do just want to clarify something on this before i go back to the drawing board. The fields on the form are controlsourced, but to fields in the select query. does this mean that even though i am not linking directly from the table (ie using a Select query) that it will still update the record in the table ? I didnt realise such things were possible.

    Again thanks for your help so far.

    EV
    Your select query is really just acting as a filter on the data. You're seeing the actual data from your table, and not a cloned snapshot. Since it's an updatable recordset, your changes are applied immediately. Most simple queries lead to updatable recordsets. That goes away as soon as you put in aggregate functions, though.
    Last edited by RedNeckGeek; 02-22-07 at 12:36.
    Inspiration Through Fermentation

  10. #10
    Join Date
    Sep 2004
    Location
    London
    Posts
    64
    Of course, well how silly do i feel now.
    I thought i had used this method before and i had but only for viewing a record (where the fields were locked) it all makes perfect sense now. I remembered i was trying to make the form run faster for the users and didnt want to use dlookups. Now i will do away with the submit button and incorporate the 'DoCmd.RunCommand acCmdUndo' code to allow users to leave the form without saving the updates.

    Guys i really appreciate your help on this one you have saved me a great deal of time.

    EV

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    @ RedNeckGeek: Loving the sig

    @ eddiesvoicebox: Good luck with the rest of this project, if you need any more help you know where to come a running!
    George
    Home | Blog

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I stole the sig from some guy in a gun forum. I doubt he'll ever notice!
    Not many people will post in a gun forum and a computer forum
    (except maybe a RedNeck-Geek)
    Inspiration Through Fermentation

Posting Permissions

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