Dont let form close if date not filled in

[megatronixs]

Hi,

I have a problem when I close a form to stop it from closing if a date is not filled in.
If the field "Case_Status" is filled in with "response received" and the date field "response_received_date" is blank, it shows a message and fils in the text box with red background.
It simply fails to keep the form from closing till the date is filled in. I have no clue where I got wrong at. Please see the code I have so far:

Private Sub CloseForm_Click()
 
    If Me.Case_Status = "response received" And IsNull(Me.response_received__date_) Then
    Me.response_received__date.BackColor = RGB(255, 0, 0)
    MsgBox ("Please fill in manatory fields!!!")
    DoCmd.CancelEvent
    Else
    DoCmd.Close
    End If
End Sub

Greetings.

 

[stumac]

By the time the form close event fires you are too late to stop it from closing. Try putting your code into the unload event....

Private Sub Form_Unload(Cancel As Integer)
    If Me.Case_Status = "response received" And IsNull(Me.response_received__date_) Then
        Me.response_received__date.BackColor = RGB(255, 0, 0)
        MsgBox ("Please fill in manatory fields!!!")
        DoCmd.CancelEvent
    End If
End Sub

 

[pr2-eugin]

When you say it fails to keep the form from closing makes very little sense, as the code should work normal. Is there any other place where you are closing the form? Or any more code that you might have missed? Other option is to make sure that the On Close event is defined.

Option Compare Database
Option Explicit

Dim allowClose As Boolean

Private Sub CloseForm_Click()
    If Me.Case_Status = "response received" And IsNull(Me.response_received__date_) Then
        allowClose = True
    Else
        allowClose = False
    End If
    
    DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Close(Cancel As Integer)
    If allowClose Then
        Me.response_received__date.BackColor = RGB(255, 0, 0)
        MsgBox ("Please fill in mandatory fields!!!")
        Me.response_received__date.SetFocus
        Cancel = True
    End If
End Sub

 

[megatronixs]

Hi all,

Thanks for the above code. At the moment I have a problem with the access that it does not open at all and it needs to be installed all again, so no access to access.
I will try later on when it works and get back to you if I can make it do the above.

Greetings.

 

[RainLover]

Why are you wanting to do this. There could be a better approach.

Your current method will not trap every possible situation where the Form closes.

 

[megatronixs]

Hi RainLover,
As I'm not that good yet at databases creation, I get things like this :-(
I'm taking over a database created by some one else and need to create new improvements, so I will come with more questions as I need to learn on the fly :-(

I finally made it work with the code provided by Stumac.

Big thanks to all of you

Greetings.

 

[RainLover]

It is not really fixed.

It is only fixed as far as the point of closing the way you want. There are however other ways to close.

The X in the top right hand corner. Alt F4 or Ctrl F4 will create some problems. Killing the power etc.

But as long as you are happy then fine.

 

[Solo712]

Hi,

I have a problem when I close a form to stop it from closing if a date is not filled in.
If the field "Case_Status" is filled in with "response received" and the date field "response_received_date" is blank, it shows a message and fils in the text box with red background.
It simply fails to keep the form from closing till the date is filled in. I have no clue where I got wrong at. Please see the code I have so far:

Private Sub CloseForm_Click()
 
    If Me.Case_Status = "response received" And IsNull(Me.response_received__date_) Then
    Me.response_received__date.BackColor = RGB(255, 0, 0)
    MsgBox ("Please fill in manatory fields!!!")
    DoCmd.CancelEvent
    Else
    DoCmd.Close
    End If
End Sub

Greetings.

Are you sure you want to tie data checking of individual fields of a record to closing of the form ? This is highly irregular. Normally, all input verification is done in the Before_Update event of the form. You close the form when you are done with all the records you want to touch, not as a way to save a single insert or update.

Best,
Jiri

 

[stumac]

The X in the top right hand corner. Alt F4 or Ctrl F4 will create some problems.

The unload event should fire if the user try's to close the form using any of those methods.

You close the form when you are done with all the records you want to touch, not as a way to save a single insert or update.

I often use a pop up form to update a single record. i.e. if there isn't enough room to show all fields on a sub-form.

 

[megatronixs]

Hi all,

I ended using the below code. It is working great (thanks to your comments I started to look for different solution )
The people working on the database also have roles like: Analyst, step 1 checker and Admin. If the below field is empty on closing the form, it will pop up a message and you cant close the form if you did not fill it in. that is good, but then if the analyst does not have permisios to fill those fields (can't do quality check for himself), then it should check is role, and based on that it should let him close or not.

On the below code, the analyst should be able to close the form without filling in the fields, but the "Step 1 Checker" not.

Any chance the below code could be adjusted easy to take in consideration the role?

Private Sub Form_Unload(Cancel As Integer)

Dim sMessage As String
sMessage = ""

'XXXXXXXXXXXXXXXXXXXXX     to change if person is not allowed to fill in
If Not IsNull(Me.Checked__date_) And IsNull(Me.cmbCurrentStatus) Then
    sMessage = sMessage & "ERROR on STEP 1 tab: Current Status can't be empty if Checked (date) is filled in!" & vbNewLine & vbNewLine
Else
End If
If sMessage = "" Then
    Exit Sub
Else
    MsgBox sMessage
    DoCmd.CancelEvent
End If
End Sub

Greeting.

 

[gemma-the-husky]

I am surprised. I thought that stopping the unload event would not help with this problem, as this happens AFTER the record tries to save, and the save has already failed by that point. (assuming that date is "required", and that there is no default - otherwise you will get the default date)
Has the unload event behaviour changed in A2010/13?

If you can't allow a null date, or use a default, then the real only alternative is to remove the close button, and add your own. Then you can test the date value entered as you want.

I do not like removing the close button, as it is non-standard, but very occasionally there is no alternative.

 

[Acropolis]

When the user opens/logs in to the system, you could assign a variable with an access level for them. Then when your code fires use a select case on the access level and do what you want from there onwards.

 

[megatronixs]

Hi all,

We are moving our database to Oracle, so the abave became not needed anymore under Access database

Greetings.