Concatenate & transfer data by command button in VBA

[HLCruz via AccessMonster.com]

My apologies if this answer already exists ... I looked but couldn't seem to
find anything that works for my situation.

I would like to programatically concatenate my fields: Address1, Address2,
City, State and Zip and then copy, or even better, cut and paste them into a
memo field, ReturnedMail within the same table.

Since this will occur in response to a RTS mail piece, I would like this code
to be run by a command button just for the record a user is viewing.

Any code suggestion would be greatly appreciated! Thank you!

 

[Tim Ferguson]

I would like to programatically concatenate my fields: Address1,
Address2, City, State and Zip and then copy, or even better, cut and
paste them into a memo field, ReturnedMail within the same table.

Almost certainly you don't want to do this: it's much better to do it in
the SELECT statement and then build forms or reports against the query.

for example:

SELECT Address1,
Address2,
City,
State,
Zip,
(Address1 + vbCrLf) & (Address2 + vbCrLf) &
(City + vbCrLf) & (State + ", ") & Zip AS FullAddress
FROM MyTable
WHERE etc etc


Note that the use of + and & suppresses empty lines if any of the fields
are blank.

Hope that helps


Tim F

 

[HLCruz via AccessMonster.com]

Thanks Tim,

1) The reason for this process is so that we can reset our address field
upon receiving returned mail, but still retain that address in another field
to be used on a report. If my concatenated field is based on a query,
wouldn't that data disappear as well once we reset those fields to null?

2) I also feel that I don't need a field of the concatenated address data
for all of my records - only those that have returned mail.

Any suggestions?

 

[Tim Ferguson]

1) The reason for this process is so that we can reset our address
field upon receiving returned mail, but still retain that address in
another field to be used on a report. If my concatenated field is
based on a query, wouldn't that data disappear as well once we reset
those fields to null?

So you have two separate bits of information: a MailingTo address and a
ClientProvided address. If they are not necessarily the same thing, then
they need two fields.

2) I also feel that I don't need a field of the concatenated address
data for all of my records - only those that have returned mail.

I don't have a problem with fields that are only sparsely filled in.
Nulls don't take up any space. If it bothers you, then you can create a
new table that looks like

CREATE TABLE MailingToAddresses
( CustomerID LONG
NOT NULL
PRIMARY KEY
FOREIGN KEY REFERENCES Customers(CustomerID),
AddressText VARCHAR(128)
NOT NULL
)


that will just hold the occasional ones.

Hope that helps


Tim F