Thanks to "Rookery" I am getting there

[newboy18]

I want to test a record set to make sure it is at the new record and then copy several sets from another table, this is how far I am, mydata1 will be the number of records to copy and mydata2 will be the start of the transfer.
The bits that are commented out are the bits I have the problem with, the "rec1 = Nothing" wont work and do I have to dim things as DAO.

Dim db As Database
Dim rec1 As Variant
Dim rec2 As Variant
Dim myData1 As Integer
Dim myData2 As Integer

Set db = CurrentDb()
Set rec1 = db.OpenRecordset("Table1&quot
rec1.MoveFirst
myData1 = rec1(0)
rec1.MoveNext
myData2 = rec1(0)
Set rec2 = db.OpenRecordset("Table2&quot
rec2.MoveLast
'If rec2 = NewRecord Then
'MsgBox "OK to move data"
'Else
'MsgBox "Not at new record"
'End If
rec1.Close
rec2.Close
'rec1 = Nothing

 

[rookery]

Newbie

I think you're getting your terminology mixed up a little. (we all do to begin with). My understanding of your question is that you're trying to copy records (not sets - although a set CAN contain just the one record) from one table to another.

If this is the case you could use the following code:
(seem to have used this a lot the last couple of days!)

Set db = CurrentDB
db.Execute ("SELECT * INTO [tblNew] FROM [tblOld]&quot
db.TableDefs.Refresh

Note that this will transfer all the records from one table to another. If you want to select only certain records you'll have to include some criteria. For example:

db.Execute ("SELECT * INTO [tblNew] FROM [tblOld] WHERE _
[your field] = criteria&quot

You must be careful here as the concatenation of criteria in SQL commands varies from data type to data type.

Note also that it is not necessary to go to a new record in the New table for the transfer to take place.

Apologies also: it should read Set rec1 = Nothing.

Lastly I'm far from the most experienced programmer on here and perhaps there are better ways to achieve your end result.

NSS

 

[newboy18]

Your fist example will copy the whole table and the second will copy records using a criteria, but I think I just want to copy a recordset, i.e. all the fields in specific rows of a table to another table.
My new VBA book (waste of mony) does not have commands like "SELECT * INTO", are these VBA, SQL or what, it looks like I need another book!

 

[rookery]

Using the criteria-based SQL statement will allow you to copy "all the fields in a specific row (record)" into another table.

For example the following code would copy all records from tblOld into tblNew wherever the text field called "Month" was equal to "November":

db.Execute ("SELECT * INTO [tblNew] FROM [tblOld] WHERE [Month] = 'November'&quot

The only problem with this Make-Table SQL though is that if the table already exists you'll get an error message. That is after the first time, unless you delete the table, this code will error. What I do is delete it first and then re-create it. Would something like that be suitable to you?