Crash at Specific Location in VBA Code (1 Viewer)

[themurph2000]

Hello. I was trying to diagnose why a form I created, which will search for a record in a table based on criteria in the form, then ask to create it if it doesn't exist, crashes whenever someone answers "no" to the question. Here's the code I cobbled together (or configured for my use, as it were)

Private Sub Command16_Click()
  Dim rt            As Object
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("RateList", dbOpenDynaset, dbAppendOnly)
If DCount("*", "RateList", "JCN = " & Forms!frm_searchjcnlist!txt_invisible_jcnnumber) = 0 Then
    If MsgBox("The Record is Not in the Rate List.  Would you like to add it?", vbYesNo) = vbYes Then
      rs.AddNew
      rs!JCN = Me.txt_invisible_jcnnumber
      rs!ParentItem = Me.txt_invisible_jcnparent
      rs!OriginalName = Me.txt_invisible_jcnparent
      rs!CreatedDate = Date
      rs.Update
      Me.Requery
      Else: Me.txt_invisible_jcnparent = ""
      Me.txt_invisible_jcnnumber = ""
      End If
End If
    ' Find the record that matches the control.
    Set rt = Forms!frm_searchjcnlist.Recordset.Clone
    rt.FindFirst "[JCN] = " & Str(Nz(Me![txt_invisible_jcnnumber], 0))
    If Not rt.EOF Then Forms!frm_searchjcnlist.Bookmark = rt.Bookmark
    Me.txt_invisible_jcnparent = ""
    Me.txt_invisible_jcnnumber = ""
End Sub

Since the code has no problem when "yes" is clicked, I figured I must be putting something in there that shouldn't be or forgot to put something in.

Thank you for any suggestions.

 

[pbaldy]

For starters, I'm not a fan of mixing the one-line and block formats of the If/Then. Don't know that it would cause any error, but I'd do this:

Else
  Me.txt_invisible_jcnparent = ""
  Me.txt_invisible_jcnnumber = ""

Secondly, if I'm reading it right if they answer no that code will fire and set the textboxes to "", but then you turn around and try to find a record based on one. I'd just exit sub at that point (or go to your exit handler if you have error handling).

 

[themurph2000]

For starters, I'm not a fan of mixing the one-line and block formats of the If/Then. Don't know that it would cause any error, but I'd do this:

Else
  Me.txt_invisible_jcnparent = ""
  Me.txt_invisible_jcnnumber = ""

Secondly, if I'm reading it right if they answer no that code will fire and set the textboxes to "", but then you turn around and try to find a record based on one. I'd just exit sub at that point (or go to your exit handler if you have error handling).

The Exit Sub did the trick. I was confused because I had the same code for a second search box, only the search was done by the job number instead of the job title (integer vs string). That one wasn't giving me any problems.

Awesome. Thanks!

 

[pbaldy]

Happy to help Patrick!