ListBox with foreign key

Creating and using forms

ListBox with foreign key

Postby Arnaugir » Sun Feb 11, 2018 6:17 pm

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
Arnaugir
 
Posts: 3
Joined: Sun Feb 11, 2018 6:11 pm

Re: ListBox with foreign key

Postby Villeroy » Sun Feb 11, 2018 7:34 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26874
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: ListBox with foreign key

Postby Arnaugir » Sun Feb 11, 2018 7:49 pm

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
Arnaugir
 
Posts: 3
Joined: Sun Feb 11, 2018 6:11 pm

Re: ListBox with foreign key

Postby Villeroy » Sun Feb 11, 2018 8:17 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26874
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: ListBox with foreign key

Postby Arnaugir » Sun Feb 11, 2018 8:23 pm

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
Arnaugir
 
Posts: 3
Joined: Sun Feb 11, 2018 6:11 pm

Re: ListBox with foreign key

Postby Villeroy » Sun Feb 11, 2018 8:25 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26874
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: ListBox with foreign key

Postby UnklDonald418 » Mon Feb 12, 2018 7:25 am

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.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