Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Best event for a msgbox prompt (2000)

    I have a form, frmPaymentEdit that has a subform frmPaymentAllocationsEdit. I would like the user to be prompted whether or not they want to/are sure they want to ..................... save record/changes to the recored on exit or when they click the save or the new record button. I've tried putting in the before update event as the lounge showed me to do on a previous form however if I do this on frmPayment edit it prompts the user each time he leaves frmPaymentEdit and tabs to frmPaymentAllocationsEdit. I only want the user to be prompted AFTER they've updated both forms. This way if they choose no for whatever reason, none of the data on either form would be saved. I've gotten the code to work (I think) but am now looking for the best place to put it. OR is there different code to use when working with subforms.

    Thanks,
    Leesha

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Best event for a msgbox prompt (2000)

    >>This way if they choose no for whatever reason, none of the data on either form would be saved. <<

    This can't be done. When you move from a mainform to a subform, Access automatically saves the mainform info. Once on the subform, if it is a continuous subform, Access will save each line when you move to the next one. It will also save the current line when you move out of the subform.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Best event for a msgbox prompt (2000)

    Well that's not very practical. The user should be given the choice if needed.

    Thanks,
    Leesha

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Best event for a msgbox prompt (2000)

    Hi Leesha,
    Though what Mark says is true to an extent, there are ways around it - they just make life a little more complicated! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    You could either use unbound forms, in which case data will only be written back to the database when you say so and not by default, or you could store the existing values in variables and roll the form back if they say no.
    In terms of which event you use, I personally would limit their navigation options - remove the built-in navigation buttons and create your own as well as Save and RollBack type buttons. (You may also want to disable scrolling through records.) Your navigation buttons can call the same code as your save button before actually moving to a different record.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Best event for a msgbox prompt (2000)

    Hi Rory,

    I'm not one to give up and I couldn't believe there wasn't a solution. I never use the built in buttons and always put my own on the forms. The form I want to add the option to is already built and bound to queries. My gut is that it would be way outta my league to use your first option without some major help rom the lounge! I'm not clear on what you mean by "storing the exisitng values in varialbes", and then rollig them back. Can you explain that a little more.
    RE the user saving before going to the next record, that is how they do it now, but where I find errors is when they exit without first and then the record is automatically saved, often times with either errors or without the intent of being saved. That is what I'm trying to prohibit.

    Thanks,
    Leesha

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Best event for a msgbox prompt (2000)

    Leesha,
    Re your last point, I think the Form's Unload event is what you want.
    As for storing the data in variables, you need to declare a variable for each of your data-bound controls (or an array if you're comfortable with that). Then in the BeforeUpdate event of the main form you can use the OldValue property of each control to populate the variables. This will get very complicated on your subform if you have a one to many relationship and you want to be able to roll back all changes made. If that is the case, I think you will need to look at unbound forms and batch transactions.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Best event for a msgbox prompt (2000)

    Hi Rory,

    Well, option two is way out of my league. So if I go with the unload piece you mentioned, would I put a message box behind the close command that asks if the user wishes to save the data. If the answer is no the record would be unloaded or erased and if yes it would be save?? If this is the case then would the same thing happen in the subform as well?

    Thanks,
    Leesha

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Best event for a msgbox prompt (2000)

    Leesha,
    You basically want your Save button, navigation buttons and the Form's Unload event all to call the same routine. That's the easy bit. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> The routine you need to call, as I explained, is going to be very complicated if you have a one to many relationship between the main form and subforms, because you will have to store every changed value, and you don't know how many of them there will be. The only other way I can think of offhand of achieving this, would be to use temporary tables and then run update queries if the user chooses yes, but that might turn out to be just as complicated!
    On the other hand, if your subform is one to one with your main form, you can use the variables method I described initially.
    I hope that hasn't confused the situation? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Best event for a msgbox prompt (2000)

    OMG Rory, confused doesn't even begin to explain it. It will be easier for me to build in something that allows the user to fix their error!! Just when I thought I was getting it.

    Thanks,
    Leesha

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Best event for a msgbox prompt (2000)

    >>Well that's not very practical. The user should be given the choice if needed. <<

    Practical or not has nothing to do with it. If you are using bound forms, this is the way Access works.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Best event for a msgbox prompt (2000)

    Tis a shame that the program itself controls us.

    Thanks anyway,
    Leesha

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Best event for a msgbox prompt (2000)

    Hi Leesha

    Try http://woodyswatch.com/kb?175066, it contains a form that uses temporary tables. I cannot take credit for finding this, it was Hans who found it.

    Cheers
    Pat

  13. #13
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Best event for a msgbox prompt (2000)

    Thanks Pat and Hans! I'll give it a shot.

    Leesha

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Best event for a msgbox prompt (2000)

    Sorry to butt in, but if you're going to try and program any language or application, you have to work within the constraints it imposes. There is no point in crying over what the application won't let you do, just learn to use the features provided. If Access forced you to confirm every save, that would drive people even crazier. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  15. #15
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Best event for a msgbox prompt (2000)

    Hi Charlotte,

    I'm a firm believer in giving options. So I guess I'd expect the same of Access. I'm not crying over what the application won't let me do, but rather was trying to find out if it would let me do what I wanted (before I spent more time than I already had trying to accomplish the task) and then only voiced an opinion. I agree, if I had to confirm every save I'd go nuts, however in some instances (knowing the users at my office) I'd rather drive them nuts than myself down the line trying to fix their screwups.

    Thanks for the input. It's always appreciated.

    Leesha

Page 1 of 2 12 LastLast

Posting Permissions

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