IsEmpty or IsNull Failure...Help

[Jramirez]

:banghead:

I am afraid I am a bit rusty with VB. Can anyone help me firgure out why this is not working? It may be something simple but I can't see it. This is a form Named LogForm. When the "Save Button" is clicked I want it to check the Packaging Name field (which is a Drop Down Combo Box where a name is selected from a list) to be sure it is not empty. If it is empty I want the focus to shift to that field and highlight the label red. Right now Access bypasses the first section entirely and goes then "If not Cancel" section. It processes all of the code in that section perfectly. Thank you.

Private Sub SaveNewRecord_Click()
On Error GoTo err_handler
Cancel = False
' Check to see if the there is a name in the "Packaging Name" Field
If IsEmpty(Combo113) Then
If MsgBox("You must select a Packaging Name.", vbCritical, "Data Missing...") Then
Me.Combo113.SetFocus
Me.PackagingName_Label = vbRed
Cancel = True
End If
End If

If Not Cancel Then
If MsgBox("Have you checked all fields for accuracy? Do you want to make corrections?", vbYesNo, "Check accuracy before saving...") = vbYes Then
Me.Undo
' If all information is verified. Go to a new record.
Else
If MsgBox("Are you sure you want to save?", vbYesNo, "Save Record.") = vbYes Then
DoCmd.GoToRecord acDataForm, "Container Closure Use and Return Log", acNewRec
End If
End If
End If
cmdSaveNewRecord_exit:
Exit Sub

err_handler:
If Err.Number <> 2105 Then
MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
End If
Resume cmdSaveNewRecord_exit
End Sub

 

[AccessMSSQL]

Try this:

Private Sub SaveNewRecord_Click()
On Error GoTo err_handler
' Check to see if the there is a name in the "Packaging Name" Field
if combo113 = "" then
 MsgBox "You must select a Packaging Name.", vbCritical, "Data Missing..."
 Me.Combo113.SetFocus
 Me.PackagingName_Label = vbRed
 exit sub
End If

If MsgBox("Have you checked all fields for accuracy? Do you want to make corrections?", vbYesNo, "Check accuracy before saving...") = vbYes Then
   Me.Undo
' If all information is verified. Go to a new record.
Else
 If MsgBox("Are you sure you want to save?", vbYesNo, "Save Record.") = vbYes Then
 DoCmd.GoToRecord acDataForm, "Container Closure Use and Return Log", acNewRec
 End If
End If

cmdSaveNewRecord_exit:
Exit Sub
err_handler:
If Err.Number <> 2105 Then
MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
End If
Resume cmdSaveNewRecord_exit
End Sub

 

[boblarson]

You should check for data in a control by using the Len function. If used in conjunction with the vbNullstring constant it will handle both NULLS and Empty String values.

If Len(Me.YourComboBoxNameHere & vbNullstring) > 0 Then
' we know there is a selection
End If

 

[ChrisO]

Cancel = False
Option Explicit is not turned on.

Me.PackagingName_Label = vbRed
That compiles but should cause a runtime error.

"Have you checked all fields for accuracy? Do you want to make corrections?"
That would be difficult to answer with a single Yes or No.

If MsgBox("You must select a Packaging Name.", vbCritical, "Data Missing...") Then
That would be true so there is no need for the If/End If block.

"Container Closure Use and Return Log"
If that is the name of the current Form then replace with Me.Name.

If no error occurs you may wish to reset the ForeColor:-
Me.PackagingName_Label.ForeColor = vbBlack

cmdSaveNewRecord_exit:
Line Labels need only be unique within procedures. Therefore each procedure can use the same Lables. Example:
On Error GoTo ErrorHandler
ExitProcedure:
ErrorHandler:
Resume ExitProcedure

If Err.Number <> 2105 Then
I don’t understand the reason for that but I’ll leave it there.

Indenting and a little bit of vertical white space can help when reading code:-

Option Explicit
Option Compare Text


Private Sub SaveNewRecord_Click()
    
    On Error GoTo ErrorHandler

    [color=green]' Check to see if the there is a name in the "Packaging Name" Field[/color]
    If Len(Me.Combo113 & vbNullString) > 0 Then
        If MsgBox("Have you checked all fields for accuracy?", vbYesNo, "Check accuracy before saving...") = vbNo Then
            Me.Undo
        Else
            [color=green]' If all information is verified, go to a new record.[/color]
            If MsgBox("Are you sure you want to save?", vbYesNo, "Save Record.") = vbYes Then
                DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
                
                [color=green]' If no error then reset the PackagingName_Label.ForeColor[/color]
                Me.PackagingName_Label.ForeColor = vbBlack
            End If
        End If
    Else
        MsgBox "You must select a Packaging Name.", vbCritical, "Data Missing..."
        Me.Combo113.SetFocus
        Me.PackagingName_Label.ForeColor = vbRed
    End If
    
ExitProcedure:
    Exit Sub
    
ErrorHandler:
    If Err.Number <> 2105 Then
        MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
    End If
    
    Resume ExitProcedure
    
End Sub

Chris.

 

[Jramirez]

Success!! Bob and Chris, all I can say is you are my heros!! I am rusty and I fear a little outdated in my Access skills and I had forgotten some basic common sense programming helps. But your simplification and clarifications solved the problem. I also learned a few new things. Thank you and God bless you!!