Not getting the correct record from query

G

Guest

I have been struggling with this for sometimes. I have main table (tblPerson)
and Event table (tblEvents) where I want to store events, FY and check box to
"Invite"

tblPerson (personID, Name)
tblEvent (InviteesID, personID, FY, EventCode, Invite)

each person can be invited to a many events so, I have id field (InviteesId)
in tblEvent.

SELECT tblInviteesList.InviteesID,
tblInviteesList.personID,tblProtocol.LastName, tblProtocol.FirstName,
tblInviteesList.FY, tblInviteesList.EventCode, tblInviteesList.Invite
FROM tblInviteesList RIGHT JOIN tblProtocol ON tblInviteesList.personID =
tblPerson.personID
ORDER BY tblperson.LastName, tblperson.FirstName;

I've created the continuous form from above SQL where I want to list all the
records from the "tblPerson" and user selects the Event from previous form
and check box to "Invite" and save the data. The problem is the sql above
always brings entire list of previous saved events. I know my SQL is not
correct and i've tried other ways but with no success.

scenario:
if it's new event, list everyone from tblPerson with unchecked "Invite"
If created event, list everyone from tblPerson and list "EventCode", checked
"Invite" so, user can modify the invitees list

Thank you in advance
 
S

strive4peace

change fieldname InviteesID in tblEvent to EventID so that
EventID is the PrimaryKey of Events and makes better sense....

also, delete personID from tblEvents -- this should JUST be
a table of events... not events as they are assigned to
people...

make a table:

*tblPersonEvents*
PerEvID, atuonumber
personID, long integer
EventID, long integer

In tblPersonEvents, make a Unique index on the combination of:
personID and EventID
(if you need help making this index, just ask... it MUST
be done in order for the following to work...)

make a relatiosnship:
1. PersonID: from tblPerson to tblPersonEvents
2. EventID: from tblEvents to tblPersonEvents



in this example, I have based the code on the control names
being the same as the controlsource for bound controls

~~~~~~~`

In the main form, the recordset is tblPerson

make sure PersonID is a control on the form and the Name
property is -- PersonID

in the subform, the RecordSource is:

SELECT
Events.*,
nz(PersonEvents.PerEventID,0) as PersonEventID,
iif(Isnull(tblPersonEvents.EventID), false, true) as Invited
FROM tblPerson
RIGHT JOIN (PersonEvents
RIGHT JOIN Events
ON tblPerson.EventID = Events.EventID)
ON tblPerson.P= PersonEvents.PersonID;

put all controls from the fieldlist on the form

make Invited as a checkbox for easier data entry

make sure PersonEventID is on the form...

its VISIBLE property can be No


For Invited

OnClick -->

[Event Procedure]

'~~~~~~~~~~~~~~~
'save parent record if it is changed
if me.parent.dirty then
me.parent.dirty = false
end if

if me.parent.newrecord then
msgbox "You must choose a Person first", _
, "Choose Person"
end if

dim s as string

if me.Invited then
s = "DELETE * FROM PersonEvents " _
" WHERE PerEvID = " & me.PersonEventID & ";"
else
'record will not go in if it is already there
' this is why the unique index is important
s = "INSERT INTO PersonEvents " _
& " ( PersonID, EventID ) " _
& " SELECT " &
& me.parent.PersonID _
& ", " & me.EventID & ";"
end if
currentdb.execute s
currentdb.tabledefs.refresh
me.requery
'~~~~~~~~~~~~~~~

this is kinda a complicated example do do wihtout testing so
please forgive me if there are errors, but hopefully, you
see the logic... if not,,, just ask!

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
G

Guest

strive4peace" <"strive4peace2006 at yaho said:
change fieldname InviteesID in tblEvent to EventID so that
EventID is the PrimaryKey of Events and makes better sense....

also, delete personID from tblEvents -- this should JUST be
a table of events... not events as they are assigned to
people...

make a table:

*tblPersonEvents*
PerEvID, atuonumber
personID, long integer
EventID, long integer

In tblPersonEvents, make a Unique index on the combination of:
personID and EventID
(if you need help making this index, just ask... it MUST
be done in order for the following to work...)

make a relatiosnship:
1. PersonID: from tblPerson to tblPersonEvents
2. EventID: from tblEvents to tblPersonEvents



in this example, I have based the code on the control names
being the same as the controlsource for bound controls

~~~~~~~`

