473,320 Members | 1,951 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How To Lock Memo field?

132 100+
I have a memo field designated as comments on a form....How would I go about "locking" the field so that the notes written by one user could not be modified at a later point by another?? In other words, a person could add to the comments field and read past comments, but once updated, the notes contained in the field could not be modified or deleted, only added to.
Jun 26 '07 #1
10 2227
missinglinq
3,532 Expert 2GB
The secret is to not give the users direct access to the memo field. Instead , you have them enter data into an unbound textbox, then add it to the current data in the memo field.

Command Button = InputData
Unbound Textbox = YourMemoFieldInput
Your Memo Field = YourMemoField

In Design View

Select YourMemoFieldInput, then goto Properties -- Format and set Visible to False

Select YourMemoField, then goto Properties -- Data and set Locked to True

Select the InputData command button and goto Properties -- Format and set its Caption to "Input New Notes" (leave off the quotes)

Now, in the form's code window:
Expand|Select|Wrap|Line Numbers
  1. Private Sub InputData_Click()
  2. If InputData.Caption = "Input New Notes" Then
  3.    YourMemoFieldInput.Visible = True
  4.    YourMemoFieldInput.SetFocus
  5.    InputData.Caption = "Add Data"
  6. Else
  7.    InputData.Caption = "Input New Notes"
  8.    Me.YourMemoField = Me.YourMemoField & Me.YourMemoFieldInput
  9.    Me.YourMemoFieldInput = " "
  10.    YourMemoFieldInput.Visible = False
  11. End If
  12. End Sub
(If you copy and paste this code and the line numbers are pasted into code, be sure to delete the numbers from the code!)

When the user needs to add a note, they click on the button whose caption reads "Input." The unbound textbox pops up and they enter their note. The Caption on the button now reads "Add Data." They click this button again, the new note is added to any previous notes, the unbound box disappears, and the button Caption reverts to "Input New Notes."

I use this routine in a db that involves medical records, where I have the requirement of not only not letting one user change another user's notes, but not allowing users to change their own notes! If a correction has to be made, they have to enter a new note with something on the order of "Correction to note of 6/26/07: yada, yada, yada..."

Good Luck!

Linq
Jun 26 '07 #2
Proaccesspro
132 100+
The secret is to not give the users direct access to the memo field. Instead , you have them enter data into an unbound textbox, then add it to the current data in the memo field.

Command Button = InputData
Unbound Textbox = YourMemoFieldInput
Your Memo Field = YourMemoField

In Design View

Select YourMemoFieldInput, then goto Properties -- Format and set Visible to False

Select YourMemoField, then goto Properties -- Data and set Locked to True

Select the InputData command button and goto Properties -- Format and set its Caption to "Input New Notes" (leave off the quotes)

Now, in the form's code window:
Expand|Select|Wrap|Line Numbers
  1. Private Sub InputData_Click()
  2. If InputData.Caption = "Input New Notes" Then
  3.    YourMemoFieldInput.Visible = True
  4.    YourMemoFieldInput.SetFocus
  5.    InputData.Caption = "Add Data"
  6. Else
  7.    InputData.Caption = "Input New Notes"
  8.    Me.YourMemoField = Me.YourMemoField & Me.YourMemoFieldInput
  9.    Me.YourMemoFieldInput = " "
  10.    YourMemoFieldInput.Visible = False
  11. End If
  12. End Sub
(If you copy and paste this code and the line numbers are pasted into code, be sure to delete the numbers from the code!)

When the user needs to add a note, they click on the button whose caption reads "Input." The unbound textbox pops up and they enter their note. The Caption on the button now reads "Add Data." They click this button again, the new note is added to any previous notes, the unbound box disappears, and the button Caption reverts to "Input New Notes."

I use this routine in a db that involves medical records, where I have the requirement of not only not letting one user change another user's notes, but not allowing users to change their own notes! If a correction has to be made, they have to enter a new note with something on the order of "Correction to note of 6/26/07: yada, yada, yada..."

Good Luck!

Linq

Works GREAT, Thanks.....However, I'd like to run a spell checker before the data is aoppended...Not quite getting the results I want...Here is the code I added:

