starrcruise
Registered User.
- Local time
- Today, 12:57
- Joined
- Mar 4, 2011
- Messages
- 18
hello. I have tried and tried to get this figured out, looked everywhere, so going to break this down. The big picture is I have a 4 tables (not full names but close) Policies, Projects, Bulletins/Announcements, Job Aids. The DB is designed that you may create a record that is the Master record (in our business a policy change may require a bulletin, and/or a job aid, or a project may require a bulletin and/or policy change and/or job aid) and I created a "master entry form" for each table that will allow you to generate a new "master or parent record entry, and then from same screen allow you to create a db entry for a new "associated policy, project, etc. By checking a box in the Master record entry form, the ck box triggers opening the form for the "associated bulletin, policy, etc, that carries over most of the info from the master record form, including a field called SourceID (all coded in onload event of triggered form). This set-up saves "foreign" keys in the various tables automatically so the user does not have to manually type in the associated "ID"s (foreign keys), or the Masterform's ID in the associated table. Its automatic.
What I am trying to do is: I have created an edit form much like above (no subforms). I have a control called SourceID and each table has this field as well (long int). This SourceID control is coded ONLY in the master record edit form, (not allowed to edit in Associated form) that allows you to double click the number in the control to open an editing screen for the Source ID. In this edit source screen, you may change the source to another source, and upon closing it, the new Source ID is automatically visible in the Edit Master record form, and saved in the underlying table on Save. I was looking for an event on the SourceID control that would allow me to code an UPDATE sql or docmd.OpenQuery (update query) for every "associated" bulletin, policy, job aid, project, to automatically change the SourceID in their table to the new SourceID when the master's changes. However, because the SourceID is actually changed in another screen, the Before Update, After Update, On Change, On Dirty, are not "triggered". Also, the new SourceID is not really saved to the underlying table until "Save" is clicked (as you know). I was not wanting to put a cmd button with coding to do this, or a msgbox the user had to okay, and I didn't know how to add it to the Click Save or Click Close coding without it running EVERY time Save or Close was clicked. I could not even use relationship integrity with the SourceID table, because the SourceID table only has the Master Record's ID number in it, not the associated's ID's. Do I need to restructure the SourceID table with foreign key fields for the Associated record ID's to set up referential integrity (I hope not), or can I code this somehow?
Thank you for your help.
What I am trying to do is: I have created an edit form much like above (no subforms). I have a control called SourceID and each table has this field as well (long int). This SourceID control is coded ONLY in the master record edit form, (not allowed to edit in Associated form) that allows you to double click the number in the control to open an editing screen for the Source ID. In this edit source screen, you may change the source to another source, and upon closing it, the new Source ID is automatically visible in the Edit Master record form, and saved in the underlying table on Save. I was looking for an event on the SourceID control that would allow me to code an UPDATE sql or docmd.OpenQuery (update query) for every "associated" bulletin, policy, job aid, project, to automatically change the SourceID in their table to the new SourceID when the master's changes. However, because the SourceID is actually changed in another screen, the Before Update, After Update, On Change, On Dirty, are not "triggered". Also, the new SourceID is not really saved to the underlying table until "Save" is clicked (as you know). I was not wanting to put a cmd button with coding to do this, or a msgbox the user had to okay, and I didn't know how to add it to the Click Save or Click Close coding without it running EVERY time Save or Close was clicked. I could not even use relationship integrity with the SourceID table, because the SourceID table only has the Master Record's ID number in it, not the associated's ID's. Do I need to restructure the SourceID table with foreign key fields for the Associated record ID's to set up referential integrity (I hope not), or can I code this somehow?
Thank you for your help.
