What is a simple way to auto populate field in a form, from a DB in Libre Base?

Question

I am creating an Acquisition Management System for a Library in LibreBase (v7.5.4), thus far, I am at the following: A table called BooksDB containing the fields

1. Accession No (NUMERIC type) (also Primary Key)
2. Title(VARCHAR type)
3. Other information, etc.

Another table called AcqSys containing the fields

1. Acq ID (Primary Key)
2. Acquisition Date
3. Acquired From
4. Order Amount
5. Other information, etc.

Another table called AcqEnt containing the fields

1. AcqUEID (Primary Key)
2. Accession No (NUMERIC type)
3. Title (VARCHAR type)
4. Book Cost
5. Acq ID
6. Other information, etc.

The Form frmAcqSys is made of two tables (Main Form = AcqSys, and Sub Form = AcqEnt, relationship linked via Acq ID, with the view that one AcqID shall contain various AcqEntries)

Now, based on Accession No entered in AcqEnt subform, I want the AcqEnt.Title to auto-populated/fetch data from BooksDB table for the corresponding Title in BooksDB for the same Accession No (AcqEnt.Title to fetch BooksDB.Title based on AcqEnt.Acession No = BooksDB.Accession No).

I have been unable to change data type to SQL in a text field, so I am using a ListBox for AcqEnt.Title in subform AcqEnt, and using the following SQL:

SELECT "Title", "Title" FROM "tblBDB" WHERE "Accession No" = "AcqEnt.Accession No"

or

SELECT "Title", "Title" FROM "tblBDB" WHERE "Accession No" = 'AcqEnt.Accession No'

They are returning the following error:

SQL Status: S0022
Error code: -28

Column not found: AcqEnt.Accession No in statement [SELECT "Title", "Title" FROM "tblBDB" WHERE "Accession No" = "AcqEnt.Accession No"]

How do I do it? Is there a better/more efficient way to achieve the output?

Answer 1

You need to JOIN both tables, so that you can access all columns from both tables.

You need to add the table name to ambiguous column names as well

SELECT "tblBDB.Title", "AcqEnt.Title" 
FROM "tblBDB"  
JOIN "AcqEnt" ON "tblBDB.Accession No" = "AcqEnt.Accession No"