Copy & Paste Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I know to most this is probably a really simple task! Here goes anyway.

I have a personal information form that contains fields to complete for a
residential address, (Address 1, Address 2, Suburb, State, Post Code) and I
also have a postal address section containing Postal Address 1, Postal
Address 2, Postal Suburb, Postal State, Postal Post Code and a tick box for
Same as Residential.

You can probably tell where thisis leading. I would like a macro to run if
the advisor ticks same as residential so that it will populate the main
address in the postal address.

Can this be done and if so how?

Many thanks

Lee
 
Lee,

Make an Update Query to update the postal address fields with the vlaue
of the corresponding residential address fields. And then run the
update query when the button is clicked. If you want to use a macro to
automate the update, you can use the OpenQuery action.
 
Hi Steve,

I created an update query and selected the postal address fields. It then
asks up date to, but I can not get it to update to the residential address,
it keeps asking me to enter the parameter.

Have I missed something in the process?

Thanks

Lee
 
Lee,

In design view of the Update Query, select SQL from the View menu, and
then post back with the SQL view of the query. Also, what Parameter is
it asking you for?
 
Hi Steve,

Here is the SQL for the query, I can only assume it is because it is in the
same table?

INSERT INTO tblClientList ( [PostalAddress 1], [PreviousAddress 2],
PostalSuburb, PostalState, [PostalPost Code] )
SELECT tblClientList.[Address 1], tblClientList.[Address 2],
tblClientList.Suburb, tblClientList.State, tblClientList.[Post Code]
FROM tblClientList
WHERE (((tblClientList.IMSReference)=[Forms]![Navigation
Bar]![IMSReference]));

Thanks
 
Lee,

1. You didn't answer my question about "what Parameter is it asking you
for?"

2. This is an Append Query, not an Update Query.
 
Hi Steve,

It was asking me to enter a parameter for each field in the query. example.

"Please enter a parameter for Address 1"

Anyway, I changed it to an update query and followed the instructions you
gave me and it just corrupted my information, it all went into some kinda
chineese writing?

Any thoughts?

Thanks

Lee

Steve Schapel said:
Lee,

1. You didn't answer my question about "what Parameter is it asking you
for?"

2. This is an Append Query, not an Update Query.

--
Steve Schapel, Microsoft Access MVP

Hi Steve,

Here is the SQL for the query, I can only assume it is because it is in the
same table?

INSERT INTO tblClientList ( [PostalAddress 1], [PreviousAddress 2],
PostalSuburb, PostalState, [PostalPost Code] )
SELECT tblClientList.[Address 1], tblClientList.[Address 2],
tblClientList.Suburb, tblClientList.State, tblClientList.[Post Code]
FROM tblClientList
WHERE (((tblClientList.IMSReference)=[Forms]![Navigation
Bar]![IMSReference]));
Click to expand...
Click to expand...
 
Lee,

Can I see the SQL of the Update Query?

Can you Compact/Repair the database?
 
Hi Steve,

The SQL is:

UPDATE tblClientList SET tblClientList.[PostalAddress 1] = [Address 1],
tblClientList.[PostalAddress 2] = [Address 2], tblClientList.PostalSuburb =
[Suburb], tblClientList.PostalState = [State], tblClientList.[PostalPost
Code] = [Post Code]
WHERE (((tblClientList.[IMSReference])=[Forms]![Navigation
Bar]![IMSReference]));

and I compact and repair the databse every day, as well as back it up every
day.

Thanks

Lee
 
Lee,

I am sorry, I don't have an answer to the data corruption problem. I
could keep peppering you with questions. For example, does this happen
with every record you try to update using this update quey approach, or
was it just an isolated incident? And, are any edits to the current
record on the form saved, before trying the update query? But I'll
admit that all this is just shooting in the dark. If it was mine, one
of the first things I would do is make a new mdb file, and import all
the objects from the present database, then compact/repair. I might
even re-build the tblClientList table. See if that helps.
 
Hi Steve,

Thanks for help anyway mate!

I will try looking at the other suggestions and hopefully it will resolve it.

Cheers for the help!

Lee

Steve Schapel said:
Lee,

I am sorry, I don't have an answer to the data corruption problem. I
could keep peppering you with questions. For example, does this happen
with every record you try to update using this update quey approach, or
was it just an isolated incident? And, are any edits to the current
record on the form saved, before trying the update query? But I'll
admit that all this is just shooting in the dark. If it was mine, one
of the first things I would do is make a new mdb file, and import all
the objects from the present database, then compact/repair. I might
even re-build the tblClientList table. See if that helps.

--
Steve Schapel, Microsoft Access MVP
Hi Steve,

The SQL is:

UPDATE tblClientList SET tblClientList.[PostalAddress 1] = [Address 1],
tblClientList.[PostalAddress 2] = [Address 2], tblClientList.PostalSuburb =
[Suburb], tblClientList.PostalState = [State], tblClientList.[PostalPost
Code] = [Post Code]
WHERE (((tblClientList.[IMSReference])=[Forms]![Navigation
Bar]![IMSReference]));

and I compact and repair the databse every day, as well as back it up every
day.
Click to expand...
Click to expand...
 
Back
Top