Delete Subform if checkbox false when mainform closes

[Hulm1]

This one has me stuck

I have a main form "Enquiry_Edit". In the main form are several subforms which become visible based on a TRUE click on a checkbox "VapourExtraction" with the following On Click code.

The following is an example of one subform [EnquiryVacuumType_Edit]

Me![EnquiryVacuumType_Edit].Form.Visible = Me.VapourExtraction

This all works fine. BUT! When I uncheck the "vapourextraction" check box, I want the subform record to be deleted.

For reference, the Primary key and first field of one of the subforms is "VacuumTypeID"
The subform is linked as a child using the fields "EnquiryID" which is in both forms and is the Primary key of "Enquiry_Edit".

Each of the subforms has a variety of text boxes, check boxes, Combo Boxes and Option Groups. They all work fine.

I created a button on the main form and used the following code:

Me.EnquiryVacuumType_Edit.Form.Recordset.Delete
Me.EnquiryVacuumType_Edit.Form.Recordset.MoveNext

It works fine, but of course is not the point. I want the user to be able to check the box, make some entries on the subform and then if he/she changes his mind, they can simply uncheck the box and know that the record will be deleted.

Therefore I would like the above code to be based on an IF function such as:

if me.vapourextraction = false then
Me.EnquiryVacuumType_Edit.Form.Recordset.Delete
Me.EnquiryVacuumType_Edit.Form.Recordset.MoveNext
End if

It doesn't work though!

Because there are so many subforms, I would really like some nice small snippet of code!


Thanks for any help in advance!

 

[PHV]

Something like this ?
With Me!EnquiryVacuumType_Edit.Form.Recordset
While Not .EOF
.Delete
.MoveNext
Wend
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hulm1]

Whoa! that worked. At first not until I realised I had missed off the If = false then bit.

Thank you so much. What a great bit of code

One thing: Can you recommend code for deleting a single field within a form?

Just like above I have a check box which, if true, makes a text box visible (or two or three text boxes)on the same form (not a subform). I would like these cleared if the check box is False.

 

[Hulm1]

PHV
So Sorry, I have encountered a little snag.

One of my subforms EnquiryCompressor_Edit becomes visible when one or all of four check boxes is TRUE.

Problem 1: I have used the following to try and make the check box "compressor" = True if say "TotalFluidsPumps" is TRUE as follows:
If Me.TotalFluidsPumps = True Then Me.Compressor = True
If Me.TotalFluidsPumps = False Then Me.Compressor = False

It does not work

Problem 2: This means that when I close the form, because the check box "compressor" is not showing TRUE, it's records get deleted by the code you gave me earlier!

I therefore deleted the code to delete the recordset for subform EnquiryCompressor_Edit and added a new one as follows


If Me.TotalFluidsPumps = False Then
With Me!Compressor_Edit.Form.Recordset
While Not .EOF
.Delete
.MoveNext
Wend
End With
End If

This of course is as well as:

If Me.TotalFluidsPumps = False Then
With Me!EnquiryPneumaticPump_Edit.Form.Recordset
While Not .EOF
.Delete
.MoveNext
Wend
End With
End If

This works fine. However, if I do it for the other check boxes for which "EnquiryCompressor_Edit" opens and they are not all TRUE the records in subform EnquiryCompressor_Edit get deleted anyway!

Solution: So I either:

a) Have an if/or scenario (I don'd know how to do these, maybe:

IF Check box 1 = False OR Check Box 2 = False OR Check Box 3 = False etc. Could that work?

Or b) find the correct code for problem 1.

Sorry for the very long enquiry!!