Automated Change Tracking in MS Access Forms: Keep a Timestamp & User Log Each Time Data is Updated

[@ILoveAvatarShow]

Hey, I just want to let you know how incredibly useful this video and your codes were! I appreciate it. Just subscribed.

[@TLDW_Tutorials]

Comments like this are why I love making videos. Thanks so much!!!

[@ILoveAvatarShow]

​@@TLDW_Tutorials Hi again! Everything works great but I encountered an error with multi-valued fields... Was wondering if this would be an easy fix? I'm very new to VBA btw so if you know a solution please kindly dumb it down for me haha

[@TLDW_Tutorials]

@@ILoveAvatarShow no worries, we all have to start somewhere. Do you mean text data like “this is it” or something else?

[@lorifoy5725]

Hi! I'm trying to add an additional table, and I've changed the "Dim tableNames (5) As String, then added tableNames(5) = "ProjsSchT," and it's not recognizing the extra table. Is there somewhere else I should change the tablename? I also changed the GetTableNameFROMSQL to postEnd = InSTr (posFrom + 5)

[@TLDW_Tutorials]

That seems right to me. I assume everything is spelled correctly? Are you able to send your code? If you'd prefer not, you can also send your code to ChatGPT and ask it to identify the issue.

[@MARTHACONTEH-v8e]

Hey I did exactly what you did when it comes to adding new and old values but the old value is still not working for me

[@TLDW_Tutorials]

Uh-oh. Ok, can you tell me more details? Are you receiving an error or is it not appending the old value into log?

[@MARTHACONTEH-v8e]

@@TLDW_Tutorials it is not adding the old value to my table

[@TLDW_Tutorials]

@@MARTHACONTEH-v8e Ok, see if this helps. I just tested it and it worked for me. Code: controlc.com/c35f3932

[@MARTHACONTEH-v8e]

@@TLDW_Tutorials Hey sorry if you don't, I believe I took to long to come back to this comment and now when I click the link you provided I get a 404-not found error, can you send the link one more time.

[@TLDW_Tutorials]

@@MARTHACONTEH-v8e Sorry about that, I have no idea why it suddenly stopped working. It may have just expired after 30 days. This link should do it: codeshare.io/XLpZ88

[@suzytan-h6m]

thank you. i am facing this error. why is that so? the expression after update you entered as the event property setting produced the following error the expression may not result in the name, the name of a user-defined function or event procedure There ma have been an error evaluation the function, event or macro.

[@TLDW_Tutorials]

If your expression calls a user-defined function or an event procedure, verify that the function or procedure exists and is correctly named. Also, ensure that the function or procedure is accessible from the context where you're using it. Usually with this, it's just syntax or missing a letter or something. Definitely make sure you added in your functions though too (and changed any field names to your own). Feel free to create a controlc.com link with your code if it would help. ChatGPT can be helpful for stuff like this too. Happy to help either way.

[@lorifoy5725]

This is great! Would it be easy to include the ID of the line changed? I'm not sure if it would need to be added to the private or public function.

[@TLDW_Tutorials]

Thank you! The absolute easiest way to add the ID is if it's in your form. You'd just modify that track_change function and add it to your SQL statement. I will show you at the bottom how to do that. If you don't want the ID (you can also make it transparent so no one sees it) in your form, it's a little trickier but very doable. You'd need to make a query or DLookup to identify your row ID and then add it to your SQL statement. Either would work. Let me know if you need any help with the code!

[@lorifoy5725]

@@TLDW_Tutorials, Thank you very much! Yes, please, I can use all the help I can get. I keep getting an error that the after-update event is producing an error. I don't care if it's seen or not.

[@lorifoy5725]

For better clarity, it's actually a project key field in the form, not the ID. The project key tells me which project the cash flow has been changed, and it's needed on all changes for identification. Right now, it only gives that ID if it's been changed. This ID needs to show whether it's been changed or not. I appreciate your help!

[@TLDW_Tutorials]

@@lorifoy5725 Ok, if you don't care then, make sure you add the field to your form. See the code below. Change "EmployeeID" to the same name as your field and that should do it. I think "EmployeeID" appears 7 times in the code (plus 2 times in commented sections), so that should do it. code: codeshare.io/Mkoz7p Let me know how it goes!

[@lorifoy5725]

@@TLDW_Tutorials It works like a charm at first! Thank you!!! I saw where I was missing the Datasource and tried to paste the code in from previous versions, and now getting afterupdate errors, so I must have put it in the wrong place.

[@acclb4376]

Thank you for this video. I added the newValue and the oldValue to the changes Table. The newValue worked fine but the oldValue gives null. How can you capture the old value of the field before update?🙂

[@TLDW_Tutorials]

