Using both Before and After Update together to track start and end dates

[wethumpback]

I have a database to track assets. I have a form that contains the details of the assets such as the owner. I want to be able to change the owner, use an update query to log the EndDateTime (unassigning the asset), and then add a new insert for the newly assigned owner at the StartDateTime (assigning the asset). In general, I am trying to achieve a start and end date for the assets per owner to track the assignment history of each asset. Assets are not immediately reassigned so I have an owner labeled "Device Unassigned". This is fine, I'm okay with seeing the un-assigned period in the history of the asset as well.

I was trying to implement a before update to track the un-assignment and use an update query to fill the EndDate time, and then an after update event to insert a new set of data which initializes it as the StartDateTime with the newly assigned owner on the form.

I can't seem to use both in conjunction. Do you have any advice on a better way to handle this?

TLDR:

This is my current table, before update of Owner field send an update query to fill the EndDateTime of the assets assignment. After Update, insert a new piece of data using the newly assigned owner and filling in the StartDateTime field.

TBL_Assignment_History

ID | Item | Owner | StartDateTime | EndDateTime

** No primary key, since assets can be assigned and reassigned multiple times, I can handle displaying data separately... just wondering on how to go about tracking a start and end date based on when the owner field is updated.

 

[jdraw]

Can you show us your table(s) design and any relationships? (relationships window)

Sounds like 2 different transactions.

1-assign asset to person and remove from inventory
2-remove asset from person and add asset to available inventory

 

[wethumpback]

So here's the thing, I can't because I am working in a locked-down environment. I can tell you that the DB was built directly off of Microsoft's Asset DB template. The "Asset Details" form is the exact same as the template and the Asset table itself is just modified to include a few extra things we need to track. If you're able to look at the template, you are viewing the DB exactly the same as I am besides external changes that have nothing to do with this issue. I have just created the table above and am using VBA on the BeforeUpdate event and AfterUpdate event specifically on the Owner field in the Asset Details form.

There is only relationships between the Owner and Asset form. Assume that everything is the same with the exception being the BeforeUpdate and AfterUpdate events on the owner field in the Asset Details form along with the table to track asset assignment history. The reason I would prefer to have it all in one table is because I need to import old assignment history which makes it far easier to do.

 

[theDBguy]

Hi. Welcome to AWF!

Are you storing the asset assignment history in a separate table (junction table)? I am not familiar with that template, that's why I am asking this question.

 

[wethumpback]

Hi. Welcome to AWF!

Are you storing the asset assignment history in a separate table (junction table)? I am not familiar with that template, that's why I am asking this question.

Thank you! I have SQL and plenty of programming experience, have never used Access before though and am on the learning curve, much would've preferred to just create a C# .NET MVC App to do this... but I cant. The template for this was there when I created a new Access DB. I tried to link it but it doesn't appear to let me on the forum.

I am assuming that a junction table is an associative entity so yes but they aren't actually linked by relationships. I am just inserting data directly into the junction table and then attempting to send an update query to fill in the inserted data with the EndDateTime.

I originally thought of just creating two tables, one for when it is unassigned, and another for when it is assigned, and then run a query to link them together. However, I need to import a lot of data from an excel sheet and felt if I could do it all in an associative table with an update query then it would be far easier.

 

[theDBguy]

Thank you! I have SQL and plenty of programming experience, have never used Access before though and am on the learning curve, much would've preferred to just create a C# .NET MVC App to do this... but I cant. The template for this was there when I created a new Access DB. I tried to link it but it doesn't appear to let me on the forum.

I am assuming that a junction table is an associative entity so yes but they aren't actually linked by relationships. I am just inserting data directly into the junction table and then attempting to send an update query to fill in the inserted data with the EndDateTime.

I originally thought of just creating two tables, one for when it is unassigned, and another for when it is assigned, and then run a query to link them together. However, I need to import a lot of data from an excel sheet and felt if I could do it all in an associative table with an update query then it would be far easier.

When you said you have SQL experience, does that include familiarity with the Normalization Rules? Just checking...

