Can I use one List Box to write in two different fields from different tables?

[jderica]

I have one List Box in my Table_1 form, that is set to “Type of list contents: SQL” which lists an “Address”, and then writes the ID_Key of an entry from Table2, to a foreign key column in Table1.

Now I want to use the same list box to also write an entry into Table_1, column “Address”, which concatenates (I think is the term) the columns mentioned in that SQL code, as a text for later search function use.

I tried using some code found, but I’m getting a syntax error. The code is this, and I’ve pasted it in the list box SQL command:

SELECT “Name_Street” || ', nr. ’ || “Number_Street” || ', ’ || “Area_Code”, “ID_ADR” FROM “Table_2”

INSERT INTO “Table_1” VALUES (“Address”)

SELECT COALESCE(“Name_Street”, ‘’) || ', nr. ’ || COALESCE(“Number_Street”, ‘’) || ', ’ || COALESCE(“Area_Code”, ‘’) FROM “Table_2”

The error is:

SQL Status: HY000
Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

[Ratslinger]

Hello,

Can see no reason to duplicate information. This concatenated information can always be retrieved using SQL for your purposes.

In a list box, Insert(used to insert new records), Update(used to update existing records) nor multiple statements will work. For SQL you are only allowed Select statements. A list box (and other controls) are only meant, on a form, to access a single table - the one tied to the form.

You can do other things using macros but that is an entirely different direction and requires a steep learning curve.

[jderica]

You are right about having no reason to write duplicate information, especially since I was using the concatenated data only for a drop-down record select list box.

I’ve experimented with that record select list box and found that this works perfectly:

SELECT “Table_2”.“Name_Street” || ', nr. ’ || “Table_2”.“Number_Street” || ', ’ || “Table_2”.“Area_Code”, “Table_1”.“ID” FROM “Table_1”, “Table_2” WHERE “Table_1”.“FK_ADR” = “Table_2”.“ID_ADR”