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 said:
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
:
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.
:
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