Just as an example of what I was saying, if I had a bunch of asset (like computer or other devices) to assign to employees, I would have two separate tables for them. One to store the information for the asset and another for the employees. Now, when I assign and unassign an asset, I would store that information in a third table.

The tables might look like this:

Asset Table
AssetID
AssetName
etc.

Employee Table
EmployeeID
EmployeeName
etc.

Asset Assignment Table
AssignmentID
AssetID
EmployeeID
StartDate
EndDate
etc.

Hope that helps...

 

[MarkK]

What is an associative table?

I would solve this with three tables, Owner, Asset, and Assignment, with Owner and Asset in a many-to-many relationship using Assignment as the junction.

To unassign an asset, you can create an assignment row with a null or zero OwnerID, or (probably easier) create a spoof owner called "Unassigned."

IMO

 

[MarkK]

Exactly as @theDBguy has outlined.

 

[wethumpback]

When you said you have SQL experience, does that include familiarity with the Normalization Rules? Just checking...

Just as an example of what I was saying, if I had a bunch of asset (like computer or other devices) to assign to employees, I would have two separate tables for them. One to store the information for the asset and another for the employees. Now, when I assign and unassign an asset, I would store that information in a third table.

The tables might look like this:

Asset Table
AssetID
AssetName
etc.

Employee Table
EmployeeID
EmployeeName
etc.

Asset Assignment Table
AssignmentID
AssetID
EmployeeID
StartDate
EndDate
etc.

Hope that helps...

I do understand and know all the normal forms (3NF etc...) I have already done exactly what you said by having the Assets, Owners, and Asset Assignment table. My issue lies in the inserting of this table for the new owner. I want the user to be able to select from the a drop down list of Owners to update the asset (this works fine). I have also already created a "Spoof" owner named "Device Unassigned" like @MarkK has said to simplify it a little bit.

Assume that I just have ChangeDateTime and am ignoring the Start&EndDate for now and will just link them with a query later. As of now the code below will insert two pieces of data with the exact same owners into the junction table. I don't understand why though because...

BeforeUpdate event is ran --> Goes to asset table -- > Grabs the current owner (one being unassigned since its before update) --> Inserts into Junction table

AfterUpdate event is ran --> Goes to asset table -- > Grabs the current owner (one who is now the owner and just assigned) --> Inserts into Junction table

Why is it grabbing the same owner twice if the Asset table and form updates properly when another owner is selected from the list.

Private Sub Owner_AfterUpdate()

    On Error GoTo Error_Handler
  
    DoCmd.RunSQL "INSERT INTO tblAssets_Assignment_History SELECT ID, Item, Owner FROM tblAssets " & _
        "WHERE ID=" & ID
    Exit Sub

Error_Handler:
    MsgBox "Failed to assign the device from the employee. Contact the developer."
    Cancel = True
    Resume Next
End Sub

Private Sub Owner_BeforeUpdate(Cancel As Integer)

    On Error GoTo Error_Handler
    DoCmd.RunSQL "INSERT INTO tblAssets_Assignment_History SELECT ID, Item, Owner FROM tblAssets " & _
        "WHERE ID=" & ID
    Exit Sub

Error_Handler:
    MsgBox "Failed to unassign the device from the employee. Contact the developer."
    Cancel = True
    Resume Next

End Sub

I know this is a really simple insert query, but in theory wouldnt this work? Am I misunderstanding how the Before&After Update events function?

 

[Pat Hartman]

I don't recommend using ANY of the sample templates. Most have seriously flawed schemas like this one and the also usually follow poor naming standards.

You have a one to many relationship BUT the relationship probably needs to maintain history so you know who had custody of which asset when. That makes it a many-many relationship with the junction table as the others have mentioned. I'll post an example of how a m-m is implemented. It isn't one based on assets so you'll have to do some logical adjustments as you think about each object. The sample shows how the relationship data can appear from each side of the relationship. The important thing to remember is that the junction table is ALWAYS the child of the relationship --- NOT the other related table. The example shows how to use a subform or a popup to show the "child" data. Both are correct but depending on the situation, one view might make more sense to you than the other.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[theDBguy]

I know this is a really simple insert query, but in theory wouldnt this work? Am I misunderstanding how the Before&After Update events function?

