Close form only if ALL fields are blank (1 Viewer)

ChrisMore

Member
Local time
Today, 14:46
Joined
Jan 28, 2020
Messages
174
Hi all,

I am using the below code to check if fields are blank before closing the form. However it's not completely what I am trying to do as it will still close the form even if only one of the fields is blank. What I want it to do is to only close the form if ALL the fields are blank. How can I change it to make this happen?

Code:
Private Sub Form_Unload(Cancel As Integer)
Dim ctr As Control
For Each ctr In Me.Controls
If ctr.Tag = "EmptyCheck" Then
Cancel = False
End If
Next ctr
End Sub

Thanks,
Chris
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:46
Joined
Sep 21, 2011
Messages
14,053
All that code does is check a control has a certain tag value, nothing else.?

You need to check the value of the control as well?
 

bob fitz

AWF VIP
Local time
Today, 14:46
Joined
May 23, 2011
Messages
4,717
Perhaps:
Code:
Private Sub Form_Unload(Cancel As Integer)
  Dim ctr As Control
  For Each ctr In Me.Controls
    If ctr.ControlType = acTextbox Then
      If ctr.Tag = "EmptyCheck" Then
          if Not Nz(ctr,0)=0 then
              Cancel = True
          End If
      End If
    End If
  Next ctr
End Sub
 

ChrisMore

Member
Local time
Today, 14:46
Joined
Jan 28, 2020
Messages
174
I've actually thought of another way around my problem without needing this code.

Thanks for the suggestion anyway, Bob.
 

ChrisMore

Member
Local time
Today, 14:46
Joined
Jan 28, 2020
Messages
174
Care to share it with us?
It was a simple fix in the end but took me a while to think of it!

Basically I just wanted the form to close only if it was blank which meant the ID field would have to be blank so I used this code:

Code:
If IsNull(Me.Ordered_Item_ID) Then
Cancel = False
Exit Sub
End If
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:46
Joined
May 7, 2009
Messages
19,175
hmm, what if the Order_Item_ID is not blank (current record) and you did not change any field.
will the form be able to close?
 

ChrisMore

Member
Local time
Today, 14:46
Joined
Jan 28, 2020
Messages
174
hmm, what if the Order_Item_ID is not blank (current record) and you did not change any field.
will the form be able to close?
I've tested with different scenarios and it seems to be working fine. I do have a continuation of the code which uses message boxes if vital fields are blank so the user can't close the form until these fields are entered. However, this new code is used when the user opens the form (possibly accidentally) and doesn't want to enter anything so they can still close the form even though the vital fields are empty.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:46
Joined
Feb 19, 2002
Messages
42,984
You will still be saving bad records.

What would be better is to put positive validation into the Form's BeforeUpdate event. That way you can stop any incomplete or invalid record from being saved by setting cancel = True. Logic in the unload is too late. It is akin to closing the barn door after the horses have escaped.
 

ChrisMore

Member
Local time
Today, 14:46
Joined
Jan 28, 2020
Messages
174
You will still be saving bad records.

What would be better is to put positive validation into the Form's BeforeUpdate event. That way you can stop any incomplete or invalid record from being saved by setting cancel = True. Logic in the unload is too late. It is akin to closing the barn door after the horses have escaped.

I understand what you are saying that it is too late to use logic in the unload event so I'm a bit confused now because what I have done in the unload event seems to work. The form closes if a new record hasn't been created (i.e the autonumbered ID field is blank) and if an incomplete record has been created the form won't close until the vital fields have been completed.

Can you explain how it will be possible to save a bad record? Due to my lack of database experience I may not be testing enough scenarios to ensure it is working 100% correctly.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:46
Joined
Feb 28, 2001
Messages
27,003
It is possible to save a bad record if Access perceives that the form is "dirty" at any time that the form is about to close. Access automatically (and silently, if there is no error) saves the dirty record as part of the CLOSE event. The UNLOAD event follows the close event. To stop this, you need to put a "whoops catcher" in the Form_BeforeUpdate event, which can be cancelled. If you cancel the update, you don't save the form. At that point, you can reset the form to be TRULY blank, perhaps by issuing a form-level UnDo (as a DoCommand option). THEN if you need to exit the form, there is nothing to save and that, Access will handle benignly.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:46
Joined
Feb 19, 2002
Messages
42,984
Since Access generates the autonumber as soon as the user types a single character in ANY field, the presence of an autonumber is an indication that a record has been dirtied . However, if you were to be using ODBC tables such as SQL Server, the autonumber isn't generated until you (or Access) actually send the record to the server to be saved and the server returns to Access the generated autonumber.

I'm not sure why it even appears that your code is working since the record gets saved prior to when the Unload event runs. If the record was dirty, it is already saved.
 

Users who are viewing this thread

Top Bottom