[Solved] Form not saving the correct data

Creating and using forms
Post Reply
jarod997
Posts: 3
Joined: Thu Nov 09, 2017 8:48 pm

[Solved] Form not saving the correct data

Post by jarod997 »

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

Re: Form not saving the correct data

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
jarod997
Posts: 3
Joined: Thu Nov 09, 2017 8:48 pm

Re: Form not saving the correct data

Post by jarod997 »

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 189 times
Apache OpenOffice 4.1.4
Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Form not saving the correct data

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
jarod997
Posts: 3
Joined: Thu Nov 09, 2017 8:48 pm

Re: Form not saving the correct data

Post by jarod997 »

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
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] Form not saving the correct data

Post by UnklDonald418 »

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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply