Best event for a msgbox prompt (2000)

Topic

New Reply

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

Reply | Quote

Replies

>>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.

Reply | Quote

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

Thanks,
Leesha

Reply | Quote

Hi Leesha,
Though what Mark says is true to an extent, there are ways around it – they just make life a little more complicated!
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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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. 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?

Reply | Quote

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

Reply | Quote

>>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.

Reply | Quote

Tis a shame that the program itself controls us.

Thanks anyway,
Leesha

Reply | Quote

Hi Leesha

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

Cheers
Pat

Reply | Quote

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

Leesha

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

There is a very solid reason for the main-form record being saved when you go to a subform. Otherwise you wouldn’t be able to add new records in the subform since you wouldn’t have a primary key in the one side of the relationship. You can’t just decide to put the cart before the horse.

Reply | Quote

Hii Wendell,

Well that makes sense. I guess in these situations I’ll need to avoid using subforms and just have a different form open up.

Have a good one,
Leesha

Reply | Quote