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

Posted on 2017-10-12
High Priority
Last Modified: 2018-02-02

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.
Question by:Ivonne Aspilcueta
LVL 11

Expert Comment

by:Anders Ebro (Microsoft MVP)
ID: 42329516
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.

Author Comment

by:Ivonne Aspilcueta
ID: 42330092
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
LVL 41

Expert Comment

ID: 42330526
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.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question