In the main form, the recordset is tblPerson

make sure PersonID is a control on the form and the Name
property is -- PersonID

in the subform, the RecordSource is:

SELECT
Events.*,
nz(PersonEvents.PerEventID,0) as PersonEventID,
iif(Isnull(tblPersonEvents.EventID), false, true) as Invited
FROM tblPerson
RIGHT JOIN (PersonEvents
RIGHT JOIN Events
ON tblPerson.EventID = Events.EventID)
ON tblPerson.P= PersonEvents.PersonID;

put all controls from the fieldlist on the form

make Invited as a checkbox for easier data entry

make sure PersonEventID is on the form...

its VISIBLE property can be No


For Invited

OnClick -->

[Event Procedure]

'~~~~~~~~~~~~~~~
'save parent record if it is changed
if me.parent.dirty then
me.parent.dirty = false
end if

if me.parent.newrecord then
msgbox "You must choose a Person first", _
, "Choose Person"
end if

dim s as string

if me.Invited then
s = "DELETE * FROM PersonEvents " _
" WHERE PerEvID = " & me.PersonEventID & ";"
else
'record will not go in if it is already there
' this is why the unique index is important
s = "INSERT INTO PersonEvents " _
& " ( PersonID, EventID ) " _
& " SELECT " &
& me.parent.PersonID _
& ", " & me.EventID & ";"
end if
currentdb.execute s
currentdb.tabledefs.refresh
me.requery
'~~~~~~~~~~~~~~~

this is kinda a complicated example do do wihtout testing so
please forgive me if there are errors, but hopefully, you
see the logic... if not,,, just ask!

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
I have been struggling with this for sometimes. I have main table (tblPerson)
and Event table (tblEvents) where I want to store events, FY and check box to
"Invite"

tblPerson (personID, Name)
tblEvent (InviteesID, personID, FY, EventCode, Invite)

each person can be invited to a many events so, I have id field (InviteesId)
in tblEvent.

SELECT tblInviteesList.InviteesID,
tblInviteesList.personID,tblProtocol.LastName, tblProtocol.FirstName,
tblInviteesList.FY, tblInviteesList.EventCode, tblInviteesList.Invite
FROM tblInviteesList RIGHT JOIN tblProtocol ON tblInviteesList.personID =
tblPerson.personID
ORDER BY tblperson.LastName, tblperson.FirstName;

I've created the continuous form from above SQL where I want to list all the
records from the "tblPerson" and user selects the Event from previous form
and check box to "Invite" and save the data. The problem is the sql above
always brings entire list of previous saved events. I know my SQL is not
correct and i've tried other ways but with no success.

scenario:
if it's new event, list everyone from tblPerson with unchecked "Invite"
If created event, list everyone from tblPerson and list "EventCode", checked
"Invite" so, user can modify the invitees list

Thank you in advance
Click to expand...
Click to expand...
 
G

Guest

Crystal, again thanks for the response and sorry for the delayed response.
I've managed to work without using the continous form. You've given me a lot
of good suggestions but I do have a couple of more questions.

How do you make a combination unique index (combine 2 field)?
And I have a table "tblInviteesList" (subform) with Invite field as check
box. I only want to save the record when it is checked. If user change
their mind and later uncheck the box don't save but record always save
whether checked or unchecked and below is my code.

Thank you for your detailed explantion.

Private Sub Invite_Click()
Dim s As String

If (Me.Invite = 0) Or IsNull(Me.Invite) Then
s = "Delete tblInviteesList.* FROM tblInviteesList " & _
"Where InviteesID = " & Me.InviteesID & " "
End If

End Sub

strive4peace" <"strive4peace2006 at yaho said:
change fieldname InviteesID in tblEvent to EventID so that
EventID is the PrimaryKey of Events and makes better sense....

also, delete personID from tblEvents -- this should JUST be
a table of events... not events as they are assigned to
people...

make a table:

*tblPersonEvents*
PerEvID, atuonumber
personID, long integer
EventID, long integer

In tblPersonEvents, make a Unique index on the combination of:
personID and EventID
(if you need help making this index, just ask... it MUST
be done in order for the following to work...)

make a relatiosnship:
1. PersonID: from tblPerson to tblPersonEvents
2. EventID: from tblEvents to tblPersonEvents



in this example, I have based the code on the control names
being the same as the controlsource for bound controls

~~~~~~~`

In the main form, the recordset is tblPerson

make sure PersonID is a control on the form and the Name
property is -- PersonID

in the subform, the RecordSource is:

SELECT
Events.*,
nz(PersonEvents.PerEventID,0) as PersonEventID,
iif(Isnull(tblPersonEvents.EventID), false, true) as Invited
FROM tblPerson
RIGHT JOIN (PersonEvents
RIGHT JOIN Events
ON tblPerson.EventID = Events.EventID)
ON tblPerson.P= PersonEvents.PersonID;

put all controls from the fieldlist on the form

make Invited as a checkbox for easier data entry

make sure PersonEventID is on the form...

its VISIBLE property can be No


For Invited

OnClick -->

[Event Procedure]

'~~~~~~~~~~~~~~~
'save parent record if it is changed
if me.parent.dirty then
me.parent.dirty = false
end if

if me.parent.newrecord then
msgbox "You must choose a Person first", _
, "Choose Person"
end if

dim s as string

if me.Invited then
s = "DELETE * FROM PersonEvents " _
" WHERE PerEvID = " & me.PersonEventID & ";"
else
'record will not go in if it is already there
' this is why the unique index is important
s = "INSERT INTO PersonEvents " _
& " ( PersonID, EventID ) " _
& " SELECT " &
& me.parent.PersonID _
& ", " & me.EventID & ";"
end if
currentdb.execute s
currentdb.tabledefs.refresh
me.requery
'~~~~~~~~~~~~~~~

this is kinda a complicated example do do wihtout testing so
please forgive me if there are errors, but hopefully, you
see the logic... if not,,, just ask!

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
I have been struggling with this for sometimes. I have main table (tblPerson)
and Event table (tblEvents) where I want to store events, FY and check box to
"Invite"

tblPerson (personID, Name)
tblEvent (InviteesID, personID, FY, EventCode, Invite)

each person can be invited to a many events so, I have id field (InviteesId)
in tblEvent.

SELECT tblInviteesList.InviteesID,
tblInviteesList.personID,tblProtocol.LastName, tblProtocol.FirstName,
tblInviteesList.FY, tblInviteesList.EventCode, tblInviteesList.Invite
FROM tblInviteesList RIGHT JOIN tblProtocol ON tblInviteesList.personID =
tblPerson.personID
ORDER BY tblperson.LastName, tblperson.FirstName;

I've created the continuous form from above SQL where I want to list all the
records from the "tblPerson" and user selects the Event from previous form
and check box to "Invite" and save the data. The problem is the sql above
always brings entire list of previous saved events. I know my SQL is not
correct and i've tried other ways but with no success.

scenario:
if it's new event, list everyone from tblPerson with unchecked "Invite"
If created event, list everyone from tblPerson and list "EventCode", checked
"Invite" so, user can modify the invitees list

Thank you in advance
Click to expand...
Click to expand...
 
S

strive4peace

you're welcome :)

***

for resolution of the problem allowing a duplicate
combination into the table that must be unique:

make a unique index in the table on a multi-field combination

From the table design, Turn on the Indexes window (from the
menu: View, Indexes)

click on the first field in the table design and set the
Index property to
Yes (duplicates ok)

that will add a line to the Indexes window

In the row just below the index you just made, in the 2nd
column of the indexes window, click in the fieldname column
and choose the 2nd fieldname
In the row below

If you have another field to add, click on the fieldname
column in the next row down, then on the drop-down arrow,
and choose the 3rd fieldname

Now, Click on top row of that Index, the first fieldname
(row) -- set
Unique --> Yes

-------------

to delete a record that is already saved...

the code you have written in your last message is not
complete because you are not executing the SQL ... besides,
if they click the checkbox, they could be turning it off
(deleting a record) or turning it on (adding a record)

firstly, make the default value of me.invited --> false

then use the code I gave you in my first post to this thread
-- it tests the Invited checkbox and either adds or deletes
a record accordingly. After you specify the SQL,

you need to RUN it :)
currentdb.execute s

then refresh the tables
currentdb.tabledefs.refresh
me.requery



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
Crystal, again thanks for the response and sorry for the delayed response.
I've managed to work without using the continous form. You've given me a lot
of good suggestions but I do have a couple of more questions.

How do you make a combination unique index (combine 2 field)?
And I have a table "tblInviteesList" (subform) with Invite field as check
box. I only want to save the record when it is checked. If user change
their mind and later uncheck the box don't save but record always save
whether checked or unchecked and below is my code.

Thank you for your detailed explantion.

Private Sub Invite_Click()
Dim s As String

If (Me.Invite = 0) Or IsNull(Me.Invite) Then
s = "Delete tblInviteesList.* FROM tblInviteesList " & _
"Where InviteesID = " & Me.InviteesID & " "
End If

End Sub

:

change fieldname InviteesID in tblEvent to EventID so that
EventID is the PrimaryKey of Events and makes better sense....

also, delete personID from tblEvents -- this should JUST be
a table of events... not events as they are assigned to
people...

make a table:

*tblPersonEvents*
PerEvID, atuonumber
personID, long integer
EventID, long integer

In tblPersonEvents, make a Unique index on the combination of:
personID and EventID
(if you need help making this index, just ask... it MUST
be done in order for the following to work...)

make a relatiosnship:
1. PersonID: from tblPerson to tblPersonEvents
2. EventID: from tblEvents to tblPersonEvents



in this example, I have based the code on the control names
being the same as the controlsource for bound controls

~~~~~~~`

