record records (1 Viewer)

rainbows · Jan 10, 2019

hi ,

I have a form called issues and a field called "targetdate " this date when entered by the user gets locked in the form , the administrator has a datesheet with all the records in and the targetdate field is unlocked incase the target date has to change.

how can I moniter if any changes have been made by the administrator to the target dates , can we record all the records ??

thanks

steve

pbaldy · Jan 10, 2019

Search on audit trail and see if that's what you're after.

rainbows · Jan 12, 2019

HI,

the form " frm_audit_test." has a field called " targetdate" if I change that date I want to keep a record of that and the ARNO number

I found some code from other examples but I have tried to apply it to my data but I cannot get it to work

I have attached a sample of the database could you please have a look and see where it is going wrong please

thanks
steve

pbaldy · Jan 12, 2019

The code is failing because the name of the textboxes don't match the name of the fields, which the code relies on. Get that sorted out and see if you can see what the next problem is.

rainbows · Jan 13, 2019

HI ,

I Have changed the field names etc but I am getting an error further down the code.
I have changed the form name to auditlog
when I try to change the target date. the macro if stops as it seems to be looking for date . which is the change I am trying to make

please see attached db

thanks for your help

steve

pbaldy · Jan 13, 2019

Can't look at the sample right now, but the next error was because the RecordSource of the form included fields not represented on the form.

rainbows · Jan 13, 2019

hi ,

yes I changed the table field properties which was wrong in the table from date/time to short text and the record now comes .

what the problem is the code is running thro all the table fields
and the fields are not on the form

so how can we stop the macro from running after the targetdate field in completed instead of try to cycle all the table fields

Code:

ublic Sub AuditLog(frm As Form, TableName As String, Action As String, PKFieldName As String, PKValue As Long)

    Dim dtAuditAt As Date
    Dim strAuditBy As String
    Dim lngID As Long
    Dim strSQL As String
    Dim bRecord As Boolean
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    
    dtAuditAt = Now()
    strAuditBy = fOSUserName()
    
    Set db = CurrentDb
  ' Set rs = db.OpenRecordset(IIf(Action = "DELETE", "ISSUES_Temp", "ISSUES"))
    
   Set rs = db.OpenRecordset(IIf(Action = "DELETE", "tbl_Audit_Temp", "tbl_Audit_Log"))
    
   [COLOR="Red"] For Each fld In frm.RecordsetClone.Fields
[/COLOR]    
        If (fld.Name = PKFieldName) Then
            bRecord = False
        ElseIf (Action = "Insert") Or (Action = "DELETE") Then
            bRecord = True
       ElseIf (Nz(frm(fld.Name).OldValue, "") <> Nz(frm(fld.Name).Value, "")) Then
            bRecord = True
        Else
            bRecord = False
        End If
        
        If bRecord Then
            
            rs.AddNew
          rs!Action = Action
            rs!TableName = TableName
            ' rs!ActionBy = "Dale"
            rs!ActionBy = strAuditBy
            rs!ActionDT = dtAuditAt
            rs!RecordID = PKValue
            rs!FieldName = fld.Name
            rs!FieldValue = CStr(Nz(frm(fld.Name).Value, ""))
            rs.Update
        
        End If
        
    Next
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
End Sub

thanks steve

so we need it to stop after the targetdate has been entered

jdraw · Jan 13, 2019

Not sure of your real requirement, but you could have field(s) on your table that records "LastModifiedBy" (and "LastModifiedDateTime") and routines on your Form where "targetDate" resides that updates these LastModified fields.

Then, you could tell who last changed the "targetDate" based on the values in those LastModified fields. I haven't tried this idea, but put it here for consideration.

I'm not saying you can't or shouldn't use an AuditLog, I'm just looking at a alternative.
If you have to record/audit all field values, then by all means an auditllog is the way.

I described my limited experience of Audit Log with data macros in this thread.

pbaldy · Jan 13, 2019

You don't need to change the code, change the record source of the form so it only includes those 2 fields. Do that by clicking on the ellipsis next to the table name and creating a query that only includes the fields on the form.

jdraw · Jan 13, 2019

@Steve,

I looked at your database. My understanding is you want to create an audit record if there is a change to "targetdate". And you aren't interested in all fields --only the ID and targetdate. I have made and tested some mods. But a few points.

-In tbl_audit_log, FieldName was a Date/Time datatype --this led to conversion error, so I reset the datatype to ShortText.
-For completeness I also added a field to the table and named it FieldValueOld --to contain the old value of targetdate.
-I added some debug.print statements within the AuditLog function
-added bTargetDateProcessed As Boolean
'to indicate if targetDate has done, so get out of routine ***JED
- I only tested with record 1
-I did not change or test anything in the Delete area

I have added comments with ***JED in the line to identify my changes

Typical Debug.print

Code:

[COLOR="Blue"]ID
targetDate
Record: 1  -  targetDate OldValue: 31-Oct-1851  NewValue: 31-Jan-1865
Audit log was updated at 13-Jan-19 11:41:25 AM[/COLOR]

I have attached a modified copy of your database.

Good luck.

pbaldy · Jan 13, 2019

Can't look at the sample right now, but why disable a function that's flexible enough to handle any number of fields when all you have to do is tweak the record source?

jdraw · Jan 13, 2019

I agree with Paul. Restricting the record source makes most sense.

OP can review what I commented in his/her code
Remove the lines and comments as appropriate
Revise the Record source as Paul said

I would also advise the OP to review
- the datatype change to the FieldName
- the addition of FieldValueOld
- the use of Debug.Print for debugging
-the Form to assist the "auditor/reviewer" with some instructions/info.

Good luck with the project

dailyxe · Jan 15, 2019

Search on audit trail and see if that's what you're after.

pbaldy · Jan 15, 2019

dailyxe said:
Search on audit trail and see if that's what you're after.

Hopefully there's a good reason you're copying other people's posts?

Frothingslosh · Jan 15, 2019

https://www.youtube.com/watch?v=a1Y73sPHKxw

rainbows · Jan 17, 2019

hi everyone

I want to thank you all for helping me solve my problem , it works great now

I did use a query as suggested with only 2 fields so I can add to it if needed

I will post another post re ontime/late to audit date/amended audit date

once again thank you all

steve

pbaldy · Jan 17, 2019

Glad you got it working Steve.

record records (1 Viewer)

rainbows

Registered User.

pbaldy

Wino Moderator

rainbows

Registered User.

Attachments

pbaldy

Wino Moderator

rainbows

Registered User.

Attachments

pbaldy

Wino Moderator

rainbows

Registered User.

jdraw

Super Moderator

pbaldy

Wino Moderator

jdraw

Super Moderator

Attachments

pbaldy

Wino Moderator

jdraw

Super Moderator

dailyxe

New member

pbaldy

Wino Moderator

Frothingslosh

Premier Pale Stale Ale

rainbows

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread