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?

[CODE=vb]
Private Sub Combo73_AfterUp date()
Dim strCriteria As String
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClo ne

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[/CODE]

[PNY]

[CODE=vb]
Private Sub Combo73_AfterUp date()
Dim strCriteria As String
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClo ne

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[/CODE]

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
[CODE=vb]
Private Sub Combo73_AfterUp date()
Dim strCriteria As String
Dim rst As DAO.Recordset

If IsNull(Me![Combo73]) Then Exit Sub

Set rst = Me.RecordsetClo ne

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[/CODE]

[PNY]

Just add Line #5
[CODE=vb]
Private Sub Combo73_AfterUp date()
Dim strCriteria As String
Dim rst As DAO.Recordset

If IsNull(Me![Combo73]) Then Exit Sub

Set rst = Me.RecordsetClo ne

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[/CODE]

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]

[CODE=vb]
Private Sub Combo73_AfterUp date()
On Error GoTo Err_Combo73_Aft erUpdate
Dim strCriteria As String
Dim rst As DAO.Recordset
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fm_lookup_resu lt"
stLinkCriteria = "[Prod_Spec]=" & "'" & Me![Combo31] & "'"

If IsNull(Me![Combo73]) Then Exit Sub

Set rst = Me.RecordsetClo ne

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_Af terUpdate:
Exit Sub

Err_Combo73_Aft erUpdate:
MsgBox Err.Description , vbExclamation, "Error in Combo73_AfterUp date()"
Resume Exit_Combo73_Af terUpdate
End Sub[/CODE]