Table to Table

User avatar
Steve_in_Kent
4StarLounger
Posts: 408
Joined: 04 Feb 2010, 11:46

Table to Table

Post by Steve_in_Kent »

I'm trying to copy records from one table to another.. it mosssstly works..
Code below.. it manages to copy the specific records from one table to another when i'm copying everything, (the first two)

however, i also need to copy a specific record from another table.. It errors out on the red line. where FindFirst doesn't seem to work

Code: Select all

Private Sub Command9_Click()
' button clicked to start a new tour
' need to loop through the line selected, and add the questions to the main Data table, ready to answer ....

Dim rec1 As DAO.Recordset
Dim rec2 As DAO.Recordset
Dim rec3 As DAO.Recordset

Dim MyRecordsFromTable As String
Dim sql As String

If IsNull(Me.Combo13) Then
    MsgBox ("Must select an entry")
    GoTo JumpToEnd
End If

MyRecordsFromTable = Me.Combo13.Column(1)

If MsgBox("Are you sure you want to create " & MyRecordsFromTable, vbYesNo + vbQuestion) = vbNo Then
    GoTo JumpToEnd
End If

Set rec1 = CurrentDb.OpenRecordset(MyRecordsFromTable)
Set rec2 = CurrentDb.OpenRecordset("Data")
Set rec3 = CurrentDb.OpenRecordset("Questions")

rec1.MoveFirst
Do Until rec1.EOF
  
rec2.AddNew
rec2!QuestionAsked = rec1!Qnumber
rec2!Respon_P = rec1!Responsible_Person

[b]rec3.FindFirst "ID = " & rec1!Qnumber[/b]
' row above here, doesn't work


Temp = rec3!MyPRP

Stop


rec2.Update

rec1.MoveNext

Loop

Set rec2 = Nothing
Set rec1 = Nothing
Set rec3 = Nothing

JumpToEnd:

End Sub
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!

User avatar
HansV
Administrator
Posts: 68805
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Table to Table

Post by HansV »

What does the error message say?
Regards,
Hans

User avatar
Steve_in_Kent
4StarLounger
Posts: 408
Joined: 04 Feb 2010, 11:46

Re: Table to Table

Post by Steve_in_Kent »

runtime error 3251, operation is not supported for this type of object
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!

User avatar
HansV
Administrator
Posts: 68805
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Table to Table

Post by HansV »

Thanks! Change the line

Code: Select all

Set rec3 = CurrentDb.OpenRecordset("Questions")
to

Code: Select all

Set rec3 = CurrentDb.OpenRecordset("Questions", dbOpenDynaset)
Regards,
Hans

User avatar
Steve_in_Kent
4StarLounger
Posts: 408
Joined: 04 Feb 2010, 11:46

Re: Table to Table

Post by Steve_in_Kent »

Perfect.. thanks Hans.

I was so close !!! :)
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!