Data Entry Form - Want Form button to submit multiple records (1 Viewer)

[kvnd]

I have a data entry form where users submit drills all our locations are required to do by law. You can see the combo boxes/fields in the image attached. Users hit the button to submit the information to the database.

Our programs can do multiple types of drills at once. For example, they can do an active shooter and medical emergency drill together simultaneously. As a result, I want the option to choose multiple drill types in the form (in the image, I have both Fire/Smoke and Medical Emergency Selected for instance).

When the user picks more than one drill type, I want this to be submitted to the database in the form of two separate records for each drill type (where all information except for the drill type is the same.

So for example, if i were to hit the submit record button in the image, what I desire my form to do is create two new records in the database which look like this (dashes separating fields)

1 - 10/11/18 - Administration - - Functional Walk Through - Fire/Smoke
2 - 10/11/18 - Administration - - Functional Walk Through - Medical Emergency

How can I get my form to do this?

I'm kind of new to access. My submit record button is an embedded macro, and the data entry property of the entire form is set to yes. I think a loop might accomplish this, however i do not know the syntax or where to even put the VBA code (since the button is a macro, not code builder). Maybe I should convert the macro to VBA? How can I do this? I was imagining some code like this, where ???? indicates code I do not know how to write (Sorry the indentations didn't carry over to this forum): UG - I've added Code Tags

Private Sub SubmitRecord_Click()
    Dim i As Control
    For Each i in Me.DrillType
        If ???? (DrillTypeOption is selected)
            ???? (Move to new blank record)
            With i
                ???? (set new record Date = cboDate)
                ???? (set new record Center = cboCenter)
                ???? (set new record Drill Type = DrillType[i])
'....
            End With
        End If
    Next
End Sub

Let me know if you can help me implement my solution or have alternative approaches I can use

 

[Uncle Gizmo]

I think you need to explain what you want to do with those two practically identical entries. The explanation will probably enable "US" to explain to you how to take advantage of the built-in features of MS Access to get what you want without duplicating data.

 

[GinaWhipp]

Hmm, have a look at...
http://www.baldyweb.com/MultiselectAppend.htm

 

[kvnd]

@Gina

Thanks! I was able to implement that perfectly. It works fantastic

 

[GinaWhipp]

Great! Glad that worked.