Pls Help me with code Builder

E

Endee

I have an active form functioning very well. My problem is that, I want any
update in any text field in the form to trigger the 'current system date' to
be displayed in one of the fields I have labelled 'date amended'. I want to
do this with codes alone and so far, this is what i came up with:

Private Sub DateAmended_AfterUpdate()
If Form.CurrentRecord = 1 Then
Me.DateAmended = Date
End If
End Sub

I dont know what to substitute currentrecord with. Please anyone, help me.
 
E

Endee

Thanks, I have tried that, and it did not update the form after updates. I
have also tried doing it from the entire form point of view using this code:

Private Sub Form_AfterUpdate()
If Me.NewRecord = True Then
Me.DateAmended = Date
End If
End Sub

Yet there's no improvement. Is there another way?
 
T

Tom Wickerath

Hi Endee,

Instead of using the Form_AfterUpdate event procedure, use the BeforeUpdate
event procedure. You can uncomment the two indicated lines if you only want
new records timestamped. I suspect that you want all records timestamped,
based on your initial post.

Note that the record must be saved in order for the Form_BeforeUpdate event
procedure to run. In other words, if you have the record selector displayed,
the record is dirty (not saved) if you can see the pencil symbol. This means
that you either have to navigate to a new record, and then come back to the
existing record, or add a Save Record command button to the form, or close
and re-open the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError

'If Me.NewRecord = True Then
Me.DateAmended = Date
'End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_AfterUpdate..."

Resume ExitProc
End Sub

If this is not acceptable (ie. you want the DateAmended to be immediate,
even if the record is still dirty), then create a new function in the form's
module like this:

Private Function DateLastEdited()
On Error GoTo ProcError

Me.DateAmended = Date

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure DateLastEdited..."
Resume ExitProc
End Function


Then, in form design view, with the properties dialog displayed, enter the
following into the AfterUpdate event procedure for all controls where the
user can change the data: =DateLastEdited()

You can use the "lasso" technique in form design view, to select several
controls at once.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thanks, I have tried that, and it did not update the form after updates. I
have also tried doing it from the entire form point of view using this code:

Private Sub Form_AfterUpdate()
If Me.NewRecord = True Then
Me.DateAmended = Date
End If
End Sub

Yet there's no improvement. Is there another way?
_______________________________________________

:

I have an active form functioning very well. My problem is that, I want any
update in any text field in the form to trigger the 'current system date' to
be displayed in one of the fields I have labelled 'date amended'. I want to
do this with codes alone and so far, this is what i came up with:

Private Sub DateAmended_AfterUpdate()
If Form.CurrentRecord = 1 Then
Me.DateAmended = Date
End If
End Sub

I dont know what to substitute currentrecord with. Please anyone, help me.
 
A

Arvin Meyer [MVP]

Try this:

Private Sub Form_AfterUpdate()
Me.DateAmended = Date
End Sub

The earlier code only applied to new records, the code above will apply to
all updates. Obviously, you'll need the DateAmended text box bound to a
field in your underlying table.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Endee said:
Thanks, I have tried that, and it did not update the form after updates. I
have also tried doing it from the entire form point of view using this code:

Private Sub Form_AfterUpdate()
If Me.NewRecord = True Then
Me.DateAmended = Date
End If
End Sub

Yet there's no improvement. Is there another way?
Click to expand...
me.
 
V

Van T. Dinh

It sounds to me that you want to use the Form_Dirty Event since you want the
current date/time to be displayed on the Form as soon as a Control, i.e.
Form is dirtied.
 
E

Endee

Thanks Tom, I tried that also but it has not resolved the problem either. I
really dont know what to do, I'd welcome something new, from anybody, please
help me out here.
 
T

Tom Wickerath

Hi Endee,

Can you describe what did or did not happen? You haven't left me with a lot
to go on...

I tested the solutions I proposed before posting them and they both worked
fine for me. However, I like Van's suggestion to use the Form_Dirty event
procedure better. However, using the Form_AfterUpdate event procedure does
not work properly (the record is always dirty).


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thanks Tom, I tried that also but it has not resolved the problem either. I
really dont know what to do, I'd welcome something new, from anybody, please
help me out here.
 
E

Endee

First of all, I wanna thank everyone for your contributions, it has been very
helpful too. The funny part is that it still doesnt work. Maybe the error is
from the way i presented the case... I have a form with about 15 different
fields in it, one of the fields in that form is the one i have labelled
'DateAmended'. Now what i want is for this DateAmended field to update with
the current system date anytime that any of the fields in the form is
updated. If i decide to change the contents of one of the fields in this
form, i want it to trigger a change in the content of the DateAmended field
to the current date. I was initially trying out:

If Me.CurrentRecord =1 Then
Me.DateAmended = Date()
End If

I then tried :

If Me.NewRecord = True Then
Me.DateAmended = Date()
End If

But that didnt work. What I need to know is how to use codes to instruct the
DateAmended field to display the current date anytime any of the other fields
is updated.
 
T

Tom Wickerath

Hi Endee,

Open your form in design view. Then click on View > Code. This should open
the code module for your form. Copy the following code and paste it into the
form's module. Make sure to comment out any other code that you already have
that was intended to achieve the same goal:

Private Sub Form_Dirty(Cancel As Integer)
On Error GoTo ProcError

Me.DateAmended = Date

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Dirty..."
Resume ExitProc
End Sub



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

First of all, I wanna thank everyone for your contributions, it has been very
helpful too. The funny part is that it still doesnt work. Maybe the error is
from the way i presented the case... I have a form with about 15 different
fields in it, one of the fields in that form is the one i have labelled
'DateAmended'. Now what i want is for this DateAmended field to update with
the current system date anytime that any of the fields in the form is
updated. If i decide to change the contents of one of the fields in this
form, i want it to trigger a change in the content of the DateAmended field
to the current date. I was initially trying out:

If Me.CurrentRecord =1 Then
Me.DateAmended = Date()
End If

I then tried :

If Me.NewRecord = True Then
Me.DateAmended = Date()
End If

But that didnt work. What I need to know is how to use codes to instruct the
DateAmended field to display the current date anytime any of the other fields
is updated.
 
Top