deleting record in subform

[joeblow2]

Hi:
I have the following situation:
I have a form called Events(based on an Events table) and a subform called Participant_line(based on a Participant_line table).

The Events form has a field Event_id while the Participant_line form has an Event_id and Participant_id fields.The two forms are linked with the Event_id. The Participant_line form links Participants to Events. (it gets a Participant_id from a Participants table).

Now, when I add an Event and then link it with Participants (ie add) it works fine. However, when I try to delete a participant from the link table ie from the Participant_line table, it doesnt do it correctly. Sometimes it deletes a wrong person, and sometimes it says that it is deleting a row, when it actually doesn't (or at least I cannot find out what it deletes).

Under a button on the subform I have the following SQL statement:

delete from Participant_line where Event_id=Form!Event_id and Participant_id = Form!Participant_id

The way I choose the participant to delete is that first I select an event in the Events form and then I choose the participant from a combo-box (on the Participant_line form) whose datasouce is participant_id.

Can anyone help me.
Thanks in advance.

 

[RuralGuy]

You are not referencing your controls properly. Have a look at this link.

 

[joeblow2]

RuralGuy thanks for your reply. I corrected the problem you mentioned and that is not the problem any more. However, I am still not able to delete a record from the Participant_line table.
I tried editing the relationship. I tried all sorts of things such as "Enforce referential integrity", or not, "Castcade delete" or not, the three types of Joints etc. Nothing works. It comes back saying "0 records will be deleted" all the time.

Any suggestions?
Thanks.

 

[RuralGuy]

Are you using a query and is it updateable? e.g. Can you open the query directly and change somethin in a field? If it just beeps, the query is not updateable and this link might help.

 

[joeblow2]

I will try what you are suggesting and let you know. I don't have access to my program which is at work and now I am home.
But my Participant_line subform is based on a table not a query.
Thanks.

 

[JANR]

delete from Participant_line where Event_id=Form!Event_id and Participant_id = Form!Participant_id

Also this sql looks odd. If your intention is to delete the hole record in your table, then try:

DELETE Participant_line.* FROM Participant_line WHERE ....

JR

 

[RuralGuy]

Go ahead and post the corrected SQL line again. It would also help us to know on which form resides the Event_id and Participant_id to which you want to match. We know the button is on the SubForm.

 

[joeblow2]

Thanks JANR and RuralGuy.
I will try the new delete stmt. Event_id is on the Main form and Participant_id is on the subform.
Thanks.

 

[joeblow2]

Hi:
I got the records to delete. I used the command button wizard and used whatever code it created. Also, I had to put the navigation buttons back as I had removed them. So the user navigates to the record to be deleted and then the button is clicked.

How do I mark this as my post has been dealt with, ie my post has been answered?

 

[RuralGuy]

How do I mark this as my post has been dealt with, ie my post has been answered?

This forum does not have a means to do that yet but thanks for post back with your success.