I have a field (txtRevisedStartDate) which holds the revised start date for works. Currently, this is overtyped if a new revised start date is given. I don't need to hold information about all of the revised dates, but I would like to know how many times this field has been altered.
I tried doing a search on here, but didn't get very far. So I am hoping that a kind person out there can give me an idea of how to do this.
Add another field to your table, lets call it revInc (type Number).
Now include revInc on your form that allows users to change the Date.
In the After Update Event of the txtRevisedStartDate put this code..
Me.revInc = Me.revInc + 1
Make sure that the revInc form field is bound to the revInc table field. You can set visible = False if you don't want users to see this field.
I followed what you said, but when I altered dates in txtRevisedStartDate, nothing happened. I'm intrigued. However, I wasn't quite sure what you meant by this "Now include revInc on your form that allows users to change the Date. "
I have added this field to my subform, it is linked to the relevant table and altered dates, but nothing happened.
It's been a long day, so I probably just did something wrong.....
Thanks for your help.
Thanks for the reply, however I just can't seem to get anything to work.
If I try the same thing on a main form, there is no problem. So it's obviously a problem with the fact that it's on a subform. I just don't know how to resolve this.
Thanks for continuing to help me.
I tried your latest suggestion, but I'm still getting nothing.
It also seem that it's not a subform problem. When I said "If I try the same thing on a main form, there is no problem", this wasn't actually quite correct. As I was having trouble with this, I mailed my other half who created a mini db and it worked perfectly in this. He mailed this to me and this is what I meant by ' it works fine on a main form'. The code he had was
me!RevInc = me!RevInc + 1
However, I have just tried doing this on other forms I have in my db, and also in another db (no subforms on either) and it just won't work.
I also just created a new mini db to see if I could get it to work. No joy there. Why won't Access work?
I feel as though I'm going mad. Please help me!
Two more things.....
I have put a message box in after the code, and when I update the relevant field, the message box appears, so the code is firing, but not doing anything.
Secondly, I have just made a new field in the db where the code worked, and tried to get it to work on the new field. Didn't work.
What's going on?
1. The recordsource for the form doesn't contain the data element
2. The controlsource for the (hidden) control doesn't point to the right data element.
To do this scrupulously right, here are the steps I would take.
1. To the table holding the data you are tracking, add a field of type Number (Long Integer). Call it RevCt or loRevCt or whatever convention you use for such things.
2. If a query is actually driving the form rather than the table, add a reference to the new field to that query.
3. On the form in design mode, create a text box for this item by calling up the recordsource field list and the toolbox. Click the text box tool icon, then drag the field from the recordsource field list to the form like you would do for any other item. Select the label and delete it (use DELETE key with the label selected). Now click the properties icon to open the box's property sheet. You want the properties as follows:
4. Now comes the tricky part. You have to decide when the record has been changed. There are easy ways and hard ways to do this. The relatively easy way is to make a list of fields on the form that would be used to decide whether a change has occurred that qualifies for this revision. Let us say that you have six fields called Text1, Text2, ..., Text6. In the form's class module, create an event routine for the Form_BeforeUpdate event.
In this event code, you want something like this...
Assume the hidden revs counter text box is called [loRevCt] and your date of last update is called [dtLstUpd], which is internally in Date format but has a display format that converts it to something useful for display purposes.
Code:
Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim boChanged as Boolean
Dim loRevs as Long
boChanged = False
boChanged = ([Item1].Value <> [Item1].OldValue) Or boChanged
boChanged = ([Item2].Value <> [Item2].OldValue) Or boChanged
...
boChanged = ([Item6].Value <> [Item6].OldValue) or boChanged
If boChanged Then
loRevs = CLng([loRevCt]) + 1
[loRevCt] = CStr( loRevs )
[dtLstUpd] = Now()
End If
End Sub
I've got to leave the office now, but I've printed off your suggestion, and will try it out over the weekend at home.
Thanks to both of you for your help, and have a great weekend.
Hmmm. It's always the simple things that catch you out.....
After trying both suggestions, I was getting nowhere. I added msgboxes to each to check the code was firing, and it was. But no changes were happening.
So I added a new record to my db rather than trying it out on an existing record. And both suggestions worked. Reason (obviously being - but not to me) was that I needed to have the default value of zero in the 'No of revisions' field for all existing records, which I hadn't.
So thank you both again very much for your persistence in helping me.