Private Sub InputData_Click()

If InputData.Caption = "Input New Notes" Then
YourMemoFieldInput.Visible = True
YourMemoFieldInput.SetFocus
InputData.Caption = "Add Data"
Else
InputData.Caption = "Input New Notes"

'If YourMemoFieldInput textbox contains data run the
'Spell Checker after data is entered.

If Len(Me!YourMemoFieldInput & "") > 0 Then
DoCmd.RunCommand acCmdSpelling
End If

Me.YourMemoField = Me.YourMemoField & " " & Me.YourMemoFieldInput & " |DATA ADDED ON " & Now() & " | "

Me.YourMemoFieldInput = " "
YourMemoFieldInput.Visible = False

End If

End Sub
Jun 27 '07 #3
missinglinq
3,532 Expert 2GB
Okay. Since YourMemoFieldInput is an unbound control, you can't refer to it as Me!YourMemoFieldInput as you did in your modification. Also, if you don't limit the spellcheck to this unbound field, it'll try to check the memo field, and some goofus will try to change something in the locked field, and you'll get an error, so you need to limit the spellcheck to the single field. You do this by selecting text that you want checked. That's what the lines

YourMemoFieldInput.SelStart = 1
YourMemoFieldInput.SelLength = Len(YourMemoFieldInput.Value)

do. I also noticed that we didn't place a space between each set of notes as they're added, so I modified that. This new code should do what you need, including your spell check!
Expand|Select|Wrap|Line Numbers
  1. Private Sub InputData_Click()
  2. If InputData.Caption = "Input" Then
  3.    YourMemoFieldInput.Visible = True
  4.    YourMemoFieldInput.SetFocus
  5.    InputData.Caption = "Add Data"
  6. Else
  7.   If Len(YourMemoFieldInput.Value) > 0 Then
  8.     DoCmd.SetWarnings False
  9.     YourMemoFieldInput.SetFocus
  10.     YourMemoFieldInput.SelStart = 1
  11.     YourMemoFieldInput.SelLength = Len(YourMemoFieldInput.Value)
  12.     DoCmd.RunCommand acCmdSpelling
  13.      YourMemoFieldInput.SelLength = 0
  14.     DoCmd.SetWarnings True
  15.     YourMemoField.SetFocus
  16.   End If
  17.    InputData.Caption = "Input"
  18.    Me.YourMemoField = Me.YourMemoField & " " & Me.YourMemoFieldInput
  19.    Me.YourMemoFieldInput = " "
  20.    YourMemoFieldInput.Visible = False
  21. End If
  22. End Sub
  23.  
  24.  
Jun 28 '07 #4
Proaccesspro
132 100+
Okay. Since YourMemoFieldInput is an unbound control, you can't refer to it as Me!YourMemoFieldInput as you did in your modification. Also, if you don't limit the spellcheck to this unbound field, it'll try to check the memo field, and some goofus will try to change something in the locked field, and you'll get an error, so you need to limit the spellcheck to the single field. You do this by selecting text that you want checked. That's what the lines

YourMemoFieldInput.SelStart = 1
YourMemoFieldInput.SelLength = Len(YourMemoFieldInput.Value)

do. I also noticed that we didn't place a space between each set of notes as they're added, so I modified that. This new code should do what you need, including your spell check!
Expand|Select|Wrap|Line Numbers
  1. Private Sub InputData_Click()
  2. If InputData.Caption = "Input" Then
  3.    YourMemoFieldInput.Visible = True
  4.    YourMemoFieldInput.SetFocus
  5.    InputData.Caption = "Add Data"
  6. Else
  7.   If Len(YourMemoFieldInput.Value) > 0 Then
  8.     DoCmd.SetWarnings False
  9.     YourMemoFieldInput.SetFocus
  10.     YourMemoFieldInput.SelStart = 1
  11.     YourMemoFieldInput.SelLength = Len(YourMemoFieldInput.Value)
  12.     DoCmd.RunCommand acCmdSpelling
  13.      YourMemoFieldInput.SelLength = 0
  14.     DoCmd.SetWarnings True
  15.     YourMemoField.SetFocus
  16.   End If
  17.    InputData.Caption = "Input"
  18.    Me.YourMemoField = Me.YourMemoField & " " & Me.YourMemoFieldInput   &  " |PRECEEDING DATA ADDED ON " & Now() & " | "
  19.    Me.YourMemoFieldInput = " "
  20.    YourMemoFieldInput.Visible = False
  21. End If
  22. End Sub
  23.  
  24.  

