copying a whole record from one table to another

[jjsaw5 via AccessMonster.com]

have a form that allows users to search a Read Only database. I have a make
table query that pulls data from this databae and puts it into a table in
access.

When a new record shows up in the linked database, the user will be able to
search for it through my form, and what i would like to be able to do is,
have a button that would allow the user to automatically import the new
record into a different table based on the Manager who entered the new record.



to simplify

users pulls up the new record added to the linked database and needs to
update the access database

i would like a button that automatically takes the new record( it has the
same fields as the access tables) and place it into the appropriate table.

 

[Carl Rapson]

have a form that allows users to search a Read Only database. I have a
make
table query that pulls data from this databae and puts it into a table in
access.

When a new record shows up in the linked database, the user will be able
to
search for it through my form, and what i would like to be able to do is,
have a button that would allow the user to automatically import the new
record into a different table based on the Manager who entered the new
record.



to simplify

users pulls up the new record added to the linked database and needs to
update the access database

i would like a button that automatically takes the new record( it has the
same fields as the access tables) and place it into the appropriate table.

You should just be able to run an SQL INSERT statement in your VBA code:

DoCmd.RunSQL "INSERT INTO [my table] SELECT * FROM [read-only table]
WHERE ID=" & Me.ID

This assumes there is an identify field that defines the record; it can be
ID or any other name. When the form is positioned on a record in the
read-only table, this SQL statement should copy that record to another table
as long as the other table has the exact same structure (field names and
data types/sizes).

Carl Rapson

 

[jjsaw5 via AccessMonster.com]

Awsome thank you it worked great!

I have been asked to do the same thing link another table and so on...but
this time the linked DB is read only and the field names are different then
they are in my access app. What should i do ?

the feilds are labeled z1, z2...etc...but the data in some of them is what i
need to put into my access tables.

 

[Carl Rapson]

Awsome thank you it worked great!

I have been asked to do the same thing link another table and so on...but
this time the linked DB is read only and the field names are different
then
they are in my access app. What should i do ?

the feilds are labeled z1, z2...etc...but the data in some of them is what
i
need to put into my access tables.

In that case, you would want to be more specific in your SQL statement:

INSERT INTO [my table] (field1,field2,...) SELECT (z1,z2,...) FROM
[read-only table] WHERE...

Again, make sure the field data types are the same or you'll get errors.

Carl Rapson