Simple error handling --> AfterUpdate

[PNY]

Hi all,

I have a form that uses the code listed below on the after update of a combo box and list box to search for a selected Name.

(code for afterupdate for combo box)

Private Sub Combo73_AfterUpdate()
  ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[Name] = '" & Me![Combo73] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

(code for afterupdate for list box)

Private Sub List75_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[Name] = '" & Me![List75] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

How would I add the ability to display an error message if the user tries to search for a Name that doesn't exist in the records?

[ADezii]

Hi all,

I have a form that uses the code listed below on the after update of a combo box and list box to search for a selected Name.

(code for afterupdate for combo box)

Private Sub Combo73_AfterUpdate()
  ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[Name] = '" & Me![Combo73] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

(code for afterupdate for list box)

Private Sub List75_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[Name] = '" & Me![List75] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

How would I add the ability to display an error message if the user tries to search for a Name that doesn't exist in the records?

Private Sub Combo73_AfterUpdate()
Dim strCriteria As String
Dim rst As DAO.Recordset
 
Set rst = Me.RecordsetClone
 
strCriteria = "[Name] = '" & Me![Combo73] & "'"
 
rst.FindFirst strCriteria
 
If rst.NoMatch Then
  MsgBox "A Name of [" & UCase$(Me![Combo73]) & "] was not found in " & _
         "the Database!", vbExclamation, "Name not Found"
Else
  Me.Bookmark = rst.Bookmark
End If
End Sub

[PNY]

Private Sub Combo73_AfterUpdate()
Dim strCriteria As String
Dim rst As DAO.Recordset
 
Set rst = Me.RecordsetClone
 
strCriteria = "[Name] = '" & Me![Combo73] & "'"
 
rst.FindFirst strCriteria
 
If rst.NoMatch Then
  MsgBox "A Name of [" & UCase$(Me![Combo73]) & "] was not found in " & _
         "the Database!", vbExclamation, "Name not Found"
Else
  Me.Bookmark = rst.Bookmark
End If
End Sub

Thanks ADezzi, it works like a charm! I tested it to see what would happen if the field was NULL and I get a runtime error ... How do I fix that?

[ADezii]

Thanks ADezzi, it works like a charm! I tested it to see what would happen if the field was NULL and I get a runtime error ... How do I fix that?

Just add Line #5

Private Sub Combo73_AfterUpdate()
Dim strCriteria As String
Dim rst As DAO.Recordset
 
If IsNull(Me![Combo73]) Then Exit Sub
 
Set rst = Me.RecordsetClone
 
strCriteria = "[LastName] = '" & Me![Combo73] & "'"
 
rst.FindFirst strCriteria
 
If rst.NoMatch Then
  MsgBox "A Name of [" & UCase$(Me![Combo73]) & "] was not found in " & _
         "the Database!", vbExclamation, "Name not Found"
Else
  Me.Bookmark = rst.Bookmark
End If
End Sub

[PNY]

Just add Line #5

Private Sub Combo73_AfterUpdate()
Dim strCriteria As String
Dim rst As DAO.Recordset
 
If IsNull(Me![Combo73]) Then Exit Sub
 
Set rst = Me.RecordsetClone
 
strCriteria = "[LastName] = '" & Me![Combo73] & "'"
 
rst.FindFirst strCriteria
 
If rst.NoMatch Then
  MsgBox "A Name of [" & UCase$(Me![Combo73]) & "] was not found in " & _
         "the Database!", vbExclamation, "Name not Found"
Else
  Me.Bookmark = rst.Bookmark
End If
End Sub

Thanks so much for the help, ADezzi!!!

[ADezii]

Thanks so much for the help, ADezzi!!!

Anytime, that's why we are all here.

[PNY]

Anytime, that's why we are all here.

Hi Again,

I'm trying to do more error handling etc, and i'm wondering: Can there be a way to add a line in the above code to open a form if the Name is found? Does this make sense:

Private Sub Combo33_AfterUpdate()
Dim strCriteria As String
Dim rst As DAO.Recordset
 
Set rst = Me.RecordsetClone
 
strCriteria = "[Name] = '" & Me![Combo33] & "'"
 
rst.FindFirst strCriteria
 
If rst.NoMatch Then
  MsgBox "A Name of [" & UCase$(Me![Combo73]) & "] was not found in " & _
         "the Database!", vbExclamation, "Name not Found"
Else
  Me.Bookmark = rst.Bookmark
    On Error GoTo Err_Command18_Click
 
            Dim stDocName As String
           Dim stLinkCriteria As String
 
        stDocName = "fm_lookup_result"
 
            stLinkCriteria = "[Prod_Spec]=" & "'" & Me![Combo31] & "'"
           DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Command18_Click:
    Exit Sub
 
Err_Command18_Click:
    MsgBox Err.Description
    Resume Exit_Command18_Click
 
End If
End Sub

[ADezii]

Private Sub Combo73_AfterUpdate()
On Error GoTo Err_Combo73_AfterUpdate
Dim strCriteria As String
Dim rst As DAO.Recordset
Dim stDocName As String
Dim stLinkCriteria As String
 
stDocName = "fm_lookup_result"
stLinkCriteria = "[Prod_Spec]=" & "'" & Me![Combo31] & "'"
 
If IsNull(Me![Combo73]) Then Exit Sub
 
Set rst = Me.RecordsetClone
 
strCriteria = "[LastName] = '" & Me![Combo73] & "'"
 
rst.FindFirst strCriteria
 
If rst.NoMatch Then
  MsgBox "A Name of [" & UCase$(Me![Combo73]) & "] was not found in " & _
         "the Database!", vbExclamation, "Name not Found"
Else
  'Guess you won't be needing this, now
  'Me.Bookmark = rst.Bookmark
   DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
 
Exit_Combo73_AfterUpdate:
  Exit Sub
 
Err_Combo73_AfterUpdate:
  MsgBox Err.Description, vbExclamation, "Error in Combo73_AfterUpdate()"
  Resume Exit_Combo73_AfterUpdate
End Sub