If then, else, VBA

[Swatskeb]

Hi all I'm trying to write some code for a command button to prevent a form from closing unless the combo boxs/text boxes are filled out properly. I have it moslty written but cant figure how to add the code to prevent it from closing.



I already have this written in the before update in event of form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.DDRSTATUS = "ERROR CORRECTED" Or Me.DDRSTATUS = "ERROR NOT CORRECTABLE" Then
   If IsNull(Me.ERROR_TYPE) Then
      MsgBox "Please select an Error Type."
      Me.ERROR_TYPE.SetFocus
      Cancel = True
   End If
End If
If Me.DDRSTATUS = "ERROR CORRECTED" Or Me.DDRSTATUS = "ERROR NOT CORRECTABLE" Then
   If IsNull(Me.ERROR_DESCRIPTION) Then
      MsgBox "Please enter an Error Description."
      Me.ERROR_DESCRIPTION.SetFocus
      Cancel = True
   End If
End If
If Me.DDRSTATUS = "NOT REVIEWED" Or Me.DDRSTATUS = "NO ERROR" Then
   If IsNull(Me.ERROR_TYPE) Then
      Cancel = ture
   Else
      MsgBox "Please select Error Corrected or Error Not Correctable."
      Me.DDRSTATUS.SetFocus
   End If
End If
End Sub

it seems to work ok when going from one record to the other but I feel it could be cleaned up/written better for example could I write and instead of a second if?

If Me.DDRSTATUS = "ERROR CORRECTED" Or Me.DDRSTATUS = "ERROR NOT CORRECTABLE" And IsNull(Me.ERROR_TYPE) Then
If Me.DDRSTATUS = "ERROR CORRECTED" Or Me.DDRSTATUS = "ERROR NOT CORRECTABLE" And IsNull(Me.ERROR_DESCRIPTION) Then
If Me.DDRSTATUS = "NOT REVIEWED" Or Me.DDRSTATUS = "NO ERROR" And Not IsNull(Me.ERROR_TYPE) Then

So I need to figure out how to also check for these to be true when the close button is pressed so that it will cancel closing the form and display the message boxs
I tried to put the code at the end using ElseIf but it doesn't seem to work or it will close the form even if the txt box don't satisfy the if statements.

Private Sub Ctl_Close_Command_Button_Click()
If Me.DDRSTATUS = "ERROR CORRECTED" Or Me.DDRSTATUS = "ERROR NOT CORRECTABLE" Then
   If IsNull(Me.ERRORTYPE) Then
      MsgBox "Please select an Error Type."
      Me.ERRORTYPE.SetFocus
      Cancel = True
   ElseIf Me.DDRSTATUS = "ERROR CORRECTED" Or Me.DDRSTATUS = "ERROR NOT CORRECTABLE" Then
      If IsNull(Me.ERRORDESCRIPTION) Then
         MsgBox "Please enter an Error Description."
         Me.ERRORDESCRIPTION.SetFocus
         Cancel = True
      ElseIf Me.DDRSTATUS = "NOT REVIEWED" Or Me.DDRSTATUS = "NO ERROR" And Not IsNull(Me.ERRORTYPE) Then
         MsgBox "Please select Error Corrected or Error Not Correctable."
         Cancel = True
      Else
         DoCmd.OpenForm "Switchboard_Form", acNormal, "", "", , acNormal
         DoCmd.Close acForm, "DDRs_Form", acSaveNo
      End If
   End If
End Sub

So if everything is good it should close the form if not is should just give a msg box and set the focus to the correct txt box. I tried putting an Else docmd closeform after every if statement but then it was closing after just the first one was checked and would display a message saying object is closed. Thanks if anyone can help. let me know if you need a better explanation or more info.

[June7]

You can but be careful when mixing OR and AND operators. Use parens as needed.

Button code does not have a Cancel argument. That would be in the BeforeUpdate event of form.

The Close code should error - appears to be missing End If.

The BeforeUpdate shows misspelled True as ture.

Have you run Debug > Compile?

One way to handle is to declare a public boolean variable in the form module. The BeforeUpdate event sets it to True if the update is canceled. The Close event aborts if the variable is true and resets the variable back to False for the next BeforeUpdate run.

BTW, I edited your post to make the code more readable. Indent your code and it will be easier to read.