Form-building problem: drawing data to a subform from two sources

I’m having trouble making a form in which I can edit the information of two tables while drawing information from three. I am designing a personnel database with information on the different projects people are involved with. The problem arises when I want to design a form which displays and allows editing all the relevant info about a given project. Simply put: In a subform, how do I replace PersonID field from one table with PersonName field from another table? I think the fix shouldn’t be that hard but I just can’t seem to get my head around it.

The tables:

  • tbl-PersonnelInfo (fields PersonID, Name, Address, etc.)
  • tbl-Projects (fields ProjectID, Description, StartDate, etc.)
  • tbl-PersonnelProjects (fields PersonnelProjectsID, PersonID, ProjectID, JobDescription, etc.)

Relationships between those are logically PersonID-PersonID and ProjectID-ProjectID.

HSQLDB database, LibreOffice Base (x64), working in Windows 10 pro.

What I have tried so far:

  • I created a main form drawing data from tbl-Projects, with a subform containing tbl-PersonnelProjects data (shown in a table control field for convenience). This basically works just fine, but since the tbl-PersonnelProjects table includes only the numerical PersonID it is not sensible for usage in this case. I want to add/edit/remove people in the project one by one by name. Preferrably from a drop-down list.
  • I created a query picking data from both tbl-PersonnelProjects and tbl-PersonnelInfo which remedies the above problem, and show that in the subform instead of tbl-PersonnelProjects. However, in this case I can not edit that info. I suspect it’s because some query feature.

Any suggestions? I’ll take a specific fix to my problem, or a work-around by altering the table structure. Thanks in advance!

Switch the control for PersonID to a listbox control. Choose as datasource SQL and create a query like

SELECT "Name", "PersonID" 
FROM "tbl-PersonnelInfo" ORDER BY "Name"

“Name” will be shown and “PersonID” will be connected to the data of the subform. Your query will need this 2 fields, but you could concatenate more content to the first field:

SELECT "Name"||', '||"Address" AS "Name", "PersonID" 
FROM "tbl-PersonnelInfo" ORDER BY "Name"

Might be sometimes there is no value for “Address”. Then the code might be:

SELECT "Name"||COALESCE(', '||"Address",'') AS "Name", "PersonID" 
FROM "tbl-PersonnelInfo" ORDER BY "Name"
1 Like