End If within End If (1 Viewer)

[daveUK]

Hi Guys

I'm trying (unsuccessfully) to create and End If statement within another End If statement. Here's what I've got so far.

Private Sub CmdClose_Click()

If IsNull(Me.TxtCompletion) Or IsNull(Me.TxtCopy) Then
MsgBox "Please complete ALL of the details", vbExclamation
Else
DoCmd.Close
End If

Basically the close cmd button checks to see if various fields have entries, if they do the form will close, if not an error message will be displayed.

What I'd like to do is create something like this:

Private Sub CmdClose_Click()

If IsNull(Me.TxtCompletion) Or IsNull(Me.TxtCopy) Then
MsgBox "Please complete ALL of the details. Do you want to quit without saving?", vbYesNo
If vbYes then
Me.TxtCompletion =Null
Me.TxtCopy = Null
DoCmd.Close
End if
End If

At the moment the form closes regardless of whether the user presses Yes or No.

Any ideas?

Dave

 

[WayneRyan]

Dave,

If you test for "If vbYes", you will always get true ...

Dim Response As Integer
If IsNull(Me.TxtCompletion) Or IsNull(Me.TxtCopy) Then
   MsgBox "Please complete ALL of the details. Do you want to quit without saving?", vbYesNo
   If Response = vbYes then
      Me.TxtCompletion =Null
      Me.TxtCopy = Null 
      DoCmd.Close 
   End if
End If

hth,
Wayne

 

[daveUK]

Thanks for your input Wayne. You pointed me in the right direction and I've managed to get it sorted.

Dave

 

[daveUK]

Spoke too soon

I though I had it sorted, but it's still playing up a bit.

Here's the code I got so far:

If IsNull(Me.TxtCopy) Or IsNull(Me.TxtCost_Code) Then
msg = "Please complete ALL of the details. Do you want to close without saving?"
title = "Reprographics"
result = MsgBox(msg, 52, title)

If result = 6 Then
Me.TxtCopy = Null
Me.TxtCost_Code = Null
DoCmd.Close
Else
Me.TxtStart.SetFocus
End If
End If

This works fine if the user hasn't completed the form and wants to close the form without saving any details. But if they HAVE completed the form and they press the close button, nothing happens

Dave

 

[bradcccs]

You need to close under two circumstances

1 - if the user wants to close even if not complete
2 - if the details are complete

If IsNull(Me.TxtCopy) Or IsNull(Me.TxtCost_Code) Then 
    If MsgBox"Please complete ALL of the details. Do you want to close without saving?", vbYesNo, "Confirmation") = vbYes Then
        DoCmd.Close  [COLOR=green]' Will close under circumstance 1 - User wants to close[/COLOR] 
    End If
Else
    DoCmd.Close  [COLOR=green]' Will close under circumstance 2 - Details complete (ie: Else to isnull if statement) [/color]
End If

I will leave you to clean up fields if required.

HTH

Brad.

 

[daveUK]

Thanks for your help Brad, but it still isn't working

Access doesn't like the second line of your code and highlights it in red. Whenever I press the close button, it highlights this line and produces a message box syaing "syntax error"

 

[daveUK]

I pretty sure I have got it working now.
I just modified the code I had to start with and ended up with;

If IsNull(Me.TxtCopy) Or IsNull(Me.TxtCost_Code) Then
msg = "Please complete ALL of the details. Do you want to close without saving?"
title = "Reprographics"
result = MsgBox(msg, 52, title)

If result = 6 Then
Me.TxtCopy = Null
Me.TxtCost_Code = Null
DoCmd.Close
Else
Me.TxtStart.SetFocus
End If
else
DoCmd.Close
End If

Thanks for the help guys

Dave

 

[Rich]

Why not just use Me.Undo instead of Null, what event are you using the code in and why no Cancel=True?

 

[daveUK]

Rich, the code in on the click event of the close command button.

I know the code probably isn't the best way of doing the job, but it does work.
There is no cancel=true because not all of the fields need to be completed.

If you've got any suggestions on how to improve the code efficiency, let me know.

Dave

 

[bradcccs]

Sorry Dave,

You are correct, there is an error in my second line.

Should read:

If MsgBox[COLOR=red]([/COLOR]"Please complete ALL of the details. Do you want to close without saving?", vbYesNo, "Confirmation") = vbYes Then

Note addition of "(" to syntax.

Thus overall code should read:

If IsNull(Me.TxtCopy) Or IsNull(Me.TxtCost_Code) Then 
    If MsgBox("Please complete ALL of the details. Do you want to close without saving?", vbYesNo, "Confirmation") = vbYes Then
        DoCmd.Close  ' Will close under circumstance 1 - User wants to close 
    End If
Else
    DoCmd.Close  ' Will close under circumstance 2 - Details complete (ie: Else to isnull if statement) 
End If

HTH

Brad.