Multiple rows in an Access 2002 record

Your code is creating one field called Notes that contains all the notes If you want different fields then you can't join them together using the &s

I want then all in one field called notes but I also want each field to be on a separate line within the same record

VBCRLF does not mean anything in SQL.
VB=Visual Basic

If you want to do this, you can do this in a form (on the current event), or in a report (on the Detail Format event).

Something roughly like this as the ControlSource:
=[note_line1] & VbLf & [note_line2] & VbLf & [note_line3]

You can really get crazy (if you are using Access 2007 or newer formats) and use a memo field for this...
...but it will require inserting the values using SQL and "formatting" them with HTML
(In HTLM, <br>, is the rough equivalent to vbcrlf in vba)

Sorry, ...in my code above it should be something like this:

Private Sub Form_Current()
    If Not Me.NewRecord Then
        Me.txtCompoundText = [note_line1] & vbLf & [note_line2] & vbLf & [note_line3]
    end if
End Sub
    ...for a form

And something like this in a report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.txtCompoundText = [note_line1] & vbLf & [note_line2] & vbLf & [note_line3]
End Sub

"If you want to do this, you can do this in a form (on the current event), or in a report (on the Detail Format event)." Hmmm. I'm pretty sure I've seen similar statements in queries previously. Can't be sure, tho. Changing the form or report is not an option.
I'm using Access 2002. Any other ideas on how to get multiple lines in the same field, same record?

Why on earth would you want to store the same information TWICE in the same record? Its a total waste of time, space and effort. There is no Practical reason for it that makes any sense whatsoever.

I don't think you are understanding what I'm trying to do. The data in each field i.e. note_line1, note_line2, note_line3 is different. In fact, It's a continuation of the same note.
note_line1 might have "Met with Mr. Smith at 10:00 AM. note_mite2 might contain "Mr. Smith appeared to be feeling better this morning". note_line3 - something else about Mr. Smith.

Our customer would like to see the contents of these fields as separate lines in the same record with only one field for the note:
ClientId     Met with Mr. Smith at 10:00 AM
                  Mr. Smith appeared to be feeling better this morning
                  Mr. Smith ......

Thats fine but you say you ALSO want to keep all nine notes fields as well in the table? What is the point was my question.

IF the customer wants to SEE the data then that is the job of the visual interface, forms, reports....

The data design is obviously very very poor. Why anyone would design a database with NINE Notes fields, one for each line of text is beyond anybody who has any experience of databases or programming at all!  

As you state that you CAN NOT alter the forms OR the reports, HOW are they going to be able to see this "NEW" field?

No, I don't want to keep all of the text fields, only one note field with all the other field content (separated by lines).
Why they did it the way they did is not my concern - and I would think any form with a text control as the control source would display it just fine.

Well if you are going to remove the Nine seperate fields you will need to redo EVERY form and EVERY report that has ANY reference to the table concerned.

A simple text box will NOT display a field that has multiplelines of text in it.

I agree with some of Neilsr's points.
(although not as forcefully)
;-)

Typically this type of data is stored in a Parent/Child (One-To-Many) design.
One Customer--->Many Notes
Then what you are asking for is *easy* to do in a form or Report.
In addition, the data will be easier to manage and manipulate:
(you can get a count of the notes per customer, you can re-sort the notes, ...etc )

Not sure why you state that you cannot use a Form or report, ...especially if it gives you the exact output you are requesting...?

Remember, (As I stated earlier), this can be done in the way you are requesting if you use a Memo field in Access 2007 or newer formats.
But it involves using SQL, knowing HTML and VBA fairly well, ...but worst of all, you would have to "Store" redundant data.

JeffCoachman

BTW

Another brute force option is to simply make one "Notes" field.
Then simply press:
    Ctrl+Enter
...between each note.
(Hold down the Control key and tap the Enter key)

This will insert a Line break between the Notes.

Some downsides to this technique are that:
A user can change the behavior of the Enter key
There is only one Notes field.
It is not "Normalized"
You have to make the row taller (manually) to see all the notes
(This will change the height of *all* the rows, regardless of the number of note/lines)
...etc

JeffCoachman

Jeff: I'm converting customer data to a different Db. That Db is frozen insofar as changes as is the fact it's Access2002. It also has only one note field. It's important to understand we have absolutely no option to change anything in the Db. What we do have control over is the data and how it goes into the Db.

I just created a new table as a test with 2 fields: ClientId, Note and entered a number for the client and "Line1" for the note. When I do a Ctrl Enter it moves to the 2nd line where I typed "Line2. Then again for "Line3".

Basically, that's all I'm trying to do with the INSERT statement and the 9 text fields.
And just to make a point - I then created a new form with 2 fields - ClientId, Note with corresponding control sources from the table.
The record displays EXACTLY the way it I want it to with multiple lines.

So, going back to my original question without everyone informing me it's a stupid idea, can you help me determine what code should I use to replicate the Ctrl Enter I used manually?

<without everyone informing me it's a stupid idea>
We did not know of your design constraints before now.
All we were trying to say was that this is not as easy as it seems, if you wanted to keep your original design..
;-)

There is no way (that I know of) to easily build a line break into an SQL statement.

But once again, it can be done in a 2007, but it has the potential of being a nightmare logistically.
...And with your constraint of not being able to control how data is entered, ...it may not even be possible...

Jeff

I've requested that this question be deleted for the following reason:

Apparently, there is no option to accomplish what I wanted to do. None, in any event, the Experts could identify.

I posted a sample that does precisely what you asked for.
http://filedb.experts-exchange.com/incoming/2012/08_w34/598595/Access-EEQ27840023HTMLRichTextIn.accdb

Did you investigate it?

I've just looked at Jeff's sample and I agree that it does exactly what was asked for in the question.

Agreed, it seems to do exactly what it said on the tin!

...?
So will you be accepting my post?