[Solved] Remove Boolean Check from multiple tables at once

[dreamquartz]

Code: Select all

REM  *****  BASIC  *****
REM Remove all "tPerson"."Check" 
Sub Main(e)
Const cReset = "UPDATE ""tPerson"" SET ""Check""=False"
  oActiveConnection = e.Source.Model.Parent.ActiveConnection
  oPrep = oActiveConnection.prepareStatement(cReset)
n = oPrep.executeUpdate()
PhoneFax 'Call Sub PhoneFax
End Sub

REM Remove all "tPhoneFax"."Check" 
Sub PhoneFax
Const cReset = "UPDATE ""tPhoneFax"" SET ""Check""=False"
  oActiveConnection = e.Source.Model.Parent.ActiveConnection
  oPrep = oActiveConnection.prepareStatement(cReset)
n = oPrep.executeUpdate()
EmailAddress 'Call Sub EmailAddres
End Sub

REM Remove all "tEmailAddress"."Check" 
Sub EmailAddress
Const cReset = "UPDATE ""tEmailAddress"" SET ""Check""=False"
  oActiveConnection = e.Source.Model.Parent.ActiveConnection
  oPrep = oActiveConnection.prepareStatement(cReset)
n = oPrep.executeUpdate()
NextRecord 'Call Sub NextRecord
End Sub

REM NextRecord
Sub NextRecord
Dim document As Object
Dim dispatcher As Object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:NewRecord", "", 0, Array())
End sub

The above code is based on the information provided by Villeroy (see viewtopic.php?f=13&t=88444#p415798)
The decision was made to add checkboxes to records in multiple tables (tPerson, tPhoneFax, and tEmailAddress), to trigger the User to verify data during data entry, because entries must be UNIQUE.
The specific tables were chosen because Persons do not change their GivenNames easily (other than start using or never providing ' nicknames', like 'Nick' or 'Nicolas'), or Surnames, other than when they get married or something similar. The same applies to Phone Numbers and/or eMail Addresses. Therefore the information in these specific tables helps the User to identify the Person who must be/has been entered.

TRIGGERS for the specific tables have been added to the DataBase, so the User will be notified when applicable.
The User is to investigate, and has to make an informed decision. This could based on a repeat of an eMail Address, and/or Phone Number, and/or GivenName & Surname.

To avoid the specific TRIGGER to fire, the statement like

"tPhoneFax"."Check"=FALSE

has been added.
The moment the User has made an informed decision, the User can Check the specific existing record, and will therefore be able to enter a copy of the present data.

To avoid the User having to reset all the Checks in the mentioned tables by hand, Villeroy was kind enough to guide me into the right direction.

Now I am dealing with multiple tables where Checks can be placed, and all need to be reset at once, so history can repeat itself.

I do not see what is wrong, but the attached screenshots show that the code does not finish.

Can anyone guide me in the right direction?

Dream

Screenshot from 2017-08-28 21-48-59.png (12.16 KiB) Viewed 3299 times

[Villeroy]

Please, read some beginners book on programming.

[UnklDonald418]

The error message “Object Variable Not Set” is telling you it can't make the assignment because there is something wrong with the statement on the highlighted line.
Working from right to left
cResetPersonCheck is defined on the previous line so that's not it.
PrepareStatement is spelled correctly so that's not it.
Which brings us to oActiveConnection. It is defined in Main but none of your Sub's know that.

[dreamquartz]

Please, read some beginners book on programming.

I agree totally with your comment.
However that is what I am doing for a long time already.
Information that I went through so far (shortlist):
- 0110GS-GettingStartedWithBase
- AndrewBase
- Base_tutorial
- BH40-BaseHandbook
- GettingStartedWithBase (OO)
-GS3508-GettingStartedWithBase (LO)
- 0113GS34-GettingStartedWithMacros )LO)
- BasicGuide_OOo3.2.0
- BH5009-Macros
- OO Basic
- DevelopersGuide_OOo3.1.0 (working through it)

One of the things I have difficulty with is the API.
Is there a good reference manual, other than the OO and LO websites?
I rather read a book, than scouring through electronic information.

