Protecting a Memo Field?

[Guest]

In my form, I have a memo field that I would like users to add comments; At
the same time, I don't want the user to be able to edit previous comments
once it has been saved.

Please help. Thanks

 

[Guest]

On the OnCurrent event of the form you can add the code that check if there
is value in the field, if it does it will lock the field

If IsNull(Me.FieldName) Or Me.FieldName = "" Then
Me.FieldName.Locked = False
Else
Me.FieldName.Locked = True
End If

 

[Allen Browne]

Instead of putting multple comments all into the one field, it might be
better to create a related table of comments, so people can add comments but
not edit or delete existing ones.

To do that, create a table containing these fields:
CommentID AutoNumber primary key
ID Number relates to the
primary key of your existing table.
Comments Memo the text of this note
CommentDateTime Date/Time Default value of: =Now()

This will be a subform on your main form.
The subform will be in continuous view.
Set these properties for the subform:
AllowAdditions: Yes
AllowDeletions: No
AllowEdits: No

Users will then be able to enter new comments at the end of the existing
records for comments, but not delete or edit existing ones.

 

[Rick B]

If all the notes are in one field, it would be difficult to do. You could
add a new control to your form and allow them to enter their notes there,
then use code to append it to the end of any existing notes. (I'd include a
line feed if it were me and also the date, time and userid).

Another way would be to store your comments in a second related table. This
is basically a one-to-many situation anyway. Then you could let your users
add to the table, but not edit or delete existing records.

 

[Guest]

Allen,

I tried it but I think I am still missing a piece. These comments need to
be associated with a specific project.

For example:
In the "Main Table" I have the following fields:
1. ProjectID
2. ProjectName
3. Accomplishments
4. Outstanding Issues
5. Risks

I need the comments for #3 - 5 do be associated with the project.

 

[Craig M. Bobchin]

When you create the comments table you will need to create a many to one
relationship with the "Main Table". The key would be project ID and you
will have to create an additional field for each of the three areas in
the main table you want comments for.

 

[Guest]

Hi Craig,

Do you have an example of this?

When you create the comments table you will need to create a many to one
relationship with the "Main Table". The key would be project ID and you
will have to create an additional field for each of the three areas in
the main table you want comments for.

 

[Allen Browne]

Craig is right. The #2 field I suggested (ID) relates to the ProjectID in
your original table, so that one ProjectID in your main table can have many
comments in this new table.

After creating the table, go to Relationships on the Tools menu to create
the relationships.

Then on your Projects form you will place a subform for the comments. When
you move to a project in the main form, the subform will show the comments
for that project in the subform.

 

[Guest]

Allen,

Thank you for your assistance. I will let this marinate overnight and give
it a try in the morning. Hopefully, the suggestions offered by yourself and
Craig will solve my problem. Also, do you know of any tutorials relating
this topic which may offer various case scenarios?

 

[Guest]

Hi Allen,

Now that I have my tables working and form layout designed. I would like to
do the following:

1. Have users add comments to the following fields: Accomplishments,
Outstanding Issues and Risks.

2. Once they have added their comments to the three fields, I want those
comments to be viewable (w/a running history) but not editable.

What would be the best way to address this? If VBA, do you know how to
write this code?

Thank you in advance for your assistance.

 

[Allen Browne]

The Current event of your form fires when you move record. Use this event to
set the Locked property of the 3 text boxes, depending on whether it is a
new record or not.

1. Open the form in design view.

2. Open the Properties box (View menu.)
Make sure the Title of the Properties box reads Form, so you are looking at
the properties of the form, not those of a text box.

3. On the Event tab of the Properties box, set the On Current property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the code window.
Set up to code like this:

Private Sub Form_Current()
Dim bLock As Boolean
bLock = Not Me.NewRecord
If Me.[Accomplishments].Locked <> bLock Then
Me.[Accomplishments].Locked = bLock
Me.[Outstanding Issues].Locked = bLock
Me.[Risks] = bLock
End If
End Sub

 