In the main form, the recordset is tblPerson

make sure PersonID is a control on the form and the Name
property is -- PersonID

in the subform, the RecordSource is:

SELECT
Events.*,
nz(PersonEvents.PerEventID,0) as PersonEventID,
iif(Isnull(tblPersonEvents.EventID), false, true) as Invited
FROM tblPerson
RIGHT JOIN (PersonEvents
RIGHT JOIN Events
ON tblPerson.EventID = Events.EventID)
ON tblPerson.P= PersonEvents.PersonID;

put all controls from the fieldlist on the form

make Invited as a checkbox for easier data entry

make sure PersonEventID is on the form...

its VISIBLE property can be No


For Invited

OnClick -->

[Event Procedure]

'~~~~~~~~~~~~~~~
'save parent record if it is changed
if me.parent.dirty then
me.parent.dirty = false
end if

if me.parent.newrecord then
msgbox "You must choose a Person first", _
, "Choose Person"
end if

dim s as string

if me.Invited then
s = "DELETE * FROM PersonEvents " _
" WHERE PerEvID = " & me.PersonEventID & ";"
else
'record will not go in if it is already there
' this is why the unique index is important
s = "INSERT INTO PersonEvents " _
& " ( PersonID, EventID ) " _
& " SELECT " &
& me.parent.PersonID _
& ", " & me.EventID & ";"
end if
currentdb.execute s
currentdb.tabledefs.refresh
me.requery
'~~~~~~~~~~~~~~~

this is kinda a complicated example do do wihtout testing so
please forgive me if there are errors, but hopefully, you
see the logic... if not,,, just ask!

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
I have been struggling with this for sometimes. I have main table (tblPerson)
and Event table (tblEvents) where I want to store events, FY and check box to
"Invite"

tblPerson (personID, Name)
tblEvent (InviteesID, personID, FY, EventCode, Invite)

each person can be invited to a many events so, I have id field (InviteesId)
in tblEvent.

SELECT tblInviteesList.InviteesID,
tblInviteesList.personID,tblProtocol.LastName, tblProtocol.FirstName,
tblInviteesList.FY, tblInviteesList.EventCode, tblInviteesList.Invite
FROM tblInviteesList RIGHT JOIN tblProtocol ON tblInviteesList.personID =
tblPerson.personID
ORDER BY tblperson.LastName, tblperson.FirstName;

I've created the continuous form from above SQL where I want to list all the
records from the "tblPerson" and user selects the Event from previous form
and check box to "Invite" and save the data. The problem is the sql above
always brings entire list of previous saved events. I know my SQL is not
correct and i've tried other ways but with no success.

scenario:
if it's new event, list everyone from tblPerson with unchecked "Invite"
If created event, list everyone from tblPerson and list "EventCode", checked
"Invite" so, user can modify the invitees list

Thank you in advance
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Crystal,

You're a great help. Thanks again and have a wonderful day!

strive4peace" <"strive4peace2006 at yaho said:
you're welcome :)

***

for resolution of the problem allowing a duplicate
combination into the table that must be unique:

make a unique index in the table on a multi-field combination

From the table design, Turn on the Indexes window (from the
menu: View, Indexes)

click on the first field in the table design and set the
Index property to
Yes (duplicates ok)

that will add a line to the Indexes window

In the row just below the index you just made, in the 2nd
column of the indexes window, click in the fieldname column
and choose the 2nd fieldname
In the row below

If you have another field to add, click on the fieldname
column in the next row down, then on the drop-down arrow,
and choose the 3rd fieldname

Now, Click on top row of that Index, the first fieldname
(row) -- set
Unique --> Yes

-------------

to delete a record that is already saved...

the code you have written in your last message is not
complete because you are not executing the SQL ... besides,
if they click the checkbox, they could be turning it off
(deleting a record) or turning it on (adding a record)

firstly, make the default value of me.invited --> false

then use the code I gave you in my first post to this thread
-- it tests the Invited checkbox and either adds or deletes
a record accordingly. After you specify the SQL,

you need to RUN it :)
currentdb.execute s

then refresh the tables
currentdb.tabledefs.refresh
me.requery



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
Crystal, again thanks for the response and sorry for the delayed response.
I've managed to work without using the continous form. You've given me a lot
of good suggestions but I do have a couple of more questions.

How do you make a combination unique index (combine 2 field)?
And I have a table "tblInviteesList" (subform) with Invite field as check
box. I only want to save the record when it is checked. If user change
their mind and later uncheck the box don't save but record always save
whether checked or unchecked and below is my code.

Thank you for your detailed explantion.

Private Sub Invite_Click()
Dim s As String

If (Me.Invite = 0) Or IsNull(Me.Invite) Then
s = "Delete tblInviteesList.* FROM tblInviteesList " & _
"Where InviteesID = " & Me.InviteesID & " "
End If

End Sub

:

change fieldname InviteesID in tblEvent to EventID so that
EventID is the PrimaryKey of Events and makes better sense....

also, delete personID from tblEvents -- this should JUST be
a table of events... not events as they are assigned to
people...

make a table:

*tblPersonEvents*
PerEvID, atuonumber
personID, long integer
EventID, long integer

In tblPersonEvents, make a Unique index on the combination of:
personID and EventID
(if you need help making this index, just ask... it MUST
be done in order for the following to work...)

make a relatiosnship:
1. PersonID: from tblPerson to tblPersonEvents
2. EventID: from tblEvents to tblPersonEvents



in this example, I have based the code on the control names
being the same as the controlsource for bound controls

