How to cut/append data from one field to another

I need a quick/simple way to cut the value in one text box field and append
it to another text box field.

I want to clear out the value in field 1 (Comment) and append it to field 2
(History) without overwriting the other values in field 2 (hence why I say
append).

Any suggestions?
Thanks!

Me.History = Me.History & " " & Me.Comment
Me.Comment = ""

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Paul" wrote in message
...
I need a quick/simple way to cut the value in one text box field and append
it to another text box field.

I want to clear out the value in field 1 (Comment) and append it to field
2
(History) without overwriting the other values in field 2 (hence why I say
append).

Any suggestions?
Thanks!

Thanks...

I gave this a shot and got a VB error:

"Run-time error '438': Object doesn't support this property or method"

Any thoughts?

"Douglas J. Steele" wrote:

Me.History = Me.History & " " & Me.Comment
Me.Comment = ""

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Paul" wrote in message
...
I need a quick/simple way to cut the value in one text box field and append
it to another text box field.

I want to clear out the value in field 1 (Comment) and append it to field
2
(History) without overwriting the other values in field 2 (hence why I say
append).

Any suggestions?
Thanks!

Which line is it complaining about?

Just to double-check: in my code below, History and Comment were intended to
be the names of the text boxes on the current form. Is that the case: are
they both text boxes?

How are you running this code? What event did you put it in?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Paul" wrote in message
...
Thanks...

I gave this a shot and got a VB error:

"Run-time error '438': Object doesn't support this property or method"

Any thoughts?

"Douglas J. Steele" wrote:

Me.History = Me.History & " " & Me.Comment
Me.Comment = ""

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Paul" wrote in message
...
I need a quick/simple way to cut the value in one text box field and
append
it to another text box field.

I want to clear out the value in field 1 (Comment) and append it to
field
2
(History) without overwriting the other values in field 2 (hence why I
say
append).

Any suggestions?
Thanks!

The first line is what it doesn't like...

Me.History = Me.History & " " & Me.Comment

Both fields are text boxes and their names are 'Comment' and 'History'.

I have an idea as to what the problem may be now...These fields are on
separate tabs, the tab names are 'Comments' and 'History' and the fields of
'Comment' and 'History' go with their respective tab.

So it is Tab=Comments, Field=Comment and Tab=History, Field=History

Not sure though how to identify the tab name in the VB.

"Douglas J. Steele" wrote:

Which line is it complaining about?

Just to double-check: in my code below, History and Comment were intended to
be the names of the text boxes on the current form. Is that the case: are
they both text boxes?

How are you running this code? What event did you put it in?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Paul" wrote in message
...
Thanks...

I gave this a shot and got a VB error:

"Run-time error '438': Object doesn't support this property or method"

Any thoughts?

"Douglas J. Steele" wrote:

Me.History = Me.History & " " & Me.Comment
Me.Comment = ""

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Paul" wrote in message
...
I need a quick/simple way to cut the value in one text box field and
append
it to another text box field.

I want to clear out the value in field 1 (Comment) and append it to
field
2
(History) without overwriting the other values in field 2 (hence why I
say
append).

Any suggestions?
Thanks!

As long as they're simply text boxes on tabs, the Tab doesn't enter into it.

If they're subforms on the tab, then yes, that's an issue. To refer to a
control on a subform, you need to use something like
Me!SubformControlName.Form!History, where "SubformControlName" is the name
of the control on the tab. Depending on how you added the subforms, the name
of the subform control may not be the same as the name of the form being
used as a subform.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Paul" wrote in message
...
The first line is what it doesn't like...

Me.History = Me.History & " " & Me.Comment

Both fields are text boxes and their names are 'Comment' and 'History'.

I have an idea as to what the problem may be now...These fields are on
separate tabs, the tab names are 'Comments' and 'History' and the fields
of
'Comment' and 'History' go with their respective tab.

So it is Tab=Comments, Field=Comment and Tab=History, Field=History

Not sure though how to identify the tab name in the VB.

"Douglas J. Steele" wrote:

Which line is it complaining about?

Just to double-check: in my code below, History and Comment were intended
to
be the names of the text boxes on the current form. Is that the case: are
they both text boxes?

