DoCmd ... sometimes errors ... sometimes doesn't! (1 Viewer)

[Ally]

I have a form with a command button opening another form and running code to pull up an input box with the following code (sorry – there’s a lot of it):

Dim rst As Recordset, strcriteria As String, bx As Variant, bx1 As Variant
Dim x As Variant, y As Variant, wordlen As Integer, pos1 As Integer, numb As Variant, numlen As Variant
8: strcriteria = "[UnitNo] Like '" & InputBox("Enter the " _
& "unit number to find", "Search Criteria Input") & "'"

wordlen = Len(strcriteria)
pos1 = InStr(1, strcriteria, Chr(39))
numlen = (wordlen - pos1)
numb = Mid(strcriteria, pos1 + 1, numlen - 1)

If numb = "" Then
bx1 = msgbox("No unit number entered", vbOKCancel + vbInformation, "Input Error")
If bx1 = vbOK Then
GoTo 8
Else
DoCmd.Close
DoCmd.OpenForm "OpeningForm"
Exit Sub
End If
End If

9: Set rst = Me.RecordsetClone
rst.FindFirst strcriteria

If rst.NoMatch Then
msgbox "No entry found", , "Search Result"
DoCmd.GoToRecord , , acNewRec
Me.UnitNo = numb
Exit Sub

Else
Me.Bookmark = rst.Bookmark
GoTo 10
End If


10: bx = msgbox(" " & [PtFName] & " " & [PtLName] _
& " " & Chr(10) & Chr(13) & Chr(13) & " Is this correct ?", vbInformation + vbYesNo, _
"Search Result")
y = [PtFName] & " " & [PtLName]

If bx = vbYes Then
Me.Bookmark = rst.Bookmark
Exit Sub
Else
GoTo 8

End If

If x = y Then
msgbox "No more matches for" & " " & strcriteria
DoCmd.GoToRecord , , acNewRec
Me.UnitNo = numb
Exit Sub
Else
GoTo 10
End If


End Sub

The problem is, when you enter a number in the Input Box that doesn’t exist, it will show the correct msgbox, “No entry found”, and is supposed to input the number into a new record in the field UnitNo, but MOST OF THE TIME it will come up with the error:

Run-time error ‘2105’
You can’t go to the specified record. You may be at the end of a recordset.

And highlights line 9: DoCmd.GoToRecord , , acNewRec

I say most of the time because sometimes it does work!!! Anyone got any ideas please?

 

[simongallop]

If it's to a new record then how about

rst.AddNew
rst("UnitNo") = NewNum
rst.Update

 

[Ally]

Thanks. It's stopped the error but isn't posting the new number in - instead shows a previously entered number. We've tried adding

rst.MoveLast

but that doesn't make any difference either.

 

[Ally]

We've added

Me.Bookmark = rst.Bookmark

after the rst.MoveLast and it's working.

Thank you for your help Harry.

Ally

[This message has been edited by Ally (edited 01-24-2002).]