Need a set of records in a subform to be complete before closing form

[robsworld78]

Hi, I've made a form what has a subform, the subform is auto populated with records after someone updates a combo box. Next the person needs to fill in an empty "text" field with data for each record in the subform, the number of records always changes.



Sometimes a record can be missed and that messes things up. I can't turn on "requires data" for the field because the records are being auto created.

I've been trying to make code that will go to the first record in the subform and then scroll through the records and if a specific field is empty in that record it will pop a message and exit sub. If it gets through all the records without that field being empty it can close.

I've been playing with loops and recordsets and at one point thought I was close but can't get it.

Any help would be appreciated!

[Rod]

Your biggest problem is to decide what event triggers the examination of the records comprising your subform. I'm not too sure which event I would use; let me ponder awhile.

Otherwise the examination is easy. Your subform has an underlying recordset. You need to loop through this recordset testing the text field in each record and, if empty, take the appropriate action. I would recommend you use a clone of the recordset for this, not the recordset itself. Why? I believe if you use the recordset there could be a lot of activity in the subform.

[June7]

Yes, the real trick is figuring out what event to put code in. Maybe the form Close. Or disable the X close button and use a command button to 'Save/Exit'.

I agree, use RecordsetClone, something like:

Sub SaveExit_Click()
With Me.RecordsetClone
.FindFirst "IsNull(fieldname)"
If Not .NoMatch Then
MsgBox "Missing data for 1 or more records"
Me.Bookmark = .Bookmark
'do not close form
Exit Sub
Else
DoCmd.Close acForm, Me.Name, acSaveNo
End If
End With
End Sub

[Rod]

I was considering the On Exit event of the subform control. It can be cancelled if a problem is discovered. However this assumes that the subform will be given the focus, which is not necessarily true if the user simply browses in the master form and never enters the subform.

There again if user intervention is not required the need to use a cancel-able (horrible English) event is negated and all events become candidates.

[robsworld78]

Ah thank-you so much, I was complicating it to much. Finding where to put the event is easy but making the event usually isn't. I have "post" buttons on forms and no x in corner to close.