Fairly simple database (compared to other examples I've seen in this forum). No numbers or calculations, all text and dates.
Two main tables: Activity and Issue and a third table called Admin used to host the Main Menu (for navigation and notification) and all the help menus that are involved. The Activity table is the heart of the database, and each Activity can have multiple Issues. there are other tables linked directly to Activity and Issue, but not relavent to this question, I think,.
Admin Activity Issue
AdminID-PK - - - -> AdminID_FK
ActivityID_PK - - - - - -> ActivityID-FK
Based on the Admin table is a Main Menu that has links to all the tables, data input layouts, and reports. All work fine. There are no records stored in the Admin table.
However, on that Main Menu I want it to run OnLayoutLoad a series of searches in the Activity and Issue tables with the results inserted in global fields. The global fields represent various numbers of records in different categories and present them on the Main Menu. This isn't working so hot. The search should run, and then the found count inserted in a specified global field. I've tried putting the global field in the Admin table and each pertinent table, but with mixed results.
I tried basing the Main Menu on the Activity table rather than Admin, but still having problems. I want the users to continually go the Main Menu for navigation among the layouts, and each time the Main Menu should refresh all the the numbers.
Should I have some other relationship than the one above? I tried making a join table (between Activity and Issue) and hosting the Main Menu there, but the same problems. Some global fields are updated, and others are not.
In the script to run OnLayoutLoad I have a series of Perform Find [Restore]/Set Field functions.
I know I'm doing something basically wrong, and suspect it's in the relationship, but reading Phil's paper on TOs is to me a lot like trying to translate an ancient Greek text using an Albanian dictionary.