Memo Field

Brian

Sorry

I thought that I understood what you meant, but that is not the case. I will
try and explain myself at bit better....

I have develloped a loan application tracker. the data entry screen has a
comments box, which is basically a diary note of what has occured for a
particular customer.

I want to be able to set this box up so that the user can enter a free
format comment into this field. Any comments typed would be proceeded with a
Date/ Time stamp (to automatically record the date the comment was made).
Once the comments is saved for the customer, I want to be able to lock the
comments so that the user cannot delete or change what has been typed, unless
they have admin rights.

Here is the snippet of the code that makes up the data entry form for my
tracker...

Option Compare Database

Private Sub Command20_Click()
GoTo switchboard
End Sub
Private Sub Find_Application_Click()
On Error GoTo Err_Find_Application_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Application_Click:
Exit Sub

Err_Find_Application_Click:
MsgBox Err.Description
Resume Exit_Find_Application_Click

End Sub
Private Sub Quit_Click()
On Error GoTo Err_Quit_Click


DoCmd.Quit

Exit_Quit_Click:
Exit Sub

Err_Quit_Click:
MsgBox Err.Description
Resume Exit_Quit_Click

End Sub
Private Sub Exit_to_Main_Menu_Click()
On Error GoTo Err_Exit_to_Main_Menu_Click


DoCmd.Close

Exit_Exit_to_Main_Menu_Click:
Exit Sub

Err_Exit_to_Main_Menu_Click:
MsgBox Err.Description
Resume Exit_Exit_to_Main_Menu_Click

End Sub
Private Sub Add_New_Customer_Click()
On Error GoTo Err_Add_New_Customer_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_New_Customer_Click:
Exit Sub

Err_Add_New_Customer_Click:
MsgBox Err.Description
Resume Exit_Add_New_Customer_Click

End Sub

Private Sub Calendar0_Updated(Code As Integer)

End Sub

Private Sub Find_Application1_Click()
On Error GoTo Err_Find_Application1_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Application1_Click:
Exit Sub

Err_Find_Application1_Click:
MsgBox Err.Description
Resume Exit_Find_Application1_Click

End Sub
Private Sub Exit_To_Main_Menu1_Click()
On Error GoTo Err_Exit_To_Main_Menu1_Click


DoCmd.Close

Exit_Exit_To_Main_Menu1_Click:
Exit Sub

Err_Exit_To_Main_Menu1_Click:
MsgBox Err.Description
Resume Exit_Exit_To_Main_Menu1_Click

End Sub
Private Sub Add_New_Application_Click()
On Error GoTo Err_Add_New_Application_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_New_Application_Click:
Exit Sub

Err_Add_New_Application_Click:
MsgBox Err.Description
Resume Exit_Add_New_Application_Click

End Sub
Private Sub Delete_Record_Click()
On Error GoTo Err_Delete_Record_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record_Click:
Exit Sub

Err_Delete_Record_Click:
MsgBox Err.Description
Resume Exit_Delete_Record_Click

End Sub
Private Sub Delete_Record1_Click()
On Error GoTo Err_Delete_Record1_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record1_Click:
Exit Sub

Err_Delete_Record1_Click:
MsgBox Err.Description
Resume Exit_Delete_Record1_Click

End Sub

Private Sub Form_Load()

End Sub

I dont know if this helps.. I am a bit of a dummy when it comes to the
technical side of Access.



Regards



"Brian" wrote:

1. Have a separate field (can be hidden controls on your form so that the
user does not even see them) for CommentDate & another for CommentTime. Set
the default values to Date() and Time(), respectively, and make sure the user
opens the form just before entering the comment. Alternatively, set their
values in Form_BeforeUpdate (i.e. when the user finishes entering the comment
and goes on to the next one):

Private Sub Form_BeforeUpdate
If IsNull(CommentDate) then CommentDate = Date()
If IsNull(CommentTime) then CommentTime = Time()
End Sub

2. Set the AllowAdditions property to true but AllowEdits & AllowDeletions
properties to false for the form. This will allow users to add new records
but not edit nor delete existing ones.


"Sam" wrote:

Hello Everyone

I am trying to do two things with a memo text box (appart from allowing the
user to input text)

1. Each time a new comment is placed in the field, I would like the date and
time to be auto captured BEFORE the comments are typed

2. I want to Set up the field so that any comments mande cannot be errased.

Cheers

Sam

Sorry about the delay. I haven't gone through all your code in detail, but
here is a general approach. If you need more specific details, repost
specifics, and I will provide more detail.

It sounds like you want a running journal that automatically inserts the
date/time before each entry, and with the consolidated entry to be read-only.

