Events and Invites - Checkbox Add Invites to Table? 1

[misscrf]

I have this new app that I am developing. It is a small and basic one with contacts.

tblContacts
PKContactID
FName
LName
etc

Each contact can be tied to 1 or more account manager

tblAcctMan
PKAMID
FName
LName
etc

They are tied through

tblContactAM
PKContactAMID
FKContact
FKAM

Not a big deal because there are only 20 Account Managers, so on the form the user has the contact card, hits a button and gets a pop-up to choose each AM. Typically only going to be 1 or 2 AMs tied to each contact.

The next part is events. An event might be sending out a card, or a party invite etc.

that has

tblEvent
PKEventID
FKEventType
EventTitle
EventDate
etc

The question I have is with the invites.

tblEventInvite
PKEventInviteID
FKEvent
FKContact
dtDateSent
dtRSVPReceived
intPlusGuest
etc

So I have a form for events and now I am starting to develop the subform section for invites. Not sure how to do this.

I really want the user to be able to choose (checkbox?) all the contacts that are being invited and then maybe set a blanket invite date for all of them.

How would I do this?

I have other questions, but I will try to start with this one, and then tackle the others.

(each invite is on behalf of 1 or more AM and I need to be able to set that too - in another child table.)

Thanks for any help!!!!



misscrf

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

 

[dhookom]

I would use a multi-select list box to select contacts. This would be on a form that had a combo box to select a event.

Perhaps you could set the Row Source of the Contact list box to exclude contacts that have already been invited to the event selected in the combo box. The Contact list box might also be limited to one or more AMs from another list or combo box.

When a user makes all of the selections, you would need to build an append query in code to create the tblEventInvite records.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thanks for the response, Duane.

what if they want to take people off the list? I guess I am not visualizing how you suggest I set this up.

I have an events form, to add an event. My next step was a subform to show people who have been invited (and to add them).

I am not sure if you are saying I should do this different, or what.
Am I thinking about this wrong?


misscrf

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

 

[dhookom]

Do you understand how to use a multi-select list box to build a where clause that can be used in a SQL statement?

Do you understand how to set the row source of the list box to exclude people who have already been invited?

If "no" to either of these questions, you might want to search the web and come back with more questions if you can't find the answers.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

ok, I am thinking of a couple of things.

I have not done the listbox with the where. I am not sure I would want the where, in case the user wanted to de-select people that had been selected. make sense?

Would this listbox be a popup where I check box the ones I want and hit submit or add? Then those add to the subform showing the invited people?

I will start researching this method and see if it looks like it will fit.

I would also need the ability to allow a user to

-Set an invite date for all users
-Set an am that would go for each invite to another table of the am(s) that invited that contact (tblinviteOBO - on behalf of)
-Ability to select or de-select all contacts to be invited for an event

If all of this is possible with this method, I will run with it and investigate.

Thank you.



misscrf

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

 

[misscrf]

Thank you for the advice, Duane. I researched the listbox stuff and stumbled upon a great example of what I needed. This is the solution I came up with:

I have a form for events. Below that a subform of event invites. Because this application has hundreds of contacts a user will not want to enter in or look up each invite. Rather, I have a command button on the main form (just above the subform) to "Add Invites".

This will take us to a pop-up form with a multi-select listbox to choose the contacts. Then under that, I have controls for the date the invite is sent, the date rsvp is received, the rsvp (response), any # of additional guests and any notes. These would be set for all contacts being chosen in the above listbox.

Most of those fields may not be filled out the same for all of them, but if the user wanted to, they could.

Below all of that in the pop-up form footer is a command button. This is the code for that:

Private Sub cmdAddClose_Click()
  Dim strSQL		As String
  Dim db			As DAO.Database
  Dim rs			As DAO.Recordset
  Dim ctl		   As Control
  Dim varItem	   As Variant

  On Error GoTo ErrorHandler

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

  '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)
	rs!dtInviteSent = Me.dtInviteSent
	rs!dtRSVPReceived = Me.dtRSVPReceived
	rs!intPlusGuest = Me.intPlusGuest
	rs!MemEventInvNotes = Me.MemEventInvNotes
	rs.Update
  Next varItem
	
	[Forms]![frmEvent].[frmSubEventInvite].[Form].Refresh
	
	DoCmd.Close

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

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




End Sub

My next step will be to
1)look up code for a button to select all contacts from the list box (shouldn't be to hard to find).

2) refresh the event invites subform on the events form when the invite contacts pop-up form is closed, so that the new records show (shouldn't be too hard to find).

3) add button for each event invite in the subform to delete that invite (got that, pretty clear).

4) add button for each event invite in the subform to open a pop-up form to add/assign which account manager or managers the invite is being sent on behalf of. (shouldn't be to bad, I got that)

5) I will also want to add to the invite contacts pop-up form, the ability to choose the account manager or managers are inviting all the contacts being bulk added to an event. bNot sure how I will tackle that one.

for #5, I have a table called tbleventinviteobo it has the following fields:

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

Not sure on the pop-up form for adding contacts if I can have a "subform" or another section to choose more things to add to another table. My wheels are spinning and I think I can add another listbox and after I close the first rs, I can open a new one for all the added records, and add the obo's... Not sure if I might run into issues though, as I need to know just which ones were just added. Have to figure out how to isolate those.

hmmm. Not sure if I am making sense, but you got me this far, so thanks!

misscrf

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

 

[dhookom]

List box code from Allen Browne ClearList() and SelectAll() functions

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

That worked. Thanks.

I now have 1,2 and 3 done. Working on 4 while I troubleshoot one unexpected part of 2. I have the subform refreshing when I add the selected invites and hit the command button. I added this:
Forms!frmevent!frmSubEventInvite.Requery

On that subform I also have a text box called txtcurrrec.

On the form current there is this code:

Private Sub Form_Current()
 If Me.NewRecord Then
 Me!txtCurrRec = "New Invite"
 Else
 Me!txtCurrRec = CStr(Me.CurrentRecord) & " of " & _
               CStr(Me.RecordsetClone.RecordCount) & " Event Invites"
End If
End Sub

When I requery the form, the navigation records show the total amount, but this is not refreshing. It is an unbound textbox control made to look like a label in the subform footer. It refreshes on all other actions. but not when I come back from the pop-up form having added new records.

misscrf

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

 

[dhookom]

You may need to run nearly the same code from your other code. Have you tried this?

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

I actually got it to work with this:
Forms!frmEvent!frmSubEventInvite.Form!txtCurrRec.Requery

Thanks. Whew! Ok, so I am moving forward. I now want to tackle my next challenge. Please let me know if I should start a new thread:


My next challenge is #5 - Each contact that is invited may be invited on behalf of (obo) 1 or more account manager.

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 controls for if the am is the primary invitee and a notes box. Then I need to be able to take all the eventinvite id's that were just created from the above code and populate tblEventInvOBO with who (1 or more) that invite was on behalf of.

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 above code 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. Then on the main subform, the user can edit specific ones if they have something different.

Make sense?

I also want to make a note that 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

Thank you.


misscrf

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

 

[dhookom]

Please start a new thread so you get more views.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thanks, I will. Thanks again for your help!

misscrf

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

 

[misscrf]

I ended up getting my code to work for not only adding invites, but also adding sub-record information for the invites. Here is the code, in case it can be of help to someone:

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

Thanks again for your help, Duane.

misscrf

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