How to alert user of data changed when adding or deleting record in SubForm?

Joe Y

I have a MainForm containg a datasheet SubForm. I want Access to alert user when closing or leaving the record in the MainForm if there is any data change. To users, adding or deleting record in datasheet SubForm is considered data change of existing MainForm record.

I applied Me.Dirty function in the Before Update in both MainForm and SubForm to trigger pop up message to alert users for any data change. However, Access does not consider adding or deleting records (rows) in the datasheet SubForm as data change. Therefore, the following codes do not apply.

Can anyone suggests how to make it work for user to be informed when they leave the MainForm if there is any record (row) in datasheet SubForm added or deleted?

Thanks.

Nov 4 '11 #1

beacon

Hi Joe,

Hopefully someone can tell me I'm wrong on this, but I believe that I asked a similar question a couple of years ago and was told that it wasn't possible to prevent saving a record on the subform until the main form was processed. Your question is slightly different, but it seems to be in the same family as mine.

There may be something you can do by allowing the user to search for records in a modal form and have it temporarily populate some unbound fields on your main form. Then, prior to the user submitting/closing the form, you could add functionality to trigger the alert for the user. If they want to save the data they've changed, you could use a SQL UPDATE to note the changes. Otherwise, you just dump the data.

This takes away from the subform functionality (including how it's nice to be able to see multiple records as you're updating), but it may be a matter of choosing the lesser of two evils.

I hope this all makes sense and that it somehow manages to help you out. I'm interested to see some of the other posts this thread receives.

beacon

Nov 4 '11 #2

NeoPa

Joe, you have a full-stop (period) in the middle of your second sentence. How is anyone supposed to make sense of this?

You understand that the lack of answers is directly related to the quality of the question right? People read it and it's nonsense - what would you expect them to do next?

Nov 11 '11 #3

Joe Y

Sorry, I have tried my best. English is my second language. VBA is also a new language to me.

Nov 12 '11 #4

NeoPa

You make a good point. My tone was incorrect, so I apologise for that. The point remains true though, unfortunately. I expect it explains the lack of responses. I'd help more, but I just don't know what you want. I suppose some general tips may help :

Referring to Items on a Sub-Form should enable you to refer to items both ways (with a little thought). If a form is used as a sub-form on another form then Me.Parent refers to the containing form from the sub-form. Each form will have its own Form_BeforeUpdate() events that can be handled separately.

I hope this is some help.

Nov 12 '11 #5

Joe Y

NeoPa,

I hope below explains the helps I need better.

The first help I need is to know the VBA function that will make Access to pop up message when record is added or deleted.

As I mentioned in the post, I used Me.Dirty function in Form_BeforeUpdate() events to trigger pop up message when data is modified in a record. I applied this in the MainForm and it worked.

I want the same response from Access when record is added or deleted in the SubForm (a datasheet form). Me.Dirty does not trigger action in Access for this scenario when I tested it. The reason, as I understand it, is that adding or deleting record is not data change in a record.

Secondly, I don’t want the alert message to pop up “immediately” when record in SubForm is added or deleted. Instead, I want the pop up message appear only when user leaves the record in MainForm.

Thanks,
Joe

Nov 14 '11 #6

NeoPa

Joe, this is not a single question (as it should be to post a question thread), however I will attempt to point you in the right direction. From there I suggest if you are unsure on any specific question then you need to post a thread about that particular question. Handling multiple subjects in the same thread is bad for you, bad for the experts, bad for the site, and bad for everyone who wants to use the site. It benefits no-one.

Joe Y:
The first help I need is to know the VBA function that will make Access to pop up message when record is added or deleted.

These are separate operations and need to be handled separately, but for each the trick is to find the appropriate event and write an event procedure for it. The Before events always supply a parameter called Cancel which you can set to True if you want to cancel the event.

1. Adding Records :
   Records are added twice. Sounds strange, and technically it's not true, but it may help to think that way (and it's surprising so gets your attention).
   First, when the record buffer is dirtied (data is actually changed) in the new record then the Insert related events (Form_BeforeInsert & Form_AfterInsert) trigger. Next, if and when the new record is actually saved, then the Update related events (Form_BeforeUpdate & Form_AfterUpdate) trigger. These events trigger for all saves, but the .NewRecord property will be True when a new record is added.
2. Deleting Records :
   For this there are DelConfirm events (Form_BeforeDelConfirm & Form_AfterDelConfirm). The Before version also supplies a Response parameter for you that enables you to prompt the user automatically.

For help on any of these event procedures use Context-Sensitive Help on the name after the Form_ part and click on the event link of the same name.

EG. type in your Immediate Pane or somewhere in your code :
beforedelconfirm
then press F1. Next click on the link reading :
Returns or sets a String indicating which macro, event procedure, or user-defined function runs when the BeforeDelConfirm event occurs. Read/write.
This should give you all the details you need.

When you have looked into this a bit more then you will have everything you need, but you can post more questions if you still need to. I have given answers previously on how to handle things in sub-forms. Again, if you need further help then ask a new, specific, question making clear exactly where you are stuck.

Nov 14 '11 #7