469,299 Members | 2,069 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,299 developers. It's quick & easy.

How to Copy Records From One Table Into Another?

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 its 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.

Its 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.


Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Public Function Fill_Job_Positions()
  5.         'On Local Error GoTo Fill_Job_Positions_Err
  6.  
  7.     '-------------------------Summary--------------------------------
  8.     '
  9.     'Step 1:
  10.     '
  11.     '   Sets "dbs" as current Database of type "Data Acess Object"
  12.     '
  13.     '   Opens and Runs a Query that Makes a Table that may already exist.
  14.     '       Click "Yes" to delete existing table and paste to remake Table.
  15.     '
  16.     '   Opens a new recordset within the table "New Schedule"
  17.     '       For later usag3
  18.     '
  19.     '   Opens a new recordset within the table "Jobs Not Yet Filled"
  20.     '       For later usage
  21.     '
  22.     '-------------------------Summary--------------------------------
  23.  
  24.  
  25.     Dim dbs As DAO.Database
  26.     Set dbs = CurrentDb
  27.  
  28.     DoCmd.OpenQuery "Make Jobs To Fill Query", acViewNormal, acReadOnly
  29.  
  30.     Dim Jobs As DAO.Recordset
  31.     Set Jobs = dbs.OpenRecordset("Jobs To Fill")
  32.  
  33.     Dim Employees As DAO.Recordset
  34.     Set Employees = dbs.OpenRecordset("Put It")
  35.  
  36.     Dim Schedule As DAO.Recordset
  37.     Set Schedule = dbs.OpenRecordset("New Schedule")
  38.  
  39.     Dim NotFilled As DAO.Recordset
  40.     Set NotFilled = dbs.OpenRecordset("Jobs Not Yet Filled")
  41.  
  42.  
  43.  
  44.  
  45.     '-------------------------Summary--------------------------------
  46.     '
  47.     'Step 2:
  48.     '
  49.     '   Sets "dbs" as current Database of type "Data Acess Object"
  50.     '
  51.     '   Opens and Runs a Query that Makes a Table that may already exist.
  52.     '       Click "Yes" to delete existing table and paste to remake Table.
  53.     '
  54.     '   Creates a new table and names it "New Schedule"
  55.     '       For later usage
  56.     '
  57.     '-------------------------Summary--------------------------------
  58.  
  59.     'Testing the extraction method (fields from one table into another)
  60.     'Test ~ Copying Jobs (DAO) into NotFilled (DAO)
  61.  
  62.     'Declare the strings needed to individually extract data
  63.     Dim FirstName As String
  64.     Dim LastName As String
  65.     Dim strMP As String
  66.     Dim RP As String
  67.  
  68.     Jobs.MoveFirst
  69.     strMP = Jobs
  70.  
  71.  
  72.  
  73.     NotFilled.MoveFirst
  74.     NotFilled.AddNew
  75.     NotFilled![me] = MP
  76.  
  77.     NotFilled.Update
  78.  
  79.  
  80.  
  81.     '-------------------------Summary--------------------------------
  82.     '
  83.     'Code to read records from a source into an array (Works)
  84.     '
  85.     'Dim varRec As Variant
  86.     'Dim intNumRet As Integer
  87.     'Dim intNumCol As Integer
  88.     'Dim varRec2 As Variant
  89.  
  90.     'varRec = Jobs.GetRows(3)
  91.     'intNumRet = UBound(varRec, 2) + 1
  92.     'intNumCol = UBound(varRec, 1) + 1
  93.  
  94.     'varRec2 = NotFilled.GetRows(3)
  95.     'intNumRet = UBound(varRec2, 2) + 1
  96.     'intNumCol = UBound(varRec2, 1) + 1
  97.  
  98.  
  99.     'Dim intRow As Integer
  100.     'Dim intCol As Integer
  101.  
  102.     'For intRow = 0 To intNumRet - 1
  103.     '    For intCol = 0 To intNumCol - 1
  104.     '        Debug.Print varRec(intCol, intRow)
  105.     '    Next intCol
  106.     'Next intRow
  107.  
  108.     'Jobs.Close
  109.     '
  110.     '-------------------------Summary--------------------------------
  111.  
  112.  
  113. End Function
  114.  
  115.  
