Results 1 to 6 of 6
  1. #1
    thart21 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    83

    INSERT multiple records from form

    I am trying to use the below code from my cmd button on my form to insert multiple new records in tblForecast.

    I need it to loop through each set of entries and insert a new record (tblForecast has an autonumber ID field)

    This code inserts only the 1st new record 5 times instead of inserting all of them once. I have been looking at "loops" but have not figured out if that is the way I should be going, any ideas? I've attached a screenshot of my form.

    Private Sub cmdUpdate_Click()
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim numRecords As Integer
    Dim i As Integer



    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset("tblForecast", dbOpenDynaset, dbOpenAppendOnly)

    For i = 1 To 6
    With rst
    rst.AddNew
    ![forecastmonth] = Me![txtForecastMonth]
    ![forecast] = Me![txtForecast]
    ![division] = Me![cboDiv]
    ![Group] = Me![cboGroup]
    rst.Update
    End With
    Next i

    MsgBox (i + 1) & " Records have been added to the Table [tblForecast]", _
    vbInformation, "Append Data"

    rst.Close
    Set rst = Nothing
    End Sub

    Thank you! Toni

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    What exactly are the 6 new records supposed to contain? Except for the autonumber ID, your code does make them all the same because the values assigned to each field come from form fields, which do not change from one iteration of the loop to the next.

    But what is the recordsource for your form? From what I can see, all you are doing is making a second copy (in tblforecast) of data you already have on your form. If this really what you want to do?

    John

  3. #3
    thart21 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    83
    Hi, thanks for taking a look at this!
    The six records will be different in the fact that they will each have a different combination of Division, Group & Forecast.

    The final records inserted into the table should look like
    ForecastMonth Division Group Forecast
    11/1/2011 10 Summer 5000
    11/1/2011 20 Summer 1000
    11/1/2011 30 Summer 2500
    11/1/2011 10 Spring 8500
    11/1/2011 20 Spring 3100
    11/1/2011 30 Spring 4525

    The form fields are unbound if that helps.

  4. #4
    bcofie is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2011
    Posts
    31
    There are 6 combo boxes and you need to loop through all the combo boxes. The code you have now is just for one combo box.Its inserting the values in the same combo box i-times.

    Do something like this:
    Dim ctl as Control
    For Each ctl in me.Controls
    if ctl.controlType = acComboBox then
    "Insert code here to insert all values entered in all combo boxes to be inserted in appropriate fields of table"
    End if

  5. #5
    bcofie is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2011
    Posts
    31

    insert multiple records

    Toni,

    See the attached. I created sample.


    Ben
    Last edited by bcofie; 12-18-2011 at 07:34 PM.

  6. #6
    KDS2822 is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2013
    Posts
    1
    How can you go to a new record after adding the multiple records to make sure they aren't duplicated?

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

Similar Threads

  1. Multiple Records On the same Form(ish)
    By Dalagrath in forum Forms
    Replies: 0
    Last Post: 03-30-2011, 11:13 AM
  2. Replies: 10
    Last Post: 12-13-2010, 11:49 PM
  3. One Form, Multiple records
    By andwhy81 in forum Forms
    Replies: 3
    Last Post: 09-21-2010, 12:12 PM
  4. Add Multiple Records on One Form
    By William McKinley in forum Forms
    Replies: 7
    Last Post: 08-18-2010, 09:31 AM
  5. Replies: 0
    Last Post: 02-24-2010, 12:56 AM

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