delete records using search form

[mseo]

hi,
I have a form for deleting records but I use unbound controls in the mainform to set the criteria of filtering the records that will be viewed in the subform
I have delete command button in the mainform
I just need when i set the criteria for viewing the details after click Find then click delete, the data in the subform to be deleted
I don't like to use cascade delete in the subforms because the record will filled with (deleted) and in the case the form will not be user friendly
thanks in advance for any help you may provide me with

[NeoPa]

Firstly, let's look at the design of a search form using unbound controls. Example Filtering on a Form should cover that for you.

Next, a command button to delete the record. This can be done on a set of records or the currently selected one alone. A filtered list would most easily be done with a delete query using the same filter. An individual record could also be done with a delete query, filtering on the ID of the record, but equally could be done using :

DoCmd.RunCommand acCmdDeleteRecord

This deletes the currently selected record only.

Last, if you ever get a situation where you have record placeholders for deleted records you should think of issuing a .Requery call for the form it's on. This will reload all records for you.

[mseo]

Firstly, let's look at the design of a search form using unbound controls. Example Filtering on a Form should cover that for you.

Next, a command button to delete the record. This can be done on a set of records or the currently selected one alone. A filtered list would most easily be done with a delete query using the same filter. An individual record could also be done with a delete query, filtering on the ID of the record, but equally could be done using :

DoCmd.RunCommand acCmdDeleteRecord

This deletes the currently selected record only.

Last, if you ever get a situation where you have record placeholders for deleted records you should think of issuing a .Requery call for the form it's on. This will reload all records for you.

hi,
I need to know about how to delete filtered records using sql statement within vba code
thank you very much

[NeoPa]

Assuming then, that you have your subform already filtered, you would need to execute some SQL similar to the following :

Call DoCmd.RunSQL("DELETE FROM [YourTable] WHERE " & _
                  Me.YourSubformControl!Filter)
Call Me.YourSubformControl!Requery

Don't forget the call to .Requery afterwards though. Without this you'll see those messy #Deleted entries.

[mseo]

Assuming then, that you have your subform already filtered, you would need to execute some SQL similar to the following :

Call DoCmd.RunSQL("DELETE FROM [YourTable] WHERE " & _
                  Me.YourSubformControl!Filter)
Call Me.YourSubformControl!Requery

Don't forget the call to .Requery afterwards though. Without this you'll see those messy #Deleted entries.

thank you
ok, I have tbl_production and child table tbl_productiond etail
the filtered records from the child table
when I executed the code you provided me

Private Sub delete_Click()
Call DoCmd.RunSQL("DELETE FROM [tbl_productiondetail] WHERE " & _
                  Me.MultiCriteria_subform!Batch_No)
Call Me.MultiCriteria_subform.Requery
End Sub

that delete all the records in the child table not the filtered records only

[NeoPa]

Your line #3 is quite different from what I suggested. You need to use the .Filter property, as this has already been set, and is already working (presumably), filtering the data exactly as you need it.

From what you've now posted I would guess the code should be :

Private Sub delete_Click()
    Call DoCmd.RunSQL("DELETE FROM [tbl_productiondetail] WHERE " & _
                      Me.MultiCriteria_subform!Filter)
    Call Me.MultiCriteria_subform.Requery
End Sub

As a final consideration, it occurs to me that you may also need to duplicate the effect of linking the main and the subform. That is to say the subform may already have an effective filter of having to match the data in the main form. If that is the case (and it usually is) then this would also need to be handled. What do you have set in the .LinkChildField s property of your subform ([MultiCriteria_s ubform]) and what is/are the control(s) on the main form that this/these relate to?

[mseo]

hi,
I attached a form that has the same problem, so the name of the controls will be different from the previous mentioned in the code
I get error because of

 Me.Production_By_Product__subform!Filter)

Run-time error '2455':
you entered an expression that has an invaild reference to the property form/report

thank you very much

[mseo]

hi,
I really need any solution for this
please give my any suggestions

[NeoPa]

I can't even look at attachments at work.

Generally, we work within the forum to posted information. Clearly, being from Egypt it is likely you will struggle with English, and I understand that, but generally we expect the question expressed clearly for us (in text on the page).

Just to be crystal clear, I'm happy to make special allowances in your case MSEO, but nevertheless I will have to wait until at least I get home to look at your attachment as I don't download from here onto my work PC.

[mseo]

I can't even look at attachments at work.

Generally, we work within the forum to posted information. Clearly, being from Egypt it is likely you will struggle with English, and I understand that, but generally we expect the question expressed clearly for us (in text on the page).

Just to be crystal clear, I'm happy to make special allowances in your case MSEO, but nevertheless I will have to wait until at least I get home to look at your attachment as I don't download from here onto my work PC.

thank you very much for your dedication
when I ask an expert like you, it's not about the english, it's all about the selection of terms that I have to use when I inquire about any dilemma, so I am sorry for the poor explanation of the problem
thank you again and over again

[NeoPa]

I understand, and though I point it out to explain why I cannot help just now, I don't do so as a criticism. My best second language is French, and I would not manage as well in French as you do in English.

I will try to get on later from home to look at this again.

[NeoPa]

There appears to be a lot of complicated stuff in here MSEO, but I'm afraid most of it seems unnecessary to me.

Something like this can be done with a single form. The search facilities in the header and the data in the detail. The footer can be used for Delete or Exit buttons or whatever.

I suggest you look again at the link I posted earlier (post #2) as that covers most of what you need. The SQL is pretty straightforward and similar to that posted in #4.

I suggest you look at getting the fundamental workings sorted before you add hiding things to your code. That just makes testing it more difficult and complicated. It's not a bad idea by any means, but best to add after the basics are working properly, then it doesn't get in the way.

I'm afraid this does mean taking a few steps backwards before going forward, but subforms were never really designed to be used for this. It's just overcomplicatin g a relatively straightforward issue.

[mseo]

There appears to be a lot of complicated stuff in here MSEO, but I'm afraid most of it seems unnecessary to me.

Something like this can be done with a single form. The search facilities in the header and the data in the detail. The footer can be used for Delete or Exit buttons or whatever.

I suggest you look again at the link I posted earlier (post #2) as that covers most of what you need. The SQL is pretty straightforward and similar to that posted in #4.

I suggest you look at getting the fundamental workings sorted before you add hiding things to your code. That just makes testing it more difficult and complicated. It's not a bad idea by any means, but best to add after the basics are working properly, then it doesn't get in the way.

I'm afraid this does mean taking a few steps backwards before going forward, but subforms were never really designed to be used for this. It's just overcomplicatin g a relatively straightforward issue.

thank you very much Neopa for your dedication
I am trying to restructure this sort of Forms
and I will follow your suggestions and comments about that form, because I really appreciate your approaches about this
thank you very much

[NeoPa]

No worries.

I hope it turns out to be simpler for you in the long run. I expect it will :)