If this is the case, I would create two separate boxes: one for the new
entry, and another for the complete history. The new entry could be a
unlocked/enabled unbound text box (let's call it NoteNew), while the
consolidated entry could be a locked and/or disabled text box bound to the
actual Notes memo field (let's call the field & box Notes).

Now, allow the user to input into the NoteNew field, then click a button
called PostNotes. Add this code:

Private Sub PostNotes
If IsNull(PostNotes) then Exit Sub ' does not append blank note
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
Notes.Refresh 'shows the newly-appended note
NoteNew = null 'blanks out new note, since it was just appended to Notes
entry
End Sub

The query PostCustomerNotes should be an udpate query on the CustomerNotes
table. The criteria of Customer should be [Forms]![YourFormName]![Customer]
to make sure it updates only the note for the current customer.

The update part of the statement (i.e. Notes field) should be something like
this:

[Notes] & " " & Date() & " " & [Forms]![YourFormName]![NoteNew]

Your form will need a combo box Customer where you can pick the customer,
and the record source needs to be filtered (Where Customer =
[Forms]![YourFormName]![Customer]) to make sure it shows only the
currenly-selected customer's notes.

Thus, when the user enters a note and clicks Post, the current NotesNew gets
added to the Notes field, the Notes field is refreshed to show its new
contents, and
the NotesNew field is nullified to prevent duplicate entry by the user.

On Form_Open, you can add something like this to allow the administrator to
edit the old notes:

Private Sub Form_Open
If UserLevel = "Admin" then
Notes.Enabled = True
Else
Notes.Enabled = False
End if
End Sub

This would require you to use a global variable UserLevel to identify the
current user's security level within your program.

Let me know if you need more specifics.

"Sam" wrote:

Brian

Sorry

I thought that I understood what you meant, but that is not the case. I will
try and explain myself at bit better....

I have develloped a loan application tracker. the data entry screen has a
comments box, which is basically a diary note of what has occured for a
particular customer.

I want to be able to set this box up so that the user can enter a free
format comment into this field. Any comments typed would be proceeded with a
Date/ Time stamp (to automatically record the date the comment was made).
Once the comments is saved for the customer, I want to be able to lock the
comments so that the user cannot delete or change what has been typed, unless
they have admin rights.

Here is the snippet of the code that makes up the data entry form for my
tracker...

Option Compare Database

Private Sub Command20_Click()
GoTo switchboard
End Sub
Private Sub Find_Application_Click()
On Error GoTo Err_Find_Application_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Application_Click:
Exit Sub

Err_Find_Application_Click:
MsgBox Err.Description
Resume Exit_Find_Application_Click

End Sub
Private Sub Quit_Click()
On Error GoTo Err_Quit_Click


DoCmd.Quit

Exit_Quit_Click:
Exit Sub

Err_Quit_Click:
MsgBox Err.Description
Resume Exit_Quit_Click

End Sub
Private Sub Exit_to_Main_Menu_Click()
On Error GoTo Err_Exit_to_Main_Menu_Click


DoCmd.Close

Exit_Exit_to_Main_Menu_Click:
Exit Sub

Err_Exit_to_Main_Menu_Click:
MsgBox Err.Description
Resume Exit_Exit_to_Main_Menu_Click

End Sub
Private Sub Add_New_Customer_Click()
On Error GoTo Err_Add_New_Customer_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_New_Customer_Click:
Exit Sub

Err_Add_New_Customer_Click:
MsgBox Err.Description
Resume Exit_Add_New_Customer_Click

End Sub

Private Sub Calendar0_Updated(Code As Integer)

End Sub

Private Sub Find_Application1_Click()
On Error GoTo Err_Find_Application1_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Application1_Click:
Exit Sub

Err_Find_Application1_Click:
MsgBox Err.Description
Resume Exit_Find_Application1_Click

End Sub
Private Sub Exit_To_Main_Menu1_Click()
On Error GoTo Err_Exit_To_Main_Menu1_Click


DoCmd.Close

Exit_Exit_To_Main_Menu1_Click:
Exit Sub

Err_Exit_To_Main_Menu1_Click:
MsgBox Err.Description
Resume Exit_Exit_To_Main_Menu1_Click

End Sub
Private Sub Add_New_Application_Click()
On Error GoTo Err_Add_New_Application_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_New_Application_Click:
Exit Sub

Err_Add_New_Application_Click:
MsgBox Err.Description
Resume Exit_Add_New_Application_Click

End Sub
Private Sub Delete_Record_Click()
On Error GoTo Err_Delete_Record_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record_Click:
Exit Sub

Err_Delete_Record_Click:
MsgBox Err.Description
Resume Exit_Delete_Record_Click

End Sub
Private Sub Delete_Record1_Click()
On Error GoTo Err_Delete_Record1_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record1_Click:
Exit Sub

Err_Delete_Record1_Click:
MsgBox Err.Description
Resume Exit_Delete_Record1_Click

End Sub

Private Sub Form_Load()

End Sub

I dont know if this helps.. I am a bit of a dummy when it comes to the
technical side of Access.



Regards



"Brian" wrote:

1. Have a separate field (can be hidden controls on your form so that the
user does not even see them) for CommentDate & another for CommentTime. Set
the default values to Date() and Time(), respectively, and make sure the user
opens the form just before entering the comment. Alternatively, set their
values in Form_BeforeUpdate (i.e. when the user finishes entering the comment
and goes on to the next one):

Private Sub Form_BeforeUpdate
If IsNull(CommentDate) then CommentDate = Date()
If IsNull(CommentTime) then CommentTime = Time()
End Sub

2. Set the AllowAdditions property to true but AllowEdits & AllowDeletions
properties to false for the form. This will allow users to add new records
but not edit nor delete existing ones.


"Sam" wrote:

Hello Everyone

I am trying to do two things with a memo text box (appart from allowing the
user to input text)

1. Each time a new comment is placed in the field, I would like the date and
time to be auto captured BEFORE the comments are typed

2. I want to Set up the field so that any comments mande cannot be errased.

Cheers

Sam

Oh, one more thing. I had " " to put spaces between the date & NoteNew
portion of the append. You actually want an Enter. I can't remember the
character to insert the Enter off the top of my head, but you can insert that
character instead of the space (" ") to ensure that the date & new notes go
on new lines. Post back if you can't find it.

"Sam" wrote:

Brian

Sorry

I thought that I understood what you meant, but that is not the case. I will
try and explain myself at bit better....

I have develloped a loan application tracker. the data entry screen has a
comments box, which is basically a diary note of what has occured for a
particular customer.

I want to be able to set this box up so that the user can enter a free
format comment into this field. Any comments typed would be proceeded with a
Date/ Time stamp (to automatically record the date the comment was made).
Once the comments is saved for the customer, I want to be able to lock the
comments so that the user cannot delete or change what has been typed, unless
they have admin rights.

Here is the snippet of the code that makes up the data entry form for my
tracker...

Option Compare Database

Private Sub Command20_Click()
GoTo switchboard
End Sub
Private Sub Find_Application_Click()
On Error GoTo Err_Find_Application_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Application_Click:
Exit Sub

Err_Find_Application_Click:
MsgBox Err.Description
Resume Exit_Find_Application_Click

End Sub
Private Sub Quit_Click()
On Error GoTo Err_Quit_Click


DoCmd.Quit

Exit_Quit_Click:
Exit Sub

Err_Quit_Click:
MsgBox Err.Description
Resume Exit_Quit_Click

End Sub
Private Sub Exit_to_Main_Menu_Click()
On Error GoTo Err_Exit_to_Main_Menu_Click


DoCmd.Close

Exit_Exit_to_Main_Menu_Click:
Exit Sub

Err_Exit_to_Main_Menu_Click:
MsgBox Err.Description
Resume Exit_Exit_to_Main_Menu_Click

End Sub
Private Sub Add_New_Customer_Click()
On Error GoTo Err_Add_New_Customer_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_New_Customer_Click:
Exit Sub

Err_Add_New_Customer_Click:
MsgBox Err.Description
Resume Exit_Add_New_Customer_Click

End Sub

Private Sub Calendar0_Updated(Code As Integer)

End Sub

Private Sub Find_Application1_Click()
On Error GoTo Err_Find_Application1_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Application1_Click:
Exit Sub

Err_Find_Application1_Click:
MsgBox Err.Description
Resume Exit_Find_Application1_Click

End Sub
Private Sub Exit_To_Main_Menu1_Click()
On Error GoTo Err_Exit_To_Main_Menu1_Click


DoCmd.Close

Exit_Exit_To_Main_Menu1_Click:
Exit Sub

Err_Exit_To_Main_Menu1_Click:
MsgBox Err.Description
Resume Exit_Exit_To_Main_Menu1_Click

End Sub
Private Sub Add_New_Application_Click()
On Error GoTo Err_Add_New_Application_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_New_Application_Click:
Exit Sub

Err_Add_New_Application_Click:
MsgBox Err.Description
Resume Exit_Add_New_Application_Click

End Sub
Private Sub Delete_Record_Click()
On Error GoTo Err_Delete_Record_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record_Click:
Exit Sub

Err_Delete_Record_Click:
MsgBox Err.Description
Resume Exit_Delete_Record_Click

End Sub
Private Sub Delete_Record1_Click()
On Error GoTo Err_Delete_Record1_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record1_Click:
Exit Sub

Err_Delete_Record1_Click:
MsgBox Err.Description
Resume Exit_Delete_Record1_Click

End Sub

Private Sub Form_Load()

End Sub

I dont know if this helps.. I am a bit of a dummy when it comes to the
technical side of Access.



Regards



"Brian" wrote:

1. Have a separate field (can be hidden controls on your form so that the
user does not even see them) for CommentDate & another for CommentTime. Set
the default values to Date() and Time(), respectively, and make sure the user
opens the form just before entering the comment. Alternatively, set their
values in Form_BeforeUpdate (i.e. when the user finishes entering the comment
and goes on to the next one):

Private Sub Form_BeforeUpdate
If IsNull(CommentDate) then CommentDate = Date()
If IsNull(CommentTime) then CommentTime = Time()
End Sub

2. Set the AllowAdditions property to true but AllowEdits & AllowDeletions
properties to false for the form. This will allow users to add new records
but not edit nor delete existing ones.


"Sam" wrote:

Hello Everyone

I am trying to do two things with a memo text box (appart from allowing the
user to input text)

1. Each time a new comment is placed in the field, I would like the date and
time to be auto captured BEFORE the comments are typed

2. I want to Set up the field so that any comments mande cannot be errased.

Cheers

Sam

Thanks for the reply Brian...

I understand part of your instructions and are confused with the others. I
will break down what I have:-

(You Wrote)
If this is the case, I would create two separate boxes: one for the new
entry, and another for the complete history. The new entry could be a
unlocked/enabled unbound text box (let's call it NoteNew), while the
consolidated entry could be a locked and/or disabled text box bound to the
actual Notes memo field (let's call the field & box Notes).

I HAVE DONE THIS!

(You wrote)

Now, allow the user to input into the NoteNew field, then click a button
called PostNotes. Add this code:

Private Sub PostNotes
If IsNull(PostNotes) then Exit Sub ' does not append blank note
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
Notes.Refresh 'shows the newly-appended note
NoteNew = null 'blanks out new note, since it was just appended to Notes
entry
End Sub

I CREATED A PUSH BUTTON THINGY AND COPIED THE CODE BELOW INTO THE "ON CLICK"
FIELD.

(You Wrote)

The query PostCustomerNotes should be an udpate query on the CustomerNotes
table. The criteria of Customer should be [Forms]![YourFormName]![Customer]
to make sure it updates only the note for the current customer.

The update part of the statement (i.e. Notes field) should be something like
this:

[Notes] & " " & Date() & " " & [Forms]![YourFormName]![NoteNew]

Your form will need a combo box Customer where you can pick the customer,
and the record source needs to be filtered (Where Customer =
[Forms]![YourFormName]![Customer]) to make sure it shows only the
currenly-selected customer's notes.

Thus, when the user enters a note and clicks Post, the current NotesNew gets
added to the Notes field, the Notes field is refreshed to show its new
contents, and
the NotesNew field is nullified to prevent duplicate entry by the user.

On Form_Open, you can add something like this to allow the administrator to
edit the old notes:

Private Sub Form_Open
If UserLevel = "Admin" then
Notes.Enabled = True
Else
Notes.Enabled = False
End if
End Sub

YOU NOW START TO LOOSE ME FROM HERE ON IN!

I created a table Called PostCustomerNotes This includes the fields ID
Customer ID & Of course....Notes (which is a memo field)

From there I created a query which only contains the "notes" field (Should I
have included the ID & Customer ID fields as well???)

From this point onwards, I get totally confused!

I know I sound like a real dummy (because I am) but if you could spell it
out to me, it would be muchly appreciated!!!!

Thanking you in advance

"Brian" wrote:

Sorry about the delay. I haven't gone through all your code in detail, but
here is a general approach. If you need more specific details, repost
specifics, and I will provide more detail.

It sounds like you want a running journal that automatically inserts the
date/time before each entry, and with the consolidated entry to be read-only.

If this is the case, I would create two separate boxes: one for the new
entry, and another for the complete history. The new entry could be a
unlocked/enabled unbound text box (let's call it NoteNew), while the
consolidated entry could be a locked and/or disabled text box bound to the
actual Notes memo field (let's call the field & box Notes).

Now, allow the user to input into the NoteNew field, then click a button
called PostNotes. Add this code:

Private Sub PostNotes
If IsNull(PostNotes) then Exit Sub ' does not append blank note
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
Notes.Refresh 'shows the newly-appended note
NoteNew = null 'blanks out new note, since it was just appended to Notes
entry
End Sub

The query PostCustomerNotes should be an udpate query on the CustomerNotes
table. The criteria of Customer should be [Forms]![YourFormName]![Customer]
to make sure it updates only the note for the current customer.

The update part of the statement (i.e. Notes field) should be something like
this:

[Notes] & " " & Date() & " " & [Forms]![YourFormName]![NoteNew]

Your form will need a combo box Customer where you can pick the customer,
and the record source needs to be filtered (Where Customer =
[Forms]![YourFormName]![Customer]) to make sure it shows only the
currenly-selected customer's notes.

Thus, when the user enters a note and clicks Post, the current NotesNew gets
added to the Notes field, the Notes field is refreshed to show its new
contents, and
the NotesNew field is nullified to prevent duplicate entry by the user.

On Form_Open, you can add something like this to allow the administrator to
edit the old notes:

Private Sub Form_Open
If UserLevel = "Admin" then
Notes.Enabled = True
Else
Notes.Enabled = False
End if
End Sub

This would require you to use a global variable UserLevel to identify the
current user's security level within your program.

Let me know if you need more specifics.

"Sam" wrote:

Brian

Sorry

I thought that I understood what you meant, but that is not the case. I will
try and explain myself at bit better....

I have develloped a loan application tracker. the data entry screen has a
comments box, which is basically a diary note of what has occured for a
particular customer.

I want to be able to set this box up so that the user can enter a free
format comment into this field. Any comments typed would be proceeded with a
Date/ Time stamp (to automatically record the date the comment was made).
Once the comments is saved for the customer, I want to be able to lock the
comments so that the user cannot delete or change what has been typed, unless
they have admin rights.

Here is the snippet of the code that makes up the data entry form for my
tracker...

Option Compare Database

Private Sub Command20_Click()
GoTo switchboard
End Sub
Private Sub Find_Application_Click()
On Error GoTo Err_Find_Application_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Application_Click:
Exit Sub

Err_Find_Application_Click:
MsgBox Err.Description
Resume Exit_Find_Application_Click

End Sub
Private Sub Quit_Click()
On Error GoTo Err_Quit_Click


DoCmd.Quit

Exit_Quit_Click:
Exit Sub

Err_Quit_Click:
MsgBox Err.Description
Resume Exit_Quit_Click

End Sub
Private Sub Exit_to_Main_Menu_Click()
On Error GoTo Err_Exit_to_Main_Menu_Click


DoCmd.Close

Exit_Exit_to_Main_Menu_Click:
Exit Sub

Err_Exit_to_Main_Menu_Click:
MsgBox Err.Description
Resume Exit_Exit_to_Main_Menu_Click

End Sub
Private Sub Add_New_Customer_Click()
On Error GoTo Err_Add_New_Customer_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_New_Customer_Click:
Exit Sub

Err_Add_New_Customer_Click:
MsgBox Err.Description
Resume Exit_Add_New_Customer_Click

End Sub

Private Sub Calendar0_Updated(Code As Integer)

End Sub

Private Sub Find_Application1_Click()
On Error GoTo Err_Find_Application1_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Application1_Click:
Exit Sub

Err_Find_Application1_Click:
MsgBox Err.Description
Resume Exit_Find_Application1_Click

End Sub
Private Sub Exit_To_Main_Menu1_Click()
On Error GoTo Err_Exit_To_Main_Menu1_Click


DoCmd.Close

Exit_Exit_To_Main_Menu1_Click:
Exit Sub

Err_Exit_To_Main_Menu1_Click:
MsgBox Err.Description
Resume Exit_Exit_To_Main_Menu1_Click

End Sub
Private Sub Add_New_Application_Click()
On Error GoTo Err_Add_New_Application_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_New_Application_Click:
Exit Sub

Err_Add_New_Application_Click:
MsgBox Err.Description
Resume Exit_Add_New_Application_Click

End Sub
Private Sub Delete_Record_Click()
On Error GoTo Err_Delete_Record_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record_Click:
Exit Sub

Err_Delete_Record_Click:
MsgBox Err.Description
Resume Exit_Delete_Record_Click

End Sub
Private Sub Delete_Record1_Click()
On Error GoTo Err_Delete_Record1_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record1_Click:
Exit Sub

Err_Delete_Record1_Click:
MsgBox Err.Description
Resume Exit_Delete_Record1_Click

End Sub

Private Sub Form_Load()

End Sub

I dont know if this helps.. I am a bit of a dummy when it comes to the
technical side of Access.



Regards



"Brian" wrote:

1. Have a separate field (can be hidden controls on your form so that the
user does not even see them) for CommentDate & another for CommentTime. Set
the default values to Date() and Time(), respectively, and make sure the user
opens the form just before entering the comment. Alternatively, set their
values in Form_BeforeUpdate (i.e. when the user finishes entering the comment
and goes on to the next one):

Private Sub Form_BeforeUpdate
If IsNull(CommentDate) then CommentDate = Date()
If IsNull(CommentTime) then CommentTime = Time()
End Sub

2. Set the AllowAdditions property to true but AllowEdits & AllowDeletions
properties to false for the form. This will allow users to add new records
but not edit nor delete existing ones.


"Sam" wrote:

Hello Everyone

I am trying to do two things with a memo text box (appart from allowing the
user to input text)

1. Each time a new comment is placed in the field, I would like the date and
time to be auto captured BEFORE the comments are typed

2. I want to Set up the field so that any comments mande cannot be errased.

Cheers

Sam

Here it is, with a slight modification. I made a very simple version of this
whole thing. It took me a little while to figure out how to force the
linefeeds so that each new entry starts on a new line.

The assumption here is that each customer will have a single CustomerNotes
entry that is a single memo field having all of the history in it. Here goes,
but keep in mind that the maximum length of a memo field is 65,535
characters. If a consolidated note for a customer eventually exceeds that,
you may find yourself trying to split the field later into individual note
entries (i.e. each note being a separate entry, displayed on a Continuous
form). Here is the simplified example:

Two tables:
1. Customers
Field 1: CustomerID (primary key, autonumber, long integer)
Field2: CustomerName (text)
2. CustomerNotes
Field1: ID (Primary key, autonumber, long integer)
Field2: CustomerID (long integer, no default)
Field3: Notes (memo)

Create a relationship (enforce referential integrity, cascade update on)
between the two tables on the CustomerID field.

Form: PostCustomerNotes. Default view: Single form. Record source (copy &
paste this into the record source, then look at the query design view using
the ellipsis to see how it was constructed):

SELECT CustomerNotes.* FROM CustomerNotes WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

In the form's header, create an UNBOUND combo box called CustomerIDSelector.
ColumnCount = 2, BoundColumn = 1, ColumnWidths = 0,2. RowSource is:

SELECT Customers.CustomerID, Customers.CustomerName FROM Customers ORDER BY
Customers.CustomerName;

This code goes into its AfterUpdate event:

Private Sub CustomerIDSelector_AfterUpdate()
Me.Requery 'display Notes for selected customer
End Sub

Also in the form header, create the UNBOUND NoteNew text box. Be sure to set
the Enter key behavior to New Line in Field for this text box so that the
user can enter multiple lines in one entry.

Also in the form header, create a button called PostNotes. Put this code in
its Click event:

PostNotes_Click()
If IsNull(CustomerIDSelector) Or IsNull(NoteNew) Or NoteNew = "" Then Exit
Sub ' does not append blank note
DoCmd.SetWarnings False
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
DoCmd.SetWarnings True
Me.Refresh 'shows the newly-appended note
NoteNew = Null 'blanks out new note, since it was just appended to Notes entry
End Sub

In the form's detail section, create a single bound but disabled text box
called Notes. Its ControlSource is just Notes (i.e. the Notes field)

Create a query called PostCustomerNotes. Go to SQL view & paste in this:

UPDATE CustomerNotes SET CustomerNotes.Notes = [Notes] & Chr(13) & Chr(10) &
Date() & Chr(13) & Chr(10) & [Forms]![PostCustomerNotes]![NoteNew]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

Again, you can look at design view to see how it is constructed.

Now, here is what happens. When a user opens the form, it is blank. When the
user selects a customer by customer name from the combo box at the top, it
shows that customers Notes field from the CustomerNotes table. The user can
then enter notes into the NoteNew text box and clicks the button. If the
NotesNew field or CustomerIDSelector are null/empty, it does nothing (to
prevent an error if there is nothing to append). If there are NotesNEw, this
udpates the existing Notes field to be:

1. Existing notes,
2. Followed by a linefeed,
3. Followed by the current date,
4. Followed by another linefeed,
5. Followed by the new notes

(You could easily insert a blank line by putting an extra Chr(13) & Chr(10)
combination before and/or after the date)

The code on the button then clears the new notes field and requeries the
form so that the new consolidated note appears in the Notes text box (better
make sure it is a big text box if these will be long entries)

One more note: if each customer really only needs one Notes entry, you could
easily enforce this by simply moving the Notes field to the Customers table,
where the CustomerID is already a primary key and thus will prevent more than
one note per customer. You would have to adjust the query & RowSource, etc.
above to match.

"Sam" wrote:

Thanks for the reply Brian...

I understand part of your instructions and are confused with the others. I
will break down what I have:-

(You Wrote)
If this is the case, I would create two separate boxes: one for the new
entry, and another for the complete history. The new entry could be a
unlocked/enabled unbound text box (let's call it NoteNew), while the
consolidated entry could be a locked and/or disabled text box bound to the
actual Notes memo field (let's call the field & box Notes).

I HAVE DONE THIS!

(You wrote)

Now, allow the user to input into the NoteNew field, then click a button
called PostNotes. Add this code:

Private Sub PostNotes
If IsNull(PostNotes) then Exit Sub ' does not append blank note
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
Notes.Refresh 'shows the newly-appended note
NoteNew = null 'blanks out new note, since it was just appended to Notes
entry
End Sub

I CREATED A PUSH BUTTON THINGY AND COPIED THE CODE BELOW INTO THE "ON CLICK"
FIELD.

(You Wrote)

The query PostCustomerNotes should be an udpate query on the CustomerNotes
table. The criteria of Customer should be [Forms]![YourFormName]![Customer]
to make sure it updates only the note for the current customer.

The update part of the statement (i.e. Notes field) should be something like
this:

[Notes] & " " & Date() & " " & [Forms]![YourFormName]![NoteNew]

Your form will need a combo box Customer where you can pick the customer,
and the record source needs to be filtered (Where Customer =
[Forms]![YourFormName]![Customer]) to make sure it shows only the
currenly-selected customer's notes.

Thus, when the user enters a note and clicks Post, the current NotesNew gets
added to the Notes field, the Notes field is refreshed to show its new
contents, and
the NotesNew field is nullified to prevent duplicate entry by the user.

On Form_Open, you can add something like this to allow the administrator to
edit the old notes:

Private Sub Form_Open
If UserLevel = "Admin" then
Notes.Enabled = True
Else
Notes.Enabled = False
End if
End Sub

YOU NOW START TO LOOSE ME FROM HERE ON IN!

I created a table Called PostCustomerNotes This includes the fields ID
Customer ID & Of course....Notes (which is a memo field)

From there I created a query which only contains the "notes" field (Should I
have included the ID & Customer ID fields as well???)

From this point onwards, I get totally confused!

I know I sound like a real dummy (because I am) but if you could spell it
out to me, it would be muchly appreciated!!!!

Thanking you in advance

"Brian" wrote:

Sorry about the delay. I haven't gone through all your code in detail, but
here is a general approach. If you need more specific details, repost
specifics, and I will provide more detail.

It sounds like you want a running journal that automatically inserts the
date/time before each entry, and with the consolidated entry to be read-only.

If this is the case, I would create two separate boxes: one for the new
entry, and another for the complete history. The new entry could be a
unlocked/enabled unbound text box (let's call it NoteNew), while the
consolidated entry could be a locked and/or disabled text box bound to the
actual Notes memo field (let's call the field & box Notes).

Now, allow the user to input into the NoteNew field, then click a button
called PostNotes. Add this code:

Private Sub PostNotes
If IsNull(PostNotes) then Exit Sub ' does not append blank note
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
Notes.Refresh 'shows the newly-appended note
NoteNew = null 'blanks out new note, since it was just appended to Notes
entry
End Sub

The query PostCustomerNotes should be an udpate query on the CustomerNotes
table. The criteria of Customer should be [Forms]![YourFormName]![Customer]
to make sure it updates only the note for the current customer.

The update part of the statement (i.e. Notes field) should be something like
this:

[Notes] & " " & Date() & " " & [Forms]![YourFormName]![NoteNew]

Your form will need a combo box Customer where you can pick the customer,
and the record source needs to be filtered (Where Customer =
[Forms]![YourFormName]![Customer]) to make sure it shows only the
currenly-selected customer's notes.

Thus, when the user enters a note and clicks Post, the current NotesNew gets
added to the Notes field, the Notes field is refreshed to show its new
contents, and
the NotesNew field is nullified to prevent duplicate entry by the user.

On Form_Open, you can add something like this to allow the administrator to
edit the old notes:

Private Sub Form_Open
If UserLevel = "Admin" then
Notes.Enabled = True
Else
Notes.Enabled = False
End if
End Sub

This would require you to use a global variable UserLevel to identify the
current user's security level within your program.

Let me know if you need more specifics.

"Sam" wrote:

Brian

Sorry

I thought that I understood what you meant, but that is not the case. I will
try and explain myself at bit better....

I have develloped a loan application tracker. the data entry screen has a
comments box, which is basically a diary note of what has occured for a
particular customer.

I want to be able to set this box up so that the user can enter a free
format comment into this field. Any comments typed would be proceeded with a
Date/ Time stamp (to automatically record the date the comment was made).
Once the comments is saved for the customer, I want to be able to lock the
comments so that the user cannot delete or change what has been typed, unless
they have admin rights.

Here is the snippet of the code that makes up the data entry form for my
tracker...

Option Compare Database

Private Sub Command20_Click()
GoTo switchboard
End Sub
Private Sub Find_Application_Click()
On Error GoTo Err_Find_Application_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Application_Click:
Exit Sub

Err_Find_Application_Click:
MsgBox Err.Description
Resume Exit_Find_Application_Click

End Sub
Private Sub Quit_Click()
On Error GoTo Err_Quit_Click


DoCmd.Quit

Exit_Quit_Click:
Exit Sub

Err_Quit_Click:
MsgBox Err.Description
Resume Exit_Quit_Click

End Sub
Private Sub Exit_to_Main_Menu_Click()
On Error GoTo Err_Exit_to_Main_Menu_Click


DoCmd.Close

Exit_Exit_to_Main_Menu_Click:
Exit Sub

Err_Exit_to_Main_Menu_Click:
MsgBox Err.Description
Resume Exit_Exit_to_Main_Menu_Click

End Sub
Private Sub Add_New_Customer_Click()
On Error GoTo Err_Add_New_Customer_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_New_Customer_Click:
Exit Sub

Err_Add_New_Customer_Click:
MsgBox Err.Description
Resume Exit_Add_New_Customer_Click

End Sub

Private Sub Calendar0_Updated(Code As Integer)

End Sub

Private Sub Find_Application1_Click()
On Error GoTo Err_Find_Application1_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Application1_Click:
Exit Sub

Err_Find_Application1_Click:
MsgBox Err.Description
Resume Exit_Find_Application1_Click

End Sub
Private Sub Exit_To_Main_Menu1_Click()
On Error GoTo Err_Exit_To_Main_Menu1_Click


DoCmd.Close

Exit_Exit_To_Main_Menu1_Click:
Exit Sub

Err_Exit_To_Main_Menu1_Click:
MsgBox Err.Description
Resume Exit_Exit_To_Main_Menu1_Click

End Sub
Private Sub Add_New_Application_Click()
On Error GoTo Err_Add_New_Application_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_New_Application_Click:
Exit Sub

Err_Add_New_Application_Click:
MsgBox Err.Description
Resume Exit_Add_New_Application_Click

End Sub
Private Sub Delete_Record_Click()
On Error GoTo Err_Delete_Record_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record_Click:
Exit Sub

Err_Delete_Record_Click:
MsgBox Err.Description
Resume Exit_Delete_Record_Click

End Sub
Private Sub Delete_Record1_Click()
On Error GoTo Err_Delete_Record1_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record1_Click:
Exit Sub

Err_Delete_Record1_Click:
MsgBox Err.Description
Resume Exit_Delete_Record1_Click

Brian... this info has been great thank you! I was just wondering if there
is an easy way to make the new notes added appear at the top of the memo
field rather than being added to the bottom of the memo, so that the notes
appear in reverse chronological order?

Eg: The most recent note goes to the top...

19-Oct-2005
Posted a question on memo fields.
18-Oct-2005
Set up client database.
Etc...

Many thanks (in advance)
Suzanne

"Brian" wrote:

Here it is, with a slight modification. I made a very simple version of this
whole thing. It took me a little while to figure out how to force the
linefeeds so that each new entry starts on a new line.

The assumption here is that each customer will have a single CustomerNotes
entry that is a single memo field having all of the history in it. Here goes,
but keep in mind that the maximum length of a memo field is 65,535
characters. If a consolidated note for a customer eventually exceeds that,
you may find yourself trying to split the field later into individual note
entries (i.e. each note being a separate entry, displayed on a Continuous
form). Here is the simplified example:

Two tables:
1. Customers
Field 1: CustomerID (primary key, autonumber, long integer)
Field2: CustomerName (text)
2. CustomerNotes
Field1: ID (Primary key, autonumber, long integer)
Field2: CustomerID (long integer, no default)
Field3: Notes (memo)

Create a relationship (enforce referential integrity, cascade update on)
between the two tables on the CustomerID field.

Form: PostCustomerNotes. Default view: Single form. Record source (copy &
paste this into the record source, then look at the query design view using
the ellipsis to see how it was constructed):

SELECT CustomerNotes.* FROM CustomerNotes WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

In the form's header, create an UNBOUND combo box called CustomerIDSelector.
ColumnCount = 2, BoundColumn = 1, ColumnWidths = 0,2. RowSource is:

SELECT Customers.CustomerID, Customers.CustomerName FROM Customers ORDER BY
Customers.CustomerName;

This code goes into its AfterUpdate event:

Private Sub CustomerIDSelector_AfterUpdate()
Me.Requery 'display Notes for selected customer
End Sub

Also in the form header, create the UNBOUND NoteNew text box. Be sure to set
the Enter key behavior to New Line in Field for this text box so that the
user can enter multiple lines in one entry.

Also in the form header, create a button called PostNotes. Put this code in
its Click event:

PostNotes_Click()
If IsNull(CustomerIDSelector) Or IsNull(NoteNew) Or NoteNew = "" Then Exit
Sub ' does not append blank note
DoCmd.SetWarnings False
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
DoCmd.SetWarnings True
Me.Refresh 'shows the newly-appended note
NoteNew = Null 'blanks out new note, since it was just appended to Notes entry
End Sub

In the form's detail section, create a single bound but disabled text box
called Notes. Its ControlSource is just Notes (i.e. the Notes field)

Create a query called PostCustomerNotes. Go to SQL view & paste in this:

UPDATE CustomerNotes SET CustomerNotes.Notes = [Notes] & Chr(13) & Chr(10) &
Date() & Chr(13) & Chr(10) & [Forms]![PostCustomerNotes]![NoteNew]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

Again, you can look at design view to see how it is constructed.

Now, here is what happens. When a user opens the form, it is blank. When the
user selects a customer by customer name from the combo box at the top, it
shows that customers Notes field from the CustomerNotes table. The user can
then enter notes into the NoteNew text box and clicks the button. If the
NotesNew field or CustomerIDSelector are null/empty, it does nothing (to
prevent an error if there is nothing to append). If there are NotesNEw, this
udpates the existing Notes field to be:

1. Existing notes,
2. Followed by a linefeed,
3. Followed by the current date,
4. Followed by another linefeed,
5. Followed by the new notes

(You could easily insert a blank line by putting an extra Chr(13) & Chr(10)
combination before and/or after the date)

The code on the button then clears the new notes field and requeries the
form so that the new consolidated note appears in the Notes text box (better
make sure it is a big text box if these will be long entries)

One more note: if each customer really only needs one Notes entry, you could
easily enforce this by simply moving the Notes field to the Customers table,
where the CustomerID is already a primary key and thus will prevent more than
one note per customer. You would have to adjust the query & RowSource, etc.
above to match.

"Sam" wrote:

Thanks for the reply Brian...

I understand part of your instructions and are confused with the others. I
will break down what I have:-

(You Wrote)
If this is the case, I would create two separate boxes: one for the new
entry, and another for the complete history. The new entry could be a
unlocked/enabled unbound text box (let's call it NoteNew), while the
consolidated entry could be a locked and/or disabled text box bound to the
actual Notes memo field (let's call the field & box Notes).

I HAVE DONE THIS!

(You wrote)

Now, allow the user to input into the NoteNew field, then click a button
called PostNotes. Add this code:

Private Sub PostNotes
If IsNull(PostNotes) then Exit Sub ' does not append blank note
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
Notes.Refresh 'shows the newly-appended note
NoteNew = null 'blanks out new note, since it was just appended to Notes
entry
End Sub

I CREATED A PUSH BUTTON THINGY AND COPIED THE CODE BELOW INTO THE "ON CLICK"
FIELD.

(You Wrote)

The query PostCustomerNotes should be an udpate query on the CustomerNotes
table. The criteria of Customer should be [Forms]![YourFormName]![Customer]
to make sure it updates only the note for the current customer.

The update part of the statement (i.e. Notes field) should be something like
this:

[Notes] & " " & Date() & " " & [Forms]![YourFormName]![NoteNew]

Your form will need a combo box Customer where you can pick the customer,
and the record source needs to be filtered (Where Customer =
[Forms]![YourFormName]![Customer]) to make sure it shows only the
currenly-selected customer's notes.

Thus, when the user enters a note and clicks Post, the current NotesNew gets
added to the Notes field, the Notes field is refreshed to show its new
contents, and
the NotesNew field is nullified to prevent duplicate entry by the user.

On Form_Open, you can add something like this to allow the administrator to
edit the old notes:

Private Sub Form_Open
If UserLevel = "Admin" then
Notes.Enabled = True
Else
Notes.Enabled = False
End if
End Sub

YOU NOW START TO LOOSE ME FROM HERE ON IN!

I created a table Called PostCustomerNotes This includes the fields ID
Customer ID & Of course....Notes (which is a memo field)

From there I created a query which only contains the "notes" field (Should I
have included the ID & Customer ID fields as well???)

From this point onwards, I get totally confused!

I know I sound like a real dummy (because I am) but if you could spell it
out to me, it would be muchly appreciated!!!!

Thanking you in advance

"Brian" wrote:

Sorry about the delay. I haven't gone through all your code in detail, but
here is a general approach. If you need more specific details, repost
specifics, and I will provide more detail.

It sounds like you want a running journal that automatically inserts the
date/time before each entry, and with the consolidated entry to be read-only.

If this is the case, I would create two separate boxes: one for the new
entry, and another for the complete history. The new entry could be a
unlocked/enabled unbound text box (let's call it NoteNew), while the
consolidated entry could be a locked and/or disabled text box bound to the
actual Notes memo field (let's call the field & box Notes).

Now, allow the user to input into the NoteNew field, then click a button
called PostNotes. Add this code:

Private Sub PostNotes
If IsNull(PostNotes) then Exit Sub ' does not append blank note
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
Notes.Refresh 'shows the newly-appended note
NoteNew = null 'blanks out new note, since it was just appended to Notes
entry
End Sub

The query PostCustomerNotes should be an udpate query on the CustomerNotes
table. The criteria of Customer should be [Forms]![YourFormName]![Customer]
to make sure it updates only the note for the current customer.

The update part of the statement (i.e. Notes field) should be something like
this:

[Notes] & " " & Date() & " " & [Forms]![YourFormName]![NoteNew]

Your form will need a combo box Customer where you can pick the customer,
and the record source needs to be filtered (Where Customer =
[Forms]![YourFormName]![Customer]) to make sure it shows only the
currenly-selected customer's notes.

Thus, when the user enters a note and clicks Post, the current NotesNew gets
added to the Notes field, the Notes field is refreshed to show its new
contents, and
the NotesNew field is nullified to prevent duplicate entry by the user.

On Form_Open, you can add something like this to allow the administrator to
edit the old notes:

Private Sub Form_Open
If UserLevel = "Admin" then
Notes.Enabled = True
Else
Notes.Enabled = False
End if
End Sub

This would require you to use a global variable UserLevel to identify the
current user's security level within your program.

Let me know if you need more specifics.

"Sam" wrote:

Brian

Sorry

I thought that I understood what you meant, but that is not the case. I will
try and explain myself at bit better....

I have develloped a loan application tracker. the data entry screen has a
comments box, which is basically a diary note of what has occured for a
particular customer.

I want to be able to set this box up so that the user can enter a free
format comment into this field. Any comments typed would be proceeded with a
Date/ Time stamp (to automatically record the date the comment was made).
Once the comments is saved for the customer, I want to be able to lock the
comments so that the user cannot delete or change what has been typed, unless
they have admin rights.

Here is the snippet of the code that makes up the data entry form for my
tracker...

Option Compare Database

Private Sub Command20_Click()
GoTo switchboard
End Sub
Private Sub Find_Application_Click()
On Error GoTo Err_Find_Application_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Application_Click:
Exit Sub

Err_Find_Application_Click:
MsgBox Err.Description
Resume Exit_Find_Application_Click

End Sub
Private Sub Quit_Click()
On Error GoTo Err_Quit_Click


DoCmd.Quit

Exit_Quit_Click:
Exit Sub

Err_Quit_Click:
MsgBox Err.Description
Resume Exit_Quit_Click

End Sub
Private Sub Exit_to_Main_Menu_Click()
On Error GoTo Err_Exit_to_Main_Menu_Click

It's been so long since I posted this that I forgot that I had done it (and
mostly, what I said, also). However, yes there is a way to make the most
current note go to the top. I have not tested this , but you should be able
to just reverse the two portions of the Update query; move the [Notes] to the
end of this:

UPDATE CustomerNotes SET CustomerNotes.Notes = [Notes] & Chr(13) & Chr(10) &
Date() & Chr(13) & Chr(10) & [Forms]![PostCustomerNotes]![NoteNew]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

so that it looks like this instead:

UPDATE CustomerNotes SET CustomerNotes.Notes = Date() & Chr(13) & Chr(10) &
[Forms]![PostCustomerNotes]![NoteNew]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector])) & Chr(13) & Chr(10) & [Notes];

This should put the existing notes at the end of the memo field instead of
the beginning.


"Suzy" wrote:

Brian... this info has been great thank you! I was just wondering if there
is an easy way to make the new notes added appear at the top of the memo
field rather than being added to the bottom of the memo, so that the notes
appear in reverse chronological order?

Eg: The most recent note goes to the top...

19-Oct-2005
Posted a question on memo fields.
18-Oct-2005
Set up client database.
Etc...

Many thanks (in advance)
Suzanne

"Brian" wrote:

Here it is, with a slight modification. I made a very simple version of this
whole thing. It took me a little while to figure out how to force the
linefeeds so that each new entry starts on a new line.

The assumption here is that each customer will have a single CustomerNotes
entry that is a single memo field having all of the history in it. Here goes,
but keep in mind that the maximum length of a memo field is 65,535
characters. If a consolidated note for a customer eventually exceeds that,
you may find yourself trying to split the field later into individual note
entries (i.e. each note being a separate entry, displayed on a Continuous
form). Here is the simplified example:

Two tables:
1. Customers
Field 1: CustomerID (primary key, autonumber, long integer)
Field2: CustomerName (text)
2. CustomerNotes
Field1: ID (Primary key, autonumber, long integer)
Field2: CustomerID (long integer, no default)
Field3: Notes (memo)

Create a relationship (enforce referential integrity, cascade update on)
between the two tables on the CustomerID field.

Form: PostCustomerNotes. Default view: Single form. Record source (copy &
paste this into the record source, then look at the query design view using
the ellipsis to see how it was constructed):

SELECT CustomerNotes.* FROM CustomerNotes WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

In the form's header, create an UNBOUND combo box called CustomerIDSelector.
ColumnCount = 2, BoundColumn = 1, ColumnWidths = 0,2. RowSource is:

SELECT Customers.CustomerID, Customers.CustomerName FROM Customers ORDER BY
Customers.CustomerName;

This code goes into its AfterUpdate event:

Private Sub CustomerIDSelector_AfterUpdate()
Me.Requery 'display Notes for selected customer
End Sub

Also in the form header, create the UNBOUND NoteNew text box. Be sure to set
the Enter key behavior to New Line in Field for this text box so that the
user can enter multiple lines in one entry.

Also in the form header, create a button called PostNotes. Put this code in
its Click event:

PostNotes_Click()
If IsNull(CustomerIDSelector) Or IsNull(NoteNew) Or NoteNew = "" Then Exit
Sub ' does not append blank note
DoCmd.SetWarnings False
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
DoCmd.SetWarnings True
Me.Refresh 'shows the newly-appended note
NoteNew = Null 'blanks out new note, since it was just appended to Notes entry
End Sub

In the form's detail section, create a single bound but disabled text box
called Notes. Its ControlSource is just Notes (i.e. the Notes field)

Create a query called PostCustomerNotes. Go to SQL view & paste in this:

UPDATE CustomerNotes SET CustomerNotes.Notes = [Notes] & Chr(13) & Chr(10) &
Date() & Chr(13) & Chr(10) & [Forms]![PostCustomerNotes]![NoteNew]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

Again, you can look at design view to see how it is constructed.

Now, here is what happens. When a user opens the form, it is blank. When the
user selects a customer by customer name from the combo box at the top, it
shows that customers Notes field from the CustomerNotes table. The user can
then enter notes into the NoteNew text box and clicks the button. If the
NotesNew field or CustomerIDSelector are null/empty, it does nothing (to
prevent an error if there is nothing to append). If there are NotesNEw, this
udpates the existing Notes field to be:

1. Existing notes,
2. Followed by a linefeed,
3. Followed by the current date,
4. Followed by another linefeed,
5. Followed by the new notes

(You could easily insert a blank line by putting an extra Chr(13) & Chr(10)
combination before and/or after the date)

The code on the button then clears the new notes field and requeries the
form so that the new consolidated note appears in the Notes text box (better
make sure it is a big text box if these will be long entries)

One more note: if each customer really only needs one Notes entry, you could
easily enforce this by simply moving the Notes field to the Customers table,
where the CustomerID is already a primary key and thus will prevent more than
one note per customer. You would have to adjust the query & RowSource, etc.
above to match.

"Sam" wrote:

Thanks for the reply Brian...

I understand part of your instructions and are confused with the others. I
will break down what I have:-

(You Wrote)
If this is the case, I would create two separate boxes: one for the new
entry, and another for the complete history. The new entry could be a
unlocked/enabled unbound text box (let's call it NoteNew), while the
consolidated entry could be a locked and/or disabled text box bound to the
actual Notes memo field (let's call the field & box Notes).

I HAVE DONE THIS!

(You wrote)

Now, allow the user to input into the NoteNew field, then click a button
called PostNotes. Add this code:

Private Sub PostNotes
If IsNull(PostNotes) then Exit Sub ' does not append blank note
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
Notes.Refresh 'shows the newly-appended note
NoteNew = null 'blanks out new note, since it was just appended to Notes
entry
End Sub

I CREATED A PUSH BUTTON THINGY AND COPIED THE CODE BELOW INTO THE "ON CLICK"
FIELD.

(You Wrote)

The query PostCustomerNotes should be an udpate query on the CustomerNotes
table. The criteria of Customer should be [Forms]![YourFormName]![Customer]
to make sure it updates only the note for the current customer.

The update part of the statement (i.e. Notes field) should be something like
this:

[Notes] & " " & Date() & " " & [Forms]![YourFormName]![NoteNew]

Your form will need a combo box Customer where you can pick the customer,
and the record source needs to be filtered (Where Customer =
[Forms]![YourFormName]![Customer]) to make sure it shows only the
currenly-selected customer's notes.

Thus, when the user enters a note and clicks Post, the current NotesNew gets
added to the Notes field, the Notes field is refreshed to show its new
contents, and
the NotesNew field is nullified to prevent duplicate entry by the user.

On Form_Open, you can add something like this to allow the administrator to
edit the old notes:

Private Sub Form_Open
If UserLevel = "Admin" then
Notes.Enabled = True
Else
Notes.Enabled = False
End if
End Sub

YOU NOW START TO LOOSE ME FROM HERE ON IN!

I created a table Called PostCustomerNotes This includes the fields ID
Customer ID & Of course....Notes (which is a memo field)

From there I created a query which only contains the "notes" field (Should I
have included the ID & Customer ID fields as well???)

From this point onwards, I get totally confused!

I know I sound like a real dummy (because I am) but if you could spell it
out to me, it would be muchly appreciated!!!!

Thanking you in advance

"Brian" wrote:

Sorry about the delay. I haven't gone through all your code in detail, but
here is a general approach. If you need more specific details, repost
specifics, and I will provide more detail.

It sounds like you want a running journal that automatically inserts the
date/time before each entry, and with the consolidated entry to be read-only.

If this is the case, I would create two separate boxes: one for the new
entry, and another for the complete history. The new entry could be a
unlocked/enabled unbound text box (let's call it NoteNew), while the
consolidated entry could be a locked and/or disabled text box bound to the
actual Notes memo field (let's call the field & box Notes).

Now, allow the user to input into the NoteNew field, then click a button
called PostNotes. Add this code:

Private Sub PostNotes
If IsNull(PostNotes) then Exit Sub ' does not append blank note
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
Notes.Refresh 'shows the newly-appended note
NoteNew = null 'blanks out new note, since it was just appended to Notes
entry
End Sub

The query PostCustomerNotes should be an udpate query on the CustomerNotes
table. The criteria of Customer should be [Forms]![YourFormName]![Customer]
to make sure it updates only the note for the current customer.

The update part of the statement (i.e. Notes field) should be something like
this:

[Notes] & " " & Date() & " " & [Forms]![YourFormName]![NoteNew]

Your form will need a combo box Customer where you can pick the customer,
and the record source needs to be filtered (Where Customer =
[Forms]![YourFormName]![Customer]) to make sure it shows only the
currenly-selected customer's notes.

Thus, when the user enters a note and clicks Post, the current NotesNew gets
added to the Notes field, the Notes field is refreshed to show its new
contents, and
the NotesNew field is nullified to prevent duplicate entry by the user.

On Form_Open, you can add something like this to allow the administrator to
edit the old notes:

Private Sub Form_Open
If UserLevel = "Admin" then
Notes.Enabled = True
Else
Notes.Enabled = False
End if
End Sub

This would require you to use a global variable UserLevel to identify the
current user's security level within your program.

Let me know if you need more specifics.

"Sam" wrote:

Brian

Sorry

I thought that I understood what you meant, but that is not the case. I will
try and explain myself at bit better....

I have develloped a loan application tracker. the data entry screen has a
comments box, which is basically a diary note of what has occured for a
particular customer.

I want to be able to set this box up so that the user can enter a free
format comment into this field. Any comments typed would be proceeded with a
Date/ Time stamp (to automatically record the date the comment was made).
Once the comments is saved for the customer, I want to be able to lock the
comments so that the user cannot delete or change what has been typed, unless
they have admin rights.

Here is the snippet of the code that makes up the data entry form for my
tracker...

Option Compare Database

Private Sub Command20_Click()
GoTo switchboard
End Sub
Private Sub Find_Application_Click()
On Error GoTo Err_Find_Application_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Application_Click:
Exit Sub

Err_Find_Application_Click:
MsgBox Err.Description
Resume Exit_Find_Application_Click

End Sub

I appreciate the help, and it's good despite the gap in the timeframe...
however there is one small problem... now when I click the Post Customer
Notes button the date goes in the top... and the memo text ends up appended
at the end of the memo field... like this:

19-Oct-05
18-Oct-05
Note added 18-Oct-05
Note added 19-Oct-05

AND... (but wait, there's more)... all of a sudden each new note I add is
being entered to EVERY customer...

I'm in WAY over my head!

"Brian" wrote:

It's been so long since I posted this that I forgot that I had done it (and
mostly, what I said, also). However, yes there is a way to make the most
current note go to the top. I have not tested this , but you should be able
to just reverse the two portions of the Update query; move the [Notes] to the
end of this:

UPDATE CustomerNotes SET CustomerNotes.Notes = [Notes] & Chr(13) & Chr(10) &
Date() & Chr(13) & Chr(10) & [Forms]![PostCustomerNotes]![NoteNew]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

so that it looks like this instead:

UPDATE CustomerNotes SET CustomerNotes.Notes = Date() & Chr(13) & Chr(10) &
[Forms]![PostCustomerNotes]![NoteNew]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector])) & Chr(13) & Chr(10) & [Notes];

This should put the existing notes at the end of the memo field instead of
the beginning.


"Suzy" wrote:

Brian... this info has been great thank you! I was just wondering if there
is an easy way to make the new notes added appear at the top of the memo
field rather than being added to the bottom of the memo, so that the notes
appear in reverse chronological order?

Eg: The most recent note goes to the top...

19-Oct-2005
Posted a question on memo fields.
18-Oct-2005
Set up client database.
Etc...

Many thanks (in advance)
Suzanne

"Brian" wrote:

Here it is, with a slight modification. I made a very simple version of this
whole thing. It took me a little while to figure out how to force the
linefeeds so that each new entry starts on a new line.

The assumption here is that each customer will have a single CustomerNotes
entry that is a single memo field having all of the history in it. Here goes,
but keep in mind that the maximum length of a memo field is 65,535
characters. If a consolidated note for a customer eventually exceeds that,
you may find yourself trying to split the field later into individual note
entries (i.e. each note being a separate entry, displayed on a Continuous
form). Here is the simplified example:

Two tables:
1. Customers
Field 1: CustomerID (primary key, autonumber, long integer)
Field2: CustomerName (text)
2. CustomerNotes
Field1: ID (Primary key, autonumber, long integer)
Field2: CustomerID (long integer, no default)
Field3: Notes (memo)

Create a relationship (enforce referential integrity, cascade update on)
between the two tables on the CustomerID field.

Form: PostCustomerNotes. Default view: Single form. Record source (copy &
paste this into the record source, then look at the query design view using
the ellipsis to see how it was constructed):

SELECT CustomerNotes.* FROM CustomerNotes WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

In the form's header, create an UNBOUND combo box called CustomerIDSelector.
ColumnCount = 2, BoundColumn = 1, ColumnWidths = 0,2. RowSource is:

SELECT Customers.CustomerID, Customers.CustomerName FROM Customers ORDER BY
Customers.CustomerName;

This code goes into its AfterUpdate event:

Private Sub CustomerIDSelector_AfterUpdate()
Me.Requery 'display Notes for selected customer
End Sub

Also in the form header, create the UNBOUND NoteNew text box. Be sure to set
the Enter key behavior to New Line in Field for this text box so that the
user can enter multiple lines in one entry.

Also in the form header, create a button called PostNotes. Put this code in
its Click event:

PostNotes_Click()
If IsNull(CustomerIDSelector) Or IsNull(NoteNew) Or NoteNew = "" Then Exit
Sub ' does not append blank note
DoCmd.SetWarnings False
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
DoCmd.SetWarnings True
Me.Refresh 'shows the newly-appended note
NoteNew = Null 'blanks out new note, since it was just appended to Notes entry
End Sub

In the form's detail section, create a single bound but disabled text box
called Notes. Its ControlSource is just Notes (i.e. the Notes field)

Create a query called PostCustomerNotes. Go to SQL view & paste in this:

UPDATE CustomerNotes SET CustomerNotes.Notes = [Notes] & Chr(13) & Chr(10) &
Date() & Chr(13) & Chr(10) & [Forms]![PostCustomerNotes]![NoteNew]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

Again, you can look at design view to see how it is constructed.

Now, here is what happens. When a user opens the form, it is blank. When the
user selects a customer by customer name from the combo box at the top, it
shows that customers Notes field from the CustomerNotes table. The user can
then enter notes into the NoteNew text box and clicks the button. If the
NotesNew field or CustomerIDSelector are null/empty, it does nothing (to
prevent an error if there is nothing to append). If there are NotesNEw, this
udpates the existing Notes field to be:

1. Existing notes,
2. Followed by a linefeed,
3. Followed by the current date,
4. Followed by another linefeed,
5. Followed by the new notes

(You could easily insert a blank line by putting an extra Chr(13) & Chr(10)
combination before and/or after the date)

The code on the button then clears the new notes field and requeries the
form so that the new consolidated note appears in the Notes text box (better
make sure it is a big text box if these will be long entries)

One more note: if each customer really only needs one Notes entry, you could
easily enforce this by simply moving the Notes field to the Customers table,
where the CustomerID is already a primary key and thus will prevent more than
one note per customer. You would have to adjust the query & RowSource, etc.
above to match.

"Sam" wrote:

Thanks for the reply Brian...

I understand part of your instructions and are confused with the others. I
will break down what I have:-

(You Wrote)
If this is the case, I would create two separate boxes: one for the new
entry, and another for the complete history. The new entry could be a
unlocked/enabled unbound text box (let's call it NoteNew), while the
consolidated entry could be a locked and/or disabled text box bound to the
actual Notes memo field (let's call the field & box Notes).

I HAVE DONE THIS!

(You wrote)

Now, allow the user to input into the NoteNew field, then click a button
called PostNotes. Add this code:

Private Sub PostNotes
If IsNull(PostNotes) then Exit Sub ' does not append blank note
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
Notes.Refresh 'shows the newly-appended note
NoteNew = null 'blanks out new note, since it was just appended to Notes
entry
End Sub

I CREATED A PUSH BUTTON THINGY AND COPIED THE CODE BELOW INTO THE "ON CLICK"
FIELD.

(You Wrote)

The query PostCustomerNotes should be an udpate query on the CustomerNotes
table. The criteria of Customer should be [Forms]![YourFormName]![Customer]
to make sure it updates only the note for the current customer.

The update part of the statement (i.e. Notes field) should be something like
this:

[Notes] & " " & Date() & " " & [Forms]![YourFormName]![NoteNew]

Your form will need a combo box Customer where you can pick the customer,
and the record source needs to be filtered (Where Customer =
[Forms]![YourFormName]![Customer]) to make sure it shows only the
currenly-selected customer's notes.

Thus, when the user enters a note and clicks Post, the current NotesNew gets
added to the Notes field, the Notes field is refreshed to show its new
contents, and
the NotesNew field is nullified to prevent duplicate entry by the user.

On Form_Open, you can add something like this to allow the administrator to
edit the old notes:

Private Sub Form_Open
If UserLevel = "Admin" then
Notes.Enabled = True
Else
Notes.Enabled = False
End if
End Sub

YOU NOW START TO LOOSE ME FROM HERE ON IN!

I created a table Called PostCustomerNotes This includes the fields ID
Customer ID & Of course....Notes (which is a memo field)

From there I created a query which only contains the "notes" field (Should I
have included the ID & Customer ID fields as well???)

From this point onwards, I get totally confused!

I know I sound like a real dummy (because I am) but if you could spell it
out to me, it would be muchly appreciated!!!!

Thanking you in advance

"Brian" wrote:

Sorry about the delay. I haven't gone through all your code in detail, but
here is a general approach. If you need more specific details, repost
specifics, and I will provide more detail.

It sounds like you want a running journal that automatically inserts the
date/time before each entry, and with the consolidated entry to be read-only.

If this is the case, I would create two separate boxes: one for the new
entry, and another for the complete history. The new entry could be a
unlocked/enabled unbound text box (let's call it NoteNew), while the
consolidated entry could be a locked and/or disabled text box bound to the
actual Notes memo field (let's call the field & box Notes).

Now, allow the user to input into the NoteNew field, then click a button
called PostNotes. Add this code:

Private Sub PostNotes
If IsNull(PostNotes) then Exit Sub ' does not append blank note
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
Notes.Refresh 'shows the newly-appended note
NoteNew = null 'blanks out new note, since it was just appended to Notes
entry
End Sub

The query PostCustomerNotes should be an udpate query on the CustomerNotes
table. The criteria of Customer should be [Forms]![YourFormName]![Customer]
to make sure it updates only the note for the current customer.

The update part of the statement (i.e. Notes field) should be something like
this:

[Notes] & " " & Date() & " " & [Forms]![YourFormName]![NoteNew]

Your form will need a combo box Customer where you can pick the customer,
and the record source needs to be filtered (Where Customer =
[Forms]![YourFormName]![Customer]) to make sure it shows only the
currenly-selected customer's notes.

Thus, when the user enters a note and clicks Post, the current NotesNew gets
added to the Notes field, the Notes field is refreshed to show its new
contents, and
the NotesNew field is nullified to prevent duplicate entry by the user.

On Form_Open, you can add something like this to allow the administrator to
edit the old notes:

Private Sub Form_Open
If UserLevel = "Admin" then
Notes.Enabled = True
Else
Notes.Enabled = False
End if
End Sub

This would require you to use a global variable UserLevel to identify the
current user's security level within your program.

Let me know if you need more specifics.

"Sam" wrote:

Brian

Sorry

I thought that I understood what you meant, but that is not the case. I will
try and explain myself at bit better....

I have develloped a loan application tracker. the data entry screen has a
comments box, which is basically a diary note of what has occured for a
particular customer.

I want to be able to set this box up so that the user can enter a free
format comment into this field. Any comments typed would be proceeded with a
Date/ Time stamp (to automatically record the date the comment was made).
Once the comments is saved for the customer, I want to be able to lock the
comments so that the user cannot delete or change what has been typed, unless
they have admin rights.

Here is the snippet of the code that makes up the data entry form for my

I should have tried the new version first. Try this for the query instead:

UPDATE CustomerNotes SET CustomerNotes.Notes = Date() & Chr(13) & Chr(10) &
[Forms]![PostCustomerNotes]![NoteNew] & Chr(13) & Chr(10) & [Notes]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

Also, unless this happens when closing the form, you will want to have
something that clears the current note and refreshes the updated/prepended
note so that the user does not accidentally click "Post" twice and end up
with the same note in there twice. You will probably also want to ensure that
the update does not happen if the user has not yet typed anything in the
notes box: If IsNull(NoteNew) then Exit Sub on the line before the query runs.


"Suzy" wrote:

I appreciate the help, and it's good despite the gap in the timeframe...
however there is one small problem... now when I click the Post Customer
Notes button the date goes in the top... and the memo text ends up appended
at the end of the memo field... like this:

19-Oct-05
18-Oct-05
Note added 18-Oct-05
Note added 19-Oct-05

AND... (but wait, there's more)... all of a sudden each new note I add is
being entered to EVERY customer...

I'm in WAY over my head!

"Brian" wrote:

It's been so long since I posted this that I forgot that I had done it (and
mostly, what I said, also). However, yes there is a way to make the most
current note go to the top. I have not tested this , but you should be able
to just reverse the two portions of the Update query; move the [Notes] to the
end of this:

UPDATE CustomerNotes SET CustomerNotes.Notes = [Notes] & Chr(13) & Chr(10) &
Date() & Chr(13) & Chr(10) & [Forms]![PostCustomerNotes]![NoteNew]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

so that it looks like this instead:

UPDATE CustomerNotes SET CustomerNotes.Notes = Date() & Chr(13) & Chr(10) &
[Forms]![PostCustomerNotes]![NoteNew]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector])) & Chr(13) & Chr(10) & [Notes];

This should put the existing notes at the end of the memo field instead of
the beginning.


"Suzy" wrote:

Brian... this info has been great thank you! I was just wondering if there
is an easy way to make the new notes added appear at the top of the memo
field rather than being added to the bottom of the memo, so that the notes
appear in reverse chronological order?

Eg: The most recent note goes to the top...

19-Oct-2005
Posted a question on memo fields.
18-Oct-2005
Set up client database.
Etc...

Many thanks (in advance)
Suzanne

"Brian" wrote:

Here it is, with a slight modification. I made a very simple version of this
whole thing. It took me a little while to figure out how to force the
linefeeds so that each new entry starts on a new line.

The assumption here is that each customer will have a single CustomerNotes
entry that is a single memo field having all of the history in it. Here goes,
but keep in mind that the maximum length of a memo field is 65,535
characters. If a consolidated note for a customer eventually exceeds that,
you may find yourself trying to split the field later into individual note
entries (i.e. each note being a separate entry, displayed on a Continuous
form). Here is the simplified example:

Two tables:
1. Customers
Field 1: CustomerID (primary key, autonumber, long integer)
Field2: CustomerName (text)
2. CustomerNotes
Field1: ID (Primary key, autonumber, long integer)
Field2: CustomerID (long integer, no default)
Field3: Notes (memo)

Create a relationship (enforce referential integrity, cascade update on)
between the two tables on the CustomerID field.

Form: PostCustomerNotes. Default view: Single form. Record source (copy &
paste this into the record source, then look at the query design view using
the ellipsis to see how it was constructed):

SELECT CustomerNotes.* FROM CustomerNotes WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

In the form's header, create an UNBOUND combo box called CustomerIDSelector.
ColumnCount = 2, BoundColumn = 1, ColumnWidths = 0,2. RowSource is:

SELECT Customers.CustomerID, Customers.CustomerName FROM Customers ORDER BY
Customers.CustomerName;

This code goes into its AfterUpdate event:

Private Sub CustomerIDSelector_AfterUpdate()
Me.Requery 'display Notes for selected customer
End Sub

Also in the form header, create the UNBOUND NoteNew text box. Be sure to set
the Enter key behavior to New Line in Field for this text box so that the
user can enter multiple lines in one entry.

Also in the form header, create a button called PostNotes. Put this code in
its Click event:

PostNotes_Click()
If IsNull(CustomerIDSelector) Or IsNull(NoteNew) Or NoteNew = "" Then Exit
Sub ' does not append blank note
DoCmd.SetWarnings False
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
DoCmd.SetWarnings True
Me.Refresh 'shows the newly-appended note
NoteNew = Null 'blanks out new note, since it was just appended to Notes entry
End Sub

In the form's detail section, create a single bound but disabled text box
called Notes. Its ControlSource is just Notes (i.e. the Notes field)

Create a query called PostCustomerNotes. Go to SQL view & paste in this:

UPDATE CustomerNotes SET CustomerNotes.Notes = [Notes] & Chr(13) & Chr(10) &
Date() & Chr(13) & Chr(10) & [Forms]![PostCustomerNotes]![NoteNew]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

Again, you can look at design view to see how it is constructed.

Now, here is what happens. When a user opens the form, it is blank. When the
user selects a customer by customer name from the combo box at the top, it
shows that customers Notes field from the CustomerNotes table. The user can
then enter notes into the NoteNew text box and clicks the button. If the
NotesNew field or CustomerIDSelector are null/empty, it does nothing (to
prevent an error if there is nothing to append). If there are NotesNEw, this
udpates the existing Notes field to be:

1. Existing notes,
2. Followed by a linefeed,
3. Followed by the current date,
4. Followed by another linefeed,
5. Followed by the new notes

(You could easily insert a blank line by putting an extra Chr(13) & Chr(10)
combination before and/or after the date)

The code on the button then clears the new notes field and requeries the
form so that the new consolidated note appears in the Notes text box (better
make sure it is a big text box if these will be long entries)

One more note: if each customer really only needs one Notes entry, you could
easily enforce this by simply moving the Notes field to the Customers table,
where the CustomerID is already a primary key and thus will prevent more than
one note per customer. You would have to adjust the query & RowSource, etc.
above to match.

"Sam" wrote:

Thanks for the reply Brian...

I understand part of your instructions and are confused with the others. I
will break down what I have:-

(You Wrote)
If this is the case, I would create two separate boxes: one for the new
entry, and another for the complete history. The new entry could be a
unlocked/enabled unbound text box (let's call it NoteNew), while the
consolidated entry could be a locked and/or disabled text box bound to the
actual Notes memo field (let's call the field & box Notes).

I HAVE DONE THIS!

(You wrote)

Now, allow the user to input into the NoteNew field, then click a button
called PostNotes. Add this code:

Private Sub PostNotes
If IsNull(PostNotes) then Exit Sub ' does not append blank note
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
Notes.Refresh 'shows the newly-appended note
NoteNew = null 'blanks out new note, since it was just appended to Notes
entry
End Sub

I CREATED A PUSH BUTTON THINGY AND COPIED THE CODE BELOW INTO THE "ON CLICK"
FIELD.

(You Wrote)

The query PostCustomerNotes should be an udpate query on the CustomerNotes
table. The criteria of Customer should be [Forms]![YourFormName]![Customer]
to make sure it updates only the note for the current customer.

The update part of the statement (i.e. Notes field) should be something like
this:

[Notes] & " " & Date() & " " & [Forms]![YourFormName]![NoteNew]

Your form will need a combo box Customer where you can pick the customer,
and the record source needs to be filtered (Where Customer =
[Forms]![YourFormName]![Customer]) to make sure it shows only the
currenly-selected customer's notes.

Thus, when the user enters a note and clicks Post, the current NotesNew gets
added to the Notes field, the Notes field is refreshed to show its new
contents, and
the NotesNew field is nullified to prevent duplicate entry by the user.

On Form_Open, you can add something like this to allow the administrator to
edit the old notes:

Private Sub Form_Open
If UserLevel = "Admin" then
Notes.Enabled = True
Else
Notes.Enabled = False
End if
End Sub

YOU NOW START TO LOOSE ME FROM HERE ON IN!

I created a table Called PostCustomerNotes This includes the fields ID
Customer ID & Of course....Notes (which is a memo field)

From there I created a query which only contains the "notes" field (Should I
have included the ID & Customer ID fields as well???)

From this point onwards, I get totally confused!

I know I sound like a real dummy (because I am) but if you could spell it
out to me, it would be muchly appreciated!!!!

Thanking you in advance

"Brian" wrote:

Sorry about the delay. I haven't gone through all your code in detail, but
here is a general approach. If you need more specific details, repost
specifics, and I will provide more detail.

It sounds like you want a running journal that automatically inserts the
date/time before each entry, and with the consolidated entry to be read-only.

If this is the case, I would create two separate boxes: one for the new
entry, and another for the complete history. The new entry could be a
unlocked/enabled unbound text box (let's call it NoteNew), while the
consolidated entry could be a locked and/or disabled text box bound to the
actual Notes memo field (let's call the field & box Notes).

Now, allow the user to input into the NoteNew field, then click a button
called PostNotes. Add this code:

Private Sub PostNotes
If IsNull(PostNotes) then Exit Sub ' does not append blank note
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
Notes.Refresh 'shows the newly-appended note
NoteNew = null 'blanks out new note, since it was just appended to Notes
entry
End Sub

The query PostCustomerNotes should be an udpate query on the CustomerNotes
table. The criteria of Customer should be [Forms]![YourFormName]![Customer]
to make sure it updates only the note for the current customer.

The update part of the statement (i.e. Notes field) should be something like
this:

[Notes] & " " & Date() & " " & [Forms]![YourFormName]![NoteNew]

Your form will need a combo box Customer where you can pick the customer,
and the record source needs to be filtered (Where Customer =
[Forms]![YourFormName]![Customer]) to make sure it shows only the
currenly-selected customer's notes.

Thus, when the user enters a note and clicks Post, the current NotesNew gets
added to the Notes field, the Notes field is refreshed to show its new
contents, and
the NotesNew field is nullified to prevent duplicate entry by the user.

On Form_Open, you can add something like this to allow the administrator to
edit the old notes:

Private Sub Form_Open
If UserLevel = "Admin" then
Notes.Enabled = True
Else
Notes.Enabled = False
End if
End Sub

This would require you to use a global variable UserLevel to identify the
current user's security level within your program.

Let me know if you need more specifics.

"Sam" wrote:

OK I've put all that in but now it is having a run time error...

Microsoft Visual Basic window says: "Run-time error '3188': Could not
updated; currently locked by another session on this machine"

The options are to either 'End', 'Debug' or 'Help'.

When I choose 'Debug' I'm taken through to the visual basic window and it's
showing me the code you provided earlier in this post to create a 'PostNotes'
button:
PostNotes_Click()
If IsNull(CustomerIDSelector) Or IsNull(NoteNew) Or NoteNew = "" Then Exit
Sub ' does not append blank note
DoCmd.SetWarnings False
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
DoCmd.SetWarnings True
Me.Refresh 'shows the newly-appended note
NoteNew = Null 'blanks out new note, since it was just appended to Notes entry
End Sub

The error is pointed out / highlighted in this line:
DoCmd.OpenQuery "PostCustomerNotes" 'appends current note

But I don't know why... like I said, I'm in WAY over my head here.





"Brian" wrote:

I should have tried the new version first. Try this for the query instead:

UPDATE CustomerNotes SET CustomerNotes.Notes = Date() & Chr(13) & Chr(10) &
[Forms]![PostCustomerNotes]![NoteNew] & Chr(13) & Chr(10) & [Notes]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

Also, unless this happens when closing the form, you will want to have
something that clears the current note and refreshes the updated/prepended
note so that the user does not accidentally click "Post" twice and end up
with the same note in there twice. You will probably also want to ensure that
the update does not happen if the user has not yet typed anything in the
notes box: If IsNull(NoteNew) then Exit Sub on the line before the query runs.


"Suzy" wrote:

I appreciate the help, and it's good despite the gap in the timeframe...
however there is one small problem... now when I click the Post Customer
Notes button the date goes in the top... and the memo text ends up appended
at the end of the memo field... like this:

19-Oct-05
18-Oct-05
Note added 18-Oct-05
Note added 19-Oct-05

AND... (but wait, there's more)... all of a sudden each new note I add is
being entered to EVERY customer...

I'm in WAY over my head!

"Brian" wrote:

It's been so long since I posted this that I forgot that I had done it (and
mostly, what I said, also). However, yes there is a way to make the most
current note go to the top. I have not tested this , but you should be able
to just reverse the two portions of the Update query; move the [Notes] to the
end of this:

UPDATE CustomerNotes SET CustomerNotes.Notes = [Notes] & Chr(13) & Chr(10) &
Date() & Chr(13) & Chr(10) & [Forms]![PostCustomerNotes]![NoteNew]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));

so that it looks like this instead:

UPDATE CustomerNotes SET CustomerNotes.Notes = Date() & Chr(13) & Chr(10) &
[Forms]![PostCustomerNotes]![NoteNew]
WHERE
(((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector])) & Chr(13) & Chr(10) & [Notes];

This should put the existing notes at the end of the memo field instead of
the beginning.


"Suzy" wrote:

Brian... this info has been great thank you! I was just wondering if there
is an easy way to make the new notes added appear at the top of the memo
field rather than being added to the bottom of the memo, so that the notes
appear in reverse chronological order?

Eg: The most recent note goes to the top...

19-Oct-2005
Posted a question on memo fields.
18-Oct-2005
Set up client database.
Etc...

Many thanks (in advance)
Suzanne