By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,795 Members | 1,660 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,795 IT Pros & Developers. It's quick & easy.

I want to copy two fields from one table to another

P: 1
First let me admit I'm a newbie with Access....

I am updating a table using one form and want to copy two of the table fields to another table upon clicking save.
I see commands to copy the full record, but want to copy just two fields.

How do I do this?
Mar 1 '10 #1
Share this Question
Share on Google+
2 Replies


patjones
Expert 100+
P: 931
If you could post what the structure of the tables are, that would be great...and also whether you are trying to do this using VBA. And, when you say two of the table fields, do you mean two of the fields from just one record?

Pat
Mar 1 '10 #2

beacon
100+
P: 579
You will need to either create an Append Query or, if you're going to use VBA, run a SQL statement from a module or subroutine.

To create an Append Query, just create a new query in Design View. Go to Query on the menu and select Append Query. It will ask you which table you want to add records to...this is the destination table. Then, you'll identify the field that needs to be updated.

Then, on your form, use your Save command to update the Append Query you just created and it will automatically add the records to your table.

If you are going to take the VBA route, you'll enter the following code being sure to put your actual table info in where it says [yourTable] or something similar:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [destinationTable] ( Field1, Field2 )
  2. SELECT [yourTable].Field1, [yourTable].Field2
  3. FROM [yourTable]
  4.  
If you need to select these fields based on a condition, you'll add a WHERE clause, like so:

Expand|Select|Wrap|Line Numbers
  1. WHERE ((([yourTable].Field1)= " & varFieldData & "));"
  2.  
varFieldData would be a variable that holds the data, although you could type the data that the condition is based on directly into the WHERE clause by removing the ampersands (&) and the double quotes (") immediately adjacent to the ampersands (&).

You won't need to put the square brackets around [yourTable] either. I just added those to help make it easy to see which table I was referring to at any time. Probably goes without saying, but you will have to change Field1 to the actual name of the field that you are trying to copy data from.
Mar 1 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.