LibreOffice Base - how to get default values into form fields?

English
, , ,
#1

LibreOffice 6.3.5.2 on Linux. LibreOffice Base file using embedded Firebird.

The attached database has one form (so far).

  1. How do I set up this form so that the values in the text fields default to 0 instead of blank – both when the form is first displayed, and when the user clicks the Reset button (lower left of form)?

  2. In some cases, when I manually enter a value into the Year field (upper left of form) and then press the Tab key to move to another field, the value in the Year field changes unexpectedly. For instance:
    a. Open the form.
    b. Use the mouse to highlight the whole contents of the Year field.
    c. Type 2010 into the Year field.
    d. Press the Tab key.
    e. The value in the Year field unexpectedly changes to 2012, but the user would expect the value to stay at 2010.
    Is there an explanation for this question #2?

Some of the code in the macros is fragments of my previous attempts to resolve those 2 questions, mostly #1 but also #2. Don’t take the non-functioning parts of the macros very seriously.

Edit: My answer to my own question has an LO Base file with workaround for part 1 & solution for part 2.

#2

Hello,

Here is the table def & opened form:

image description
image description1099×336 41.9 KB

There are no text fields on the form or in the table. The form already defaults to a display of zero in the fields. Not certain why you would use a field type of Double for quantity. Better as Integer.

For the Year field, on the form it is a Formatted field. You assigned this to the table field Year_Month and that is expecting a Date to be entered not just a year which you have restricted the field to 4 digits. If you are attempting to create something with you form fields Year & Month you cannot assign either to a date field in the table.

Edit:

Can also add a new record in the table and the fields do default to zero.

You form seems to have multiple issues. Do not know your intentions but this is not the way I would create a form to data input.

Edit 2020-04-28:

Possibly this may help. Have eliminated most of the macros. Incorporated the form sizing. The new form is Basic Form and the default values come from the table definition.

Just a simple form for defaults to be displayed. Can change values to other than zero also.

Second Edit 2020-04-28 :

Changed the sample again just to briefly illustrate the ability to take the date field and transform it into the list box and numeric field you have. There is no need for SQL or VB. Only did the read side (triggered by After record change on internal form). New and update should be easy to implement. New routine is RecChange.

Sample — ** Removed per request of OP **

Don’t see any need to use SQL

End 2nd edit

Disabled & commented out many of your macro elements in doing some testing. Since discovering the Resize macro modified so drastically, figured there may be more problems in your other macros. Decided to abandon and just create a new form.

#3

@cme_linux,

From glancing a your macros, there is a different macro for Form sizing which includes positioning on screen found here → Adjust size and placement of a form in Base

Edit:

For setting a default value for a table field, you can do this with SQL. See this post → How to autofill timestamp field with Firebird. The sql updates a timestamp but other field types apply. There is also a link to the Firebird documentation there.

#4

@Ratslinger,

Thanks for your reply.

  • In my question, I used the term “text field” generically. Yes, more specifically, I selected the “Formatted Field” control type for numeric entry.
  • When I open the form, the default values for the form fields show momentarily, then blank out before the form is fully displayed.
    These default values are set as follows:
    In main LibreOffice Base screen, right-click the form & choose Edit…
    Right-click the entry field & choose Control Properties…
    On General tab of that dialog box, edit “Default value” field.
    I hope for a way that the default values will stay visible when the form opens.
  • The user may need to enter fractional quantities A, B, C. That’s why I chose type Double for those fields.
  • I intend to edit my question with updated database file that shows SQL UPDATE query to combine year & month data into Year_Month database column.
#5
  • I tried revised ResizeTheForm subprogram (which I will include when adding modified database into my original question) but it causes type mismatch error (as soon as the form opens) in EnteredDate$ = line which collects values from 2 form controls in DateIntoDatabase subprogram. Apparently, event handler my_YearFocusAway is running when the form isn’t fully initialized. Since this is worse than what I was getting with previous ResizeTheForm, I have left the original ResizeTheForm for now.
  • I’ve added an auto-incrementing primary-key column to Table_DataByMonth_Input table because of same issue (or issue similar to this) pointed out in final comment to this answer - Firebird generates error message if asked to update a primary key, even if the new value isn’t the same as any existing value in that column.
  • I still hope for solution to year value unexpectedly incrementing.
#6

Do not see the problem you specify. Values always are visible when I open the form and for as long as the form is open and no matter what is done on the form.

As for field types and other descriptive elements, please use specific terms as it can make a big difference in answering questions.

You also do not mention which Linux distro you are using. My testing was with Ubuntu 18.04 Mate with LO v6.4.3.2 from TDF.

#7

Thanks for looking at this again. Distro here is Linux Mint 18.3. I installed LibreOffice using the .deb files from LibreOffice.org’s download page.

I had tried 6.4.3.2 but as soon as I opened Base (no .odb file), I got the << LibreOffice has crashed and will now recover your documents >> dialog. So I reverted to 6.3.

Also, in latest version of .odb (filename chrono_records2.odb), now when I open the form & use the Tab key to cycle through the fields, the 4 numeric fields get the value 0 as I tab past them, even though I gave the GUI a default value of 1 after reading your first reply.

#8

@cme_linux,

