Adding Data to Text Box

[Guest]

I am using Access.adp. My question is:
In my form i have text box 'txtNotes' and button 'cmdAdd',
On click I have code;
----------------------
Dim strDate as String
Dim strNotes as string

strDate = Format(Now, "MM/DD/YYYY")
strNotes = txtNotes

txtNotes = strNotes & " " & strDate
--------------------------
The problem is, if i have 6 rows in me text box and i click on button, it
will add date but it will remove first 4 rows from text box.
If any body now what i am doing wrong please let me know.
Thank you.
The form source base on View. Data type for txtNotes is 'varchar', length
'6000'

 

[John Vinson]

I am using Access.adp. My question is:
In my form i have text box 'txtNotes' and button 'cmdAdd',
On click I have code;
----------------------
Dim strDate as String
Dim strNotes as string

strDate = Format(Now, "MM/DD/YYYY")
strNotes = txtNotes

txtNotes = strNotes & " " & strDate
--------------------------
The problem is, if i have 6 rows in me text box and i click on button, it
will add date but it will remove first 4 rows from text box.
If any body now what i am doing wrong please let me know.
Thank you.
The form source base on View. Data type for txtNotes is 'varchar', length
'6000'

Well... I'd REALLY REALLY recommend that you reconsider your table
design.

It appears that you are using a textbox txtNotes (bound to a Memo
field I'd guess) as if it were a "many" side Table containing multiple
notes, each date-stamped.

You're using a relational database! Why not model a one (item) to many
(notes) relationship, *as a one to many relationship*? Create a Notes
table related to this table; it should have three fields (maybe more
but at least three) - a foreign key linking it to your current table's
Primary Key, a NoteDate field defaulting to Date(), and a Note memo
field. Rather than stuffing many notes into one field, you would use a
Subform and see all of your notes, each with a real, searchable,
sortable Date/Time field, and each note standing on its own yet
related to the main table.

John W. Vinson[MVP]

 

[Guest]

The form source base on View. The Data type for txtNotes is 'varchar', length

'6000' not the 'Memo'.

 

[John Vinson]

The form source base on View. The Data type for txtNotes is 'varchar', length

<shrug>

Fine. My answer still applies. Storing multiple dates and multiple
notes in a Varchar is simply INCORRECT DESIGN.

John W. Vinson[MVP]

 

[Guest]

Do I need to change data type for this field? If yes, then what data type
should i use?
Thank you

 

[John Vinson]

Do I need to change data type for this field?

No. You need to change the STRUCTURE of your tables; you need to *add
a new table*.

To repeat my previous message:

You're using a relational database! Why not model a one (item) to many
(notes) relationship, *as a one to many relationship*? Create a Notes
table related to this table; it should have three fields (maybe more
but at least three) - a foreign key linking it to your current table's
Primary Key, a NoteDate field defaulting to Date(), and a Note memo
field. Rather than stuffing many notes into one field, you would use a
Subform and see all of your notes, each with a real, searchable,
sortable Date/Time field, and each note standing on its own yet
related to the main table.


John W. Vinson[MVP]