How are you running this code? What event did you put it in?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Paul" wrote in message
...
Thanks...

I gave this a shot and got a VB error:

"Run-time error '438': Object doesn't support this property or method"

Any thoughts?

"Douglas J. Steele" wrote:

Me.History = Me.History & " " & Me.Comment
Me.Comment = ""

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Paul" wrote in message
...
I need a quick/simple way to cut the value in one text box field and
append
it to another text box field.

I want to clear out the value in field 1 (Comment) and append it to
field
2
(History) without overwriting the other values in field 2 (hence why
I
say
append).

Any suggestions?
Thanks!

Nope, no subforms involved here...

"Douglas J. Steele" wrote:

As long as they're simply text boxes on tabs, the Tab doesn't enter into it.

If they're subforms on the tab, then yes, that's an issue. To refer to a
control on a subform, you need to use something like
Me!SubformControlName.Form!History, where "SubformControlName" is the name
of the control on the tab. Depending on how you added the subforms, the name
of the subform control may not be the same as the name of the form being
used as a subform.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Paul" wrote in message
...
The first line is what it doesn't like...

Me.History = Me.History & " " & Me.Comment

Both fields are text boxes and their names are 'Comment' and 'History'.

I have an idea as to what the problem may be now...These fields are on
separate tabs, the tab names are 'Comments' and 'History' and the fields
of
'Comment' and 'History' go with their respective tab.

So it is Tab=Comments, Field=Comment and Tab=History, Field=History

Not sure though how to identify the tab name in the VB.

"Douglas J. Steele" wrote:

Which line is it complaining about?

Just to double-check: in my code below, History and Comment were intended
to
be the names of the text boxes on the current form. Is that the case: are
they both text boxes?

How are you running this code? What event did you put it in?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Paul" wrote in message
...
Thanks...

I gave this a shot and got a VB error:

"Run-time error '438': Object doesn't support this property or method"

Any thoughts?

"Douglas J. Steele" wrote:

Me.History = Me.History & " " & Me.Comment
Me.Comment = ""

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Paul" wrote in message
...
I need a quick/simple way to cut the value in one text box field and
append
it to another text box field.

I want to clear out the value in field 1 (Comment) and append it to
field
2
(History) without overwriting the other values in field 2 (hence why
I
say
append).

Any suggestions?
Thanks!

Hi Paul,

I may be way off on my thinking as to what you want here.

(1) I wonder if you are referring to a batch of records with texts already
existing in the Comments field and a lot of texts in the History field of the
same records. And your goal is to simply transfer all the texts from the
Comments field to the History Field on a one-time basis.

Or (2) are you trying to create an ongoing system of having a user fill in
the Comments field and then have those comments to be transferred
automatically to the History field without overwriting any text already
existing in the History field (and the comments in the comments field would
then be deleted so that future comments could be added to that field and
automatically transferred to the History field)?