ALMOST THERE!!! During testing, I encountered a bug

If you click on the INPUT NEW NOTES button and do not actually type any data and then click the ADD DATA button, it will append to YourMemoField. If you do it again (click INPUT NEW NOTES button, not actually type any data and then click the ADD DATA button), the spell checker then moves to the YourMemoField (permanent locked memo field). After it completes the check it will abend on the following line of code:

YourMemoFieldInput.SelLength = 0

and then produce this error message:

you can't reference a property or method for a control unless the control has the focus.

My thought is if the INPUT NEW NOTES field is BLANK, then nothing is added to the permanent field.
Jun 28 '07 #5
Proaccesspro
132 100+
ALMOST THERE!!! During testing, I encountered a bug

If you click on the INPUT NEW NOTES button and do not actually type any data and then click the ADD DATA button, it will append to YourMemoField. If you do it again (click INPUT NEW NOTES button, not actually type any data and then click the ADD DATA button), the spell checker then moves to the YourMemoField (permanent locked memo field). After it completes the check it will abend on the following line of code:

YourMemoFieldInput.SelLength = 0

and then produce this error message:

you can't reference a property or method for a control unless the control has the focus.

My thought is if the INPUT NEW NOTES field is BLANK, then nothing is added to the permanent field.

The first time you click the ADD DATA button, if the field is empty, it appends "preceeding data added on 6/27/2007 12:43 AM". The second time you clik the ADD DATA button; if the field is again blank, you get the error descibed above. If you actually add dat the second time, it works fine. It basically blows up after encountering blank data TWICE....
Jun 28 '07 #6
missinglinq
3,532 Expert 2GB
This is like the single misplaced comma that brought down the entire Washington, D.C. are phone system in the 70's!

Expand|Select|Wrap|Line Numbers
  1. Private Sub InputData_Click()
  2.  If InputData.Caption = "Input" Then
  3.     YourMemoFieldInput.Visible = True
  4.     YourMemoFieldInput.SetFocus
  5.     InputData.Caption = "Add Data"
  6.  Else
  7.    If Len(YourMemoFieldInput.Value) > 0 Then
  8.      DoCmd.SetWarnings False
  9.      YourMemoFieldInput.SetFocus
  10.      YourMemoFieldInput.SelStart = 1
  11.      YourMemoFieldInput.SelLength = Len(YourMemoFieldInput.Value)
  12.      DoCmd.RunCommand acCmdSpelling
  13.       YourMemoFieldInput.SelLength = 0
  14.      DoCmd.SetWarnings True
  15.      YourMemoField.SetFocus
  16.    End If
  17.     InputData.Caption = "Input"
  18.     Me.YourMemoField = Me.YourMemoField & " " & Me.YourMemoFieldInput
  19.      Me.YourMemoFieldInput = ""    '*******Change Here*************
  20.     YourMemoFieldInput.Visible = False
  21.  End If
  22.  End Sub
Line 19 was originally
Me.YourMemoFieldInput = " "
instead of
Me.YourMemoFieldInput = ""
which meant that
If Len(YourMemoFieldInput.Value) > 0
was always true! This meant that the code following the Else, including the spell check, was always carried out, even if the user didn't enter any data! That's fixed with this single space removed!
Jun 28 '07 #7
Proaccesspro
132 100+
This is like the single misplaced comma that brought down the entire Washington, D.C. are phone system in the 70's!

