ListBox with foreign key

Creating and using forms
Post Reply
Arnaugir
Posts: 3
Joined: Sun Feb 11, 2018 6:11 pm

ListBox with foreign key

Post by Arnaugir »

Hello,

Please I need help with using a listbox control with a foreign key poiting to another table.

What I want to achieve: in my table "costumer" there is a "country" column (INT) which is a 1-to-n relation to an "Id" (primary key) column in another table ("countries").

I have a form with a listbox control whose SQL content is: SELECT `countryname`, `Id` FROM `countries` ORDER BY `countryname` ASC. The bound field is set to 1. I assume then that it will display the countryname in the list but the Id value is what will be actually set in the field.

The list populates perfectly with the values. However when I add a new row, or I try to edit another one, an error is thrown saying that it was unable to update the current register. If I click on "more", it says: SQL state: HY000.

Any ideas? Thank you in advance.
OpenOffice 4.1.3.2 on Win8.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: ListBox with foreign key

Post by Villeroy »

Linked field: the form's field representing the foreign key
Source type: SQL or Query
Source: Your valid SQL statement or the name of the query which stores the valid statement
Bound field: 1 (the visible field is 0)
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
Arnaugir
Posts: 3
Joined: Sun Feb 11, 2018 6:11 pm

Re: ListBox with foreign key

Post by Arnaugir »

Villeroy wrote:Linked field: the form's field representing the foreign key
Source type: SQL or Query
Source: Your valid SQL statement or the name of the query which stores the valid statement
Bound field: 1 (the visible field is 0)
that's exactly what I have, and an error is thrown (that's why I am asking).
OpenOffice 4.1.3.2 on Win8.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: ListBox with foreign key

Post by Villeroy »

Are you sure, the error is raised by the list box? It could be any form, subform, listbox or combo box. Replace the listbox with some other control where you can enter some ID value directly.
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
Arnaugir
Posts: 3
Joined: Sun Feb 11, 2018 6:11 pm

Re: ListBox with foreign key

Post by Arnaugir »

Villeroy wrote:Are you sure, the error is raised by the list box? It could be any form, subform, listbox or combo box. Replace the listbox with some other control where you can enter some ID value directly.
You're right I just tried to change it for a regular text box. If I link the text box with the field which has the foreign key, it displays the name from the table with the primary key (in the example before, "countryname"). If I try to change it, it still throws the same error.

What am I doing wrong??
OpenOffice 4.1.3.2 on Win8.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: ListBox with foreign key

Post by Villeroy »

Click [Post Reply], scroll down and upload the Base document.
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
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: ListBox with foreign key

Post by UnklDonald418 »

when I add a new row, or I try to edit another one, an error is thrown saying that it was unable to update the current register
here are two things to check
The "costumer" table you are attempting to update has a primary key set to auto increment.
One of the controls on the form is attached to the primary key of the "costumer" table (it can be hidden but it must exist for the form to make changes to the table).
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