MsgBox on close if field value equals Unallocated (1 Viewer)

[Tor_Fey]

Good Morning All;

Is it possible to have a YES/NO msgbox display if a field on a form is equal to a certain value?

On my form; I have a close button with the following code that works fine:

DoCmd.Close acForm, "frm_TF_Ben_Main"
           Forms!frm_tf_menu!mycaseis.Requery

What I would like to do is.. when the close button is clicked; is to show a message box asking do you want to change the status of the record, if the answer is yes; I would like the form to remain open, but if the answer is no then I would like to close the form and run the code above.

I have tried this code so far (5 is equal to Unallocated in my table; as I save the lookup number rather than text in main table):

If Me.TF_Ben_Status = 5 Then
        If MsgBox("This record has a status of 'UNALLOCATED', you need to change the status to make this record live! Would you like to change the record status now?", vbQuestion + vbYesNo, "RECORD UNALLOCATED") = vbYes Then
        Cancel = True
    Else
        DoCmd.Close acForm, "frm_TF_Ben_Main"
        Forms!frm_tf_menu!mycaseis.Requery
    End If

Unfortunately this errors, would one of you kind people give me a point in the right direction please?

Kind Regards
Tor Fey

 

[bob fitz]

Put validation code in the forms UnLoad event which can be canceled with the code:

Cancel = True

if the validation fails.

 

[Minty]

What is the error?
There is no Cancel method on form close - so you can't do an Cancel = True remove that bit you don't need it.

 

[Tor_Fey]

Hi Minty;

With or Without the code:

 Cancel =True

the code errors as follows: 'Compile Error: Block if without End If'

Kind Regards
Tor Fey

What is the error?
There is no Cancel method on form close - so you can't do an Cancel = True remove that bit you don't need it.

 

[Minty]

You have two if statement and you're not closing one of them. You actually can simplify it to

        If Me.TF_Ben_Status = 5 Then
		If MsgBox("This record has a status of 'UNALLOCATED', you need to change the status to make this record live! Would you like to change the record status now?", vbQuestion + vbYesNo, "RECORD UNALLOCATED") = vbYes Then
		    Exit Sub   [COLOR="Green"]  ' Because you don't want to close the form[/COLOR]
		End If
 	End If
        DoCmd.Close acForm, "frm_TF_Ben_Main"
        Forms!frm_tf_menu!mycaseis.Requery

 

[Tor_Fey]

Hi Minty;

That's excellent, the issue has been resolved; thanks so much for your help

Kind Regards
Tor Fey

You have two if statement and you're not closing one of them. You actually can simplify it to

        If Me.TF_Ben_Status = 5 Then
        If MsgBox("This record has a status of 'UNALLOCATED', you need to change the status to make this record live! Would you like to change the record status now?", vbQuestion + vbYesNo, "RECORD UNALLOCATED") = vbYes Then
            Exit Sub   [COLOR=green] ' Because you don't want to close the form[/COLOR]
        End If
     End If
        DoCmd.Close acForm, "frm_TF_Ben_Main"
        Forms!frm_tf_menu!mycaseis.Requery