Copy Selected records from one table to another. 1

[kkarren]

I have two tables in my database and I want to do the following in an event procedure
Copy tableone where fieldone = x into tabletwo. I need some assistance in using the correct syntex making this happen. The table definitions of the two tables are the same. [sig][/sig]

 

[DougP]

First I would create a recordset that has all of the records that meet the "X" criteria.
You can create a query to see the number of records involved. Then use the SQL code that the query makes in your VBA code.
After you have the records that match, then loop through those records and add them one at a time to the second table.
---------------------
Dim db As Database
Dim rst, rst2 As Recordset
Dim SQL As String
Dim A As Integer

Set db = CurrentDb
SQL = "Select * From Table1 Where Field1 = X"
Set rst = db.OpenRecordset(SQL)
Set rst2 = db.OpenRecordset("Table2&quot
rst.MoveLast
rst.MoveFirst

For A = 1 To rst.RecordCount
rst2.AddNew 'Add a new record to the second table
rst2!("field1&quot = rst!("field1&quot 'Set fields in Table 2 to values in Table 1's recordset
rst2!("field2&quot = rst!("field2&quot
rst2.Update 'update record in table 2 "Must have"
rst.MoveNext 'move to the next record in the first recordset
Next

rst.Close
rst2.Close
db.Close
----------------------------
[sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]