Coding problem with Exit button

[NightWalker]

I have a button that saves and exits the form. The problem I am having is if I have one of the text boxes empty (required to be full) it will tell me I cannot save. I am trying to add an If statement to the code to not save any of the other data on the form if that box is empty and just exit the form without any errors. Here is the current code. The txt box that is required is named SerialNumber. I keep getting an error 424 object required when I try to exit the form.



Thank you in advance.
Walker

'------------------------------------------------------------
' btn_Exit_Click.................Saves data and exits form.
'
'------------------------------------------------------------
Private Sub btn_Exit_Click()
On Error GoTo EH
    If SerialNumber Is Null Then
        Me.Undo
        MsgBox "Data not Saved"
        DoCmd.Close , ""
    Else
        DoCmd.RunCommand acCmdSaveRecord
        MsgBox "Data Saved"
        DoCmd.Close , ""
    End If
    
Exit Sub
EH:
    MsgBox Err.Number & vbCrLf & Err.Description
    
End Sub

[JoeM]

I think you need a "Me." in front of "SerialNumber", i.e.

If Me.SerialNumber Is Null Then

[NightWalker]

Joe,

Thank you for your quick response.
I tried that and I still get the same error.

Walker

[Micron]

Is Null is a sql comparison. IsNull() is a vba comparison. Use If IsNull(SerialNumber) Then
Why do you have DoCmd.Close, "" ? You are not passing the object type and are specifying an empty string for its name.
Might as well be DoCmd.Close if you are not going to be specific.

[NightWalker]

Micron,

Thank you for your reply it works perfectly.
The reason for the DoCmd.Close, "" is I converted it from a macro to code and that is what Access put there. I didn't know I could remove it. I am still quite new to all of this.

Again Thank you greatly for your help. I have been putting up with that error message for a couple months now trying to figure it out myself. It is always the small things that get me.

Walker

[JoeM]

Is Null is a sql comparison. IsNull() is a vba comparison. Use If IsNull(SerialNumber) Then

Good catch! That still trips me up too sometimes.
I usually try one and if it doesn't work, I try the other!

[Micron]

I have been putting up with that error message for a couple months now trying to figure it out myself. It is always the small things that get me.
Walker

Interesting that you would suffer so long before seeking input. BTW, if the user realizes their mistake and wants to supply the missing info, they cannot since you don't provide any options. The form closes and the input is lost.

[NightWalker]

Micron,

Well this was a small issue that I had to fix for a form that I am only the user of currently. I had other challenges that I have been working on for the production and testing departments. What would you suggest for if they want to add their info before exiting because that will probably be my challenge when I forget to add something. I have not tried to add any options for those kind of situations.


Thank you,

Walker

[Micron]

Change your message box to some other type that gives you the options you want; e.g. YesNo, OKCancel along with a message appropriate to the options you provide. Research the message box function to learn about the types and returned values (you can use numeric or vb constants to deal with the user choice). Following is 'air code' - untested. You cannot leave my red text in the code; replace as indicated or delete, or comment out.

Dim result as Integer    

If IsNull(SerialNumber) Then
  result = Msgbox(put the message, type/title parameters here)
  If result = number or constant for stop choice goes here Then
    Exit Sub this will stop execution to allow input of missing value(s)
  Else only the other option could have been chosen in a 2 option message box
    Me.Undo another message box not needed if your first message explains what will happen based on the choices
    Docmd.Close 'active object closes if none specified
  End if
End If

DoCmd.RunCommand acCmdSaveRecord
MsgBox "Data Saved"
DoCmd.Close

[NightWalker]

Micron,

Thank you for the code example. I modified it a little and it works great. Much more user friendly. I didn't know there were other msgboxes. the new ones that I researched allow for many more options that I was looking for in some of my other forms.

Here is what I added to my code and it works beautifully.

Private Sub btn_Exit_Click()
On Error GoTo EH
Dim result As Integer
    If IsNull(SerialNumber) Then
        result = MsgBox(("Data will not be Saved." & vbCrLf & vbCrLf & "Do you want to exit without saving?"), vbYesNo, "")
        If result = 7 Then
            Exit Sub
        Else
            Me.Undo
            DoCmd.Close
        End If
        
    Else
        DoCmd.RunCommand acCmdSaveRecord
        MsgBox "Data Saved"
        DoCmd.Close
    
    End If
    
    
Exit Sub
EH:
    MsgBox Err.Number & vbCrLf & Err.Description
    
End Sub

Thank you again for all your time and help.
Walker

[Micron]

You're welcome. The only comment I'd make without benefit of seeing the form or knowing the process is to wonder if I as the user will completely understand the message. I might expect to exit with a save if I choose No since clicking to exit is what I attempted to do. After all, I'm saying "No, I don't want to exit without saving." I want to exit and save.
Nor do I know why I got this message (i.e. no idea that it's because a field has no value).
Ignore the comments if you're happy with it.

[NightWalker]

Micron,

Once again Thank you for things I have not thought of. I allowed a user to try to break it when I finished coding and every possibility was covered. I also asked him for any input and none was given but your idea would be much better to explain what happens when either button is pressed.

Thank you again,
Walker