[Solved] Reminder before record change if changes were made

Creating and using forms
Post Reply
preks
Posts: 10
Joined: Fri Mar 03, 2017 2:31 am

[Solved] Reminder before record change if changes were made

Post by preks »

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.
Attachments
Customers sample.odb
(17.07 KiB) Downloaded 248 times
Last edited by preks on Sun Mar 05, 2017 8:06 pm, edited 1 time in total.
LibreOffice 5.2.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Reminder before record change if changes were made

Post by Villeroy »

Requires macro code.
Quick and dirty:

Code: Select all

Function before_Record_Action(e) As Boolean
'oMRI = CreateUnoService( "mytools.Mri" )
' oMri.inspect(e)
	With com.sun.star.sdb.RowChangeAction
		if e.Action = .INSERT then
			sWord = "insert"
		elseif e.Action = .UPDATE then
			sWord = "update"
		elseif e.Action = .DELETE then
			REM there is a built-in warning message
			before_Record_Action = True
			exit function
		endif
	End With
	iAnswer = Msgbox("Are you sure you want to "& sWord &" this record?", 36, "Confirmation")
	before_Record_Action = (iAnswer = 6)
End Function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
preks
Posts: 10
Joined: Fri Mar 03, 2017 2:31 am

Re: Reminder before record change if changes were made

Post by preks »

Villeroy thank you for your prompt answer. 2 issues:

1) Your macro worked like a charm except one thing. When I changed something in the form and then clicked the drop down list it immediately asked if if I would like to update the record. I pressed "Yes" but for some strange reason the warning message would appear for second time. By pressing "Yes", it would do the job successfully. It happened all the times, needed 2 times to press "Yes" in order to save my record. I have this structure:

FilterForm
SubForm
SubSubForm1
SubSubForm2

I placed it on the Events tab (Before record change) in the SubForm's properties. Did I do anything wrong?

2)The second issue has anything to do with YOUR macro. It happened with a previous macro I had tried. I closed, saved and tried to test the macro again some hours later but anything would happen like no macro existed. I checked and all things were in their right position. The macro command in the Events tab of the SubForm, The Module existed normally in the Macro Organizer. Don't know why this happens. Do you have any idea?
LibreOffice 5.2.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Reminder before record change if changes were made

Post by Villeroy »

Indeed. For some mysterious reason the event is called twice.

On top of the module:

Code: Select all

Global g_Stop as Boolean

Code: Select all

Function before_Record_Action(e) As Boolean
'oMRI = CreateUnoService( "mytools.Mri" )
' oMri.inspect(e)
if g_Stop then
	g_Stop = False
	Exit Function
else
	With com.sun.star.sdb.RowChangeAction
		if e.Action = .INSERT then
			sWord = "insert"
		elseif e.Action = .UPDATE then
			sWord = "update"
		elseif e.Action = .DELETE then
			REM there is a built-in warning message
			before_Record_Action = True
			exit function
		endif
	End With
	iAnswer = Msgbox("Are you sure you want to "& sWord &" this record?", 36, "Confirmation")
	before_Record_Action = (iAnswer = 6)
endif
g_Stop = True
End Function
Most people do not understand that there are 2 differnt scopes where Basic code may be stored.

1. Any ODF document or template is the scope for macros that are tightly related to the specific document. The code is embedded like the forms and reports of a database document. If your code refers to specificly named objects like "MyForm", "MySheetName" or "MyPageStyle", this is the right scope for your code.

2. 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.
2.1.1 Code that is stored in subfolder "Basic" of the user profile folder is shown under "My Macros".
2.1.2. Some extensions install Basic code in the user profile's subfolder "uno_packages". These libraries also appear under "My Macros".
2.2.1. Code that is stored in subfolder "share/Basic" of the installation folder is shown under "OpenOffice Macros" or "LibreOffice Macros" respectively.
2.2.2. Some extensions install Basic code in the installation subfolder "uno_packages". These libraries also appear under "OpenOffice Macros" or "LibreOffice Macros" respectively.

The above macro can be installed for the global scope. It does not assume any specific form or database. It is supposed to work with any database form. For portability reasons you can install it in the database documents as well but the embedded code is usable with the containing database only. It is no longer in the macro organizer after closing the 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 (variant 2.1.2 or 2.2.2).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
preks
Posts: 10
Joined: Fri Mar 03, 2017 2:31 am

Re: Reminder before record change if changes were made

Post by preks »

Your solution worked like a charm! I understood the scopes thing. I really was not aware about something like that. I exported the .oxt file too, so it is portable.

Only one little improvement I would like about the macro code. Would it be easy to revert changes when "No" is pressed? Because at the time it just ignore the changed fields so they keep their changed values.

It's just a detail, so if it is not something really easy we can leave it as it is.
LibreOffice 5.2.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Reminder before record change if changes were made

Post by Villeroy »

preks wrote:Your solution worked like a charm! I understood the scopes thing. I really was not aware about something like that. I exported the .oxt file too, so it is portable.

Only one little improvement I would like about the macro code. Would it be easy to revert changes when "No" is pressed?
I could not find out quickly.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Reminder before record change if changes were made

Post by Arineckaig »

for some strange reason the warning message would appear for second time
According to his invaluable book "Database Programming OpenOffice.org Base & Basic" by Roberto Benitez the Before Record Action and the Before Record Change events in Base tend to trigger twice: first by the Form Controller Service and secondly by the DataForm Service. The book does also offer coding to test for the source of each event so as to avoid duplication if not required.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
preks
Posts: 10
Joined: Fri Mar 03, 2017 2:31 am

Re: Reminder before record change if changes were made

Post by preks »

Thank you very much both! I will mark it as solved!

@Arineckaig the problem was solved by Villeroy by importing some commands to my macro but thank you for your information!!
LibreOffice 5.2.5 on Windows 10
Post Reply