[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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

Posted on 2017-10-12
High Priority
Last Modified: 2017-10-17

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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

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

Expert Comment

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

656 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