~~~~~~~`

In the main form, the recordset is tblPerson

make sure PersonID is a control on the form and the Name
property is -- PersonID

in the subform, the RecordSource is:

SELECT
Events.*,
nz(PersonEvents.PerEventID,0) as PersonEventID,
iif(Isnull(tblPersonEvents.EventID), false, true) as Invited
FROM tblPerson
RIGHT JOIN (PersonEvents
RIGHT JOIN Events
ON tblPerson.EventID = Events.EventID)
ON tblPerson.P= PersonEvents.PersonID;

put all controls from the fieldlist on the form

make Invited as a checkbox for easier data entry

make sure PersonEventID is on the form...

its VISIBLE property can be No


For Invited

OnClick -->

[Event Procedure]

'~~~~~~~~~~~~~~~
'save parent record if it is changed
if me.parent.dirty then
me.parent.dirty = false
end if

if me.parent.newrecord then
msgbox "You must choose a Person first", _
, "Choose Person"
end if

dim s as string

if me.Invited then
s = "DELETE * FROM PersonEvents " _
" WHERE PerEvID = " & me.PersonEventID & ";"
else
'record will not go in if it is already there
' this is why the unique index is important
s = "INSERT INTO PersonEvents " _
& " ( PersonID, EventID ) " _
& " SELECT " &
& me.parent.PersonID _
& ", " & me.EventID & ";"
end if
currentdb.execute s
currentdb.tabledefs.refresh
me.requery
'~~~~~~~~~~~~~~~

this is kinda a complicated example do do wihtout testing so
please forgive me if there are errors, but hopefully, you
see the logic... if not,,, just ask!

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

skk wrote:

I have been struggling with this for sometimes. I have main table (tblPerson)
and Event table (tblEvents) where I want to store events, FY and check box to
"Invite"

tblPerson (personID, Name)
tblEvent (InviteesID, personID, FY, EventCode, Invite)

each person can be invited to a many events so, I have id field (InviteesId)
in tblEvent.

SELECT tblInviteesList.InviteesID,
tblInviteesList.personID,tblProtocol.LastName, tblProtocol.FirstName,
tblInviteesList.FY, tblInviteesList.EventCode, tblInviteesList.Invite
FROM tblInviteesList RIGHT JOIN tblProtocol ON tblInviteesList.personID =
tblPerson.personID
ORDER BY tblperson.LastName, tblperson.FirstName;

I've created the continuous form from above SQL where I want to list all the
records from the "tblPerson" and user selects the Event from previous form
and check box to "Invite" and save the data. The problem is the sql above
always brings entire list of previous saved events. I know my SQL is not
correct and i've tried other ways but with no success.

scenario:
if it's new event, list everyone from tblPerson with unchecked "Invite"
If created event, list everyone from tblPerson and list "EventCode", checked
"Invite" so, user can modify the invitees list

Thank you in advance
Click to expand...
Click to expand...
Click to expand...
 
S

strive4peace

you're welcome :) happy to help

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
Crystal,

You're a great help. Thanks again and have a wonderful day!

:

you're welcome :)

***

for resolution of the problem allowing a duplicate
combination into the table that must be unique:

make a unique index in the table on a multi-field combination

From the table design, Turn on the Indexes window (from the
menu: View, Indexes)

click on the first field in the table design and set the
Index property to
Yes (duplicates ok)

that will add a line to the Indexes window

In the row just below the index you just made, in the 2nd
column of the indexes window, click in the fieldname column
and choose the 2nd fieldname
In the row below

If you have another field to add, click on the fieldname
column in the next row down, then on the drop-down arrow,
and choose the 3rd fieldname

Now, Click on top row of that Index, the first fieldname
(row) -- set
Unique --> Yes

-------------

to delete a record that is already saved...

the code you have written in your last message is not
complete because you are not executing the SQL ... besides,
if they click the checkbox, they could be turning it off
(deleting a record) or turning it on (adding a record)

firstly, make the default value of me.invited --> false

then use the code I gave you in my first post to this thread
-- it tests the Invited checkbox and either adds or deletes
a record accordingly. After you specify the SQL,

you need to RUN it :)
currentdb.execute s

then refresh the tables
currentdb.tabledefs.refresh
me.requery



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
Crystal, again thanks for the response and sorry for the delayed response.
I've managed to work without using the continous form. You've given me a lot
of good suggestions but I do have a couple of more questions.

How do you make a combination unique index (combine 2 field)?
And I have a table "tblInviteesList" (subform) with Invite field as check
box. I only want to save the record when it is checked. If user change
their mind and later uncheck the box don't save but record always save
whether checked or unchecked and below is my code.

Thank you for your detailed explantion.

Private Sub Invite_Click()
Dim s As String

If (Me.Invite = 0) Or IsNull(Me.Invite) Then
s = "Delete tblInviteesList.* FROM tblInviteesList " & _
"Where InviteesID = " & Me.InviteesID & " "
End If

End Sub

:



change fieldname InviteesID in tblEvent to EventID so that
EventID is the PrimaryKey of Events and makes better sense....

also, delete personID from tblEvents -- this should JUST be
a table of events... not events as they are assigned to
people...

make a table:

*tblPersonEvents*
PerEvID, atuonumber
personID, long integer
EventID, long integer

In tblPersonEvents, make a Unique index on the combination of:
personID and EventID
(if you need help making this index, just ask... it MUST
be done in order for the following to work...)

make a relatiosnship:
1. PersonID: from tblPerson to tblPersonEvents
2. EventID: from tblEvents to tblPersonEvents



in this example, I have based the code on the control names
being the same as the controlsource for bound controls

~~~~~~~`

