Copying from 'table to table' or 'table to form' or 'form to table'

[Jamy]

Hi All!

I have a small problem with my code. I have a form with a subform called ArtworkVSClientReq (also name of the table). In this subform two fields need to be copied to another table (or form) when the client request is converted to an order (on click event).

I've managed to copy all the info from the main form "Client Request" to another main form called "Order Entry Form". On the Order entry form a subform called ArtworkVSOrder (also name of the table) is located. This is the destination of the records.

So what needs to be done is to copy all the records (with only the fields Artwork nr and Quantity) from subform or table "ArtworkVSClientReq" (with same field [Client Request nr]) to the subform or table "ArtworkVSOrder" (the field [BLE Ordernr] needs to be filled as well.

So far i came up with the following, doesn't work though:

Option Compare Database
Option Explicit

Function ScanRecs() As Boolean

Dim rstFrom As Recordset
Dim rstTo As Recordset
Dim fld As Field
Dim lngKey As String
Dim lng2Key As String
Dim db As DAO.Database

lngKey = CLng(Forms![Client Request]![Client request nr])
lng2Key = CLng(Forms![Convert Form]![BLE Ordernr])
Set rstFrom = ("Select Artwork_nr, Quantity From ArtworkVSClientReq Where Client_Request_nr = " & lngKey)
If rstFrom.EOF Then
Exit Sub
Else
Set rstTo = ("Select * From ArtworkVSOrder Where BLE Ordernr = " & lng2Key)
If rstTo.EOF Then
rstTo.AddNew
For Each fld In rstFrom.Fields
rstTo(fld.Name) = rstFrom(fld.Name)
Next
rstTo.Update
Else
MsgBox "Already exists"
End If
End If


rstFrom.Close
Set rstFrom = Nothing
rstTo.Close
Set rstTo = Nothing
Set db = Nothing
End Function

(this also gives a Compile Error: Type mismatch, but that's not really relevant)

Thanks in advance,
Jamy!

[Jamy]

Anyone?? Please help...