Update Record with Listbox Selection

I have MySQL on Windows and I am using LibreOffice as a front end. I have a base table (with primary key and two foreign keys). I have two smaller tables with two columns each for selecting additional attibutes. The smaller tables have primary keys. I have defined a 1 to n relation between the tables.

I can populate the listbox on my form. The listbox reflects the text value, when it is already defined in the principle table. I can select a new value in the list box, but I receive an error message when I try to save the record. Simply, “cannot write the current record”. If I remove the listbox then I can save a changed or new record.

Any idea what I have done wrong? Or how to get more information about what field is giving problems?

It is not clear what you are doing with this listbox. It appears this listbox is for one of the “smaller” tables and the contents are items from the main table? If so, why the duplication? Is this “smaller” table the only thing you are dealing with on the form or are all three tables on the form? Also, since it can be removed & the record updates, what is its’ purpose? A little more clarity please.

You could try to enable logging of run commands (queries) in the MySQL Server and try this again the way it simply fails, then try with the way it worked, shut down MySQL and check the commands in the log, they might reveal why one fails and one succeeds. MySQL :: MySQL 5.7 Reference Manual :: 5.4 MySQL Server Logs assumes MySQL 5.7 and explains it, among other loggings. Just don’t keep this on for longer than you need to cuz it’s a performance sink.

Tables: I would like the user to select from a list of attributes rather than make one up each time. So, there are two tables for the selection lists. No duplication and no users mispelling the attributes. (so I store in the database “1” for substrate and we all know, by looking in the lookup table, that it is really “canvas”, 2= paper etc)

I have tried MySQL logging and either I have done it wrong or the query is not getting to MySQL and LibreOffice is puking on the query. Is there a way to see the query that LibreOffice is generating? It smell like a data type mismatch or hinky raltionship.

I dont think I have the logging on. Let me play with that and see if it has some good information. Thanks for the suggestion.

Ok, query logging is on. If I open the form I can see the query. If I modify a record it will update–I can see the “Inser Into”–as long as I do not select a value from my listbox. If I select from the list box LibreOffice complains, but there is no movement in MySQL. So it looks like the query is not getting to MySQL.

I believe I now understand your configuration. The primary table holds the keys but the listbox displays the secondary table names. The possible problem is how you have set up the listbox SQL. The SQL should retrieve both the Name and ID. The name is used for the display (first item in the SQL select) and the ID will be placed in the primary table field. This is done by setting the Bound Field in the listbox properties Data tab to a 1 (the second item in the SQL select).

To further clarification here is a sample: ListBoxAltSave.odb

Thanks. I think I have that part right: I am seeing the list correctly (column 0) and I am using column 1 as the bound column. Column 1 (autoincrement, Primary key). I can manipulate the list and select a new value. I cannot save the record, though. I have turned on the MySQL query logging and I can see the SELECT as the form loads, but I do not see and UPDATE or ALTER. Which makes me wonder is LibreOffice is puking before sending the query to MySQL.

Just copied tables (less records with ID = 0; MySQL doesn’t like) and the form from the sample noted above into a MySQL .odb and have no problems adding or changing records. Note: running LO v5.3.0.3 with Linux Mint 18 and MySQL v5.7.17

The listbox query: SELECT CategoryTxt, CategoryID FROM artdatabase.tblcategory

I dont think MySQL is the problem. I must be doing something stupid in LibreOffice.

Primary table: Say you have a table called addresses and in it you have a field called City ID to select a city from a list of cities.

# Address, City ID, Postal_code_ID, Country_Code_ID

1 333 Main st, 2, 33, 44   (Melbourne)
2 444 Broadway, 1, 345, 69   (New York)

Lookup table: The list of cities has two fields and looks roughly like this:

# City

1 New York
2 Melbourne
3 Paris

Form: So you create a form called addresses_form. In Form Properties you set the Data Tab’s Content to addresses (the table you are editing).

List Box: And you add to this form a list box control called something like City ID.

Then for this list box, in your Properties: List Box dialog:

  • Data field should be something like City ID, where City ID is the name of the foreign key (integer) that you are editing with this list box.

  • Type of list contents should be Sql.

  • List content should be SQL code that returns at the very least 2 fields, where the first field is what displays in the list box, and the 2nd field is the key that gets stored into your table (normally this is an integer). NOTE! this is backwards. Not ID, Text, but rather Text, ID. (I think it’s too bad this was designed this way, but we’re stuck with it this way now.)

  • Bound field should be 1 in this case (zero based, saying that when you select an item from the list box, the value stuffed into the table is in field 1 (in this case the integer that represents a given city name).

You can also set Type of list contents to Query and define a query to return the 2+ list box fields, but I don’t often do it that way unless the query will be used by more than one list box.

(BTW, credits go to Ratslinger, as he taught me some of this.)

Well, this might be the problem. I built the form based on a query rather than on the fields from the table. I thought I needed to do that to establish the relationship between the tables. Wrong. That would explain why it puked when I tried to update modified fields. I am going to play with this for a while to verify that that was the problem. Thanks everyone for your help.