It appears you did not understand my statements in the answer. Just as you have no Data field assgned to the “Month” list box, there should be none assigned to the “Year” formatted field either. If you remove that, all seems to work for the date to be updated.

#9

@cme_linux,

Have also debugged the ResizeTheForm issue. First you changed the code from what is posted in the link. Also you did not follow the instructions:

This second sub is then attached to the Open Document event of the form.

Changing those items the form opened correctly with the macro.

#10

@cme_linux,

Modified my sample in answer to show there is no need for use of SQL in what you are trying to do.

#11
  1. In the .odb file, versions 1 & 2, I inadvertently left personal information that I don’t want published. I’ve removed that info from the version 3 that is in my answer to this question. Can you please delete your existing modified .odb files. To show the techniques that you proposed, you may make your changes again to my version 3.
  2. Yes, I changed the ResizeTheForm code. The provided code had an option to reposition the window, which I didn’t need, so I removed it.
  3. Thank you for pointing out Open Document. Steps to access this:
  • In main LO Base window, right-click the form & choose Edit…
  • In resulting window, choose main-menu item Tools > Customize…
  • On Events tab, assign macro to Open Document event.

I had been aware only of the following:

  • In same Edit window as above, right-click a control on the form & choose Form Properties…
  • On Events tab, assign macro to “When loading”.
#12
  1. Assigning ResizeTheForm macro to Open Document event (as opposed to When loading event) didn’t correct any issues for me.
  2. In ResizeTheForm macro, the Wait statements and second setPosSize() statement didn’t seem to be needed. (The form seems to be resized just the same without them.)
#13

Six. There is an important difference between Open Document and When loading: When loading happens only when the end-user opens the form to enter data, but ‘Open Document’ also happens when the admin begins to edit the form. In the 2nd case, resizing the containing window (which is what ResizeTheForm really does) to the same size as for the end user is undesirable:

  • the admin needs the toolbars (which are hidden by a macro when the end-user opens the form), but toolbars decrease the space available for the form within the containing window;
  • the admin often will want to edit the form at a larger scale factor than what the end-user sees, which means the end-user window size would put some controls outside of the initial edit window.

Also, most events that will be used are set up within “Form Properties…”. So to keep the event definitions in fewer places, and to avoid interfering with the admin’s editing of the form, I left ResizeTheForm assigned to When loading.

#14

Seven. I’m not surprised that much or all of the SQL that I was using might not be needed. That falls in the category of

Some of the code in the macros is fragments of my previous attempts to resolve those 2 questions, mostly #1 but also #2. Don’t take the non-functioning parts of the macros very seriously.

Now that I have the solution & workaround as mentioned for this question, I can remove unneeded SQL as I continue to develop this database.

#15

Eight. Despite

There is no need for SQL or VB.

… the RecChange event handler is written in BASIC.

Nine. The latest version of Chrono_Modified.odb has a few usability problems:

  • The year & month don’t default to the correct ones, but in chrono_records3.odb they do.
  • For the end user, the zoom factor should be standardized when the form opens, and there shouldn’t be toolbars that are meant for editing the form itself. The code to deal with both of these display issues, I copied-and-pasted from public sources; the code for removing toolbars is from TDF’s LO Base PDF manual.

Ten. I plan to close this question once you’ve uploaded a version of Chrono_Modified.odb that’s based on chrono_records3.odb.

Eleven. Despite the misunderstandings, it was extremely valuable to be able to communicate with someone who could help me troubleshoot. I appreciate your (@Ratslinger’s) work on this Q&A site.

#16

VB (M$) is not LO (Star) Basic. Refer → Option VBASupport Statement

There are easy methods to bypass a macro when in Edit mode to do Admin things:

Rem If Design mode, just exit the routine
Dim xCurrentConroller As Object
xCurrentController = thisComponent.CurrentController
If xCurrentController.isFormDesignMode Then
    Exit Sub
End If

So much more not worth getting into.

#17

The answer to part 1 of my question is that the 4 entry fields in question (3 Formatted Fields and a Numeric Field) need to be set to Left Alignment for their value to be shown as soon as the form is opened. I had them set to Right Alignment. LO 6.4 might have this issue corrected; if I can find out why LO 6.4 was crashing for me, then I could use LO 6.4 and I might be able to set these fields to Right Alignment successfully.

The answer to part 2 of my question is, as @Ratslinger said,

there should be no Data field assigned to the “Year” formatted field

Apparently, LO Base automatically reading the field value from the database table was conflicting with my code that was reading what the user had typed into that field.

I find that, usually, assigning a Data field (i.e., a field in a database table) at all to an end-user-editable form field is a bad idea, since the person who designs the form either won’t want the default value of the form field to be exactly the same as an existing value in the database, or will want the value entered by the end user to be validated before being written to the database.

Updated database with workaround to part 1 & solution to part 2: chrono_records3.odb

Edit: Since I don’t have enough reputation to accept my own answer, I will try to move this answer to be a comment to my own question, then I will accept @Ratslinger’s answer which led to my finding out this info.

Edit 2: I can’t make this answer into a comment either (without more editing than I want to do) - this answer has over 1000 characters, so can’t be converted to a comment.

#18

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.