[Guest]

Ok. I gave it a try and then received a Microsoft Visual Basic Erro which
reads:

Received runtime error - '2147352567 (800200009)': This recordset is not
updateable.

At the bottom of that box, I have to option to end, Debug and Help.

What does all this mean and how can I fix it?

Thanks.

The Current event of your form fires when you move record. Use this event to
set the Locked property of the 3 text boxes, depending on whether it is a
new record or not.

1. Open the form in design view.

2. Open the Properties box (View menu.)
Make sure the Title of the Properties box reads Form, so you are looking at
the properties of the form, not those of a text box.

3. On the Event tab of the Properties box, set the On Current property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the code window.
Set up to code like this:

Private Sub Form_Current()
Dim bLock As Boolean
bLock = Not Me.NewRecord
If Me.[Accomplishments].Locked <> bLock Then
Me.[Accomplishments].Locked = bLock
Me.[Outstanding Issues].Locked = bLock
Me.[Risks] = bLock
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Now that I have my tables working and form layout designed. I would like
to
do the following:

1. Have users add comments to the following fields: Accomplishments,
Outstanding Issues and Risks.

2. Once they have added their comments to the three fields, I want those
comments to be viewable (w/a running history) but not editable.

What would be the best way to address this? If VBA, do you know how to
write this code?

Thank you in advance for your assistance.

 

[Allen Browne]

The last example should have been the same as the first two:
Me.[Risks].Locked = bLock

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The Current event of your form fires when you move record. Use this event
to set the Locked property of the 3 text boxes, depending on whether it is
a new record or not.

1. Open the form in design view.

2. Open the Properties box (View menu.)
Make sure the Title of the Properties box reads Form, so you are looking
at the properties of the form, not those of a text box.

3. On the Event tab of the Properties box, set the On Current property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the code window.
Set up to code like this:

Private Sub Form_Current()
Dim bLock As Boolean
bLock = Not Me.NewRecord
If Me.[Accomplishments].Locked <> bLock Then
Me.[Accomplishments].Locked = bLock
Me.[Outstanding Issues].Locked = bLock
Me.[Risks] = bLock
End If
End Sub

Hi Allen,

Now that I have my tables working and form layout designed. I would like
to
do the following:

1. Have users add comments to the following fields: Accomplishments,
Outstanding Issues and Risks.

2. Once they have added their comments to the three fields, I want those
comments to be viewable (w/a running history) but not editable.

What would be the best way to address this? If VBA, do you know how to
write this code?

Thank you in advance for your assistance.

 

[Guest]

That corrected the error but all fields are completely locked - nothing can
be added. How can additonal text be added but not modified after saving?

The last example should have been the same as the first two:
Me.[Risks].Locked = bLock

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The Current event of your form fires when you move record. Use this event
to set the Locked property of the 3 text boxes, depending on whether it is
a new record or not.

1. Open the form in design view.

2. Open the Properties box (View menu.)
Make sure the Title of the Properties box reads Form, so you are looking
at the properties of the form, not those of a text box.

3. On the Event tab of the Properties box, set the On Current property to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the code window.
Set up to code like this:

Private Sub Form_Current()
Dim bLock As Boolean
bLock = Not Me.NewRecord
If Me.[Accomplishments].Locked <> bLock Then
Me.[Accomplishments].Locked = bLock
Me.[Outstanding Issues].Locked = bLock
Me.[Risks] = bLock
End If
End Sub

Hi Allen,

Now that I have my tables working and form layout designed. I would like
to
do the following:

1. Have users add comments to the following fields: Accomplishments,
Outstanding Issues and Risks.

2. Once they have added their comments to the three fields, I want those
comments to be viewable (w/a running history) but not editable.

What would be the best way to address this? If VBA, do you know how to
write this code?

Thank you in advance for your assistance.

:

Craig is right. The #2 field I suggested (ID) relates to the ProjectID
in
your original table, so that one ProjectID in your main table can have
many
comments in this new table.

