Object Required

[Michael Abrams]

I have a form - ISSUES
Combobox cboSPECIFICISSUE

If the user ties to close the form using the CLOSE FORM button and the cboSPECIFICISSUE is null, I want a message box "MISSING DATA" and set focus back to the combobox.

I am receiving the Object required error.

Here are 2 versions of the code:

Code: Select all

Private Sub cmd_CloseForm_Click()

On Error GoTo Err_cmd_CloseForm_Click

If Me.cboSPECIFIC_ISSUE Is Null Then

    MsgBox "Please fill in SPECIFIC ISSUE", vbInformation, "MISSING DATA"
    
    Me.cboSPECIFIC_ISSUE.SetFocus

    
End If
    
    DoCmd.Close

Exit_cmd_CloseForm_Click:
    Exit Sub

Err_cmd_CloseForm_Click:
    MsgBox Err.Description
    Resume Exit_cmd_CloseForm_Click
   
   End Sub

Code: Select all

Private Sub cmd_CloseForm_Click()

On Error GoTo Err_cmd_CloseForm_Click

If Me.cboSPECIFIC_ISSUE Is Null Then

    MsgBox "Please fill in SPECIFIC ISSUE", vbInformation, "MISSING DATA"
    
    Me.cboSPECIFIC_ISSUE.SetFocus
    
    Else
       
    DoCmd.Close
    
End If

Exit_cmd_CloseForm_Click:
    Exit Sub

Err_cmd_CloseForm_Click:
    MsgBox Err.Description
    Resume Exit_cmd_CloseForm_Click
    
End Sub

I have been doing this for 20 years and just get a brain lock every so often. This is one of those times.

[HansV]

Try changing the line

Code: Select all

If Me.cboSPECIFIC_ISSUE Is Null Then

to

Code: Select all

If IsNull(Me.cboSPECIFIC_ISSUE) Then

You should use the second version of the code; the first one will close the form even if the message box was displayed.

[Michael Abrams]

Thank you HansV.

[Michael Abrams]

So I tried to simplify the code and came up with this: (nothing happens when user clicks yes or no)

Code: Select all

Private Sub cmd_CloseForm_Click()

On Error GoTo Err_cmd_CloseForm_Click


If IsNull(Me.cboSPECIFIC_ISSUE) Then
    
        MsgBox "Do you want to exit with missing data?", vbYesNo
    
        If vbYesNo = 6 Then DoCmd.Close
Else
    
        If vbYesNo = 7 Then Me.cboSPECIFIC_ISSUE.SetFocus
    
End If


Exit_cmd_CloseForm_Click:
    Exit Sub

Err_cmd_CloseForm_Click:
    MsgBox Err.Description
    Resume Exit_cmd_CloseForm_Click
    
End Sub

[Gasman]

Walk through your code line by line.
How can you have a return from the MSGBOX in the Else part of the code? if you never issued a MSGBOX?
You assign a variable to the result of the MSGBOX choice, not vbYesNo, that is the options for the message box.
This also tells me you do not have Option Explicit at the top of the module, which is a huge mistake.

Walking though your code will have shown these issues.
Walking through the code is ALWAYS my first point of call.

[Michael Abrams]

Thank you for help and lesson.

[Gasman]

Thank you for help and lesson.

Let's see if it works first.

[Michael Abrams]

Works - YAY

Guess at 65 I am still learning.

But I am still glad that I am retiring soon

[Gasman]

Not really a valid excuse. I am 68 this year and only touched Access for personal projects in 2012.

I am not an expert by any means. Glad it worked for you.
If you can post your final code, it might help someone else in the future with same issue?

[Michael Abrams]

Code: Select all

Private Sub cmd_CloseForm_Click()

On Error GoTo Err_cmd_CloseForm_Click

If MsgBox("Do you want to want to exit with data missing?", _
                vbQuestion + vbYesNo, "EXIT?") = vbNo Then
                Me.cboSPECIFIC_ISSUE.SetFocus
              
                Else
                
                DoCmd.Close

End If


Exit_cmd_CloseForm_Click:
    Exit Sub

Err_cmd_CloseForm_Click:
    MsgBox Err.Description
    Resume Exit_cmd_CloseForm_Click
    
End Sub

[HansV]

But this version doesn't check whether cboSPECIFIC_ISSUE is empty...

[Michael Abrams]

There are many fields that may be empty, so I (the boss, actually) decided to have the user decide if it was OK to exit with any missing data.

I set focus to that particular field because it is the one most often missed.

[HansV]

I would formulate the question slightly differently - you now suggest that there ARE missing data.

Code: Select all

Private Sub cmd_CloseForm_Click()
    On Error GoTo Err_cmd_CloseForm_Click

    If MsgBox("Have you filled in all necessary data?", _
                vbQuestion + vbYesNo, "EXIT?") = vbNo Then
        Me.cboSPECIFIC_ISSUE.SetFocus
    Else
        DoCmd.Close
    End If

Exit_cmd_CloseForm_Click:
    Exit Sub

Err_cmd_CloseForm_Click:
    MsgBox Err.Description
    Resume Exit_cmd_CloseForm_Click
End Sub

But the decision is up to you, of course.

[Michael Abrams]

Much more eloquent, I must say.

Sometimes my "Brooklyn" comes out.

Thanks for the help guys!