Bulk Add Records and Sub-Records?

[misscrf]

For more on this project I am working on, please see this thread:
thread702-1610544

As best as I can summarize, I have an appplication SQL backend, linked forms front end. For each form I describe there is a normalized table behind it.

I have an events form, and a subform for event invites. I have a command button on the main events form to open a pop-up form that allows me to choose from a multi-select listbox the contacts that are being invited to an event, and set a common date that the invites are sent, etc.

I found most of this code on a sample that pbaldy had posted on his personal website in an faq. It was extremely helpful and I want to make sure he gets credit for that. I found it here:

http://www.baldyweb.com/MultiselectAppend.htm

My next step is that I need to see if I can add a section to the pop-up form for a listbox of the account managers and text box control for a notes box. Then I need to be able to take all the event invite id's that were just created from the above code and populate tblEventInvOBO with who (0 or more) that invite was on behalf of. 0 would not add any of course.

the tblEventInvOBO has the following:

PKEventInviteOBO
FKEventInvite(from the tblEventInvite table with the event id and contact id)
FKAM (from the Account managers table)
Notes (memEventInviteOBO)

So this is what gets tricky. The final code in the previous thread is going to create each of those eventinviteid's. I also need to take each of those id's and add an obo record for each am that an invite is obo.... I may only add 1 obo for all invites or 2 for all invites. Generally a bunch of invites getting added will all have the same 1 or 2 account managers inviting them (obo's). If I can get this to work, then on the main subform, the user can edit specific ones if they have something different.

I also have a post on that pop-up form, but it is an entirely different issue, so I kept them separate.

Does Make sense?

I appreciate if anyone can delve into this a bit and see if they have some thoughts on how to accomplish this.

Thanks.


Thank you.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I ended up getting this to work, so I wanted to post the solution in case it helps someone else:

Private Sub cmdAddClose_Click()
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim ctl2          As Control
  Dim varItem       As Variant
  Dim varitemobo    As Variant
  Dim holdID        As Long
  Dim ID 'Dim the ID variable

  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tblEventInvite", dbOpenDynaset, dbSeeChanges)

  'make sure a selection has been made
        If Me.lstInviteContact.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 contact to invite;-)"
        Exit Sub
            End If

  
  'add selected value(s) to table
        Set ctl = Me.lstInviteContact
        
        For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!FKEvent = [Forms]![frmEvent].PKEventID
    rs!FKContact = ctl.ItemData(varItem)
    
        If Me.dtInviteSent.Value <> "" Then
    rs!dtInviteSent = Me.dtInviteSent
        End If
    
        If Me.dtRSVPReceived.Value <> "" Then
    rs!dtRSVPReceived = Me.dtRSVPReceived
        End If
    
        If Me.intPlusGuest.Value <> "" Then
    rs!intPlusGuest = Me.intPlusGuest
        End If
    
        If Me.FKRSVP.Value <> "" Then
    rs!FKRSVP = Me.FKRSVP
        End If
    
        If Me.MemEventInvNotes.Value <> "" Then
    rs!MemEventInvNotes = Me.MemEventInvNotes
        End If
    
    rs.Update
    
    rs.Bookmark = rs.LastModified

Set rs2 = db.OpenRecordset("tblEventInvOBO", dbOpenDynaset, dbSeeChanges)

'    make sure a selection has been made
        If Me.lstAMOBO.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 Account Manager to assign as sending on behalf of. ;-)"
        Exit Sub
            End If

Set ctl2 = Me.lstAMOBO
        For Each varitemobo In ctl2.ItemsSelected
    rs2.AddNew
    rs2!FKEventInvite = rs!PKEventInviteID
    rs2!FKAM = ctl2.ItemData(varitemobo)
    'rs2!FKAM = ctl2.ItemData(ctl2.ItemsSelected(varitemobo))
    
    
        If Not IsNull(Me.MemOBONotes.Value) Then
    rs2!memEventInvOBO = Me.MemOBONotes
        End If

    rs2.Update
   
    Next varitemobo
    Next varItem
''
''    Forms!frmEvent!frmSubEventInvite.Requery
''    Forms!frmEvent!frmSubEventInvite.Form!txtCurrRec.Requery
''    Forms!frmEvent.Visible = True
''
''    DoCmd.Close acForm, "frmInviteContact", acSaveNo

ExitHandler:
  Set rs = Nothing
  Set rs2 = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select

End Sub
Private Sub cmdClearForm_Click()
On Error GoTo Err_cmdClearForm_Click

 With Me.lstInviteContact

        For Each varItm In .ItemsSelected
            .Selected(varItm) = False
        Next varItm

    End With
    
    Me.dtInviteSent.Value = ""
    Me.FKRSVP.Value = ""
    Me.dtRSVPReceived.Value = ""
    Me.intPlusGuest.Value = ""
    Me.MemEventInvNotes.Value = ""
    
     With Me.lstAMOBO

        For Each varItm In .ItemsSelected
            .Selected(varItm) = False
        Next varItm
    End With

    Me.MemOBONotes.ValidationRule = ""
    
Exit_cmdClearForm_Click:
    Exit Sub

Err_cmdClearForm_Click:
    MsgBox Err.Description
    Resume Exit_cmdClearForm_Click
    
End Sub

Hope this helps someone know. I know so many on this site have helped me from time to time!



misscrf

It is never too late to become what you could have been ~ George Eliot