Expand|Select|Wrap|Line Numbers
  1. Private Sub InputData_Click()
  2.  If InputData.Caption = "Input" Then
  3.     YourMemoFieldInput.Visible = True
  4.     YourMemoFieldInput.SetFocus
  5.     InputData.Caption = "Add Data"
  6.  Else
  7.    If Len(YourMemoFieldInput.Value) > 0 Then
  8.      DoCmd.SetWarnings False
  9.      YourMemoFieldInput.SetFocus
  10.      YourMemoFieldInput.SelStart = 1
  11.      YourMemoFieldInput.SelLength = Len(YourMemoFieldInput.Value)
  12.      DoCmd.RunCommand acCmdSpelling
  13.       YourMemoFieldInput.SelLength = 0
  14.      DoCmd.SetWarnings True
  15.      YourMemoField.SetFocus
  16.    End If
  17.     InputData.Caption = "Input"
  18.     Me.YourMemoField = Me.YourMemoField & " " & Me.YourMemoFieldInput
  19.      Me.YourMemoFieldInput = ""    '*******Change Here*************
  20.     YourMemoFieldInput.Visible = False
  21.  End If
  22.  End Sub
Line 19 was originally
Me.YourMemoFieldInput = " "
instead of
Me.YourMemoFieldInput = ""
which meant that
If Len(YourMemoFieldInput.Value) > 0
was always true! This meant that the code following the Else, including the spell check, was always carried out, even if the user didn't enter any data! That's fixed with this single space removed!

Well, I made the change, but I still get the |PRECEEDING DATA ADDED ON " & Now() & " | added to the YourMemofield even though nothing was typed in the input field. The spell checker, however, does not operate unless there is text in the input field. WE ARE CLOSE!!!!

Here is the CODE:

Private Sub InputData_Click()

If InputData.Caption = "Input New Notes" Then
YourMemoFieldInput.Visible = True
YourMemoFieldInput.SetFocus
InputData.Caption = "Add Data"
Else
If Len(YourMemoFieldInput.Value) > 0 Then
DoCmd.SetWarnings False
YourMemoFieldInput.SetFocus
YourMemoFieldInput.SelStart = 1
YourMemoFieldInput.SelLength = Len(YourMemoFieldInput.Value)
DoCmd.RunCommand acCmdSpelling
YourMemoFieldInput.SelLength = 0
DoCmd.SetWarnings True
YourMemoField.SetFocus
End If
InputData.Caption = "Input New Notes"
Me.YourMemoField = Me.YourMemoField & " " & Me.YourMemoFieldInput & " |PRECEEDING DATA ADDED ON " & Now() & " | "
Me.YourMemoFieldInput = ""
YourMemoFieldInput.Visible = False
End If
Jun 28 '07 #8
missinglinq
3,532 Expert 2GB
I still get the |PRECEEDING DATA ADDED ON " & Now() & " | added to the YourMemofield even though nothing was typed in the input field.
You're getting this because you've added that to your code! Here's your code

Expand|Select|Wrap|Line Numbers
  1. Me.YourMemoField = Me.YourMemoField & " " & Me.YourMemoFieldInput & " |PRECEEDING DATA ADDED ON " & Now() & " | "
Here's the code I posted
Expand|Select|Wrap|Line Numbers
  1. Me.YourMemoField = Me.YourMemoField & " " & Me.YourMemoFieldInput & " " & Now()
  2.  
Copy all of this code and replace all of your current code with it! Remember, if the line numbers show up when you paste, you'll have to delete them.
Expand|Select|Wrap|Line Numbers
  1. Private Sub InputData_Click()
  2. If InputData.Caption = "Input New Notes" Then
  3.    YourMemoFieldInput.Visible = True
  4.    YourMemoFieldInput.SetFocus
  5.    InputData.Caption = "Add Data"
  6. Else
  7.   If Len(YourMemoFieldInput.Value) > 0 Then
  8.     DoCmd.SetWarnings False
  9.     YourMemoFieldInput.SetFocus
  10.     YourMemoFieldInput.SelStart = 1
  11.     YourMemoFieldInput.SelLength = Len(YourMemoFieldInput.Value)
  12.     DoCmd.RunCommand acCmdSpelling
  13.     YourMemoFieldInput.SelLength = 0
  14.     DoCmd.SetWarnings True
  15.     YourMemoField.SetFocus
  16.     Me.YourMemoField = Me.YourMemoField & " " & Me.YourMemoFieldInput & " " & Now
  17.   End If
  18.    InputData.Caption = "Input New Notes"
  19.    Me.YourMemoFieldInput = ""
  20.    YourMemoFieldInput.Visible = False
  21. End If
  22. End Sub
  23.  