So far, I still can not find proper information about programming this way.
Do you have suggestions about where to find more information that can be useful?

Dream

[dreamquartz]

The error message “Object Variable Not Set” is telling you it can't make the assignment because there is something wrong with the statement on the highlighted line.
Working from right to left
cResetPersonCheck is defined on the previous line so that's not it.
PrepareStatement is spelled correctly so that's not it.
Which brings us to oActiveConnection. It is defined in Main but none of your Sub's know that.

I must have missed that.
Thanks.
Will look into it.

Dream

[UnklDonald418]

One of the things I have difficulty with is the API.
Is there a good reference manual, other than the OO and LO websites?
I rather read a book, than scouring through electronic information.

When working with the API one book that I found particularly helpful is “Database Programming with OpenOffice.org Base & Basic” by Roberto Benitez.
http://www.lulu.com/shop/roberto-benite ... 68728.html
There is further information including the source code for the examples in the book at
http://www.baseprogramming.com/resources.html

[Villeroy]

One of the things I have difficulty with is the API.

If you do not understand the scope of variables and the use of parameters, then you can't to cope with simple APIs. This API requires that you master the chosen programming language before anything else.

[dreamquartz]

One of the things I have difficulty with is the API.
Is there a good reference manual, other than the OO and LO websites?
I rather read a book, than scouring through electronic information.

When working with the API one book that I found particularly helpful is “Database Programming with OpenOffice.org Base & Basic” by Roberto Benitez.
http://www.lulu.com/shop/roberto-benite ... 68728.html
There is further information including the source code for the examples in the book at
http://www.baseprogramming.com/resources.html

Appreciate the suggestions.

Dream

[dreamquartz]

One of the things I have difficulty with is the API.

If you do not understand the scope of variables and the use of parameters, then you can't to cope with simple APIs. This API requires that you master the chosen programming language before anything else.

Well I do have some experience (30+ years), but trying to program for OO/LO, turns out to be interesting.

Code: Select all

REM  *****  BASIC  *****

'Remove all ""Check" 
Sub Main(e)
Const cResetPersonCheck = "UPDATE ""tPerson"" SET ""Check""=False"
Const cResetPhoneFaxCheck = "UPDATE ""tPhoneFax"" SET ""Check""=False"
Const cResetEmailAddressCheck = "UPDATE ""tEmailAddress"" SET ""Check""=False"
  oActiveConnection = e.Source.Model.Parent.ActiveConnection
  oPrepPersonCheck = oActiveConnection.prepareStatement(cResetPersonCheck)
n = oPrepPersonCheck.executeUpdate()
  oPrepPhoneFaxCheck = oActiveConnection.prepareStatement(cResetPhoneFaxCheck)
n = oPrepPhoneFaxCheck.executeUpdate()
  oPrepEmailAddressCheck = oActiveConnection.prepareStatement(cResetEmailAddressCheck)
n = oPrepEmailAddressCheck.executeUpdate()
NextRecord 'Call Sub NextRecord
End Sub

Sub NextRecord
Dim oDocument As Object
Dim oDispatcher As Object
oDocument   = ThisComponent.CurrentController.Frame
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oDispatcher.executeDispatch(oDocument, ".uno:NewRecord", "", 0, Array())
End sub

This is how I solved it so far. I think there might be a more elegant way, but it works for now.

Dream

[Villeroy]

This way it takes days to solve tiny little mistakes.

[UnklDonald418]

The Sub NextRecord is not needed because as written the Updates to the tables "tPerson" , "tPhoneFax", "tEmailAddress" will affect every record in each of those tables.

[dreamquartz]

The Sub NextRecord is not needed because as written the Updates to the tables "tPerson" , "tPhoneFax", "tEmailAddress" will affect every record in each of those tables.

The DataBase has 3 TRIGGERS, related to this Macro.

Code: Select all

CREATE TRIGGER PUBLIC.EMAIL_CHECK BEFORE INSERT ON PUBLIC."tEmailAddress"
	 REFERENCING NEW ROW AS NEWROW
		 FOR EACH ROW BEGIN ATOMIC
			 IF EXISTS
				(
					SELECT
						 1
					 FROM
						(
							SELECT
								 NEWROW."EmailAddress" "EmailAddress"
							 FROM
								 PUBLIC."tEmailAddress"
						)"NewEmailAddress"
						 INNER JOIN
							(
								SELECT
									 "tEmailAddress"."EmailAddress" "EmailAddress"
								 FROM
									 PUBLIC."tEmailAddress"
								 WHERE
									 "tEmailAddress"."Check"=FALSE
							)"ExistingEmailAddress" ON
								 "NewEmailAddress"."EmailAddress"="ExistingEmailAddress"."EmailAddress"
				)
				THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT='The same EmailAddress has already been recorded. Please verify the Exisitng entry!';
			END IF;
END
CREATE TRIGGER PUBLIC.PHONEFAX_CHECK BEFORE INSERT ON PUBLIC."tPhoneFax"
	 REFERENCING NEW ROW AS NEWROW
		 FOR EACH ROW BEGIN ATOMIC
			 IF EXISTS
				(
					SELECT
						 1
					 FROM
						(
							SELECT
								 NEWROW."PhoneFaxNumber" "PhoneFaxNumber"
							 FROM
								 PUBLIC."tPhoneFax",
								PUBLIC."tTypeOfEmailPhoneFax"
						)"NewPhoneFaxNumber"
						 INNER JOIN
							(
								SELECT
									 "tPhoneFax"."PhoneFaxNumber" "PhoneFaxNumber"
								 FROM
									 PUBLIC."tPhoneFax",
									PUBLIC."tTypeOfEmailPhoneFax"
								 WHERE
									 "tPhoneFax"."FKTypeOfEmailPhoneFaxID"="tTypeOfEmailPhoneFax"."TypeOfEmailPhoneFaxID" AND
									 "tTypeOfEmailPhoneFax"."TypeOfEmailPhoneFax"
										 IN('Cell','Cell, business','Cell, private')AND
									 "tPhoneFax"."Check"=FALSE
							)"ExistingPhoneFaxNumber" ON
								 "NewPhoneFaxNumber"."PhoneFaxNumber"="ExistingPhoneFaxNumber"."PhoneFaxNumber"
				)
				THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT='The same PhoneFaxNumber has already been recorded. Please verify the Exisitng entry!';
			END IF;
END
CREATE TRIGGER PUBLIC.PERSON_CHECK BEFORE INSERT ON PUBLIC."tPerson"
	 REFERENCING NEW ROW AS NEWROW
		 FOR EACH ROW BEGIN ATOMIC
			 IF EXISTS
				(
					SELECT
						 1
					 FROM
						(
							SELECT
								 NEWROW."Surname" "Surname",
								NEWROW."GivenName" "GivenName"
							 FROM
								 PUBLIC."tPerson"
						)"NewPerson"
						 INNER JOIN
							(
								SELECT
									 "tPerson"."Surname" "Surname",
									"tPerson"."GivenName" "GivenName"
								 FROM
									 PUBLIC."tPerson"
								 WHERE
									 "tPerson"."Check"=FALSE
							)"ExistingPerson" ON
								 "NewPerson"."Surname"="ExistingPerson"."Surname" AND
								 "NewPerson"."GivenName"="ExistingPerson"."GivenName"
				)
				THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT='There is a Person who has the same Name. Please verify the Exisitng entry!';
			END IF;
END

The Macro is used in a form and linked to a Push Button on a Person Data Entry Form.
In stead of using 'New Record' as a selected 'Action' for a Push Button (which is also present on the form, and is normally used), now the User is able to remove all potential "Checks" that could be present in any of the 3 tables, as a result of a Duplicity, without worrying about it. After that a New Record is created. In a sense the specific Push Button has, next to a selected 'Action' of creating a New Record, the additional function to clear all potential "Checks".
This button is normally not used for data entry, but only in case of duplicity. The User has determined that it is acceptable to add a Duplicate for either table, and must therefore have the opportunity to do so.

Dream

[dreamquartz]

This way it takes days to solve tiny little mistakes.

See also answer to UnklDonald418.
The macro is not normally used. Only when required.

Dream