How do I delete a record on a subform?

[Seth Schrock]

I need to know how to use a For Each/Next loop to delete all records on a subform that have the same values in a field. The subform is a form based on a linking table with both fields combined as the primary key and the field that hold the criteria is the same as the primary key of the main form (I hope that makes sense). I have tried making the field null using the Form!MainForm!SubForm.Form!Control.Value = null for both fields, but it just deletes the first record and then when I leave it, it says I can't leave the primary key field blank. So I think that I have to delete the record.

The fields in the subform are KeyFobSN and BranchNumber. The subform is linked to the main form by the KeyFobSN. Each KeyFob will have many records as each has access to many branches.

[NeoPa]

Deleting matching records would not typically done via the form interface at all, nor would it be done using a VBA For/Next structure. The way to accomplish this most straightforwardly would be to create and use a SQL DELETE query.

I guess from the question it would help to point out that records are never stored on or in forms. They are merely shown via the form object as if using a window.

Perhaps with that in mind you could restate the question in a way that makes more sense logically (if you still need to with your new understanding).

[Seth Schrock]

I'll look into the sql delete query.

I am aware that the records are stored in the tables and not in the forms. I just said it that way because that is the way that I see the information.

[NeoPa]

Seth Schrok:
I am aware that the records are stored in the tables and not in the forms. I just said it that way because that is the way that I see the information.

I expect a little thought will tell you to avoid that approach in future then.

It's hard to phrase a question that makes sense within a concept that doesn't, and we generally prefer dealing with questions that make sense.

[Seth Schrock]

The delete query works perfectly. Thank you so much.

[Seth Schrock]

I was having trouble wording my question without making it be a 10 page paper. I will keep that in mind next time I have a question. Thanks for the tip.

[NeoPa]

I do appreciate that Seth :-) Phrasing a question so that it's clear and understandable is actually much harder than it looks. Especially within a technical area such as this.

It may be encouraging to know though, that many people report that having taken the trouble to think it through themselves (in order to express it clearly for the thread), they often don't need to ask it any more. Not universally of course, but it shows it can help the questioner too, as well as those willing to help.

Anyway, I'm very glad the tip of using SQL for this has helped. Good luck with your project.