Results 1 to 2 of 2
  1. #1
    Jamy is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2010
    Posts
    36

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

    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!

  2. #2
    Jamy is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2010
    Posts
    36
    Anyone?? Please help...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-23-2011, 10:39 AM
  2. Replies: 0
    Last Post: 02-24-2010, 12:56 AM
  3. Replies: 2
    Last Post: 10-27-2009, 07:09 AM
  4. Use form to populate a table from another table
    By sammer021486 in forum Forms
    Replies: 3
    Last Post: 09-28-2009, 09:06 AM
  5. Replies: 0
    Last Post: 08-01-2009, 12:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums