Base - Simple Lookup / Autofill fields

User101 · March 31, 2023, 7:06pm

LibreOffice 7.5.1.2 / Linux x64

Hi everyone, I am coming from Access.

I’m trying to create a simple lookup field, to auto populate two fields:

TableA
ClientCode (PrimaryKey)
ClientName

TableB
ClientCode
ClientName
Units
Activity

Relationships
TableA - ClientCode → TableB ClientCode (Update & Delete Cascade)

In my form (database attached), I want to either type in the client code, or click/scroll/select and that value is entered into ClientCode, and also the associated ClientName field to auto populate, and both stored in TableB

In Access, I’d use a lookup/autofill wizard, on Base I’m struggling

Help appreciated

Database.odb (13.9 KB)

Villeroy · March 31, 2023, 8:32pm

Database.odb (13.8 KB)

User101 · March 31, 2023, 8:47pm

Thankyou @Villeroy !

How did you do that on the form? I right click and look at properties, but don’t see data source references to the query?

Villeroy · March 31, 2023, 9:57pm

The form wizard is a very stupid tool which covers only a small fraction of the available options. In particular, the form wizard can not produce any list boxes.
I let it create a simple form from the timesheet table. The foreign key “ClientCode” is represented by some numeric form control.

Ctrl+click the form control in order to select it without associated label.
Right-click>Replace with>Listbox
On the general tab: Dropdown=Yes (stupid default is no which gives a tall box with a scrollbar)
On the data tab: Source type=Query or SQL, Source= SELECT “visible text”, “primary key” FROM “other table” ORDER BY “visible text”, Bound Field=1 (which refers to the second field)