After creating the table, go to Relationships on the Tools menu to
create
the relationships.

Then on your Projects form you will place a subform for the comments.
When
you move to a project in the main form, the subform will show the
comments
for that project in the subform.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Craig,

Do you have an example of this?

:

When you create the comments table you will need to create a many to
one
relationship with the "Main Table". The key would be project ID and
you
will have to create an additional field for each of the three areas
in
the main table you want comments for.

Allen,

I tried it but I think I am still missing a piece. These comments
need
to
be associated with a specific project.

For example:
In the "Main Table" I have the following fields:
1. ProjectID
2. ProjectName
3. Accomplishments
4. Outstanding Issues
5. Risks

I need the comments for #3 - 5 do be associated with the project.

:

Instead of putting multple comments all into the one field, it
might
be
better to create a related table of comments, so people can add
comments but
not edit or delete existing ones.

To do that, create a table containing these fields:
CommentID AutoNumber primary key
ID Number relates to
the
primary key of your existing table.
Comments Memo the text of this
note
CommentDateTime Date/Time Default value of: =Now()

This will be a subform on your main form.
The subform will be in continuous view.
Set these properties for the subform:
AllowAdditions: Yes
AllowDeletions: No
AllowEdits: No

Users will then be able to enter new comments at the end of the
existing
records for comments, but not delete or edit existing ones.

In my form, I have a memo field that I would like users to add
comments;
At
the same time, I don't want the user to be able to edit
previous
comments
once it has been saved.

 

[Allen Browne]

Please go back and read the first reply I gave you.

It explains how you can have multiple related *records*, so you can add as
many more notes as you wish, but you cannot go back and change the existing
ones.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

That corrected the error but all fields are completely locked - nothing
can
be added. How can additonal text be added but not modified after saving?

The last example should have been the same as the first two:
Me.[Risks].Locked = bLock

The Current event of your form fires when you move record. Use this
event
to set the Locked property of the 3 text boxes, depending on whether it
is
a new record or not.

1. Open the form in design view.

2. Open the Properties box (View menu.)
Make sure the Title of the Properties box reads Form, so you are
looking
at the properties of the form, not those of a text box.

3. On the Event tab of the Properties box, set the On Current property
to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the code window.
Set up to code like this:

Private Sub Form_Current()
Dim bLock As Boolean
bLock = Not Me.NewRecord
If Me.[Accomplishments].Locked <> bLock Then
Me.[Accomplishments].Locked = bLock
Me.[Outstanding Issues].Locked = bLock
Me.[Risks] = bLock
End If
End Sub

Hi Allen,

Now that I have my tables working and form layout designed. I would
like
to
do the following:

1. Have users add comments to the following fields: Accomplishments,
Outstanding Issues and Risks.

2. Once they have added their comments to the three fields, I want
those
comments to be viewable (w/a running history) but not editable.

What would be the best way to address this? If VBA, do you know how
to
write this code?

Thank you in advance for your assistance.

:

Craig is right. The #2 field I suggested (ID) relates to the
ProjectID
in
your original table, so that one ProjectID in your main table can
have
many
comments in this new table.

After creating the table, go to Relationships on the Tools menu to
create
the relationships.

Then on your Projects form you will place a subform for the comments.
When
you move to a project in the main form, the subform will show the
comments
for that project in the subform.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Craig,

Do you have an example of this?

:

When you create the comments table you will need to create a many
to
one
relationship with the "Main Table". The key would be project ID
and
you
will have to create an additional field for each of the three
areas
in
the main table you want comments for.

Allen,

I tried it but I think I am still missing a piece. These
comments
need
to
be associated with a specific project.

For example:
In the "Main Table" I have the following fields:
1. ProjectID
2. ProjectName
3. Accomplishments
4. Outstanding Issues
5. Risks

I need the comments for #3 - 5 do be associated with the
project.

:

Instead of putting multple comments all into the one field, it
might
be
better to create a related table of comments, so people can
add
comments but
not edit or delete existing ones.

To do that, create a table containing these fields:
CommentID AutoNumber primary key
ID Number relates
to
the
primary key of your existing table.
Comments Memo the text of this
note
CommentDateTime Date/Time Default value of: =Now()

This will be a subform on your main form.
The subform will be in continuous view.
Set these properties for the subform:
AllowAdditions: Yes
AllowDeletions: No
AllowEdits: No

Users will then be able to enter new comments at the end of
the
existing
records for comments, but not delete or edit existing ones.

In my form, I have a memo field that I would like users to
add
comments;
At
the same time, I don't want the user to be able to edit
previous
comments
once it has been saved.

 

[Guest]

I have created the subform with the following properties:
Allow Edits: No
Allow Deletions: No
Allow Additions: Yes

Now I am receiving a complie error: Method or data member not found.

Which highlights: .Locked in the If statement

Main form: Allow Edits: Yes
Allow Deletions: No
Allow Additions: Yes
Data Entry: Yes

(I am very appreciative of the assistance you have offered thus far and I do
apologize for the multiple assistance requests.)

Please go back and read the first reply I gave you.

It explains how you can have multiple related *records*, so you can add as
many more notes as you wish, but you cannot go back and change the existing
ones.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

That corrected the error but all fields are completely locked - nothing
can
be added. How can additonal text be added but not modified after saving?

The last example should have been the same as the first two:
Me.[Risks].Locked = bLock

The Current event of your form fires when you move record. Use this
event
to set the Locked property of the 3 text boxes, depending on whether it
is
a new record or not.

1. Open the form in design view.

2. Open the Properties box (View menu.)
Make sure the Title of the Properties box reads Form, so you are
looking
at the properties of the form, not those of a text box.

3. On the Event tab of the Properties box, set the On Current property
to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the code window.
Set up to code like this:

Private Sub Form_Current()
Dim bLock As Boolean
bLock = Not Me.NewRecord
If Me.[Accomplishments].Locked <> bLock Then
Me.[Accomplishments].Locked = bLock
Me.[Outstanding Issues].Locked = bLock
Me.[Risks] = bLock
End If
End Sub

Hi Allen,

Now that I have my tables working and form layout designed. I would
like
to
do the following:

1. Have users add comments to the following fields: Accomplishments,
Outstanding Issues and Risks.

2. Once they have added their comments to the three fields, I want
those
comments to be viewable (w/a running history) but not editable.

What would be the best way to address this? If VBA, do you know how
to
write this code?

Thank you in advance for your assistance.

:

Craig is right. The #2 field I suggested (ID) relates to the
ProjectID
in
your original table, so that one ProjectID in your main table can
have
many
comments in this new table.

After creating the table, go to Relationships on the Tools menu to
create
the relationships.

Then on your Projects form you will place a subform for the comments.
When
you move to a project in the main form, the subform will show the
comments
for that project in the subform.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Craig,

Do you have an example of this?

:

When you create the comments table you will need to create a many
to
one
relationship with the "Main Table". The key would be project ID
and
you
will have to create an additional field for each of the three
areas
in
the main table you want comments for.

Allen,

I tried it but I think I am still missing a piece. These
comments
need
to
be associated with a specific project.

For example:
In the "Main Table" I have the following fields:
1. ProjectID
2. ProjectName
3. Accomplishments
4. Outstanding Issues
5. Risks

I need the comments for #3 - 5 do be associated with the
project.

:

Instead of putting multple comments all into the one field, it
might
be
better to create a related table of comments, so people can
add
comments but
not edit or delete existing ones.

To do that, create a table containing these fields:
CommentID AutoNumber primary key
ID Number relates
to
the
primary key of your existing table.
Comments Memo the text of this
note
CommentDateTime Date/Time Default value of: =Now()

This will be a subform on your main form.
The subform will be in continuous view.
Set these properties for the subform:
AllowAdditions: Yes
AllowDeletions: No
AllowEdits: No

Users will then be able to enter new comments at the end of
the
existing
records for comments, but not delete or edit existing ones.

