Solved

Adding Notes In Database

Posted on 2004-03-28
8
184 Views
Last Modified: 2010-05-01
Hello, I have a database of customers that has a column to add notes about the customer. each note will have the current date and time the note was entered along with the note itself.

my problem: I am currently trying to do this using a screen that has RTF embedded.  when this screen opens up it should have all the notes for the current customer and the ability to add more notes which will be update to the customers notes column.  After hours of working on this I have not been able to make it work.  

So can some tell me how companies add notes about a customer and view those very notes for the specific customer if need be?

This is my last 70 points I just dont have anymore until I get my allowance from my wife  Please help
0
Comment
Question by:Rickstar
  • 4
  • 3
8 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 10700665
Rickstar - This seems so easy that I'm afraid I'm missing something.  Let me make sure I understand the situation.  You have a database of customers.  The structure of that database includes a notes field.  You want the notes field to contain a running history of notes for each customer.  So, for example, if there were a record in the database for me, then the notes field in my record would have a running history of notes for me.  The notes themselves will appear in an RichTextBox control.  Do I have it right?  If so, then the following should solve the problem.

1.  As the program moves from record to record and populates whatever controls you have on your form, set the RichTextBox control to the value of the notes field from the current record.

2.  Use the Change event for the RichTextBox control to detect whether the user altered the notes field.  I'd recommend creating a boolean variable for this.  Set the variable to false each time the program moves to a new record.

3.  Before completing a move to another record test the variable from step #2.  If it's false, then the notes field wasn't altered and you can move on.  If it's true, then the notes field was changed and you need to save it by updating the current record.  If you want to add a date/time stamp to the note, then use something like this:

  RichTextBox.Text = Date & " " & Time & vbCrLf & RichTextBox.Text

Overall it'll look something like this:

Dim bolRecordChanged As Boolean

Private Sub RichTextBox1_Change()
    bolRecordChanged = True
End Sub

Private Sub GoToAnotherRecord()
    If bolRecordChanged Then
        RichTextBox1.Text = Date & " " & Time & vbCrLf & RichTextBox1.Text
        'Code to update the record goes here
    End If
    bolRecordChanged = False
    'Code to move to another record goes here
    'Code to load the record fields into the controls goes here
    'The code for the Notes field (RichTextBox1 control) will look something like:
    '  RichTextBox1.Text = Record.Fields("Notes")
End Sub
0
 
LVL 11

Accepted Solution

by:
jmwheeler earned 70 total points
ID: 10703864
I don't know what type of database you are using but I had a similar problem that I solved by saving the information from a RichTextBox in a file and then entering a link to that file in the database.
ex.  RichTextBox1.SaveFile("C:\YourFilePath\YourFileName.???")
      rs![Notes] = "C:\YourFilePath\YourFileName.???")                    'where rs is the name of your RecordSet and Notes is the name of your Field


You can plan a scheme for the filename based on the customer such as CustomerID-CustomerLastName.txt
ex. 1804-Smith.txt

Then when you want to load the data from that file you can use this
ex.  RichTextBox1.LoadFile(rs![Notes])
0
 
LVL 76

Expert Comment

by:David Lee
ID: 10709888
jmwheeler,

Good solution.  I'm curious though why you chose to store the notes in a separate file instead of in the database?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Rickstar
ID: 10709962
Thanks, it worked.  I use color in my RTF boxes. Not sure if my access database would store such color formatting. What do you think?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 10710237
It works just fine.  You just need to remember to save/load the TextRTF property of the RichTextBox control.
0
 
LVL 11

Expert Comment

by:jmwheeler
ID: 10718511
I often use access to store data and it limits the size of your data to 255 characters.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 10719011
jmwheeler,

That's only if you use a text field in the database.  Memo fields store 64K.  You can save the contents of a TextBox or a RichTextBox into either one.  Naturally in this case a memo field would be what's needed.
0
 
LVL 11

Expert Comment

by:jmwheeler
ID: 10729035
Learn something new everyday.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question