Assign an Event to a Entire Group

[twilliamson2]

I am using MS Access 2003.

I have a Event database that I created. I would like to create an option that allows me to assign an event to a group of people with the same "Role" so that I do not have to spend time on a lot of data entry.

At this time, I have a number of tables for example:

Participant Table:
Last Name
First Name
Role

Role Table:
RoleID
RoleName

Event Table:
EventID
EventName
EventDate
EventTime

Please Help!

 

[maxhugen]

Hi, I'd suggest you add a Primary Key to the participant table, eg participantID. Then create a table that will link participants to events:

table eventParticipant
eventID
participantID

If you don't already have one, create a table that links roles to participants:

table participantRole
participantID
roleID

Once you've linked these, and assigned roles to participants, use a form to select a role to link to events.

Use a combo or list to allow user to select role on a form that shows the event. Then, a button "cmdAddParticipants" could trigger code such as:

Private Sub cmdAddParticipants_Click
Dim db as DAO.database
Dim strSQL$

    strSQL = "INSERT INTO eventParticipant( eventID, participantID) " & _
             "SELECT " & Me!eventID & ", participantID " & _
             "FROM participantRole " & _
             "WHERE roleID = " & Me!cboRole
    set db=CurrentDb
    db.execute strSQL 
    if db.RecordsAffected =0 then MsgBox "No records appended!"
    set db=nothing

end sub

HTH

Max Hugen
Australia

 

[twilliamson2]

Thanks maxhugen! I'll try your suggestions.