[Solved+Issue] Subform data lost when using next/last button
[Solved+Issue] Subform data lost when using next/last button
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: (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.
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: (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.
Last edited by TheGurkha on Sat Sep 04, 2010 7:08 pm, edited 2 times in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
OpenOffice 3.2.1 on MacOS 10.5
Re: Subform data lost (or worse) when using next/last button
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
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
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: Subform data lost (or worse) when using next/last button
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.
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
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
In this this link there are several post about default dates. Before you change your form see there what can be the best.
Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: Subform data lost (or worse) when using next/last button
'Nother reference: http://user.services.openoffice.org/en/ ... 575#p78467
Take care of
Take care of
myself wrote: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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Subform data lost (or worse) when using next/last button
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
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
OpenOffice 3.2.1 on MacOS 10.5
Re: Subform data lost (or worse) when using next/last button
Try this: defaults.odb
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Subform data lost (or worse) when using next/last button
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.
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.
OpenOffice 3.2.1 on MacOS 10.5
Re: Subform data lost (or worse) when using next/last button
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.
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
OpenOffice 3.2.1 on MacOS 10.5
Re: [Solved+Issue]Subform data lost when using next/last but
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.
Romke
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
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: [Solved+Issue]Subform data lost when using next/last but
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!
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!
OpenOffice 3.2.1 on MacOS 10.5
Re: [Solved+Issue]Subform data lost when using next/last but
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
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
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: Subform data lost (or worse) when using next/last button
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.Amelia B. wrote: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.
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice