Putting the result of a query as a suggested value

[nofue]

It’s been a while since I last raised a question, which was this one:

How to limit selection in listbox

So far my Form - SubForm - SubSubForm stuff works pretty well. Now I have yet to solve one more issue which is beyond my knowledge. Given is this set of tables: [Bildschirmfoto 2024-08-28 um 19.04.43] These (and some more) are presented in this form: [Bildschirmfoto 2024-08-28 um 18.57.15] Everything works fine as it is, however, to make the self reference in tEffort table work (tEffort.effortID = tEffort.effortRef) the user has still to enter the effortRef value in the SubSubForm (which is…

After using the application I built for more than 750 “Efforts” (see the above link for a description) I’m looking for the best way to solve the clumsy listbox issue. Limiting the listbox, as planned previously, won’t work well while still allowing for errors when clicking the wrong listbox item.

So I created this nice query

SELECT MAX( "tEffort"."effortID" ) 
FROM "tAssignment", "tCampaign", "tEffort", "tProspect" 
WHERE "tAssignment"."campaignRef" = "tCampaign"."campaignId" 
AND "tEffort"."assignmentRef" = "tAssignment"."assignmentID" 
AND "tAssignment"."prospectRef" = "tProspect"."prospectID" 
AND "tCampaign"."campaignId" = :campaign_id 
AND "tProspect"."prospectID" = :prospect_id

which works pretty well – outside the table control showing all efforts for the particular prospect and the particular campaign, that is. Now I would like to enter a new effort by clicking the ‘+’ in front of the next empty line in the table control, showing the history of efforts.

Question A: Is there an event triggered by clicking that ‘+’ of the table control?

This event should trigger a macro which runs above query and inserts the result into the table column “VorID”. Additional fields will be filled in manually, and when saving the row, the ID will be autoinserted by the db. This way I would have always the proper “PreID”. Maybe it would be even smarter to fetch the MAX(tEffort.effortID) only within the INSERT statement, but this raises

Question B: How can I write and trigger a macro which runs my variant of the insert code?

[Villeroy]

A subform’s new record inherits linked fields from its master form. This works without any macro.

Connect Form to database, with readonly or read/write mode set per field

defaults2.odb (40.4 KB) The first form reads preset values stored in a separate table into a new record. The second form reads values from the parent form’s currently selected record into a new record.

[nofue]

Where did you answer my questions? Your suggestion to do things “your way” is appreciated and already in effect. Now I want to do it my way, and this is why I asked above.