How to Copy Records From One Table Into Another?

[Laneyshia]

I'll cut to the chase:

I'm writing a program written in Access Module. I never wrote in VBA code before although I do have programming experience. The idea is broken down into 3 steps.

Step 1:

Have a table that contains the Main Job Position that need to be filled and a table with the list of Employees.

Compare the "Main Position" of the Employee with the "Main Position" that needs to be filled.

If it’s a match, copy Employee record into "New Schedule" table and if not copy Job Position into "Job Position Not Yet Filled" table.

I could go on but I'm not even past this part yet which is the most important.

It’s very simple yet frustrating to know that the idea is in my head but I'm having a great amount of difficulties putting it on paper.

Below is the code that I have so far. What I'm trying to do is to be able to copy records (individually or with an array) from one table into another. I have done extensive research as you may be able to tell by the commented code. You guys are my last hope!

Just like VBA, I am completly new to this and I thank you GREATLY in advance.

Option Compare Database

Public Function Fill_Job_Positions()
        'On Local Error GoTo Fill_Job_Positions_Err
    
    '-------------------------Summary--------------------------------
    '
    'Step 1:
    '
    '   Sets "dbs" as current Database of type "Data Acess Object"
    '
    '   Opens and Runs a Query that Makes a Table that may already exist.
    '       Click "Yes" to delete existing table and paste to remake Table.
    '
    '   Opens a new recordset within the table "New Schedule"
    '       For later usag3
    '
    '   Opens a new recordset within the table "Jobs Not Yet Filled"
    '       For later usage
    '
    '-------------------------Summary--------------------------------
    
    
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    
    DoCmd.OpenQuery "Make Jobs To Fill Query", acViewNormal, acReadOnly
    
    Dim Jobs As DAO.Recordset
    Set Jobs = dbs.OpenRecordset("Jobs To Fill")
    
    Dim Employees As DAO.Recordset
    Set Employees = dbs.OpenRecordset("Put It")
    
    Dim Schedule As DAO.Recordset
    Set Schedule = dbs.OpenRecordset("New Schedule")
    
    Dim NotFilled As DAO.Recordset
    Set NotFilled = dbs.OpenRecordset("Jobs Not Yet Filled")
    
    
    
    
    '-------------------------Summary--------------------------------
    '
    'Step 2:
    '
    '   Sets "dbs" as current Database of type "Data Acess Object"
    '
    '   Opens and Runs a Query that Makes a Table that may already exist.
    '       Click "Yes" to delete existing table and paste to remake Table.
    '
    '   Creates a new table and names it "New Schedule"
    '       For later usage
    '
    '-------------------------Summary--------------------------------
    
    'Testing the extraction method (fields from one table into another)
    'Test ~ Copying Jobs (DAO) into NotFilled (DAO)
    
    'Declare the strings needed to individually extract data
    Dim FirstName As String
    Dim LastName As String
    Dim strMP As String
    Dim RP As String
      
    Jobs.MoveFirst
    strMP = Jobs
    
    
    
    NotFilled.MoveFirst
    NotFilled.AddNew
    NotFilled![me] = MP
    
    NotFilled.Update
    
    
    
    '-------------------------Summary--------------------------------
    '
    'Code to read records from a source into an array (Works)
    '
    'Dim varRec As Variant
    'Dim intNumRet As Integer
    'Dim intNumCol As Integer
    'Dim varRec2 As Variant
    
    'varRec = Jobs.GetRows(3)
    'intNumRet = UBound(varRec, 2) + 1
    'intNumCol = UBound(varRec, 1) + 1
    
    'varRec2 = NotFilled.GetRows(3)
    'intNumRet = UBound(varRec2, 2) + 1
    'intNumCol = UBound(varRec2, 1) + 1
    
    
    'Dim intRow As Integer
    'Dim intCol As Integer
    
    'For intRow = 0 To intNumRet - 1
    '    For intCol = 0 To intNumCol - 1
    '        Debug.Print varRec(intCol, intRow)
    '    Next intCol
    'Next intRow
    
    'Jobs.Close
    '
    '-------------------------Summary--------------------------------
    
    
End Function

[Laneyshia]

Line 69. above was to be typed:

strMP = Jobs![Main Position]

And with they the main error I recieve is:

