Ask Your Question

Reminder before record change if changes were made?

asked 2017-03-04 16:07:18 +0200

preks gravatar image

updated 2017-03-06 09:45:36 +0200

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
edit retag flag offensive close merge delete



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.

frofa gravatar imagefrofa ( 2017-03-06 10:51:58 +0200 )edit

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?

preks gravatar imagepreks ( 2017-03-06 15:14:17 +0200 )edit

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.

frofa gravatar imagefrofa ( 2017-03-06 17:51:15 +0200 )edit

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

preks gravatar imagepreks ( 2017-03-07 08:45:51 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-03-05 00:46:55 +0200

Ratslinger gravatar image

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.

edit flag offensive delete link more


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. :) Thank you veeery much for your help.

preks gravatar imagepreks ( 2017-03-05 11:52:35 +0200 )edit

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?

preks gravatar imagepreks ( 2017-03-05 14:04:24 +0200 )edit

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 gravatar imagepreks ( 2017-03-05 14:31:21 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2017-03-05 17:09:13 +0200 )edit

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

preks gravatar imagepreks ( 2017-03-05 19:01:58 +0200 )edit

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 gravatar imagepreks ( 2017-03-05 19:03:10 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2017-03-05 20:16:32 +0200 )edit

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.

preks gravatar imagepreks ( 2017-03-05 23:39:35 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2017-03-06 00:49:17 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-03-06 00:55:38 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-03-04 16:07:18 +0200

Seen: 218 times

Last updated: Mar 06 '17