Error 30121 "No Current record" recently started

[Mick Ruthven]

An access 97 app at a client recently began showing a run-time error 3021
"No Current record" for the following code that's on AfterUpdate of a combo
box (after selecting an entry from the combo box). This app has worked for a
few years with no trouble, and that code was executed frequently. Compact &
Repair has been run on both the code database and data database with no
success. Any ideas?

Private Sub LastNameCombo_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Person ID] = " & Me![LastNameCombo]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

 

[Allen Browne]

Mick, I know the wizards generate code that looks like this, but the logic
is flawed on four levels:

1. It does not test whether a match was found by the FindFirst. There are
scenaios where it might not (e.g. if the form is filtered.)

2. If the combo is null (e.g. the user chooses a value and backspaces it
out), the FindFirst string will be mal-formed.

3. If the form is dirty at the time when you run this code, the record must
be saved before the form can move to the new record. That can fail (e.g.
validation rule not met), so this can trigger other weird errors as a result
(typically a nonsense message about "Update without Edit".)

4. The code assumes a DAO recordset, but that might not be the case.

Try something like this:

Sub LastNameCombo_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.LastNameCombo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Person ID] = " & Me.LastNameCombo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

More info:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

 

[Mick Ruthven]

Thanks for the quick reply, Alan. You're right about that code, and I'll
change it soon using your suggestions. The existing code works fine for me,
but of course I have different data than the client. And it worked for her
for a long time.

Mick, I know the wizards generate code that looks like this, but the logic
is flawed on four levels:

1. It does not test whether a match was found by the FindFirst. There are
scenaios where it might not (e.g. if the form is filtered.)

2. If the combo is null (e.g. the user chooses a value and backspaces it
out), the FindFirst string will be mal-formed.

3. If the form is dirty at the time when you run this code, the record must
be saved before the form can move to the new record. That can fail (e.g.
validation rule not met), so this can trigger other weird errors as a result
(typically a nonsense message about "Update without Edit".)

4. The code assumes a DAO recordset, but that might not be the case.

Try something like this:

Sub LastNameCombo_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.LastNameCombo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Person ID] = " & Me.LastNameCombo
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

More info:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

An access 97 app at a client recently began showing a run-time error 3021
"No Current record" for the following code that's on AfterUpdate of a
combo
box (after selecting an entry from the combo box). This app has worked for
a
few years with no trouble, and that code was executed frequently. Compact
&
Repair has been run on both the code database and data database with no
success. Any ideas?

Private Sub LastNameCombo_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Person ID] = " & Me![LastNameCombo]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub