[Solved+Issue] Subform data lost when using next/last button

[Amelia B.]

I have been working on a rather complex database form (with subform), which includes navigation buttons to move between records. As expected, when you use a navigation button to change records on the main form, it submits the current subform as well. The problem occurs if there is an error when submitting the subform (in my case, caused by not filling in values that cannot be null): the navigation continues and the data in the half-filled form is lost.

It gets more complicated. I am fairly certain that this same situation is what has been triggering OO to crash frequently on me while I've been working on this problem, and I have submitted an Issue to the tracker to that effect: http://www.openoffice.org/issues/show_bug.cgi?id=113554

If I'm right about the cause for the crashes, then I guess the good thing is that one work-around should hopefully fix both problems! But that's where I need your help...

I have been trying to write a simple macro, assigned to the "before record change" event of the main form, that would submit the subform, catch any errors, and cancel the record change if there is an error. The idea is similar to that recommended in this thread about a similar problem: http://user.services.openoffice.org/en/ ... orm+update

However, the resulting macro throws an error if it is called when the subform is empty (i.e., hasn't had any data entered). The SQL Exception is "Function sequence error." I haven't the foggiest idea what that means, but I'm pretty sure it has something to do with trying to insert a row before having defined anything about what that row contains.

I'm not familiar with the OpenOffice API, and I have spent far too much time today going around in circles on the byzantine API website, as well as reading through the OO Basic guide, trying to figure out a proper additional check to avoid that situation. I'm sure that the answer is simple, but only if you know what question to ask!

I'm attaching my simple debugging database with the imperfect macro included:

TestDB.odb

Sample database with embedded macro.

(14.71 KiB) Downloaded 266 times

(Please forgive the rather trivial table and field names -- this is purely for debugging, not actually trying to do something useful!). Both fields in the subform have non-null constraints, so filling in either one but not the other causes an SQL insert error.

Any suggestions greatly appreciated.

P.S. If you want to try to see if you can reproduce the crashing problem, remove the macro from the event handler or set it to always return True.

[RPG]

Hello

I think there is no need for the macro you use.
You use the macro as I can see for repairing a design error and it makes the problem bigger. The design error seemes me that you have no bookid in your subform.
Study a book about designing a database in OOo-base
You can find links here

Romke

[RPG]

Hello

After I have post the message before this one I remember me this.:

On several place I read people have problem with subforms. I have not the problem but I do not work a lot with data import. So I will test it a little more but what I did wrote looks me true.

I have the same error as you.

I think when you have a default date in your subform there is no problem more. The best is make this default date in your table design and not in your form design.

I have test now more
First delete the table "CheckOutActions"
then make it again without default values. The next line can help you with the default value for the date.

Code: Select all

Alter table "CheckOutActions"  alter Column "CheckoutDate" set default  Current_date

The reason I follow this way that actions defined in table design in OOo-base and in the database engine you can get problems who are difficult to understand for new users.
In this this link there are several post about default dates. Before you change your form see there what can be the best.

Romke

[Villeroy]

'Nother reference: http://user.services.openoffice.org/en/ ... 575#p78467
Take care of

db-menu:Edit>Database>AdvancedSettings... "Form input checks for required fields". Otherwise the form will reject to store the record with the missing date-value. With this option unset, the underlying HSQLDB takes care of the integrity, adding the field's default value.

[Amelia B.]

Hi Romke, VilleRoy,

Thanks for working on this one. A couple clarifications:

(1) There is a primary key on the underlying table, but it is set to auto-increment, and so is not shown in the form. The subform-mainform relationship connects the BookID to the CheckOutActions record.

(2) The database I uploaded is just an example, the real one is much more complicated and the fields that are causing problems aren't anything as simple as insert current date (they're actually list boxes -- pick the appropriate category for this record). If need be, I will create default values for any required fields, but that creates potential for data input errors -- which leaves me with incorrect data, but without the software knowing there's a problem. Actually, I will be more likely to just remove the non-null constraint, since it is easier to identify an empty field when reviewing the data than to identify a field that has been incorrectly left as the default. But I actually wanted to have the form issue a warning if the user hadn't filled in the required fields.

Hope that helps,
--AB

[Villeroy]

Try this: defaults.odb

[Amelia B.]

I'm afraid you'll have to explain in more detail, Villeroy. I can't figure out the connection between your sample database and my problem.

Are you suggesting that I should make the problem fields part of the main form instead of the subform, and then linking them to the subform? But that doesn't work when you have more than one subform record associated with the each mainform record. Even if the link is just in the form's filters, and not in an actual database relationship, you wouldn't be able to traverse the subform records, because all the ones with the non-matching fields would be filtered out.

[Amelia B.]

For anyone who's interested, I have got a functional, if rather inelegant, macro solving the problem. Any suggestions on an improved code would still be appreciated.

What's more interesting is I discovered that cancelling the main form record change event wasn't always enough to prevent OpenOffice from crashing. However, pausing the macro using a breakpoint in the code or a message box to the user, and then cancelling the record change, seems to do the trick. (This breakpoint occurs just after the user has pressed "OK" to OpenOffice's warning message about attempting to insert a row with null values.)
Definitely inelegant, but it'll do for now.

Code: Select all

Function CheckSubForm(oEvent)

	REM Called when iterating through the main form's records, to first test
	REM whether or not the subform can be successfully submitted.
	REM If not, the iteration action is cancelled, so that the current 
	REM record is not changed and data is not lost (and OO doesn't crash!).
	
	Dim EventSource
	Dim SubForm
	
	EventSource = oEvent.Source
	If NOT(EventSource.supportsService("com.sun.star.form.FormController")) then
		REM The event wasn't created by the form, e.g., initialization. 
		CheckSubForm = True
		Exit Function 
	End If
	
	SubForm = EventSource.getModel().getByName("SubForm")
	
	REM Need a check for when the subform is empty (hasn't been changed)
	REM Otherwise SQL throws a "Function sequence error" exception.
	
	REM For now, this is handled in the ErrorHandler, which examines the 
	REM error message to see which type of error was thrown.
	
	'If NOT(SubForm.isModified) Then
		REM This check doesn't work.  "isModified" always returns false,
		REM even when the data in the form has been modified.
	'	CheckSubForm = True
	'	Exit Function
	'End If
	
	On Error Goto ErrorHandler
		
		If SubForm.IsNew then 
			SubForm.insertrow()
		Else
			SubForm.updaterow()
		End if
		
		CheckSubForm = True
		Exit Function
		
	ErrorHandler:
		If (InStr(Error$, "Function sequence")) then
			REM The error was thrown because the subform is empty
			CheckSubForm = True 'okay to change record
			
		ElseIf (InStr(Error$, "vetoed")) then
			REM An SQL insert was vetoed by the database
			
			MsgBox "Either reset or complete the form.", MB_ICONINFORMATION,  _
				"Crash prevention box."
			REM For reasons I don't quite understand, but suspect are related to 
			REM the synchronization of different threads, the above msgbox is
			REM necessary to prevent OpenOffice from crashing.
			REM (Putting a BASIC IDE breakpoint at this point in the code has the 
			REM same effect.)
			
			CheckSubForm = False 'don't change record	
		Else
			REM Unexpected error
			MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")" 
			CheckSubForm = False
		End if
		
End Function

[RPG]

Hello

There you define your problem as changing from a record and then also a record of the mainform you make it more difficult as I think it is.When you define your problem as not having a value in a databox then the problem is smaller. But I must confess I donot fully understand the problem. I can not combine the post and the forms and macros
But as far I understand it I think this macro is doing what you expect.

Code: Select all

function Go_on (oEvent)
	Dim EventSource,oForm
	EventSource = oEvent.Source
	
	If NOT(EventSource.supportsService("com.sun.star.form.FormController")) then
		oForm= EventSource
	else 
		oForm= EventSource.model
	end if
	
	Dim SubForm,odatCheckoutDate
	
	SubForm=oForm.getbyname("SubForm")
	odatCheckoutDate=SubForm.getbyname("datCheckoutDate")
	if isempty(odatCheckoutDate.date) =true then
		Go_on=false
		msgbox  "Give a value in your box"
	Else
		Go_on=true	
	End If

end function

Romke

[Amelia B.]

Hi Romke,

That is of course another solution -- directly query and check each element on the subform to see if it should be submitted, rather than submitting the form and catching the error. I had been trying to avoid this, though, since the subform on my real-life application has nearly a dozen required fields (although most of them have default values). I think I might stick with my messy-but-functional method, because it doesn't require detailed knowledge of the form components, so I can change those later without messing up the macro.

Nonetheless, many thanks for your help!
--AB

P.S. Thanks to the moderator who set the title to "solved+issue" -- I wasn't sure how to express that I'd found a sort-of solution, but there was still an underlying problem!

[RPG]

Hello

I agree with you that you must not change a working version. After the posting of the solution I did found an other problem in my code. This problem makes that I should need also an on error part.

One thing is maybe important for other readers. You test on a wrong service, it is: "com.sun.star.form.FmXFormController". I did found this also later when I search better
But I think this is for your code also not important there it works.

The basic idea of my first comment and that of Villeroy is have all values ready. When you can not have ready some part store then values what can be enough. The same maybe in other: try to avoid errors in the next step.
This is not an idea of me but I have read it in books.
It is nice it is working for you.

Romke

[Villeroy]

I'm afraid you'll have to explain in more detail, Villeroy. I can't figure out the connection between your sample database and my problem.

Are you suggesting that I should make the problem fields part of the main form instead of the subform, and then linking them to the subform? But that doesn't work when you have more than one subform record associated with the each mainform record. Even if the link is just in the form's filters, and not in an actual database relationship, you wouldn't be able to traverse the subform records, because all the ones with the non-matching fields would be filtered out.

The forms are designed to set default values for each new record. The form/subform relation affects default values for new records as well as visible records based on based main form values. The form's property "New Records Only" is checked, so this type of form is designed to enter the default values and enter one record after the other with the default values set in the parent form. You can traverse the priviously entered records of this session.
You can also uncheck the form property which lets you see all the matching records AND set the same criteria as defaults for new records.