Hello, I apologize if I am not posting this in the correct place.
I have recently created an Access database for work. Essentially we use it to reach out to customer's that are having account issues and work with them to correct them. We have 8 team members using the database. Start to end, we may take 2+ weeks to correct an issue and update the db after each customer interaction. *BTW did I mention i'm new to access* My boss is trying to track our productivity daily. Not so much on if we are successful, just how many customers/accounts we add or edit via interaction daily. From what I'm reading, I think an audit trail might be able to help me. Our db currently consists of 1 table containing all of the data, 2 forms (one for entry, the other for filtering purposes by date) a simple macro which just brings up a welcome message. There are no logins or passwords and users cannot access the actual table. We can easily track cases opened and closed by going into the table and filtering by date, but I may have 12 pending call backs that I work that day and I don't add any new cases, making it look like i did nothing all day. Im looking for an audit trail to add data to a new form that can capture new accounts added, and edits daily. We have multiple fields in our data entry form, but I'm not looking for something that says everything (or anything) that was changed, just like a ticker that says that it was modified/added. We have multiple drop down selectors and date selectors, one of our drop downs is for a rep name. Ideally, I'd like the audit trail to log DATE/TIME, "ACCOUNT" (which is our primary id), and if possible, record the "AGENT" (which is in the form) that is assigned to that case. I don't want it to record every time the account is populated just to view, only when of the fields are changes or modified and the rep clicks the save button. I have included a screenshot of the data entry form and a proposed table for the audit trail. I'd like to add a field that states new or edit, but it is not a must if it means reinventing the wheel. Im assuming this will need to go through a module (from what i've read) and part in the form properties before update. I have some existing code there as well that I will paste at the end. Can anyone help baby step me through what I need to do? BTW, data entry form is called: FORM, and the table is called: DATA.
Private Sub Form_BeforeUpdate(Cancel As Integer)If Me![COMPLETE DATE] > Numm Then
If IsNull(Me![Final Resolution]) Then
MsgBox "A final resolution must be selected upon completion of a cusotmer.", vbOKOnly
Cancel = True
If Me![COMPLETE DATE] > Numm Then
Me![FOLLOW UP DATE] = Null