Dec 28 '10 #1
7 6811
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"
Dec 28 '10 #2
Lysander
344 Expert 100+
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.OpenRecordset("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.
Dec 29 '10 #3
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. rstEmployees!LastName.Value = strName
  3. rstEmployees!LastName = strName
  4. rstEmployees![LastName] = strName
  5.  
  6.  
  7.  
My Code Below

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Public Function Fill_Job_Positions()
  5.         'On Local Error GoTo Fill_Job_Positions_Err
  6.  
  7.     '-------------------------Summary--------------------------------
  8.     '
  9.     'Step 1:
  10.     '
  11.     '   Sets "dbs" as current Database of type "Data Acess Object"
  12.     '
  13.     '   Opens and Runs a Query that Makes a Table that may already exist.
  14.     '       Click "Yes" to delete existing table and paste to remake Table.
  15.     '
  16.     '   Opens a new recordset within the table "New Schedule"
  17.     '       For later usag3
  18.     '
  19.     '   Opens a new recordset within the table "Jobs Not Yet Filled"
  20.     '       For later usage
  21.     '
  22.     '-------------------------Summary--------------------------------
  23.  
  24.  
  25.     Dim dbs As DAO.Database
  26.     Set dbs = CurrentDb
  27.  
  28.     DoCmd.OpenQuery "Make Jobs To Fill Query", acViewNormal, acReadOnly
  29.  
  30.     Dim Jobs As DAO.Recordset
  31.     Set Jobs = dbs.OpenRecordset("Jobs To Fill")
  32.  
  33.     Dim Employees As DAO.Recordset
  34.     Set Employees = dbs.OpenRecordset("Put It")
  35.  
  36.     Dim Schedule As DAO.Recordset
  37.     Set Schedule = dbs.OpenRecordset("New Schedule")
  38.  
  39.     Dim NotFilled As DAO.Recordset
  40.     Set NotFilled = dbs.OpenRecordset("Jobs Not Yet Filled")
  41.  
  42.  
  43.     'Testing the extraction method (fields from one table into another)
  44.     'Test ~ Copying Jobs (DAO) into NotFilled (DAO)
  45.     'Declare the strings needed to individually extract data
  46.  
  47.     Dim FirstName As String
  48.     Dim LastName As String
  49.     Dim MP As String
  50.     Dim RP As String
  51.  
  52.     Jobs.MoveFirst
  53.  
  54.     MP = Jobs![Main Positions]
  55.  
  56.  
  57.  
  58.     While Not Jobs.EOF
  59.  
  60.         NotFilled.AddNew
  61.  
  62.         NotFilled![Main Positions] = MP
  63.  
  64.         Jobs.MoveNext
  65.  
  66.     Wend
  67.  
  68.  
  69.  
  70.  
  71.  
  72.  
  73.  
  74.  
  75.  
  76. End Function
  77.  
  78.  
Thank you, for responding.
Dec 29 '10 #4
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.
Dec 29 '10 #5
Lysander
344 Expert 100+
I can see what is missing

Look at line 60 of your code above

NotFilled.AddNew

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

as in below, from one of my functions

Expand|Select|Wrap|Line Numbers
  1.  
  2.         rs.AddNew
  3.             rs!ControlName = ctl.Name
  4.         rs.Update
  5.  
See if that works.
Dec 29 '10 #6
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.
Dec 29 '10 #7
Lysander
344 Expert 100+
You're welcome, nice to solve a problem:)
Jan 2 '11 #8

Post your reply

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

Similar topics

reply views Thread by Krasimir_Slaveykov | last post: by
6 posts views Thread by Dale | last post: by
6 posts views Thread by sql_server_user | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.