In the main form, the recordset is tblPerson

make sure PersonID is a control on the form and the Name
property is -- PersonID

in the subform, the RecordSource is:

SELECT
Events.*,
nz(PersonEvents.PerEventID,0) as PersonEventID,
iif(Isnull(tblPersonEvents.EventID), false, true) as Invited

FROM tblPerson

RIGHT JOIN (PersonEvents
RIGHT JOIN Events
ON tblPerson.EventID = Events.EventID)
ON tblPerson.P= PersonEvents.PersonID;

put all controls from the fieldlist on the form

make Invited as a checkbox for easier data entry

make sure PersonEventID is on the form...

its VISIBLE property can be No


For Invited

OnClick -->

[Event Procedure]

'~~~~~~~~~~~~~~~
'save parent record if it is changed
if me.parent.dirty then
me.parent.dirty = false
end if

if me.parent.newrecord then
msgbox "You must choose a Person first", _
, "Choose Person"
end if

dim s as string

if me.Invited then
s = "DELETE * FROM PersonEvents " _
" WHERE PerEvID = " & me.PersonEventID & ";"
else
'record will not go in if it is already there
' this is why the unique index is important
s = "INSERT INTO PersonEvents " _
& " ( PersonID, EventID ) " _
& " SELECT " &
& me.parent.PersonID _
& ", " & me.EventID & ";"
end if
currentdb.execute s
currentdb.tabledefs.refresh
me.requery
'~~~~~~~~~~~~~~~

this is kinda a complicated example do do wihtout testing so
please forgive me if there are errors, but hopefully, you
see the logic... if not,,, just ask!

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

skk wrote:


I have been struggling with this for sometimes. I have main table (tblPerson)
and Event table (tblEvents) where I want to store events, FY and check box to
"Invite"

tblPerson (personID, Name)
tblEvent (InviteesID, personID, FY, EventCode, Invite)

each person can be invited to a many events so, I have id field (InviteesId)
in tblEvent.

SELECT tblInviteesList.InviteesID,
tblInviteesList.personID,tblProtocol.LastName, tblProtocol.FirstName,
tblInviteesList.FY, tblInviteesList.EventCode, tblInviteesList.Invite

FROM tblInviteesList RIGHT JOIN tblProtocol ON tblInviteesList.personID =

tblPerson.personID
ORDER BY tblperson.LastName, tblperson.FirstName;

I've created the continuous form from above SQL where I want to list all the
records from the "tblPerson" and user selects the Event from previous form
and check box to "Invite" and save the data. The problem is the sql above
always brings entire list of previous saved events. I know my SQL is not
correct and i've tried other ways but with no success.

scenario:
if it's new event, list everyone from tblPerson with unchecked "Invite"
If created event, list everyone from tblPerson and list "EventCode", checked
"Invite" so, user can modify the invitees list

Thank you in advance
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is the table design wrong? 3
query for values not in use 2

Top