Wow, this is a pretty tricky one, huh? See this: controlc.com/4085dfcf Assuming you create a field called "old_value" and "new_value" in your appended table that tracks the changes, this should do it. I just tested it on my end and it works. Here is the SQL statement below in particular so you can see the array of how it works. Let me know if it works for you! strSQL = "INSERT INTO change_tracking (Field_Name, Old_Value, New_Value, Change_Date, User_Change, Data_Source) " & _ "VALUES ('" & fieldName & "', '" & oldValue & "', '" & newValue & "', #" & dtNow & "#, '" & UserName & "', '" & DataSource & "')"

[@acclb4376]

Thank you very much😆. It worked very well.🙏

[@cw458]

Any advice on how to handle other data types such as Yes/No?

[@TLDW_Tutorials]

That's a good question - I didn't even think about yes/no data when I made this video. Luckily, it's a pretty easy fix. We just have to add another OR statement and add the acCheckbox. I just tested it and it worked. So just change: If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then TO THIS If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox Then

[@cw458]

That worked! I ran into an odd issue though with the overall process. I have a form with a sub-form, both have the same code, and all changes are tracked. When I click "New Blank Record" it throws an error "The expression On Got Focus you entered as the event...: Type mismatch". @@TLDW_Tutorials

[@TLDW_Tutorials]

Did it specify what part had the error with the data mismatch? Sounds like it might be expecting another type of data (I.e., number instead of text, yes/no instead of number, etc.)

[@acclb4376]

Hi@@TLDW_Tutorials , This error comes when it set focus on an empty field and tries to capture it value. I fixed it by adding If Then statement: If Not IsNull(ctl.Value) Then ctl.Tag = ctl.Value End If It is working now perfectly 😊

[@TLDW_Tutorials]

@@acclb4376 - It's always the little things that have such a big impact. I'm glad it works! Awesome!

[@jornt1534]

Awesome! Could you reshare the code please?

[@TLDW_Tutorials]

Oh no, it looks like my old URL expired. I just updated this in the video, but here you go: codeshare.io/XLpZ88

[@jornt1534]

@@TLDW_Tutorials Awesome. Thanks for your quick answer. The code works great. Just one question. On a few forms where the form data is pulled with an SQL statement to pull data from multiple tables onto one from, your code write the SQL statement in to the "data_source" field. Any advise on that?

[@TLDW_Tutorials]

@@jornt1534 I am glad it worked! To handle multiple tables in your MS Access form, we may want to do things a little differently. See the revised code and make sure to change the table names to your own. I've modified the original code to dynamically check for fields from different tables before assigning event handlers. I introduced an array that lists all potential tables the form may pull data from, and the code now loops through this array to determine which tables are used in the form's RecordSource. Hopefully this helps... Let me know. If it doesn't work, I'll test it on my end and get back to you. Code: codeshare.io/1V0Xj1

[@jornt1534]

@@TLDW_Tutorials You are amazing. The codes works pretty well, however I encounter two problems: 1. When adding a new record-set a warning pops up "The expresssion on got focus...: type mismatch". Just clicking "ok" and the form can be completed, this is for each field until it has a value. This did not happen with the first edition of your code 2. When adding a new record-set it keeps the data from the previous record-set in the memory and stores the values in the old_Value field. Any ideas? Thanks for your influance

[@TLDW_Tutorials]

@@jornt1534 I’m going to check this out today and see if I can resolve this without asking you details about your tables. I will get back to you (I have an alarm on my phone so I don’t forget!)

[@GrantWhite-vv3yk]

Thank you so much for this tutorial!! I my quite new to VBA programming and this has helped me greatly!! The question I have had to do with the comment from @acclb4376 in regard to setting focus on an empty field. I see the If Then statement that they used: If Not IsNull(ctl.Value) Then ctl.Tag = ctl.Value End If and my question is where is it added in the VBA Code so an empty field would be ignored but it would still capture data from a non empty field? I hope this makes sense as I am still new and trying to find my way around VBA!! I am assuming it would go in the area where ctl.OnGotFocus = "=SetCurrentField(""" & ctl.Name & """)" is but I am not 100% sure exactly where, as I am also getting the On Got Focus Type Mismatch error when it finds a field without any data in it. Any help would be greatly appreciated!! Thanks everyone!!!

[@TLDW_Tutorials]

Hi there, thank you so much! I appreciate you watching the video! If you are new to VBA, this one is a little hard to explain. Here is some revised code that might help if you looked at though: That could help. We will figure SOMETHING out here. The data mismatch error typically happens when it expects one type of data, but it is another. For example, it might expect "yes/no" data, when in fact it is text data. I revised this code to make it a little more flexible. See this: controlc.com/3147b7f2 Bottom line, we actually added the part you mentioned to our SetCurrentField function. I know this probably sounds very complicated, but once you get it up and running and THEN look at the code, it might make more sense. Let me know if this code helps. Thanks again for watching my video!

[@GrantWhite-vv3yk]

@@TLDW_Tutorials I copied in the new code from the link you provicded and everything seems to work perfectly!! Thank you so much for your help!!!!

[@TLDW_Tutorials]

@@GrantWhite-vv3yk Awesome!!!!!! So glad to hear it!