Reminder before record change if changes were made?

Hello to all,

I have created a CRM database to store my customers’ company details, personnel, visits on their companies. Although, there is something I would like to get fixed. Because this database will be managed by 2-3 people, I would like to avoid data loss. To get started:

Everything that is written in the form, even by mistake, (or maybe if someone erases data by mistake) is saved automatically on the record change. What I would like is to put a reminder before the record change to remind the user that somewhere he made changes. If he did it on purpose he will click “Yes” and jump to next record. If not, he will click “No”, the changes will revert and he will jump to next record.

Is there any way to do this without macro? If macro is the only way, could someone help me by creating the right macro for me?

Thank you in advance.

Customers sample.odb

Just a macro code solution someone gave to me and it works. It now undo changes if you click “No”. The only disadvantage is that it warns you even when you click on save button:

sub BeforeRecordAction(oEvent as object)
   dim oForm
   select case  oEvent.Source.implementationname
      case "" : oForm= oEvent.Source.model
      case ""
         oForm= oEvent.Source
             If MsgBox("Update record?", 4,"Confirm")=7 Then UndoRecord()
      case "org.openoffice.comp.svx.FormController" : oForm= oEvent.Source.model
      case else
         rem print oEvent.Source.implementationname
   end select
End Sub

sub UndoRecord
   dim document   as object
   dim dispatcher as object
   rem ----------------------------------------------------------------------
   rem get access to the document
   document   = ThisComponent.CurrentController.Frame
   dispatcher = createUnoService("")

   rem ----------------------------------------------------------------------
   dispatcher.executeDispatch(document, ".uno:RecUndo", "", 0, Array())
end sub

It’s an interesting problem. Question: Can it be done WITHOUT macros? A strategy might be to store all the info in a changed record temporarily as a PROPOSED record; perhaps in a dedicated table of ONE row (like a filter table). The contents of the FOCAL record (the UNchanged record) could be presented beside the proposed record on screen; the user would decide ‘do I want to authorize this change or not’. Only if they pressed YES, would an SQL TRIGGER run to update the FOCAL record. Idea only.

Yeah, I understood, really nice idea. It sounds perfect, especially when you create a new record, but it seems like it will be much complicated when you are going to edit existing data. What do you think?

Yes agreed, on reflection, I think it would be very complicated to handle the two cases seamlessly. Another idea (much easier to implement) would be to auto time-date stamp all amended (and new) records using a dedicated date-time column. The user (or supervisor) could then review all recent new & changed records to check no unintended changes have been made.

Yes, this is easier to implement indeed. But as you said it is double work for the user or the supervisor. I really appreciate your ideas, but I’ll leave it as it is because I’m bored enough to do changes. :stuck_out_tongue:

After spending hours on this, there is no easy solution. For what you want, it definitely requires macros.

The major problem is this - since there are three different tables in a single document, when clicking from one form/subform to another the previous table is already updated. This is without even changing companies. This does not mean it cannot be done (already have surmised ways & semi-functional) but probably requires a complete rethinking of the entire form.

Now, even with that, the problem still lies with having all this functioning through macros. Based upon a previous question of yours, just the filter was tough for you. Macros are thousands of times harder. Yeah, it is easy to have someone do it for you (if they are willing), but if ANYTHING changes, how do you fix the macro? Adding/deleting a field or Form/subform can disrupt the entire process. Even deleting a field and adding it back in can be a problem if the right naming isn’t used.

Hmmm… Yes, you are right about the second paragraph. And I’m really not willing to change the entire form’s structure. I prefer to leave it as it is. :slight_smile: Thank you veeery much for your help.

Oh, only one more tiny detail. In the Companies list box there is in the top, above all companies, an empty row. Do you know why does this row appear and how I can get rid of this?

And by the way, just for your info I just found that the solution for the macro is to put it in the global scope. I mean that you can put it in MyMacros and not in specific database’s library. That means that it would be for general use and not connected to your specific fields.

@preks You cannot get rid of the blank line in the listbox. Don’t know what you mean about the macros? You typically don’t put macros there unless needed at startup when no other libraries are available. Also they are not transferred when moving from machine to machine (like a post here).

Check this: “The global scope for code that serves a general purpose for a all text documents, all spreadsheets, all databases or even any type of document. For portability you can also store the above code in a specificly named library (not “Standard”) and use the macro organizer dialog to create an extension package (*.oxt). The extension package with the global scope macros can be installed with 2 or 3 clicks.”

I’ve made it on general purpose, I’ve exported it as .oxt and I will install it to the 2-3 more computers that will use the database. Really thank you very much for your effort! If you need I can upload the macro code of course.

@preks I’ve been working with macros for years but what you are talking about makes no sense. What SPECIFIC macro is it you are talking about? I’ve created extensions and they have their place but the vast majority of macros are typically kept within the documents modules. This eliminates the necessity of having to install the same macros over & over again - they travel with the document.

Check my question, I’ve edited it. He suggested it to not put it in the document module when I said an issue that was happening. I had placed it at the document module and it worked only on first time. After restart of the program the form was acting as no macro existed at all. Even if I could see it in both the Events tab and the Macro Organizer.
Now I placed it in MyMacros and exported it as .oxt so I can easily install it to other workstations. It works brilliantly.

OK. Just a couple of notes. The confusing part was this macro. It was somewhat mentioned in another question of yours but I never saw it. It was being pointed to by an event but it was removed by you prior to posting. That is why I was so confused by your comments about a macro. Second, I fully understand about where & why code is stored in different ( and actually it is more than what is mentioned).

I tried out the code and it works if you want confirmation EVERY SINGLE TIME even if you press the save. Not acceptable in my book. If you knowingly press the save you should not get yet another message asking if you want to save. Don’t know what you were doing wrong but this does NOT need to be in the “Global” libraries. Works fine time after time in the document library. Open/close the .odb and it makes no difference. Being there (where it should be) saves .otx headaches.

Bottom line is, if all is acceptable to you and it works the way you want, then no matter what anyone else thinks it’s OK. I am glad you have something that works for you.

Yes right, sorry about the confusion. I just never mentioned it here when I involved it again and I confused you.
Finally, I used another macro which does exactly the same job and in addition it undo changes if “No” is clicked. I saved it in global libraries on purpose again because I will just have to double click the .oxt in 3 workstations in order to get it work but I will never see the silly warning message about macros when program is starting.

I placed the code in my initial post and I’m glad to get any recommendation or improvement for the code from you. Well, this code has the same attitude as previous. It warns you even when you click on save button. If you think it’s easy to fix it and can’t help, I would prefer it. Otherwise, I can live with it…

In addition if you think that there is another reason, except from portability, like stability reasons etc. to put the macro in the document’s library instead of global library, you are welcome to give your opinion.