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

Creating and using forms

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

Postby Amelia B. » Sat Jul 31, 2010 7:19 am

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/forum/viewtopic.php?f=39&t=27745&start=0&hilit=subform+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 152 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.
Last edited by TheGurkha on Sat Sep 04, 2010 7:08 pm, edited 2 times in total.
Reason: tagged [Solved].
OpenOffice 3.2.1 on MacOS 10.5
Amelia B.
 
Posts: 25
Joined: Tue Jul 27, 2010 11:13 pm

Re: Subform data lost (or worse) when using next/last button

Postby RPG » Sat Jul 31, 2010 11:10 am

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
LibreOffice 6.3.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2204
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Subform data lost (or worse) when using next/last button

Postby RPG » Sat Jul 31, 2010 11:15 am

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   Expand viewCollapse view
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
LibreOffice 6.3.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2204
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Subform data lost (or worse) when using next/last button

Postby Villeroy » Sat Jul 31, 2010 1:36 pm

'Nother reference: viewtopic.php?f=21&t=12575#p78467
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
User avatar
Villeroy
Volunteer
 
Posts: 29928
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Subform data lost (or worse) when using next/last button

Postby Amelia B. » Sat Jul 31, 2010 6:28 pm

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
OpenOffice 3.2.1 on MacOS 10.5
Amelia B.
 
Posts: 25
Joined: Tue Jul 27, 2010 11:13 pm

Re: Subform data lost (or worse) when using next/last button

Postby Villeroy » Sat Jul 31, 2010 7:16 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 29928
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Subform data lost (or worse) when using next/last button

Postby Amelia B. » Sun Aug 01, 2010 5:43 am

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.
OpenOffice 3.2.1 on MacOS 10.5
Amelia B.
 
Posts: 25
Joined: Tue Jul 27, 2010 11:13 pm

Re: Subform data lost (or worse) when using next/last button

Postby Amelia B. » Sun Aug 01, 2010 9:31 pm

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   Expand viewCollapse view
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
Amelia B.
 
Posts: 25
Joined: Tue Jul 27, 2010 11:13 pm

Re: [Solved+Issue]Subform data lost when using next/last but

Postby RPG » Sun Aug 01, 2010 11:59 pm

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   Expand viewCollapse view
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 6.3.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2204
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Solved+Issue]Subform data lost when using next/last but

Postby Amelia B. » Tue Aug 03, 2010 7:09 am

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!
OpenOffice 3.2.1 on MacOS 10.5
Amelia B.
 
Posts: 25
Joined: Tue Jul 27, 2010 11:13 pm

Re: [Solved+Issue]Subform data lost when using next/last but

Postby RPG » Tue Aug 03, 2010 11:22 am

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
LibreOffice 6.3.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2204
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Subform data lost (or worse) when using next/last button

Postby Villeroy » Sat Sep 04, 2010 1:33 pm

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.

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.
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
User avatar
Villeroy
Volunteer
 
Posts: 29928
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests