Updated fields query

[hobbit2612 via AccessMonster.com]

Hi I wonder whether someone may be able to help me please.

I am trying to create a query that shows updated fields within a given time
period.

I have set up a 'DateChanged' field for every field I want to track in the
tables affected and I've added the statement 'Me.Field = date' to the
BeforeUpdate event on the forms that hold that date I want to track. I have
also added the coding that allows current fields to be amended in the forms.

The problem I've got is that rather than just the field that I've changed
showing that it's being updated in the query, the whole record does e.g.

Three of the fields I have are:

InvoiceNumber
DateReceived
DatePaymentSent

If I make an update on the 'DateReceived' box today, all three fields will
show as also being updated.

Can anyone tell me please is there a way around this?

Many thanks

Chris

 

[John Spencer]

Try something like the following for each field you are checking.

If Me.InvoiceNumber & "" <> Me.InvoiceNumber.OldValue & "" THEN
Me.InvoiceNumberChangeDate = Date()
END IF

For a more comprehensive audit trail check out

Audit Trail - Log changes at the record level at:
http://allenbrowne.com/AppAudit.html

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[hobbit2612 via AccessMonster.com]

John many thanks for the quick reply.

Can I just check please, the coding that you suggest, does this go in the
text boxes on the forms, rather than the =Date() in the Default value setting?


Many thanks and regards

Chris

Try something like the following for each field you are checking.

If Me.InvoiceNumber & "" <> Me.InvoiceNumber.OldValue & "" THEN
Me.InvoiceNumberChangeDate = Date()
END IF

For a more comprehensive audit trail check out

Audit Trail - Log changes at the record level at:
http://allenbrowne.com/AppAudit.html

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi I wonder whether someone may be able to help me please.

[quoted text clipped - 23 lines]

Chris

 

[John Spencer]

You would put code like that in the before update event of the form where you
are entering the data.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John many thanks for the quick reply.

Can I just check please, the coding that you suggest, does this go in the
text boxes on the forms, rather than the =Date() in the Default value setting?


Many thanks and regards

Chris

Try something like the following for each field you are checking.

If Me.InvoiceNumber & "" <> Me.InvoiceNumber.OldValue & "" THEN
Me.InvoiceNumberChangeDate = Date()
END IF

For a more comprehensive audit trail check out

Audit Trail - Log changes at the record level at:
http://allenbrowne.com/AppAudit.html

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi I wonder whether someone may be able to help me please.

[quoted text clipped - 23 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

John,

Many thanks for this, it works a treat. I really do appreciate your help and
guidance.

Kind regards

Chris

You would put code like that in the before update event of the form where you
are entering the data.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John many thanks for the quick reply.

[quoted text clipped - 25 lines]

[quoted text clipped - 23 lines]
Chris

 

[hobbit2612 via AccessMonster.com]

John,

You very kindly gave me assistance with the problem I had on tracking
changes to my database fields which as I posted earlier works a treat.
However, I've now noticed
that the checkboxes on the records now show that they have been changed when
they haven't.

I've narrowed this down to it happening when the record is created. So e.g.
when I add an Invoice Number for example the checkbox also shows it has been
updated.

I've tried a few things to sort this, changing the default value to 0, then
changing the checkbox physically on screen to a text box but this caused
further problems, but I just can't fathom it out.

Could you tell me please is there anyway to get around this.

Many thanks

Chris

John,

Many thanks for this, it works a treat. I really do appreciate your help and
guidance.

Kind regards

Chris

You would put code like that in the before update event of the form where you
are entering the data.

[quoted text clipped - 9 lines]

[quoted text clipped - 23 lines]
Chris