How to prevent the form from closing if not ALL of the records have been updated?

[Morris]

HI,

I've found your post how to prevent form from closing by setting the
Cancel variable to True, but I don't really know how can I extend it?


The form that opens is linked to a table with let's say 35 records.

Now based on a SELECT query as a rowsource, it finds 4 records that
need to be updating.

Updating means asking user to populate three text fields.

I want it to error and not close the form if all three text fields in
all four records are not populated and that's where I stop.

Private Sub Form_Unload(Cancel As Integer)
If IsNull(Me.txtField1.Value) Or IsNull(Me.txtField2.Value) Or _
IsNull(Me.txtField3.Value) Then
MsgBox "Not all of the three required fields have been
populated!", vbCritical, "Cannot continue"
Cancel = True
End If

Now, it means that if user populates all three fields but just on one
page (record) the code will allow him to close the form, and I want it
to be able to check those fields for all pages (records).

Any help greatly appreciated,

Morris

 

[Stefan Hoffmann]

hi Morris,

Now, it means that if user populates all three fields but just on one
page (record) the code will allow him to close the form, and I want it
to be able to check those fields for all pages (records).

Private Sub Form_Unload(Cancel As Integer)

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.Requery
rs.MoveLast

Cancel = (rs.RecordCount > 0)

rs.Close
Set rs = Nothing

End If


mfG
--> stefan <--

 

[Morris]

Private Sub Form_Unload(Cancel As Integer)

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.Requery
rs.MoveLast

Cancel = (rs.RecordCount > 0)

rs.Close
Set rs = Nothing

End If

It looks neat, but could you provide me with some comment please? and
is the End If at the end of your code just as a mistake? Or should
there be an If somewhere there as well?

Danke fur dein help

 

[Stefan Hoffmann]

hi Morris,

It looks neat, but could you provide me with some comment please?

Private Sub Form_Unload(Cancel As Integer)

Dim rs As DAO.Recordset

Me.Requery 'get the remaining datasets

Set rs = Me.RecordsetClone 'get a copy of the form recordset
rs.MoveLast 'ensure that .RecordCount is correct

Cancel = (rs.RecordCount > 0) 'dont exit, if any record left

rs.Close
Set rs = Nothing

End Sub


mfG
--> stefan <--