New Form

[Jeff]

I'm creating a new form that allows a non-profit to track who attended
a specific fundraising event. I want to be able to do a few things
that I'm unsure of.

1) First, many events are limited in the number of people who may
attend. The form has a field for how many people may attend. I'd like
to be able to include that many dropdown boxes (from the member info
table) to represent that many people. This would keep the number of
invitees limited to the number allowed. Is that possible? If so, how?

2) I want to tie the event to the members who are attending. Do I have
to include the key (MemberID) of each member who attends on the event
table in order to look them up later?

Thanks.

Jeff

 

[tina]

forget forms for a minute. to give you good advice, we need to know how your
tables/relationships are set up. please post that info.

hth

 

[Jeff]

Event Table
--Event Name, Event Date, Place, Event Program, Number of attendees
permitted, Number of Staff to attend

Member Table
--MemberID, MemberName, MemberAddress, etc

Staff Table
-- StaffID, Staff Name, StaffAddress, etc

I have not established any relationships yet, wanting to find out if I
could do this efficiently.

Jeff

 

[Damon Heron]

Events can have multiple members attending and multiple staff.
Members can attend multiple events.
Staff can attend multiple events.

If these statements are true, then you need an additional table, maybe
tblAttendees
that consists of EventID, MemberID, and StaffID
so a particular event, say EventID 3, would have a series of records like

EventID MemberID StaffID
3 1 2
3 2 4
3 6 5

For any event, you could query this table to get the members and staff
attending.

Re: your original question, a bound form for the events could have a Listbox
with all the names of the members set for Multiselect, and you could select
all the members attending that event. Code could compare the members
selected with the Event's "Numberofattendeespermitted" to limit the
selection.
Same goes for staff picks.

If this sounds like something you might use, post back and I will give you
more details.

Damon

 

[Jeff]

Events can have multiple members attending and multiple staff.
Members can attend multiple events.
Staff can attend multiple events.

If these statements are true, then you need an additional table, maybe
tblAttendees
that consists of EventID, MemberID, and StaffID
so a particular event, say EventID 3, would have a series of records like

EventID MemberID StaffID
  3                1            2
  3                2            4
  3                6            5

For any event, you could query this table to get the members and staff
attending.

Re: your original question, a bound form for the events could have a Listbox
with all the names of the members set for Multiselect, and you could select
all the members attending that event.  Code could compare the members
selected with the Event's "Numberofattendeespermitted" to limit the
selection.
Same goes for staff picks.

If this sounds like something you might use, post back and I will give you
more details.

Damon

Yes, that does sound like what I need. ...

 

[Damon Heron]

First, set recordsource for a form to Events table. The form should have a
textbox bound to the Max # of attendees, lets call in txtNum.
Add a listbox control, with the rowsource your member table.
Set listbox to simple multiselect. Add a Command button next to the listbox.
At the top of the form module in VB Window:

Option Compare Database
Option Explicit
Dim myarray() As Integer
_________________________

Code for Command button click:

Private Sub cmdSelect_Click()
Dim ctl As Control
Dim ct As Integer
Dim varItm As Variant
Dim MaxNum As Integer
ReDim myarray(100)'or whatever max number of attendees expected
If (Nz(Me.txtNum, "") = "") Then
Exit Sub ' this tests for empty max number, you could put a msg here
End If
MaxNum = CInt(Me.txtNum)
Set ctl = Me!lstMembers
For Each varItm In ctl.ItemsSelected
If ct < MaxNum Then
myarray(ct) = ctl.ItemData(varItm)
End If
ct = ct + 1
Next varItm

End Sub

Now you have an array filled with the member ids selected for the event.
Next, we need to write them to the Attendees table.
We add another command button to the form, called cmdStore:

Private Sub cmdStore_Click()
On Error GoTo Err_cmdStore
Dim ct As Integer

Dim DB As DAO.Database
Dim rs As DAO.Recordset
Set DB = CurrentDb
Set rs = DB.OpenRecordset("tblAttendees", dbOpenDynaset)
Do
rs.AddNew
rs![EventID] = Me.EventID
rs![memID] = myarray(ct)
rs.Update
ct = ct + 1
Loop Until myarray(ct) = 0

rs.Close

exit_cmdStore:
Set rs = Nothing
Set DB = Nothing
Exit Sub
Err_cmdStore:
MsgBox Err.Description
Resume exit_cmdStore

End Sub

That's it. The staff listbox would be similar. Of course this is barebones,
no catching of user input errors, clicking buttons repeatedly, so you will
need to enable/disable the buttons and listbox as needed. Now that you have
the member's IDs associated with an event, you can use a query with the
attendee table linked to member table to get a list of names for each event.

Good Luck!

Damon

Events can have multiple members attending and multiple staff.
Members can attend multiple events.
Staff can attend multiple events.

If these statements are true, then you need an additional table, maybe
tblAttendees
that consists of EventID, MemberID, and StaffID
so a particular event, say EventID 3, would have a series of records like

EventID MemberID StaffID
3 1 2
3 2 4
3 6 5

For any event, you could query this table to get the members and staff
attending.

Re: your original question, a bound form for the events could have a
Listbox
with all the names of the members set for Multiselect, and you could
select
all the members attending that event. Code could compare the members
selected with the Event's "Numberofattendeespermitted" to limit the
selection.
Same goes for staff picks.

If this sounds like something you might use, post back and I will give you
more details.

Damon

Yes, that does sound like what I need. ...