"Run-Time error 3265: Item not found in collection"

[Lysander]

My first guess is a simple spelling mistake in either your code, or one of your tables or field names. It helps not to have spaces in object names. e.g. instead of "Main Position", call it Main_Position.

Are you able to put a breakpoint line 26 of your code and then, when the debugger comes up, step through, line by line, until you come across the line that is giving the error.

If, for example, the error is coming up at the line

Set NotFilled = dbs.OpenRecords et("Jobs Not Yet Filled")

Check that the query, or table, is really called "Jobs Not Yet Filled" and not something like "Jobs Not Yet Filled" (Two spaces after jobs)

In a live commercial database I run, I have dozens of tables with the field WomanID. In one table, by mistake, it was called WomenID and looking in that table for WomanID would give Item not found error.

[Laneyshia]

Below is my slightly modified code. I did double check what you said and no I did not have any more spaces than one between the words. I also check the spelling of the code compared to the spelling of the Table in the BD.

I am simply trying now, to copy all the records in Jobs![Main Positions] over to NotFilled![Main Positions].

This is the short code I am basing it off of:

found: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

 
rstEmployees!LastName.Value = strName
rstEmployees!LastName = strName
rstEmployees![LastName] = strName

My Code Below

Option Compare Database

Public Function Fill_Job_Positions()
        'On Local Error GoTo Fill_Job_Positions_Err
    
    '-------------------------Summary--------------------------------
    '
    'Step 1:
    '
    '   Sets "dbs" as current Database of type "Data Acess Object"
    '
    '   Opens and Runs a Query that Makes a Table that may already exist.
    '       Click "Yes" to delete existing table and paste to remake Table.
    '
    '   Opens a new recordset within the table "New Schedule"
    '       For later usag3
    '
    '   Opens a new recordset within the table "Jobs Not Yet Filled"
    '       For later usage
    '
    '-------------------------Summary--------------------------------
    
    
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    
    DoCmd.OpenQuery "Make Jobs To Fill Query", acViewNormal, acReadOnly
    
    Dim Jobs As DAO.Recordset
    Set Jobs = dbs.OpenRecordset("Jobs To Fill")
    
    Dim Employees As DAO.Recordset
    Set Employees = dbs.OpenRecordset("Put It")
    
    Dim Schedule As DAO.Recordset
    Set Schedule = dbs.OpenRecordset("New Schedule")
    
    Dim NotFilled As DAO.Recordset
    Set NotFilled = dbs.OpenRecordset("Jobs Not Yet Filled")
    
    
    'Testing the extraction method (fields from one table into another)
    'Test ~ Copying Jobs (DAO) into NotFilled (DAO)
    'Declare the strings needed to individually extract data
    
    Dim FirstName As String
    Dim LastName As String
    Dim MP As String
    Dim RP As String
      
    Jobs.MoveFirst
    
    MP = Jobs![Main Positions]
    
    
    
    While Not Jobs.EOF
    
        NotFilled.AddNew
    
        NotFilled![Main Positions] = MP
        
        Jobs.MoveNext
        
    Wend
    
    
    
    
      
      
      
    
    
End Function

Thank you, for responding.

[Laneyshia]

Sorry once again, I move line 54 inside the while loop so that the string MP actually changes as the marker pointing to Jobs is changing.

I don't understand. When I run it with the green arrow, it doesn't give me any errors.

It runs making or remaking the table requested asking me yes or no questions and then it's over.

I then look within the DB to see if the table "Jobs Not Yet Filled" has been filled and it has not.

What am I doing wrong?

I don't understand.

[Lysander]

I can see what is missing

Look at line 60 of your code above

NotFilled.AddNe w

After adding the new data, you have to complete it with an
NotFilled.Updat e

as in below, from one of my functions

        rs.AddNew
            rs!ControlName = ctl.Name
        rs.Update

See if that works.

[Laneyshia]

THANK YOU!!

Actually, I just logged in to inform you that I got the "msgbox" function to work properly which displayed the MP string meaning that it (MP) is actually reading the string from the table. That alone made me excited but then you answered my question with such a simple answer!!

THANK YOU SO MUCH!!

Now I can proceed further in hopes to reach step 3 before tomorrow.

[Lysander]

You're welcome, nice to solve a problem:)