Page 1 of 1

[Solved] Form not saving the correct data

PostPosted: Thu Nov 09, 2017 8:59 pm
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.)

Re: Form not saving the correct data

PostPosted: Thu Nov 09, 2017 10:15 pm
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").

Re: Form not saving the correct data

PostPosted: Sun Nov 12, 2017 12:24 am
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 45 times

Re: Form not saving the correct data

PostPosted: Sun Nov 12, 2017 1:14 am
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

Re: Form not saving the correct data

PostPosted: Mon Nov 13, 2017 2:31 am
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?

Re: [Solved] Form not saving the correct data

PostPosted: Mon Nov 13, 2017 6:21 am
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.