Auto initials & auto date not working properly

[GD]

I have a subform (defaulted as continuous form) within a form that consists
of comment, user initials, and date fields. I need the user initials & date
fields to auto fill when an entry is made to the comment field. For those
comments that already exist, the user initials & dates need to remain as they
were at the time of their entry. My code in the subform is:

Private Sub txtComment_AfterUpdate()
If Me.txtComment <> "" Then
Me.txtCommentBy = CurrentUser()
Me.txtCommentDate = Date
Else
Me.txtCommentBy = ""
Me.txtCommentDate = ""
End If

End Sub

Private Sub Form_Current()
If Me.txtComment <> "" Then
Me.txtCommentBy = CurrentUser()
Me.txtCommentDate = Date
Else
Me.txtCommentBy = ""
Me.txtCommentDate = ""
End If

End Sub

Does the form need code, too?

Right now, for every form I open the user initials & dates change to my user
initials and today's date. Have I explained it adequately?

THANKS!!!

 

[BruceM]

It's doing what you are telling it to do. If you need the initials and date
to be completed automatically only when a new comment is added, you can use
Default Value in txtComment_AfterUpdate():

Me.txtCommentBy.DefaultValue = CurrentUser()
Me.txtCommentDate.DefaultValue = Date()

Default Value applies only with a new record. As you have it, any change to
the comment will cause the initials and date to reset. That may be your
intention, but if not, DefaultValue should do what you need.

I don't see the need to use any code in the Current event. If the values
are stored they should display automatically. If txtCommentBy and
txtCommentDate are unbound text boxes, what information would you have
appear in them?

BTW, you may want to use something like this to test txtComment:

If Len(Me.txtComment & vbNullString) = 0 Then ...

As it is a Null value (not the same as an empty string) in txtComment may
give you unexpected results.

 

[Dale Fye]

I agree with Bruce that I don't think the AfterUpdate or Current events are
the place to put this. I think, the way I would handle it is in the
Form_BeforeUpdate event, with code similar to:

Private Sub Form_BeforeUpdate

if LEN(me.txtComment & "") <> 0 AND _
LEN(me.txtCommentBy & "") = 0 THEN
me.txtCommentBy = CurrentUser()
me.txtCommentDate = Date()
end if

End Sub

This code will identify whether txtComment is NULL or "". But will only set
txtCommentBy and txtCommentDate if txtCommentBy has not already been set.

Since you are recording who made the entry, I assume this is for a
multi-user application. In which case I am concerned that this process will
allow a 2nd user to open this record, and make changees to txtComment, and
that you are not going to keep track of the change. My preferred method is
to have another table, where I store the "Comment History", so that if
txtComment changes, I write the original values (use the OldValue property of
the controls) to the history table, and retain the current value (along with
who made the changes and the date) in your main table.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[GD]

Thanks, Bruce! But I'm still having trouble. The 3 fields are bound to a
table. I changed my code to:

Private Sub txtComment_AfterUpdate()
Me.txtCommentBy.DefaultValue = CurrentUser()
Me.txtCommentDate.DefaultValue = Date

End Sub

(No form current)

But nothing appears in the txtCommentBy or txtCommentDate for the new
txtComment. However, #Name? appears in the next line of the continuous
subform in the txtCommentBy field.

It's a little tough to explain. Can I email the DB to you?

 

[GD]

Dale - When I change to your code below I get the following error message:
Procedure declaration does not match description of event or procedure having
the same name. Also, the initials and date field do not auto fill when a
comment is entered. Am I doing something wrong?

It is a multi-user database, but it is designed to have many comment fields,
so old comments should not be getting altered.

 

[Dale Fye]

The error you are getting is because you probably have two copies of the
Form_BeforeUpdate event. With the BeforeUpdate code, those values will be
filled in just before the record is saved, and not until. I recommend you go
back into the VB editor and delete that code.

If you want to see those values immediately, when a record is initiated, you
might try the Form_Current event. Open the form in design view. Select the
form itself, and in the Event tab of the Properties window, click the
dropdown at the right of the Current event. Select "Event Procedure", then
click the "..." button to the right.
In the VB Editor, you should see the following two lines:

Private Sub Form_Current()

End Sub

Cut the following lines and paste these inside the event procedure:

If me.NewRecord then
me.txtCommentBy = CurrentUser()
me.txtCommentDate = Date()
end if

Now, when you go to a new record, it will automatically fill in the
CommentBy and CommentDate fields, and make that information visible
immediately.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[BruceM]

I think the *text box* After Update event would be a good place for the
code. To the OP, if the record has been saved then neither DefaultValue or
the Me.NewRecord test will add a value to txtCommentBy or txtCommentDate.
Is there any code to save the record automatically?

Dale, I try to avoid automatically assigning a value in the Current event
because if the user backs out of creating a new record the inserted values
need to be deleted. DefaultValue avoids that problem in the Current event.
It should work in the text box After Update event, but for some reason it is
not. It may be helpful to test whether it is a new record. Just before
inserting the CurrentUser and Date values:
Debug.Print "New Record: " & Me.NewRecord
or:
MsgBox "New Record: " & Me.NewRecord

Changes to the code could be avoided by setting Allow Edits and Allow
Deletions to No. This would also prevent the comment's author from going
back to edit the comment. This could probably be avoided with some extra
work, if need be, perhaps by allowing edits only if the CurrentUser matches
the name in the CommentBy field. The use of CurrentUser suggest User Level
Security, but I don't think that has been specifically stated.

 

[BruceM]

Continued in the other part of the thread.

Thanks, Bruce! But I'm still having trouble. The 3 fields are bound to a
table. I changed my code to:

Private Sub txtComment_AfterUpdate()
Me.txtCommentBy.DefaultValue = CurrentUser()
Me.txtCommentDate.DefaultValue = Date

End Sub

(No form current)

But nothing appears in the txtCommentBy or txtCommentDate for the new
txtComment. However, #Name? appears in the next line of the continuous
subform in the txtCommentBy field.

It's a little tough to explain. Can I email the DB to you?