Auto Register Contacts by contact type

[Doctor]

Take a look at this loaded question.

I have frmEventRegistration which is based on tblevents. I would like to
create two buttons on the form. One would automatically register all contacts
from tblLeaders where LPosition is 2. The other button would automatically
register all contacts from tblLeaders where LActive is -1.

Here is a breakdown of exactly what I am working with
tblLeaders (LeaderID, LFirstName, LActive, LPosition, etc.)
tblEvents (EventId, EventDate, EventName, etc.)
tblEventRegistration (ERegistrationID, EventID, LeaderID, etc.)

frmEvents (based on tblEvents)

sfrmEventRegistration (based on tblEvent Registration) This subform is used
in frmEvents as a continuous form.

Again, based on this information, how would I auto register a group based on
their position?

Any help would be greatly appreciated.

Thanks,

 

[Rafi]

How about using an append query.. You will have to modify the insert
statement to fit your exact logic. In particular you will have to modify the
Select portion to tie together records from tblLeaders and tblEvents

Dim stSQL as String
dim db as DAO.Database
stSQL = "Insert Into tblEventRegistration (ERegistrationID, EventID,
LeaderID)" _
& " Select EventID, LeaderID" _
& " From tblEvents" _
& " Where LPosition = 2"
currentdb().execute (stSQL)

 

[Doctor]

Thanks for the reply. Would the button for this code need to be in the main
form or in the subform?

 

[Doctor]

Great, Thanks. Okay, your idea works, except I have these problems:

If I use the code (with my changes) as you suggested (pasted below), I get
this error: Run-time error 3346: number of query values and desitination
fields are not the same.

Code:
Dim db As DAO.Database
stSQL = "INSERT INTO tblEventAttendance ( EventAttendID, 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)

If I take out [EventAttendID], I don't get any errors; however the code adds
all the right leaders to the wrong event. For instance, I click the button on
event number 2 and all of the right leaders get registered for event number
1. I pasted this code below.

Code:
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)



Any help? Thanks.