Questions on Data in Form and Table 1

[ktchan741]

I have created a Form_A based on Fields from Table_A.

In Form_A, I have a command button called "CANCEL" and a command button "SAVE".

The "CANCEL" button when click will run a marco that will close Form_A without saving any data. The "SAVE" button when click will run a marco that will saved all the data.

In Form_A properties, I set the OnClose to a marco that will close the Form_A without saving any data.

I enter some of the field in Form_A and click on "CANCEL". However when I open the Table_A, the data which was enter in Form_A was saved in Table_A. The marco did not seem to work?

Can anyone advise how to ensure that whatever I enter in the Form_A will not be saved in Table_A unless I click on the "SAVE" button?

Thanks.

 

[RoyVidar]

Hi!

The save thingie in a "close" macro determines whether any changes in the object is saved - that means design changes of the form, and does unfortunately not consider the data in the form.

You do know that whenever you move from one record to another, the previous record is saved?

Therefore, to have control whether to save or not data, I think the before update event of the form would be better. That event fires whenever a user tries to move to a new record, close the form etc...

You'll probably see lot's of threads discouraging members from using macros, I'll do the same, VBA can do this much better/more elegant, you can set conditions, trap for errors...

Here are two alternatives that might be used in a forms before update event. First one is perhaps closer to what you describe:

[tt]if me.dirty then
if msgbox("Wanna save?",vbOkCancel)=vbCancel then
me.undo
end if
end if[/tt]

If cancel is pressed, this will undo any changes to the data, leaving them just as when you opened the form, and if the action that fired this event, was close, the form will close.

[tt]if me.dirty then
if msgbox("Wanna save?",vbOkCancel)=vbCancel then
cancel=true
end if
end if[/tt]

When cancel is pressed, this will not leave this record until one either hit ESC or hit OK in the message box.

To use any of them, find the before update event of the form, use the button with three dots at the right, select Code Builder and paste one of the above snippets where the cursor is. The text between the double quotes might be altered to suit your needs.

HTH Roy-Vidar

 

[ktchan741]

Hi Roy,
Thanks for the advise.
The code you provided work if the form is "Dirty".
However, if the user just happen to open the form and did not fill-in any entry and click on the "CANCEL", the code does not seem to work. Can you advise how to go about it?
Regards.

 

[RoyVidar]

Hi again, and thanx for the star!

I'm not sure what you mean by "doesn't work".

The code will only give a messge box when trying to save a new record or changes to an existing record.

Could you please state what it performs (or what doesn't work), and what functionality you'd like, and we'll see;-)

Roy-Vidar

 

[ktchan741]

Hi Roy-Vidar,
Apologise, your code work perfectly. I place the code in the wrong event.

In Form_A, I further added a Tab_Control with field from Table_B and Table_C. Form_A now has field entry from Table_A, Table_B and Table_C where Table_B and Table_C are in the Tab_Control.

I fill-in some of the field in Form_A and I applied the code you given.
I click on the "CANCEL" command button, everthing seem fine.
However, when I open Table_A, Table_B and Table_C, unwanted data entry which are not supposed to be saved are saved into Table_A, Table_B and Table_C respectively. Can you advise?

Thanks and good weekend.

 

[RoyVidar]

Hi!

I'm not sure how you've accomplished adding your fields. There are two ways that I'm using for adding fields from other tables. One of them is using a query to combine the data, the other one is using main form/subform structures.

As I read you post, I'm assuming you've added a subform in your tab control - if that's so, I think the same type of test would probably have to be added also to the subform. But to be sure, I think we'll need to know more about the structure and how you've build it (tables, fields relationships, form/subform).

Roy-Vidar

 

[ktchan741]

Hi,
I am using mainform/subform structures.
in Form_A, Table_A is the main form
Tabel_B and Table_C are the subform in the Control Tab.
The three tables are related by the PNo that is in each Table.
Thanks.

 

[RoyVidar]

This would depend on what you really want and how your forms and tables are organized.

No I also know that you have at least one subform.

As I stated in previous post, you could try to add the same thingie also in the subform(s) before update events.

Don't use this myself, tried it out just now, It's going to be a bit messy, it'll result in a message box per each time someone tries to save a record in either the main form or the subform(s) + when they click from one for to antother and there are changes in the first... But try it, see if it's appropriate to your situation, else delete (or just delete the backup where you are testing this)

The strange thing, is that you say that changes appears also in table_A, even if you say no fields from table_A is present in your subform(s) - so either does the previously code not work, or you change fields from table_A in your subform(s) where you say only data from table_B and table_C is present - hmmm.

HTH Roy-Vidar

 

[ktchan741]

Hi,
Thanks again.
I tried what you suggested. It is kind of messy really.
What I am looking at is to ensure that when I click on the "CANCEL" command button, no data is saved in Table_A, Table_B and Table_C. Your code work perfectly with only Table_A in the Form_A. When I inserted Table_B and Table_C into Form_A under Control Tab, upon clicking the "CANCEL" button, the data will still be saved in all the three Table. Can you offer any advise to go about his? Thanks.

 

[RoyVidar]

I'm afraid I can't provide more advice than I've done. As stated before, I don't do this with main/sub forms, so this was the best/only I have:-(

You might either hope someone else visit this thread, or post again (new thread) with this question (specifying main/sub and information on the structure). There might be someone knowing a way around this.

Good luck, Roy-Vidar

 

[ktchan741]

Hi, Thanks.

 

[jbento]

Check this entire thread, it might help because it is something similar in what you are trying to do:
thread702-555937

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[mckenneyj]

In the on click event for this button

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close


Thanks,
John McKenney
Work Hard... Play Harder