I’m assuming you are referring to an operation that would take place only
once (#1 above), otherwise I would have expected different language in your
question.

If you are referring to a one-time operation, I would think you would use an
update query to accomplish what you want. In a query window where you would
have your table that contains the two fields, Comments and History, you would
use the following sample expression to capture in the History field all the
text in the Comments filed and keep all existing text in the History field.
I am also suggesting that you might want to separate your Comments text from
the History text by perhaps paragraphing. Here is the sample expression that
you would place on the Update line in the History field of your Update Query:

[Comment] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [History]

The chr(13) and chr(10) create “carriage returns� and “line feeds�.

If your goal is #2 above, then Doug Steele’s information is the correct way
to go. I’d like to add a few comments however, hopefully for clarity at the
expense of verbosity.

Doug asked, “How are you running this code? What event did you put it in?�
I didn’t see any reply to his questions.

I would recommend that you replace your comment field with an unbound text
box (that is not linked to any field --- no control source). It can be on a
separate tabbed page of your form or placed just above your History field
text box. If you’re short of space, you can make it no deeper than one line
and use a zoom effect to visually blow up the text box for easier data entry.

Since you might be storing a lot of ongoing texts in the History field, I’m
assuming the History field is a memo type field rather than a text type
limited to 255 characters.

In Form design view, from the Toolbox bar, drag the text box icon ab| to a
suggested spot just above your History field text box. Highlight your unbound
control and open the property sheet (Go to Menu Bar, click View, click
Properties). Name the unbound control, “CommentUnbound� (or any other name
you wish) and keep the control source line blank (i.e. unbound).

Click the Event tab in the Property Sheet and place your cursor on the line,
“After Update�. When your cursor is on that line, you will see two control
buttons on the far right side of that line. Click the first button and
highlight the words, “Event Procedure� on the drop-down list. Then click the
second button (ellipsis…) and you will be taken to the Event Procedure
screen. Between the two existing lines, “Private Sub CommentUnbound After
Update () and “End Sub�, add your event procedure, as shown below:

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Private Sub CommentUnbound After Update ()

ADD YOUR EVENT PROCEDURE HERE (See Below)

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Assuming your unbound comment control is named “CommentUnbound�, and your
History field text box is named “History�, remembering that the Text box
names might be different than the actual field names (at your discretion),
here is a suggested event procedu

-------------- DESCENDING ORDER -------------------

Private Sub CommentUnbound_AfterUpdate()

Me.History = vbNewLine & vbNewLine & Me.CommentUnbound &� “& Me.History

Me.CommentUnbound = vbNullString

DoCmd.GotoControl “CommentUnbound�

End Sub
-----------------------------------------------------------------

Note: If your comment entries are usually one-liners, that is very, very
brief. You could use only one NewLine, rather than the suggested two, which
may visually aid a person reviewing all the comment entries. If vbNewLine
does not work in an application using an earlier version of Access, you might
try its equivalent, chr(13) & chr(10).

In an earlier discussion on a similar problem, Doug suggested using vbCrLf
rather than vbNewLine. Any of the three should work.

The last line in the Event Procedure is not critical. The “DoCmd.GoToControl
“CommentUnbound� allows the cursor to remain in the unbound control for
another entry. The procedure above allows for a double line feed so that the
next entry into the Notes field control will appear two lines above the
previous entry for easy reviewing. Your comments therefore appear in
descending order with the latest at the top.

THIS PRODUCES THE FOLLOWING FORMATTED SAMPLE DATA:

Comment #2, Please advise Bud we can ship all 25 products.

Comment #1, Thursday is here. Where is the answer to Bud’s question?

Wednesday’s History summary. Bud wants to know how many products can be
shipped on Friday.


Hope this helps in your decision-making.
------
Tank

Wow, that was a very thorough reply! )

I guess in a way, it's a combo of both that I'm looking to do.

I have two tabs, one for Comments and one for History. The idea is the user
would initially open a 'ticket' (create a record) and input information in
the 'Comment' box on the 'Comments' tab. Then in the future, they would open
the ticket (record) and click on a button, hypothetically named 'Move
Comments to History', which would cut the text from the 'Comment' field on
the 'Comments' tab and append it to the 'History' field on the 'History' tab.
This would clear out the 'Comment' field for them to input their current
status/comments.

Make sense?

"Tank" wrote:

Hi Paul,

I may be way off on my thinking as to what you want here.

(1) I wonder if you are referring to a batch of records with texts already
existing in the Comments field and a lot of texts in the History field of the
same records. And your goal is to simply transfer all the texts from the
Comments field to the History Field on a one-time basis.

Or (2) are you trying to create an ongoing system of having a user fill in
the Comments field and then have those comments to be transferred
automatically to the History field without overwriting any text already
existing in the History field (and the comments in the comments field would
then be deleted so that future comments could be added to that field and
automatically transferred to the History field)?

