When updating a field in a table, need to update two other tables as well


I am really new to access, hopefully you can guide me over to what can I use when as soon as an employee table is made inactive the field Active/Inactive or leave date is entered in the leave date field the position needs to become available in the Fills screen table and its fields update  as well, also it needs to be available in a 3rd table recruits.
I am not sure if do a query for that using Append or Update, or using a Macro or link the tables, which one will be more accurate to use to be able to work? these 3 tables also have 3 forms and 3 sub-forms.
Ivonne AspilcuetaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Ideally, the logic of queries and such should be created so that it is not necessary to update two places in once, if at all possible (and it usually is). If it isn't in your case, it is quite probably because the table design is not fully normalized.

Obviously there are exceptions. In those cases the correct approach is to use a transaction, which ensures that either all changes are made, or none of the changes are made.
Ivonne AspilcuetaAuthor Commented:
When I change a control on a form, like checking or unchecking a checkbox or
entering or changing a date value, I can use the AfterUpdate event to control what I can do
after that is To set a TEMPVAR (I will use the name IsActive for example)  can I use the
command TempVars!IsActive = "Y" then I can test for that value as I open other forms.  Can I change the open subroutine in
the other forms to test to see if the IsActive tempvar = "Y" or not.  And when I am done with the active or
inactive process, Can I reset the TempVar to "N" or delete it with TempVars. Remove "IsActive"?

Also I did:

Private Sub Active__Inactive_AfterUpdate()

If Me.Active__Inactive = "INACTIVE" Then
   If Not IsNull(Me.Leave_Date) And Me.Leave_Date <> "" Then
   'Populate fills table with all of this position
   Dim TempVar As String
   TempVar = "SELECT [Name],[Position],[Reporting Level 1],[Group],[Location],[Leave date]" & _
              "FROM tbl_Register_MemberList" & "WHERE [Name] = 'Name'"
   'Now I need an insert to add all this data in the other table
    MsgBox "Please make sure you have entered a Leave Date otherwise this position will no appear in the Postion Fills File"
End If
End Sub
By not normalizing your tables, you have to create program logic and run multiple queries to compensate.  If you used a single table for all PEOPLE, you could simply change a status code in the one table.  Then the person would show up in whatever query you ran depending on the status' you selected..

If you don't normalize the table and so need to code this yourself, then there is no need for a tempvar.  You have to make all the modifications to the other tables in the AfterUpdate event of the Form.  To make this work, you will need a form level variable that you can set to trigger your AfterUpdate code to finish the process.  TempVars are useful if you need a variable to be available in multiple forms or pass into a query.  For localized needs, a private form level variable will suffice.  As Anders said, this is risky without a transaction.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.