I just did a quick experiment and found out the BeforeUpdate and AfterUpdate events function exactly like you were expecting. You might step through your code to see what else is affecting the behavior you're seeing.

 

[MarkK]

If you want help with your table structure, you need to show your table structure. @jdraw asked in Post #2
All the best,

 

[wethumpback]

I just did a quick experiment and found out the BeforeUpdate and AfterUpdate events function exactly like you were expecting. You might step through your code to see what else is affecting the behavior you're seeing.

Adding a Me.Requery on the After Update seems to have fixed the weird behavior. I will now attempt to use the Start and End date on the junction table and see if I can get that working.

I should just need to modify the Before Update event to an update sql command. Thank you again for all of your help. I will respond back once I finish this so it can be closed.

 

[theDBguy]

Adding a Me.Requery on the After Update seems to have fixed the weird behavior. I will now attempt to use the Start and End date on the junction table and see if I can get that working.

I should just need to modify the Before Update event to an update sql command. Thank you again for all of your help. I will respond back once I finish this so it can be closed.

Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

You were using the Before and After events of a control. Logging should be done in the FORM level events. What happens if you change the assignedto ID and then later not complete the update.

Also, the Requery in the AfterUpdate event of the control was forcing the record to be saved. This means that a NEW record will get saved with incomplete values. This should be caught by your Form level validation but you apparently don't have any.

 

[wethumpback]

You were using the Before and After events of a control. Logging should be done in the FORM level events. What happens if you change the assignedto ID and then later not complete the update.

Also, the Requery in the AfterUpdate event of the control was forcing the record to be saved. This means that a NEW record will get saved with incomplete values. This should be caught by your Form level validation but you apparently don't have any.

Apologize ahead of time if I am being dumb here haha...

So you are saying that I should move the before and after update events to within the FORM itself . Then, programmatically check to see if the owner control has changed and log from there (this makes sense, I can do that).

The form has validation in that only owners in the database can be selected, the id can not be changed, and the rest is random information which is always modifiable. What other form validation would I need? I think I am little bit confused on the second half. Why would a re-query save a new record with incomplete values? Are implying that if someone makes a mistake then a record exists that is never filled in?

 

[Pat Hartman]

Are implying that if someone makes a mistake then a record exists that is never filled in?

I was stating that as a fact, not implying it.

Requery does exactly what it says. It runs the query again to bring back what is saved in the table. If it did not first save what is in the form, then that data would be lost as it was replaced by the stored data. So, both Requery and Refresh have the side effect of saving the current record whatever it's state. That is why most validation code goes in the form's BeforeUpdate event. So, you can ensure that all the required data is there and whatever validation you can do to ensure correctness is also done there. Although validation works in the BeforeUpdate event of each control, that event cannot be used to ensure that a required field is populated because if the user doesn't change a value in a control, its BeforeUpdate event doesn't run. Therefore, rather than have all validation code scattered in multiple events, I tend to do it all together in the Form's BeforeUpdate event. Do what you want, but all required fields must be checked for data in the form's BeforeUpdate event.

The most important event of a form is it's BeforeUpdate event. Think of this as the flapper at the bottom of a funnel. If the flapper is open, the record is saved. If the flapper is closed, the save is prevented. It is the last event that runs before a record is saved and there is no way to bypass it. Whatever caused the record to be saved, this code will run. Many people make the mistake of putting validation code in something like the click event of a save button never realizing that there are many ways to save a form and most are not under your control. Access takes it as a personal mission to not loose data. That means it saves dirtied forms for you no matter what. In fact, most people want Access to stop saving and get very frustrated if they don't discover this most important event that has total control over whether or not the record is saved. All you have to do to prevent a save is to use:

Cancel = True

That leaves the record dirty so the user can fix it and it has to go through the save cycle again. If you want to go further and get rid of the offending data, you can use:

Me.SomeField.Undo --- to remove the last unsaved change from a control. I rarely do this because usually I want to the user to see the value he typed.
or
Me.Undo --- to remove all unsaved changes from the entire form. I never do this unless the user is not allowed to update at all. It is too punitive.