Update Memo field and not allow edit of old info 1

[Qino]

I have the following code in a subform which adds the date, user and a dividing line in a memo field:

Private Sub Command2_Click()
Me!Correspondence = Now() & "--" & CurrentUser() & vbCrLf & "-----------------------------------------------------------------------------------------------------------------------------------------------" & " " & vbCrLf & Me Correspondence


What I have not been able to do is protect the data that is already there from edits, I have tried Allowedits = False but it also shuts me out from adding to the field. I do have the security set up so that Admin and Full-users can have the run of the data and tables, but I need this one memo field to be accessed by all users.

I also haven't had any luck in moving the focus/cursor to the date/user line the above command creates, but that is a distant second to controlling access.

Any ideas will be welcome, Thanks

 

[RoyVidar]

This, I'm sorry to say, is one of the reasons why one should not use a memo field for this kind of thing, but rather have a related table, with one record per each alteration.

The new table could have one field for Date/Time, one for Username, one for the actual comment, and in this case, perhaps also a field for what kind of correspondence (letter, fax, e-mail, phone, msn...)

Roy-Vidar

 

[Qino]

Well, even if I create a seperate table, it needs to be accessed and added to by all users, but edits and deletes limited to myself and a few others. The request was to have it in a memo field because that is something all the users are familiar with.

Is there some way to link this to a word or text document that is password protected so that edits and deletes can only occur in the seperate documant and not in Access?

The security issue is key though. Allowing all staff to update this field for each unique cutomer, and allowing us to remove them as they are finished.

Any ideas?

 

[Knicks]

Hey, I just did this. Super easy.

1. Create an unbound field where you will type the latest contents of the note field

2. Create a button (no code yet)

3. Your note field set Locked = true


The button code:

me.notes.locked = false
me.notes = me.notes & " " & [unbound]
me.notes.locked = true


This way you simply keep adding to the note field which the button unlocks adds and then adds again. You can tinker with the code in the button to add time stamp ect..

 

[Qino]

Excellent! Thank you Knicks, I knew I was in the right neighborhood with the code but I just couldn't get it to do what I wanted. Now I have this version which works fine:


Me.Correspondence.Locked = False
Me.Correspondence = Now() & "--" & CurrentUser() & "--" & [ADD] & vbCrLf & "-----------------------------------------------------------------------------------------------------------------------------------------------" & vbCrLf & Me.Correspondence
Me.Correspondence.Locked = True
Me.ADD = Null
[/blue]

It also clears out the Add field so I can switch to a different record and not have to clear out the field manually.

I'll admit this is what my boss wants because I was able to do the separate memo idea that RoyVidar suggested using a new record in a separate table with a many to one relationship. She found it clunky and since 90% of the notes will be 10 words or less, this is perfect.

For anyone else who reads/needs this, also remember to modify the security settings on the table where the correspondence field is so ordinary users can insert data.

All modifications and deletions must occur in the table since the form field is locked (but admin can unlock it) but remember that if lower level users find that table, if they can add, they can delete as well. You might want to hide the table.