[Okta Webinar] Learn how to a build a cloud-first strategyRegister 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-28

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 10

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 40

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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Join & Write a Comment

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

873 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