RSVP and Event form

[kattzndagz]

Hi everyone

I am creating and event management database (which is nothing like the template Microsoft has)

I have the following tables:

TblClient
PK ClientID
CompanyName
Address
etc

TblContact
PK ContactID
FK ClientID
FK EventID
Invite-Yes/NO
FirstName
LastName
etc

TblEventDetail
PK EventDetailID
FK ContactID
LOOKUP EventType
EventDate
EventTime
NoofPlaces
NoofInvitationsSent
etc

TblEventAttended
PK EventAttendedID
FK ContactID
FK Event ID
RSVP Yes/No
Attended Yes/No

I thought about having an event form with subforms in tab controls for Invitees and Cost. Then i will need to do reports on who hasn't RSVP'd, who didn't attend, what the total cost of the event was etc.

My problem is i have a contact list of over 200 people, how do i get this list into the form and then invite them to the particular event? I can create queries, but that hasn't helped me much either.

I've looked at this for so long and gotten no where fast. Am i going about this all wrong, or am i missing something simple.

thanks in advance for your help

 

[dhookom]

Seems like this would be an append query to TblEventAttended. What table/field is [FK Event ID] related to?

Duane
Hook'D on Access
MS Access MVP

 

[kattzndagz]

Hi Duane

FK EventID is actually what used to be EventdetailID - I removed the "detail" part as it wasn't necessary - I forgot to change it in my post

I have also changed the RSVP cell to a choice of accept or decline instead of yes or no.

The code for the query is below (i couldn't put the code into the correct forum format as it wouldn't work via the posting click buttons)

SELECT TblEvent.EventID, TblEventAttended.EventAttendedID, TblEvent.EventType, TblEventAttended.ContactID, TblEventAttended.RSVP, TblEventAttended.Attended
FROM TblEvent INNER JOIN (TblClientContact INNER JOIN TblEventAttended ON TblClientContact.ContactID = TblEventAttended.ContactID) ON TblEvent.EventID = TblEventAttended.EventID;

I have then created a form based on the query which means I have a main form which is the event and a subform where i can pick the people to invite from my list of contacts.

Once the list of contacts is selected though, I can't see what to do next. I have been working on this for so long, i can't see the woods for the trees.

I would like to have a button on the form which would let me print out a report of all invitees etc, I know that's not hard to do but my mind is blank.

thanks for your help

 

[dhookom]

Did you want to batch add invites or pick one at a time?

Duane
Hook'D on Access
MS Access MVP

 

[kattzndagz]

Im not entirely sure - batching invites sounds better though. It has to be easy to use for the end user as they are not computer literate.

 

[dhookom]

You need to make up your mind what you want for a user interface.

Duane
Hook'D on Access
MS Access MVP

 

[kattzndagz]

If batching means selecting multiple values - then yes, that is what i require

thanks

 

[dhookom]

Let's assume you have a form with a record source of TblEventDetail. You could add a button with code that would run a query like:

Dim strSQL as String
strSQL = "INSERT INTO TblEventAttended (ContactID, EventID, RSVP, Attended) " & _
    "SELECT  ContactID, " & Me.EventID & ", 'DefaultValueForRSVP', 0 " & _
    "FROM TblContact"
DoCmd.RunSQL strSQL

This assumes EventID is numeric, RSVP is text, and Attended is yes/no.

Duane
Hook'D on Access
MS Access MVP