Is this possible?

  • Thread starter Thread starter bastraker via AccessMonster.com
  • Start date Start date
B

bastraker via AccessMonster.com

Hello All

I've got a lofty goal here!!!

I have a field in my table called "Completion Date Commited", this field is
populated by the user. I need to somehow keep a log of the changes made to
this field.

If the date needs to change I would like the user to put a new date in and
have the database move the old date to a new field. Every update should
append the old "Commited Date" to the new field (4/10/06, 4/17/06, 4/21/06).
In addition I would like to be able to keep a running total of how many times
the "Commited Date" has been changed and be able to report that as well.

Any ideas, outlines, starting points, ect... would be greatly appreciated
D
 
For this, you should use another table. It should be a child table to the
record source of your form. It should have (at least)
COMMIT_ID Autnumber Pk
FK - Primary key of the parent table
OLD_DATE = Date Before Change
NEW_DATE = Date Changed To

Then in the after update event of the control where the date changes, write
the code to update the table. If you want to be able to review previous
dates, I would suggest a sub form or a pop up form to show the previous dates.
 
bastraker via AccessMonster.com said:
Hello All

I've got a lofty goal here!!!

I have a field in my table called "Completion Date Commited", this field is
populated by the user. I need to somehow keep a log of the changes made to
this field.

If the date needs to change I would like the user to put a new date in and
have the database move the old date to a new field. Every update should
append the old "Commited Date" to the new field (4/10/06, 4/17/06, 4/21/06).
In addition I would like to be able to keep a running total of how many times
the "Commited Date" has been changed and be able to report that as well.

Any ideas, outlines, starting points, ect... would be greatly appreciated
D
Click to expand...

Have your committed dates in another, related table, one record per date,
and use a subform to display them.
 
OK good start but does anyone know if code can take the previous value and
append into a new field with a comma. I do not need each old date in a field
of its own, unless thats the only way to do it.
 
bastraker via AccessMonster.com said:
OK good start but does anyone know if code can take the previous value and
append into a new field with a comma. I do not need each old date in a field
of its own, unless thats the only way to do it.
Click to expand...

Your goal is not lofty, it's very easy. It is, however, very poor database
design and I do not recommend it. My previous suggestion is the way to do
it.

Still, if you must, put something like this in the form's BeforeUpdate
event:

If [Completion Date Commited] <> [Completion Date Commited].OldValue Then
[my_grungy_field] = [my_grungy_field] & ", " & Format([Completion Date
Commited].OldValue,"mm/dd/yyyy")
[grungy_change_count] = [grungy_change_count] + 1
End If

Also, it's very poor practice to use spaces in your field names.

Good luck, with a design like that you're gonna need it.
 
I guess I'm at a loss as to how your previous suggestion would work, it was
kind of vague. I'm a newbie no doubt about it but how/why is this a poorly
designed database?

The Completion Date Commited is used in code to determine if the project is
still "On time", compared in two seperate instances one against a completion
date and another time against the current date if no completion date exists
yet.

The requestor of the database now informs me they need to be able to update
this field if things change and they would like to keep a record of the
previous dates and a running total of iterations.

I guess what your saying is to have a seperate field in another table for
every time the user makes a change? OldDate1, OldDate2, ect...

Any assitance anyone could provide would be greatly appreciated
Thanx
D
 
OK so working from what Baz has offered

If [Completion Date Commited] <> [Completion Date Commited].OldValue Then
[my_grungy_field] = [my_grungy_field] & ", " & Format([Completion Date
Commited].OldValue,"mm/dd/yyyy")
[grungy_change_count] = [grungy_change_count] + 1
End If

This works to append the field and iterate a counter, GREAT. Only problem is
it runs any time the form is saved, CommitedDate will never actually be equal
to PreviousCommitedDate, so I guess I will need a comparison field and a date
recording field. I try using the following but for some reason the counter
doesn't update.

If [DateCommited] <> [PreviousCommitedDate] Then
[OldCommitedDates] = [OldCommitedDates] & ", " & Format(
[PreviousCommitedDate], "mm/dd/yy")
[PreviousCommitedDate] = [DateCommited]
[CommitedCounter] = [CommitedCounter] + 1
End If

Next issue that comes up is the initial value is Null. I think the following
would take care of that but I'm not sure where to put it. If I put it in the
before update section nothing would happen for the very first date change.

If IsNull([PreviousCommitedDate]) Then
[PreviousCommitedDate] = [DateCommited]
End If


