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

Simple error handling --> AfterUpdate

P: 20
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)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo73_AfterUpdate()
  2.   ' Find the record that matches the control.
  3.     Me.RecordsetClone.FindFirst "[Name] = '" & Me![Combo73] & "'"
  4.     Me.Bookmark = Me.RecordsetClone.Bookmark
  5. End Sub
(code for afterupdate for list box)
Expand|Select|Wrap|Line Numbers
  1. Private Sub List75_AfterUpdate()
  2.     ' Find the record that matches the control.
  3.     Me.RecordsetClone.FindFirst "[Name] = '" & Me![List75] & "'"
  4.     Me.Bookmark = Me.RecordsetClone.Bookmark
  5. 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?
May 26 '08 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,638
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)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo73_AfterUpdate()
  2.   ' Find the record that matches the control.
  3.     Me.RecordsetClone.FindFirst "[Name] = '" & Me![Combo73] & "'"
  4.     Me.Bookmark = Me.RecordsetClone.Bookmark
  5. End Sub
(code for afterupdate for list box)
Expand|Select|Wrap|Line Numbers
  1. Private Sub List75_AfterUpdate()
  2.     ' Find the record that matches the control.
  3.     Me.RecordsetClone.FindFirst "[Name] = '" & Me![List75] & "'"
  4.     Me.Bookmark = Me.RecordsetClone.Bookmark
  5. 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?
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo73_AfterUpdate()
  2. Dim strCriteria As String
  3. Dim rst As DAO.Recordset
  4.  
  5. Set rst = Me.RecordsetClone
  6.  
  7. strCriteria = "[Name] = '" & Me![Combo73] & "'"
  8.  
  9. rst.FindFirst strCriteria
  10.  
  11. If rst.NoMatch Then
  12.   MsgBox "A Name of [" & UCase$(Me![Combo73]) & "] was not found in " & _
  13.          "the Database!", vbExclamation, "Name not Found"
  14. Else
  15.   Me.Bookmark = rst.Bookmark
  16. End If
  17. End Sub
May 27 '08 #2

P: 20
PNY
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo73_AfterUpdate()
  2. Dim strCriteria As String
  3. Dim rst As DAO.Recordset
  4.  
  5. Set rst = Me.RecordsetClone
  6.  
  7. strCriteria = "[Name] = '" & Me![Combo73] & "'"
  8.  
  9. rst.FindFirst strCriteria
  10.  
  11. If rst.NoMatch Then
  12.   MsgBox "A Name of [" & UCase$(Me![Combo73]) & "] was not found in " & _
  13.          "the Database!", vbExclamation, "Name not Found"
  14. Else
  15.   Me.Bookmark = rst.Bookmark
  16. End If
  17. 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?
May 27 '08 #3

ADezii
Expert 5K+
P: 8,638
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo73_AfterUpdate()
  2. Dim strCriteria As String
  3. Dim rst As DAO.Recordset
  4.  
  5. If IsNull(Me![Combo73]) Then Exit Sub
  6.  
  7. Set rst = Me.RecordsetClone
  8.  
  9. strCriteria = "[LastName] = '" & Me![Combo73] & "'"
  10.  
  11. rst.FindFirst strCriteria
  12.  
  13. If rst.NoMatch Then
  14.   MsgBox "A Name of [" & UCase$(Me![Combo73]) & "] was not found in " & _
  15.          "the Database!", vbExclamation, "Name not Found"
  16. Else
  17.   Me.Bookmark = rst.Bookmark
  18. End If
  19. End Sub
May 28 '08 #4

P: 20
PNY
Just add Line #5
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo73_AfterUpdate()
  2. Dim strCriteria As String
  3. Dim rst As DAO.Recordset
  4.  
  5. If IsNull(Me![Combo73]) Then Exit Sub
  6.  
  7. Set rst = Me.RecordsetClone
  8.  
  9. strCriteria = "[LastName] = '" & Me![Combo73] & "'"
  10.  
  11. rst.FindFirst strCriteria
  12.  
  13. If rst.NoMatch Then
  14.   MsgBox "A Name of [" & UCase$(Me![Combo73]) & "] was not found in " & _
  15.          "the Database!", vbExclamation, "Name not Found"
  16. Else
  17.   Me.Bookmark = rst.Bookmark
  18. End If
  19. End Sub

Thanks so much for the help, ADezzi!!!
May 28 '08 #5

ADezii
Expert 5K+
P: 8,638
Thanks so much for the help, ADezzi!!!
Anytime, that's why we are all here.
May 28 '08 #6

P: 20
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo33_AfterUpdate()
  2. Dim strCriteria As String
  3. Dim rst As DAO.Recordset
  4.  
  5. Set rst = Me.RecordsetClone
  6.  
  7. strCriteria = "[Name] = '" & Me![Combo33] & "'"
  8.  
  9. rst.FindFirst strCriteria
  10.  
  11. If rst.NoMatch Then
  12.   MsgBox "A Name of [" & UCase$(Me![Combo73]) & "] was not found in " & _
  13.          "the Database!", vbExclamation, "Name not Found"
  14. Else
  15.   Me.Bookmark = rst.Bookmark
  16.     On Error GoTo Err_Command18_Click
  17.  
  18.             Dim stDocName As String
  19.            Dim stLinkCriteria As String
  20.  
  21.         stDocName = "fm_lookup_result"
  22.  
  23.             stLinkCriteria = "[Prod_Spec]=" & "'" & Me![Combo31] & "'"
  24.            DoCmd.OpenForm stDocName, , , stLinkCriteria
  25.  
  26. Exit_Command18_Click:
  27.     Exit Sub
  28.  
  29. Err_Command18_Click:
  30.     MsgBox Err.Description
  31.     Resume Exit_Command18_Click
  32.  
  33. End If
  34. End Sub
Jun 5 '08 #7

ADezii
Expert 5K+
P: 8,638
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo73_AfterUpdate()
  2. On Error GoTo Err_Combo73_AfterUpdate
  3. Dim strCriteria As String
  4. Dim rst As DAO.Recordset
  5. Dim stDocName As String
  6. Dim stLinkCriteria As String
  7.  
  8. stDocName = "fm_lookup_result"
  9. stLinkCriteria = "[Prod_Spec]=" & "'" & Me![Combo31] & "'"
  10.  
  11. If IsNull(Me![Combo73]) Then Exit Sub
  12.  
  13. Set rst = Me.RecordsetClone
  14.  
  15. strCriteria = "[LastName] = '" & Me![Combo73] & "'"
  16.  
  17. rst.FindFirst strCriteria
  18.  
  19. If rst.NoMatch Then
  20.   MsgBox "A Name of [" & UCase$(Me![Combo73]) & "] was not found in " & _
  21.          "the Database!", vbExclamation, "Name not Found"
  22. Else
  23.   'Guess you won't be needing this, now
  24.   'Me.Bookmark = rst.Bookmark
  25.    DoCmd.OpenForm stDocName, , , stLinkCriteria
  26. End If
  27.  
  28. Exit_Combo73_AfterUpdate:
  29.   Exit Sub
  30.  
  31. Err_Combo73_AfterUpdate:
  32.   MsgBox Err.Description, vbExclamation, "Error in Combo73_AfterUpdate()"
  33.   Resume Exit_Combo73_AfterUpdate
  34. End Sub
Jun 5 '08 #8

Post your reply

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