Jun 29 '07 #9
Proaccesspro
132 100+
You're getting this because you've added that to your code! Here's your code

Expand|Select|Wrap|Line Numbers
  1. Me.YourMemoField = Me.YourMemoField & " " & Me.YourMemoFieldInput & " |PRECEEDING DATA ADDED ON " & Now() & " | "
Here's the code I posted
Expand|Select|Wrap|Line Numbers
  1. Me.YourMemoField = Me.YourMemoField & " " & Me.YourMemoFieldInput & " " & Now()
  2.  
Copy all of this code and replace all of your current code with it! Remember, if the line numbers show up when you paste, you'll have to delete them.
Expand|Select|Wrap|Line Numbers
  1. Private Sub InputData_Click()
  2. If InputData.Caption = "Input New Notes" Then
  3.    YourMemoFieldInput.Visible = True
  4.    YourMemoFieldInput.SetFocus
  5.    InputData.Caption = "Add Data"
  6. Else
  7.   If Len(YourMemoFieldInput.Value) > 0 Then
  8.     DoCmd.SetWarnings False
  9.     YourMemoFieldInput.SetFocus
  10.     YourMemoFieldInput.SelStart = 1
  11.     YourMemoFieldInput.SelLength = Len(YourMemoFieldInput.Value)
  12.     DoCmd.RunCommand acCmdSpelling
  13.     YourMemoFieldInput.SelLength = 0
  14.     DoCmd.SetWarnings True
  15.     YourMemoField.SetFocus
  16.     Me.YourMemoField = Me.YourMemoField & " " & Me.YourMemoFieldInput & " " & Now
  17.   End If
  18.    InputData.Caption = "Input New Notes"
  19.    Me.YourMemoFieldInput = ""
  20.    YourMemoFieldInput.Visible = False
  21. End If
  22. End Sub
  23.  
Yes....I added that snipit of code to ID the operator....I have it working correctly now...I simply added an IF statement BEFORE the code is executed that verifies there was actually something typed in the field....THANKS FOR ALL OF YOUR WISDOM!!
Jul 2 '07 #10
missinglinq
3,532 Expert 2GB
Glad we could help! ;0)>
Jul 3 '07 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Shyguy | last post by:
I want to create two buttons on a form. One would allow the user to Copy the contents of the current records memo field, the other would allow them to print. I set up a report based on the memo...
3
by: GorDon | last post by:
Hi, I have a report based on a query. The query grabs a memo field from my main table, yet when I display the memo field in the report it truncates the memo field (the field needs to hold more...
2
by: Bob Dydd | last post by:
Hi Everybody I have a Listbox that has a Memo field attached to it's 2nd column which on double click copies the memo field to another Memo Field. Forms!! = ListClassification.Column(1) My...
2
by: Kevin | last post by:
Thanks for the very quick response posting that I can use a memo datatype to store large amounts of text. I understand that I can turn on "Can Grow" for a memo type, but is there either a...
9
by: RMC | last post by:
Hello, I'm looking for a way to parse/format a memo field within a report. The Access 2000 database (application) has an equipment table that holds a memo field. Within the report, the memo...
2
by: Roger | last post by:
I've got two tables in sql2005 which have an 'ntext' field when I linked the first table in access97 last week using an odbc data source the access-field type was 'memo' when I link the 2nd...
10
by: ARC | last post by:
This is mainly a speed question. In this example: I have a QuotesHdr table that has a few memo fields. If these memo fields are used extensively by some users, and if their are a large number of...
2
by: austris | last post by:
Hi, It's single user .mdb (for now). i've created a generic form for a user to add a note to existing note in a memo Note field in a table (myTbl). The relevant part of the code: ' NoteText...
5
by: prakashwadhwani | last post by:
The Delete Event/Proc & "Save_Fields_In_Form_Header" Event/Proc in my form were working perfectly. However, after I added a call to the "Save_Fields_In_Form_Header" Event/Proc in the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.