Results 1 to 10 of 10
  1. #1
    Takiman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    21

    Append Query for Subform


    I have created the following append query:

    INSERT INTO tblAuditMsg ( MsgID )
    SELECT tblMsg.MsgID
    FROM tblMsg;

    I have tested it and it appends (inserts) 14 records into tblMsg. However, what I would like to know is What are the steps to setup and run this query in my Subform?

    - Basically, for every Main form record i create I would like this Query to be executed/run for my Subform (i.e. create 14 records).
    - Where do i insert this code in the Main or Subform? and is this the correct script and/or scripts to make this all come together?

    Any help with this would be great, thanks.
    Thanks

  2. #2
    kemas is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    6
    Docmd.openquery "ur query name"

    أرسلت من SM-G7102 بإستخدام تاباتلك

  3. #3
    Takiman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    21
    Thanks .. sorry i am fairly new to access and programming.. where would i enter this information -- in what part of form properties? and do i add that to the start of my script? thanks am still learning

  4. #4
    kemas is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    6
    U can make a command buttom and in event
    Onclick put that code
    When u click on form
    That code will done

    أرسلت من SM-G7102 بإستخدام تاباتلك

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Put the code below in the AfterInsert event on the form or subform.

    Docmd.SetWarnings False
    Docmd.RunSQL "INSERT INTO tblAuditMsg ( MsgID )
    SELECT tblMsg.MsgID
    FROM tblMsg"
    Docmd.SetWarnings True

    Curious as that code adds all MsgIDs from that table to the audit table each time it is run. Is that what you want to do or do you need to only add the newly created record MsgID to the Audit table?

  6. #6
    Takiman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    21
    Hi Bulzie, I have tried to enter your code into the Before insert event for the both the form and then tried again in the subform -- however attempts attempts came up with Syntax errors -- the text below is showing as Red in the code builder section. Any help with fixing this issue would be great?

    Also to answer your question -- Yes I want all MsgIDs (in total there are 14) to be run/created (in the subform) each time a new Mainform record is created. I have a textbox with a control source MsgShown in the subform. For each MsgID created in the subform (i.e 14) the user can select Yes or No in the MsgShown textbox. Because each Audit has the same repetitive Messages that must be answered for every Audit, that is my only choice unless there is a better way? again i am happy for any advice to make this happen.

    Private Sub Form_BeforeInsert(Cancel As Integer)
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblAuditMsg (MsgID)"
    SELECT tblMsg.MsgID
    FROM tblMsg"

    DoCmd.SetWarnings True

    End Sub

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    DoCmd.RunSQL "INSERT INTO tblAuditMsg (MsgID) SELECT tblMsg.MsgID FROM tblMsg"
    Maybe it's just that space that's missing.

  8. #8
    Takiman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    21
    Ok thanks Davegri it was the spacing that was the problem.. it now saves without any errors. This is the code now:

    Private Sub Form_AfterInsert()
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblAuditMsg (MsgID)SELECT tblMsg.MsgID FROM tblMsg"
    DoCmd.SetWarnings True
    End Sub


    However, what I forgot to mention earlier is that the Mainform and Subform are linked by "AuditID". Can someone help me with Code to make the AuditID also update when the MsgID updates? please note the AuditID for the 14 MSgID records will be the same number (i.e. AuditID 1 will have MsgID (1-14), Audit ID 2 will have MsgID (1-14) etc).

    Thanks any help would be great,

  9. #9
    Takiman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    21
    All good i found some code online that I have adapted and it has created exactly what I needed! It is inserted into the Mainforms AfterInsert event: Now for every new record in the Mainform my Subform automatically creates 14 pre-populated records in the Subform :-)

    The code is as follows:

    Private Sub Form_AfterInsert()
    Dim strSQL As String

    ' insert rows into Tbl_tblAuditMsg table
    strSQL = "INSERT INTO tblAuditMsg(AuditID,MsgID) " & _
    "SELECT " & Me.AuditID & ", MsgID " & _
    "FROM tblMsg"

    CurrentDb.Execute strSQL, dbFailOnError

    ' requery subform to show new rows
    Me.tblAuditMsg.Requery

    End Sub

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Excellent. Good job.

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

Similar Threads

  1. Replies: 3
    Last Post: 11-23-2015, 02:41 AM
  2. Replies: 4
    Last Post: 11-17-2015, 04:01 PM
  3. Access can't append all the records in the append query
    By fluffyvampirekitten in forum Access
    Replies: 2
    Last Post: 08-27-2015, 01:53 AM
  4. Replies: 3
    Last Post: 03-11-2012, 08:24 PM
  5. Replies: 1
    Last Post: 10-06-2011, 08:37 AM

Tags for this Thread

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