Automatic Registration Button Problems

[Doctor]

I am using the code below for a button in a form. It searches the Leaders
table to find all leaders who are directors. Then registers all of these
leaders for whichever event the button is in. However, when I press the
button, it does something that I don't understand. Right now I have two
events in my test database that I could register for. Both of them have about
12 people registered for them. I typed each of these registrants in. When I
create a third event to test my new nifty button, it seems like it takes the
first two events and doubles their registrations and register no one for the
third event??? For instance, in my third new event, I pressed the button,
then there were 24 registerd guests for events one and two. Then, if I
pressed it again, there were 48 registered guests in the first and second
event.

It doesn't make sense to me! What am I doing wrong. Any help would be
greatly appriciated.

Thanks,
Doc



Dim stSQL As String
Dim db As DAO.Database
stSQL = "INSERT INTO tblEventAttendance ( EventID, LeaderID )" _
& " SELECT tblEvent.EventID, tblLeaders.LeaderID" _
& " FROM (tblEvent INNER JOIN tblEventAttendance ON tblEvent.EventID =
tblEventAttendance.EventID) INNER JOIN tblLeaders ON
tblEventAttendance.LeaderID = tblLeaders.LeaderID" _
& " WHERE (((tblLeaders.LPosition1)=2))"
CurrentDb().Execute (stSQL)

 

[Douglas J. Steele]

You're joining the three tables together, so it has one row for every person
registered for every event. Your WHERE clause is saying "Give me all the
rows for every event that anyone who's LPosition1 value is 2 is currently
registered for", and then you're adding those back into the table again. The
fact that it's letting you implies that you haven't set the combination of
EventID and LeaderID to be a unique index on table tblEventAttendance, but
more to the point, all your SQL is capable of doing is repeating rows that
are already in the table.

 

[Doctor]

Then how do I make it get rows from tblLeaders where LPosition is 2 and place
them into tblEventAttendance while setting the EventId in tblEventAttendance
to the EventID of the record that is open in the Event form? What am I
missing.

Also, I tried to make the combination of the two fields in
tblEventAttendance a unique index, but when I tried, Access said that it
couldn't save the doc due to errors. I changed the unique back to No and then
was able to save it. What gives?

Doc

 

[Douglas J. Steele]

Since you presumably already know the EventID (you say it's open on the
form), try the following SQL:

stSQL = "INSERT INTO tblEventAttendance (EventID, LeaderID) " _
& "SELECT " & Me.EventId & ", tblLeaders.LeaderID " _
& "FROM tblLeaders " _
& "WHERE tblLeaders.LPosition1=2"

I suspect that the reason you couldn't set the index as unique is because
you already had duplicate rows in the table.