Page 1 of 1

ListBox with foreign key

Posted: Sun Feb 11, 2018 6:17 pm
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.

Re: ListBox with foreign key

Posted: Sun Feb 11, 2018 7:34 pm
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)

Re: ListBox with foreign key

Posted: Sun Feb 11, 2018 7:49 pm
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).

Re: ListBox with foreign key

Posted: Sun Feb 11, 2018 8:17 pm
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.

Re: ListBox with foreign key

Posted: Sun Feb 11, 2018 8:23 pm
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??

Re: ListBox with foreign key

Posted: Sun Feb 11, 2018 8:25 pm
by Villeroy
Click [Post Reply], scroll down and upload the Base document.

Re: ListBox with foreign key

Posted: Mon Feb 12, 2018 7:25 am
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).