Getting closer
D
 
bastraker via AccessMonster.com said:
OK so working from what Baz has offered

If [Completion Date Commited] <> [Completion Date Commited].OldValue Then
[my_grungy_field] = [my_grungy_field] & ", " & Format([Completion Date
Commited].OldValue,"mm/dd/yyyy")
[grungy_change_count] = [grungy_change_count] + 1
End If

This works to append the field and iterate a counter, GREAT. Only problem is
it runs any time the form is saved, CommitedDate will never actually be equal
to PreviousCommitedDate, so I guess I will need a comparison field and a date
recording field. I try using the following but for some reason the counter
doesn't update.

If [DateCommited] <> [PreviousCommitedDate] Then
[OldCommitedDates] = [OldCommitedDates] & ", " & Format(
[PreviousCommitedDate], "mm/dd/yy")
[PreviousCommitedDate] = [DateCommited]
[CommitedCounter] = [CommitedCounter] + 1
End If

Next issue that comes up is the initial value is Null. I think the following
would take care of that but I'm not sure where to put it. If I put it in the
before update section nothing would happen for the very first date change.

If IsNull([PreviousCommitedDate]) Then
[PreviousCommitedDate] = [DateCommited]
End If


Getting closer
D
Click to expand...

Why are you comparing CommittedDate with PreviousCommittedDate? Of course
they will always be different! My suggested code wasn't doing that, it was
comparing the OldValue property of the DateCommitted control with it's
current value. These two values will only be different if the DateCommitted
has been changed. OldValue is a property exposed by Access, it's not a
separate field that I invented, look it up in help.

Handle nulls something like this:

If Not IsNull([DateCommitted]) Then
If [DateCommitted] <> Nz([DateCommitted].OldValue) Then
[PreviousCommitedDate] = ([PreviousCommitedDate] + ", ") &
Format([DateCommitted].OldValue,"mm/dd/yyyy")
[CommitedCounter] = [CommitedCounter] + 1
End If
End If

Cramming multiple values into a single field like this flies in the face of
database design principles. A field is for storing a value in. *One*
value. Access offers an ideal structure for storing multiple values,
expressly designed for the purpose: it's called a table.

Your hideous field full of dates will be virtually impossible to query for
individual dates when you need to. What's more, you are having to maintain
a separate counter field. If the historical dates were simply rows in a
table, you could simply count them whenever you needed to instead of having
to maintain a separate counter.
 
Whoaaaa there Baz

Thanks for the help and all but it's like your about to blow a gasket. If
your gonna help people on this board you should really take your pills.

"Cramming multiple values into a single field like this flies in the face of
database design principles." - Is my design upsetting your perfect world?

"Your hideous field full of dates will be virtually impossible to query for
individual dates when you need to." - Hideous field full of dates, are you
serious? Hideous?

The fact is I will never have to query any of those dates, might be flying in
the face of database design principles, but hey I'm a rebel baby!! I also
have no idea how many times the field may change, could be, 5, 10, 20, times.
Simply counting the rows in a table seems to me to be just as simple as
"maintaining a seperate counter field"

My apologies for mistaking the .OldValue usage but in the future you can
refrain from answering any of my requests for assitance, I would rather bang
my head against the wall.
D
 
bastraker via AccessMonster.com said:
Whoaaaa there Baz

Thanks for the help and all but it's like your about to blow a gasket. If
your gonna help people on this board you should really take your pills.

"Cramming multiple values into a single field like this flies in the face of
database design principles." - Is my design upsetting your perfect world?

"Your hideous field full of dates will be virtually impossible to query for
individual dates when you need to." - Hideous field full of dates, are you
serious? Hideous?

The fact is I will never have to query any of those dates, might be flying in
the face of database design principles, but hey I'm a rebel baby!! I also
have no idea how many times the field may change, could be, 5, 10, 20, times.
Simply counting the rows in a table seems to me to be just as simple as
"maintaining a seperate counter field"

My apologies for mistaking the .OldValue usage but in the future you can
refrain from answering any of my requests for assitance, I would rather bang
my head against the wall.
D
Click to expand...

Nice one, I see others have been falling over themselves to help you with
this.

Given that you are clearly a beginner with Access and databases in general,
your over-sensitivity to criticism is going to seriously restrict your
ability to get help. As I said before, good luck, you're gonna need it.

Incidentally, I will choose what threads I post on. If you don't want me
hurting your tender feelings, either don't post in a public forum, or
killfile me.
 
Back
Top