I’m assuming you are referring to an operation that would take place only
once (#1 above), otherwise I would have expected different language in your
question.

If you are referring to a one-time operation, I would think you would use an
update query to accomplish what you want. In a query window where you would
have your table that contains the two fields, Comments and History, you would
use the following sample expression to capture in the History field all the
text in the Comments filed and keep all existing text in the History field.
I am also suggesting that you might want to separate your Comments text from
the History text by perhaps paragraphing. Here is the sample expression that
you would place on the Update line in the History field of your Update Query:

[Comment] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [History]

The chr(13) and chr(10) create “carriage returns� and “line feeds�.

If your goal is #2 above, then Doug Steele’s information is the correct way
to go. I’d like to add a few comments however, hopefully for clarity at the
expense of verbosity.

Doug asked, “How are you running this code? What event did you put it in?�
I didn’t see any reply to his questions.

I would recommend that you replace your comment field with an unbound text
box (that is not linked to any field --- no control source). It can be on a
separate tabbed page of your form or placed just above your History field
text box. If you’re short of space, you can make it no deeper than one line
and use a zoom effect to visually blow up the text box for easier data entry.

Since you might be storing a lot of ongoing texts in the History field, I’m
assuming the History field is a memo type field rather than a text type
limited to 255 characters.

In Form design view, from the Toolbox bar, drag the text box icon ab| to a
suggested spot just above your History field text box. Highlight your unbound
control and open the property sheet (Go to Menu Bar, click View, click
Properties). Name the unbound control, “CommentUnbound� (or any other name
you wish) and keep the control source line blank (i.e. unbound).

Click the Event tab in the Property Sheet and place your cursor on the line,
“After Update�. When your cursor is on that line, you will see two control
buttons on the far right side of that line. Click the first button and
highlight the words, “Event Procedure� on the drop-down list. Then click the
second button (ellipsis…) and you will be taken to the Event Procedure
screen. Between the two existing lines, “Private Sub CommentUnbound After
Update () and “End Sub�, add your event procedure, as shown below:

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Private Sub CommentUnbound After Update ()

ADD YOUR EVENT PROCEDURE HERE (See Below)

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Assuming your unbound comment control is named “CommentUnbound�, and your
History field text box is named “History�, remembering that the Text box
names might be different than the actual field names (at your discretion),
here is a suggested event procedu

-------------- DESCENDING ORDER -------------------

Private Sub CommentUnbound_AfterUpdate()

Me.History = vbNewLine & vbNewLine & Me.CommentUnbound &� “& Me.History

Me.CommentUnbound = vbNullString

DoCmd.GotoControl “CommentUnbound�

End Sub
-----------------------------------------------------------------

Note: If your comment entries are usually one-liners, that is very, very
brief. You could use only one NewLine, rather than the suggested two, which
may visually aid a person reviewing all the comment entries. If vbNewLine
does not work in an application using an earlier version of Access, you might
try its equivalent, chr(13) & chr(10).

In an earlier discussion on a similar problem, Doug suggested using vbCrLf
rather than vbNewLine. Any of the three should work.

The last line in the Event Procedure is not critical. The “DoCmd.GoToControl
“CommentUnbound� allows the cursor to remain in the unbound control for
another entry. The procedure above allows for a double line feed so that the
next entry into the Notes field control will appear two lines above the
previous entry for easy reviewing. Your comments therefore appear in
descending order with the latest at the top.

THIS PRODUCES THE FOLLOWING FORMATTED SAMPLE DATA:

Comment #2, Please advise Bud we can ship all 25 products.

Comment #1, Thursday is here. Where is the answer to Bud’s question?

Wednesday’s History summary. Bud wants to know how many products can be
shipped on Friday.


Hope this helps in your decision-making.
------
Tank

Hi Paul,

I think I have a good idea of your concept. I think my suggestions would
meet your needs. I don’t think you’d need a control button to move the
comment texts to the History field. Your comments imply that you might want
to keep the Comment texts in the comment field, possibly for others to read
during a specific period prior to moving the text to the History field. But
I would think moving it to the History field immediately after typing the
text in the Comment field would allow the readers to view all comments in
context from the History field.

Since you have both Comment and History fields in the same form (on separate
pages), I’m assuming you are allowing and encouraging users to read the
contents of the History field at any time. All the more reason to use the
Comment field simply to enter data and move it to the History field as soon
as possible, so that others can use the Comment field if appropriate to enter
new or additional information.

Of course, you could have the History field open for editing (i.e. unlocked)
by users if you or the person responsible for the data want it that way. Or,
the field could be locked so that no data can be edited or changed, but that
might be too restrictive to the users, keeping in mind that the database
normally is built to help all users, not restrict them.

Good luck on your work.
------
Tank