Re: Automatic Registration Button Problems
- From: "Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx>
- Date: Thu, 20 Mar 2008 06:58:21 -0400
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.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Doctor" <Doctor@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:341952C8-BF1A-46C3-8D6C-E24D9D804132@xxxxxxxxxxxxxxxx
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" wrote:
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.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Doctor" <Doctor@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:09868929-4FCC-49DB-95F6-E45E966E3EE4@xxxxxxxxxxxxxxxx
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)
.
- References:
- Re: Automatic Registration Button Problems
- From: Douglas J. Steele
- Re: Automatic Registration Button Problems
- From: Doctor
- Re: Automatic Registration Button Problems
- Prev by Date: Re: another combo box poulation challenge
- Next by Date: how do I customize my menubar using a2007?
- Previous by thread: Re: Automatic Registration Button Problems
- Next by thread: Expression Builder on a Form
- Index(es):
Relevant Pages
|