In my form, I have a memo field that I would like users to
add
comments;
At
the same time, I don't want the user to be able to edit
previous
comments
once it has been saved.

 

[Allen Browne]

NP, I will need to leave it with you now.

You have lots of sample code snippets to use, and a design issue to
implement to get the related table of notes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have created the subform with the following properties:
Allow Edits: No
Allow Deletions: No
Allow Additions: Yes

Now I am receiving a complie error: Method or data member not found.

Which highlights: .Locked in the If statement

Main form: Allow Edits: Yes
Allow Deletions: No
Allow Additions: Yes
Data Entry: Yes

(I am very appreciative of the assistance you have offered thus far and I
do
apologize for the multiple assistance requests.)

Please go back and read the first reply I gave you.

It explains how you can have multiple related *records*, so you can add
as
many more notes as you wish, but you cannot go back and change the
existing
ones.

That corrected the error but all fields are completely locked - nothing
can
be added. How can additonal text be added but not modified after
saving?

:

The last example should have been the same as the first two:
Me.[Risks].Locked = bLock

The Current event of your form fires when you move record. Use this
event
to set the Locked property of the 3 text boxes, depending on whether
it
is
a new record or not.

1. Open the form in design view.

2. Open the Properties box (View menu.)
Make sure the Title of the Properties box reads Form, so you are
looking
at the properties of the form, not those of a text box.

3. On the Event tab of the Properties box, set the On Current
property
to:
[Event Procedure]

4. Click the Build button (...) beside this.
Access opens the code window.
Set up to code like this:

Private Sub Form_Current()
Dim bLock As Boolean
bLock = Not Me.NewRecord
If Me.[Accomplishments].Locked <> bLock Then
Me.[Accomplishments].Locked = bLock
Me.[Outstanding Issues].Locked = bLock
Me.[Risks] = bLock
End If
End Sub

Hi Allen,

Now that I have my tables working and form layout designed. I
would
like
to
do the following:

1. Have users add comments to the following fields:
Accomplishments,
Outstanding Issues and Risks.

2. Once they have added their comments to the three fields, I want
those
comments to be viewable (w/a running history) but not editable.

What would be the best way to address this? If VBA, do you know
how
to
write this code?

Thank you in advance for your assistance.

:

Craig is right. The #2 field I suggested (ID) relates to the
ProjectID
in
your original table, so that one ProjectID in your main table can
have
many
comments in this new table.

After creating the table, go to Relationships on the Tools menu to
create
the relationships.

Then on your Projects form you will place a subform for the
comments.
When
you move to a project in the main form, the subform will show the
comments
for that project in the subform.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Craig,

Do you have an example of this?

:

When you create the comments table you will need to create a
many
to
one
relationship with the "Main Table". The key would be project ID
and
you
will have to create an additional field for each of the three
areas
in
the main table you want comments for.

(e-mail address removed) says...
Allen,

I tried it but I think I am still missing a piece. These
comments
need
to
be associated with a specific project.

For example:
In the "Main Table" I have the following fields:
1. ProjectID
2. ProjectName
3. Accomplishments
4. Outstanding Issues
5. Risks

I need the comments for #3 - 5 do be associated with the
project.

:

Instead of putting multple comments all into the one field,
it
might
be
better to create a related table of comments, so people can
add
comments but
not edit or delete existing ones.

To do that, create a table containing these fields:
CommentID AutoNumber primary key
ID Number
relates
to
the
primary key of your existing table.
Comments Memo the text of
this
note
CommentDateTime Date/Time Default value of:
=Now()

This will be a subform on your main form.
The subform will be in continuous view.
Set these properties for the subform:
AllowAdditions: Yes
AllowDeletions: No
AllowEdits: No

Users will then be able to enter new comments at the end of
the
existing
records for comments, but not delete or edit existing ones.

In my form, I have a memo field that I would like users
to
add
comments;
At
the same time, I don't want the user to be able to edit
previous
comments
once it has been saved.