By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,425 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

Nulls Escaping If Else Logic

100+
P: 132
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdUpdateSSN_Click()
  2.  
  3.     Dim SSNreplace As String
  4.     Dim strSearch As String
  5.     Dim iReturn As Integer
  6.  
  7.  
  8. On Error GoTo Error_Routine
  9.  
  10. 'Check txtSearch for Null value or Nill Entry first.
  11.  
  12.    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
  13.        If blnOnButtonClicked = True Then
  14.              Me![txtSearch].SetFocus
  15.        Else
  16.               MsgBox "Please enter an SSN.", vbOKOnly, "Enter Search Criteria"
  17.               Me![txtSearch].SetFocus
  18.        End If
  19.    End If
  20.  
  21. '-------------------------------------------------------------
  22. 'Performs the search using value entered into txtSearch
  23. 'and evaluates this against values in SSN. The FindRecord
  24. ' command requires focus to be set on the control that is returning
  25. ' the match to the search string
  26.  
  27.     DoCmd.ShowAllRecords
  28.  
  29.     If Nz(Me!txtSearch, "") <> "" Then
  30.         Me.SSN.SetFocus
  31.         DoCmd.FindRecord Me!txtSearch
  32.     Else
  33.             txtSearch.SetFocus
  34.     End If
  35.     SSN.SetFocus
  36.     SSNreplace = SSN.Text
  37.     txtSearch.SetFocus
  38.     strSearch = txtSearch.Text
  39.  
  40. 'If matching record found sets focus in SSN and shows msgbox
  41. 'and clears search control
  42.  
  43.     If Val(SSNreplace) = Val(strSearch) Then
  44.         MsgBox "Match Found For: " & strSearch, , "Match Found"
  45.         SSN.SetFocus
  46.         txtSearch = ""
  47.         btnToggle_Click
  48.  
  49.     'Value not found sets focus back to txtSearch and shows msgbox   
  50.    Else
  51.       If Nz(Me!txtSearch, "") = "" Then
  52.         iReturn = MsgBox("No SSN Entered.: - Retry or Cancel? ", vbRetryCancel)
  53.       Else
  54.         iReturn = MsgBox("Match Not Found For: " & strSearch & " - Retry or Cancel? ", vbRetryCancel, "No Match Found")
  55.       End If
  56.       If iReturn = 4 Then
  57.             txtSearch.SetFocus
  58.       Else
  59.             btnToggle_Click
  60.       End If
  61.    End If
  62. Exit_Continue:
  63.     Exit Sub
  64. Error_Routine:
  65.     MsgBox "Error# " & Err.Number & " " & Err.Description
  66.     Resume Exit_Continue
  67. End Sub
  68.  
Aug 8 '07 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,669
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.
Expand|Select|Wrap|Line Numbers
  1. 'Check txtSearch for Null value or Nill Entry first.
  2. If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
  3.   If blnOnButtonClicked = True Then
  4.     Me![txtSearch].SetFocus
  5.   Else
  6.     MsgBox "Please enter an SSN.", vbOKOnly, "Enter Search Criteria"
  7.     Me![txtSearch].SetFocus
  8.       Exit Sub
  9.   End If
  10. End If
Aug 8 '07 #2

100+
P: 132
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.
Expand|Select|Wrap|Line Numbers
  1. 'Check txtSearch for Null value or Nill Entry first.
  2. If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
  3.   If blnOnButtonClicked = True Then
  4.     Me![txtSearch].SetFocus
  5.   Else
  6.     MsgBox "Please enter an SSN.", vbOKOnly, "Enter Search Criteria"
  7.     Me![txtSearch].SetFocus
  8.       Exit Sub
  9.   End If
  10. End If
No difference.....

How does one use code tags??
Aug 9 '07 #3

missinglinq
Expert 2.5K+
P: 3,532
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)>
Aug 9 '07 #4

JKing
Expert 100+
P: 1,206
Expand|Select|Wrap|Line Numbers
  1.         If Val(SSNreplace) = Val(strSearch) Then 
  2.         MsgBox "Match Found For: " & strSearch, , "Match Found"
  3.         SSN.SetFocus
  4.         txtSearch = ""
  5.         btnToggle_Click
  6.  
The bolded line will still be true if both strings = ""

I think you need to rethink your if else logic to properly trap this.
Aug 9 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.