Nulls Escaping If Else Logic

[Proaccesspro]

The code pasted below does not quite work as intended. I have a text box (txtSearch) on a form. Once the user enters a number (SSN) it willsearch for a match. One of the tests checks for a null or blank value. If the user fails to enter a number and clicks the UPDATE button, they should get a message that reads "No SSN Entered, Please Enter SSN" Instead they get this message "Match found for " - - ". I'm pulling my hair out over this one......See anything I'm missing as to why the code does not work?? I am using Access 2002. The code in question is in BOLD

Private Sub CmdUpdateSSN_Click()
 
    Dim SSNreplace As String
    Dim strSearch As String
    Dim iReturn As Integer
 
 
On Error GoTo Error_Routine
 
'Check txtSearch for Null value or Nill Entry first.
 
   If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
       If blnOnButtonClicked = True Then
             Me![txtSearch].SetFocus
       Else
              MsgBox "Please enter an SSN.", vbOKOnly, "Enter Search Criteria"
              Me![txtSearch].SetFocus
       End If
   End If
 
'-------------------------------------------------------------
'Performs the search using value entered into txtSearch
'and evaluates this against values in SSN. The FindRecord
' command requires focus to be set on the control that is returning
' the match to the search string
 
    DoCmd.ShowAllRecords
 
    If Nz(Me!txtSearch, "") <> "" Then
        Me.SSN.SetFocus
        DoCmd.FindRecord Me!txtSearch
    Else
            txtSearch.SetFocus
    End If
    SSN.SetFocus
    SSNreplace = SSN.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text
 
'If matching record found sets focus in SSN and shows msgbox
'and clears search control
 
    If Val(SSNreplace) = Val(strSearch) Then
        MsgBox "Match Found For: " & strSearch, , "Match Found"
        SSN.SetFocus
        txtSearch = ""
        btnToggle_Click
 
    'Value not found sets focus back to txtSearch and shows msgbox   
   Else
       If Nz(Me!txtSearch, "") = "" Then
        iReturn = MsgBox("No SSN Entered.: - Retry or Cancel? ", vbRetryCancel)
      Else
        iReturn = MsgBox("Match Not Found For: " & strSearch & " - Retry or Cancel? ", vbRetryCancel, "No Match Found")
      End If
      If iReturn = 4 Then
            txtSearch.SetFocus
      Else
            btnToggle_Click
      End If
   End If
Exit_Continue:
    Exit Sub
Error_Routine:
    MsgBox "Error# " & Err.Number & " " & Err.Description
    Resume Exit_Continue
End Sub
 

[ADezii]

The code pasted below does not quite work as intended. I have a text box (txtSearch) on a form. Once the user enters a number (SSN) it willsearch for a match. One of the tests checks for a null or blank value. If the user fails to enter a number and clicks the UPDATE button, they should get a message that reads "No SSN Entered, Please Enter SSN" Instead they get this message "Match found for " - - ". I'm pulling my hair out over this one......See anything I'm missing as to why the code does not work?? I am using Access 2002. The code in question is in BOLD

Private Sub CmdUpdateSSN_Click()

Dim SSNreplace As String
Dim strSearch As String
Dim iReturn As Integer


On Error GoTo Error_Routine

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
If blnOnButtonClicked = True Then
Me![txtSearch].SetFocus
Else
MsgBox "Please enter an SSN.", vbOKOnly, "Enter Search Criteria"
Me![txtSearch].SetFocus
End If
End If

'-------------------------------------------------------------
'Performs the search using value entered into txtSearch
'and evaluates this against values in SSN. The FindRecord
' command requires focus to be set on the control that is returning
' the match to the search string

DoCmd.ShowAllRecords

If Nz(Me!txtSearch, "") <> "" Then
Me.SSN.SetFocus
DoCmd.FindRecord Me!txtSearch
Else
txtSearch.SetFocus
End If
SSN.SetFocus
SSNreplace = SSN.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

'If matching record found sets focus in SSN and shows msgbox
'and clears search control

If Val(SSNreplace) = Val(strSearch) Then
MsgBox "Match Found For: " & strSearch, , "Match Found"
SSN.SetFocus
txtSearch = ""
btnToggle_Click

'Value not found sets focus back to txtSearch and shows msgbox
Else
If Nz(Me!txtSearch, "") = "" Then
iReturn = MsgBox("No SSN Entered.: - Retry or Cancel? ", vbRetryCancel)
Else
iReturn = MsgBox("Match Not Found For: " & strSearch & " - Retry or Cancel? ", vbRetryCancel, "No Match Found")
End If
If iReturn = 4 Then
txtSearch.SetFocus
Else
btnToggle_Click
End If
End If
Exit_Continue:
Exit Sub
Error_Routine:
MsgBox "Error# " & Err.Number & " " & Err.Description
Resume Exit_Continue
End Sub

It looks like a failure to Exit the Sub-Routine if the User does not enter a SSN. Try adding Line #8 to your code. Good Luck. For future reference, please use Code Tags when displaying you code, it is very difficult to read without them.

'Check txtSearch for Null value or Nill Entry first.
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
  If blnOnButtonClicked = True Then
    Me![txtSearch].SetFocus
  Else
    MsgBox "Please enter an SSN.", vbOKOnly, "Enter Search Criteria"
    Me![txtSearch].SetFocus
      Exit Sub
  End If
End If

[Proaccesspro]

It looks like a failure to Exit the Sub-Routine if the User does not enter a SSN. Try adding Line #8 to your code. Good Luck. For future reference, please use Code Tags when displaying you code, it is very difficult to read without them.

'Check txtSearch for Null value or Nill Entry first.
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
  If blnOnButtonClicked = True Then
    Me![txtSearch].SetFocus
  Else
    MsgBox "Please enter an SSN.", vbOKOnly, "Enter Search Criteria"
    Me![txtSearch].SetFocus
      Exit Sub
  End If
End If

No difference.....

How does one use code tags??

[missinglinq]

I can't show you, because placing the tags here would format the text as code, and you wouldn't see them!

Select/hi-lite the code in your post.
Click on the # icon at the top of the editing box.
You'll see

{code}
Your code here
{/code}

except the brackets will actually be square brackets [ ]. For VBA code you need to then change the the {code} to {code=vb} one word with no space between code and =vb.

Linq ;0)>

[JKing]

        If Val(SSNreplace) = Val(strSearch) Then 
        MsgBox "Match Found For: " & strSearch, , "Match Found"
        SSN.SetFocus
        txtSearch = ""
        btnToggle_Click
 

The bolded line will still be true if both strings = ""

I think you need to rethink your if else logic to properly trap this.