making a field editable once it has data.

[ART]

I have a form in which multiple users will update data, however there are 4
fields which i need for them to be able to add new data and not overwrite
what is existing. Example: They user 1 may type a comment in the field. user
2 may later need to add additional comments but not change what was
previously entered.
Is this possible? again i only want this for a few fields not all on on the
form.
If not possible, can i simply make these fields uneditable once they are no
longer null?
I tried using a macro to setvalue to lock once the focus is lost but upon
closing and reopening the form the property resets to unlock.
what am i doing wrong?
thanks

 

[John W. Vinson]

I have a form in which multiple users will update data, however there are 4
fields which i need for them to be able to add new data and not overwrite
what is existing. Example: They user 1 may type a comment in the field. user
2 may later need to add additional comments but not change what was
previously entered.
Is this possible? again i only want this for a few fields not all on on the
form.

Well... it can be done, but it's not really a good design. If you have one
record with multiple comments, a better design would be to have two tables in
a one to many relationship; the second table would have a foreign key to the
primary key of your table, a Memo field for the comment (text field if your
comments will never exceed 255 characters), and probably a UserID field to
record who made the comment and a date/time field defaulting to Now() to
record when the comment was made. You could use a Subform to enter the
comments.

If not possible, can i simply make these fields uneditable once they are no
longer null?

You can set the control's Enabled property to No and its Locked property to
Yes in the Form's Current event if it is not Null.

You can't really allow a user to append a new comment and at the same time
*prevent* them from editing the existing comment. You can make it a bit easier
to add data to the existing field with code like this in the textbox's
GotFocus event:

Private Sub txtComment_GotFocus()
Me.txtComment.SelStart = Len(Me!txtComment & "")
End Sub

to put the cursor at the end of the data. This won't prevent the user from
manually backspacing or clicking into the existing data, though.

 

[NetworkTrade]

or use JV's advice in a modified way; add an unbound text box for 'Add A
Comment'

and then AfterUpdate of this new box put in a little code that appends it to
the existing comment; something like

Dim Comment as String
Comment = me.ExistingComment
Comment = Comment & me.AddComment
me.ExistingComment=Comment

now that I think of it you probably have to unlock that ExistingComment
field then relock it

this keeps the user out of the actual comment box....

 

[John W. Vinson]

or use JV's advice in a modified way; add an unbound text box for 'Add A
Comment'

and then AfterUpdate of this new box put in a little code that appends it to
the existing comment; something like

Dim Comment as String
Comment = me.ExistingComment
Comment = Comment & me.AddComment
me.ExistingComment=Comment

now that I think of it you probably have to unlock that ExistingComment
field then relock it

this keeps the user out of the actual comment box....

Nice! I've never seen it done that way but it would certainly work. You could
even throw in some dividers, e.g.

Comment = Comment & vbCrLf & vbCrLf & Now() & vbCrLf & me.AddComment

 

[ART]

Thank you so much. This logic makes perfect sense. I believe that I can
follow this. I am not very saavy with VBA. Learning as i go. Can you direct
me to a good tutorial for VBA?
thanks again

 

[Linq Adams via AccessMonster.com]

I've used this hack for a number of years for comment fields on medical
record apps. The idea is that corrections can be made to previous entries by
posting an addendum, but you can't actually alter the previous entry. It
utilizes the same ideas that NTC and John have mentioned.

In this hack a second, unbound textbox (TempDataBox) is used to enter the
data into. This textbox is originally hidden, and when the command button
(IndirectDataInput) is clicked, it appears. Data is entered, and when the
command button (now captioned “Commit Dataâ€) is clicked again, the entered
data is added to the memo field.

Now, in this example, there are two memo fields, but only one is bound, since
the other memo field is simply a temporary holding area. The memo field is
also locked so that all data entry has to be done thru the temporary textbox.

TempDataBox is unbound, and in the Property Box its Visible Property is set
originally set to No. I place mine side by side with CommentsField so the
user can refer to what's currently in the CommentsField section while
entering new notes.

CommentsField is bound to the underlying table/query, and its Locked Property
is set to Yes.

Place a command button on the form. Name it IndirectDataInput and in the
Properties Box set its Caption to “Add New Data.â€

Now use this code if you ***DO NOT*** want a ***TimeStamp*** with each entry

Private Sub IndirectDataInput_Click()
If IndirectDataInput.Caption = "Add New Data" Then
TempDataBox.Visible = True
TempDataBox.SetFocus
IndirectDataInput.Caption = "Commit Data"
Else
IndirectDataInput.Caption = "Add New Data"
If IsNull(Me.CommentsField) Then
If Len(Me.TempDataBox) > 0 Then
Me.CommentsField = Me.TempDataBox
Me.TempDataBox = ""
TempDataBox.Visible = False
Else
TempDataBox.Visible = False
End If
Else
If Len(Me.TempDataBox) > 0 Then
Me.CommentsField = Me.CommentsField & vbNewLine & Me.TempDataBox
Me.TempDataBox = ""
TempDataBox.Visible = False
Else
TempDataBox.Visible = False
End If

End If
End If
End Sub

Use this code if you ***DO WANT*** a ***TimeStamp*** with each entry

Private Sub IndirectDataInput_Click()
If IndirectDataInput.Caption = "Add New Data" Then
TempDataBox.Visible = True
TempDataBox.SetFocus
IndirectDataInput.Caption = "Commit Data"
Else
IndirectDataInput.Caption = "Add New Data"
If IsNull(Me.CommentsField) Then
If Len(Me.TempDataBox) > 0 Then
Me.CommentsField = Now() & " " & Me.TempDataBox
Me.TempDataBox = ""
TempDataBox.Visible = False
Else
TempDataBox.Visible = False
End If
Else
If Len(Me.TempDataBox) > 0 Then
Me.CommentsField = Me.CommentsField & vbNewLine & Now() & " " & Me.
TempDataBox
Me.TempDataBox = ""
TempDataBox.Visible = False
Else
TempDataBox.Visible = False
End If

End If
End If
End Sub

 

[Linq Adams via AccessMonster.com]

BTW, NTC, you don't have to Unlock the field then re-lock it. Locking a
control prevents you from ***physically*** entering data, but you can still
enter data ***programatically*** which is what my example does and what you
were suggesting.