[Solved] Form not saving the correct data

Creating and using forms

[Solved] Form not saving the correct data

Postby jarod997 » Thu Nov 09, 2017 8:59 pm

I have a form which saves data into a master table we'll call "Master List". There are three fields on this form, each are linked to other tables. These tables are similar in that they have an ID field and a text field. These four tables have their relationships set correctly.

In the form, when I'm entering data I can use the drop-down box and see all the text values "imported" from each related sub-table. My problem is when I select a value from one of these drop-down boxes and hit "Save Record" the drop-down boxes revert to "0" (numeric zero) and don't save the correct ID associated with the text I've just entered.

Sometimes when I'm moving from one drop-down box to the next, the entered value doesn't remain either. I can use the Next and Previous Record buttons to see other records - and for records which I've edited the correct information directly in the table, the correct ID number shows up in the drop-down. I just can't seem to get the values to save or update or something.

(I'd also prefer the drop-downs to show the related text instead of the ID's, but one problem at a time.)
Last edited by jarod997 on Mon Nov 13, 2017 2:32 am, edited 1 time in total.
Apache OpenOffice 4.1.4
Windows 10
jarod997
 
Posts: 3
Joined: Thu Nov 09, 2017 8:48 pm

Re: Form not saving the correct data

Postby Villeroy » Thu Nov 09, 2017 10:15 pm

My recipe for working list boxes:

Linked field: The field which stores the value (typically a foreign key)
Source Type: SQL
Source: SELECT "Visible Column", "Bound Column" FROM "Some Table" ORDER BY "Visible Column"
Bound Field: 1
Field #0 is the first column which is the visible one
Field #1 (the bound field) is the second column, typically the other table's primary key that is written into the linked field (this table's foreign key)

"Visible Column" may be a concatenation ("Surname"||','||"Forename") or the same as the the bound one when the visible value has to be entered (SELECT "A","A" FROM "X" ORDER BY "A").
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26867
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Form not saving the correct data

Postby jarod997 » Sun Nov 12, 2017 12:24 am

So in terms of displaying - when I press the "down triangle" on the combo-box, it will correctly list the text values, but when I "select" the value I want, it turns around and displays the ID number in the "base" of the combo-box.

WorldOfWarships.odb
Original DB attached.
(26.25 KiB) Downloaded 48 times
Apache OpenOffice 4.1.4
Windows 10
jarod997
 
Posts: 3
Joined: Thu Nov 09, 2017 8:48 pm

Re: Form not saving the correct data

Postby Villeroy » Sun Nov 12, 2017 1:14 am

A combo box is just a text box with auto-complete functionality. You have to use a list box. WHile in design mode, right-click the combo and choose Replace With > Listbox
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26867
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Form not saving the correct data

Postby jarod997 » Mon Nov 13, 2017 2:31 am

Yea, the list box with the SQL change to the binding worked for both items! Thanks.

As a side note, why can't I do the same thing by selecting "Table" (instead of SQL) and choosing the correct table and column to display?
Apache OpenOffice 4.1.4
Windows 10
jarod997
 
Posts: 3
Joined: Thu Nov 09, 2017 8:48 pm

Re: [Solved] Form not saving the correct data

Postby UnklDonald418 » Mon Nov 13, 2017 6:21 am

The HSQLDB database engine like most relational database engines uses the SQL language to manage the data stored in its tables. But using SQL statements for everything isn't very user friendly, so the OpenOffice developers created the Base front end as a GUI to help translate some of the user's intent into SQL commands. Apparently, the developers decided that a user designed Query with a Boundfield as the data source for List Boxes was the most flexible approach.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1179
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Forms

Who is online

Users browsing this forum: No registered users and 1 guest