Ivonne Aspilcueta
asked on
When updating a field in a table, need to update two other tables as well
Hello,
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.
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.
ASKER
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_AfterUpda
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],[Reporti
"FROM tbl_Register_MemberList" & "WHERE [Name] = 'Name'"
'Now I need an insert to add all this data in the other table
Else
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
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_AfterUpda
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],[Reporti
"FROM tbl_Register_MemberList" & "WHERE [Name] = 'Name'"
'Now I need an insert to add all this data in the other table
Else
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.
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.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
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.