Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Location
    Colorado
    Posts
    12

    Question Unanswered: Subform Copy Help

    All,
    I am trying to get the below to work, but the subforms records are not copying to my tbl_sub1_copy, tbl_sub2_copy, & tbl_sub3_copy:

    The only thing that is working is the copying of the records in tbl_Main_copy, but I can't get all the records from the subforms to copy to their respective sub copy tables.

    I want the record on the frm_main and the records associated with tbl_sub1, tbl_sub2, & tbl_sub3 to copy to these tables: tbl_sub1_copy, tbl_sub2_copy, & tbl_sub3_copy, but again the below is not working.

    How can I fix this?


    Private Sub Command14_Click()

    Dim db As DAO.Database, rst As DAO.Recordset

    'Copy record
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tbl_Main_copy")
    Set rst1 = Me.frm_sub1.Form.RecordsetClone
    Set rst2 = Me.frm_sub2.Form.RecordsetClone
    Set rst3 = Me.frm_sub3.Form.RecordsetClone
    rst.AddNew
    rst1.AddNew
    rst2.AddNew
    rst3.AddNew

    rst!MainID = Me!MainID
    rst!field1 = Me!field1
    rst!field2 = Me!field2
    rst!field3 = Me!field3
    rst1!MainID = Forms!frm_Main!frm_sub1.Form!MainID
    rst1!cdr_number = Forms!frm_Main!frm_sub1.Form!cdr_number
    rst2!MainID = Forms!frm_Main!frm_sub2.Form!MainID
    rst2!sdr_number = Forms!frm_Main!frm_sub2.Form!sdr_number
    rst3!MainID = Forms!frm_Main!frm_sub3.Form!MainID
    rst3!pts_number = Forms!frm_Main!frm_sub3.Form!pts_number
    rst3!severity = Forms!frm_Main!frm_sub3.Form!severity

    rst.Update
    rst1.Update
    rst2.Update
    rst3.Update
    rst.Close
    rst1.Close
    rst2.Close
    rst3.Close
    db.Close

    End Sub

    Any help will greatly be appreciated.

    Thanks in advance,

  2. #2
    Join Date
    Nov 2003
    Location
    Warren, PA
    Posts
    52
    Try using this command to update table fields

    CurrentDb.Execute "Update TABLENAME Set FIELD = VALUE Where RECORDID = " & Me![CURRENTFORMRECORDID]

    so if your trying to update table: Clients field: customername to the current forms customer field: Cust_name

    CID = Table record ID field
    FCID = Forms record ID field


    CurrentDb.Execute "Update Clients Set customername =" & me!cust_name & "Where CID = " & Me![FCID]

    This should work for you if you need more info to understand let me know

    Brent

  3. #3
    Join Date
    Dec 2003
    Location
    Colorado
    Posts
    12

    Subform Copy Help

    Ok I am going to use the real name for the fields and table, and please let me know if it is correct.

    CurrentDb.Execute "Update tbl_sub1_copy Set MainID = VALUE Where RECORDID = " & Me![MainID]

    I get a too few parameters error. Here are the names of items that are playing a part in this.

    The table I want to update is: tbl_sub1_copy
    The related field that is in the form table and subform table is: MainID

    What do I put in for VALUE?

    Please help. Thanks so much.

  4. #4
    Join Date
    Dec 2003
    Location
    Colorado
    Posts
    12

    Talking Subform Copy Help

    I found the answer, and it is below:

    DoCmd.RunSQL "INSERT INTO tbl_sub1_copy SELECT * FROM tbl_sub1 WHERE MainID = " & Me.MainID & ";"


    There is a message that comes asking me if I want to append, but I really would like to suppress that message.

    Do you know how to suppress that message?

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    docmd.setwarnings false
    DoCmd.RunSQL "INSERT INTO etc
    docms.setwarnings true

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2003
    Location
    Colorado
    Posts
    12

    Thumbs up Subform Copy Help

    Yes I did find that answer as well.

    THANK YOU SO VERY MUCH!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •