Why does me.requery result in "no current record"

L

LAS

Why do I get a "no current record" error on Me.Requery??? The first query
certainly happens when there's no current record, and I have other forms
that open with no current record (sql parameters just return no rows), but a
Me.Requery works... I think...

If Not (fncDelCurrentRec(Me)) Then
MsgBox "An Error occurred in deleting the Incident!"
Else
Me.Requery THIS IS WHERE I GET A 'NO CURRENT
RECORD' ERROR
Call Refresh_Form
End If

Here is the code for fncDelCurrentRec. It works fine if there is a current
record before it's called. In the case where it doesn't work the record has
not yet been updated, but the user doesn't know that, of course.

Public Function fncDelCurrentRec(ByRef frmSomeForm As Form) As Boolean

With frmSomeForm
If .NewRecord Then
.Undo
fncDelCurrentRec = True
GoTo Exit_DelCurrentRec
End If
End With

With frmSomeForm.RecordsetClone
.Bookmark = frmSomeForm.Bookmark 'Move to the current record of the
passed form
.Delete
' frmSomeForm.Requery
End With
fncDelCurrentRec = True

Exit_DelCurrentRec:
Exit Function

Err_DelCurrentRec:


MsgBox (Err.Description)

fncDelCurrentRec = False
Resume Exit_DelCurrentRec

End Function
 
K

Ken Snell

When you delete a record, the form is not on any record until you move to
another record. So, in your code, after you delete the record, move the
form's recordset to a record that still exists (.MoveFirst for example). You
just have to watch out if there are no records left after you delete the
record.
 
L

LAS

Yeah, but what if there are no records left??? The parameters will have
changed, so Me.Requery would repopulate my form. How can I get a new set of
records after deleting the last record in the current set?
 
D

David W. Fenton

With frmSomeForm.RecordsetClone
.Bookmark = frmSomeForm.Bookmark 'Move to the current
record of the
passed form

This line of code makes no sense whatsoever, as it has not been
preceeded by anything that would move the RecordsetClone's bookmark
away from the current record.
 
D

David W. Fenton

Yeah, but what if there are no records left??? The parameters
will have changed, so Me.Requery would repopulate my form. How
can I get a new set of records after deleting the last record in
the current set?

Well, what's the Recordsource? Do you have a filter applied? Are you
in Data Entry mode? All of these things can lead to a case where you
don't have any records to move to.
 
D

David W. Fenton

Public Function fncDelCurrentRec(ByRef frmSomeForm As Form) As
Boolean

With frmSomeForm
If .NewRecord Then
.Undo
fncDelCurrentRec = True
GoTo Exit_DelCurrentRec
End If
End With

With frmSomeForm.RecordsetClone
.Bookmark = frmSomeForm.Bookmark 'Move to the current
record of the
passed form
.Delete
' frmSomeForm.Requery
End With
fncDelCurrentRec = True

Exit_DelCurrentRec:
Exit Function

Err_DelCurrentRec:


MsgBox (Err.Description)

fncDelCurrentRec = False
Resume Exit_DelCurrentRec

End Function

I would rewrite this as:

Public Function fncDelCurrentRec(ByRef frmSomeForm As Form) _
As Boolean
On Error GoTo errHandler

With frmSomeForm
If .NewRecord Then
.Undo
Else
DoCmd.SelectObject acForm, .Name
DoCmd.RunCommand acCmdDeleteRecord
.Requery
End If
End With
fncDelCurrentRec = True

exitRoutine:
Exit Function

errHandler:
MsgBox Err.Description
Resume exitRoutine
End Function

I would never delete records from the RecordsetClone. I would never
edit them, either. I would only ever use the RecordsetClone for
navigation.
 
L

LAS

I don't necessarily want to move to a record. If Me.Requery results in no
rows, that's fine. I just don't want Me.Requery to result in an error
message "no current record." Are you saying that somewhere under the covers
I'm trying to move to a record? That Me.Requery by itself would not produce
this error?
 
L

LAS

I tried this and I get "The command or action DELETE RECORD isn't available
now." I get it both on new records and pre-existing records. The old
function worked OK on pre-existing records.
 
L

LAS

I don't WANT to have any records to move to. I expect the recordset to be
empty after the delete. In fact the form ends up looking empty, which is
what I want when I delete the last record. I have stepped through the code
and I don't see anything happening between the start of me.requery and the
end except a debug statement I had in the form_beforeupdate event
('gb_boolean = Me.NewRecord) which I commented out and it made no
difference.

The question is not "how do I get to a current record." The question is
"why am I getting a "no current record" message when I do a me.requery which
results in the DESIRED no records?
 
D

David W. Fenton

I don't necessarily want to move to a record. If Me.Requery
results in no rows, that's fine. I just don't want Me.Requery to
result in an error message "no current record." Are you saying
that somewhere under the covers I'm trying to move to a record?
That Me.Requery by itself would not produce this error?

Me.Requery should not produce a "no current record" error, no. If
your form allows additions, a Requery that results in no more
records returned will land you on a new record. If you aren't
allowing records, it will just load the form with no records loaded
(and no controls, either).

I suspect there's more going on that what you've explained. That is,
you've misdiagnosed the cause of the problem and not given us all
the context we need to figure out what's causing the problem.
 
D

David W. Fenton

I don't WANT to have any records to move to. I expect the
recordset to be empty after the delete. In fact the form ends up
looking empty, which is what I want when I delete the last record.
I have stepped through the code and I don't see anything
happening between the start of me.requery and the end except a
debug statement I had in the form_beforeupdate event ('gb_boolean
= Me.NewRecord) which I commented out and it made no difference.

You realize your code had two requiries, right? First you requeried
the form after the delete in your deletion function, and then you
requeried it again in the main code loop. I don't know why either of
them should produce an error, but certainly both are not needed. It
would seem obvious that the Requery in the deletion function is not
throwing an error, so, if you remove the redundant one, your problem
should go away.
The question is not "how do I get to a current record." The
question is "why am I getting a "no current record" message when I
do a me.requery which results in the DESIRED no records?

Well, it's not clear from your code what's causing the error. I
would expect the .Bookmark = frmSomeForm.Bookmark line to produce an
error, and perhaps it would be "no current record." It's an
unnecessary line, and shouldn't be there -- unless you've navigated
within the RecordsetClone and want to return to the record displayed
in the form, there is no reason to set the RecordsetClone's
bookmark. Indeed, I have never set the RecordsetClone's bookmark in
any of my code -- I only use the RecordsetClone to find a bookmark
that I can use to set the form's edit buffer to.
 
L

LAS

I've stepped through with the debugger. When it gets to Me.Requery it then
goes to form_beforeupdate and then as soon as it exits that sub there's the
message. What you describe is what I would expect from a me.requery
returning no records. But I don't know how to diagnose any further.
 
L

LAS

It's hard to see, I know, with no color cues, but the requery in the
function was commented out. As the comment says, the bookmark is supposed
to insure that I'm on the proper record (not sure how this works, just
copied it). But I commented it out and I still got the same error. From
what you say it sounds like I might just have a corrupt form. This has
happened before. I have to copy all the controls and code bit by bit into a
new form..... sigh...
 
L

LAS

Nope, no subform. See response to your other post. Thanks for confirming
that this is unexpected behavior.
 
D

David W. Fenton

I've stepped through with the debugger. When it gets to
Me.Requery it then goes to form_beforeupdate and then as soon as
it exits that sub there's the message.

What's in the form's BeforeUpdate event?
What you describe is what I would expect from a me.requery
returning no records. But I don't know how to diagnose any
further.

Did you remove the extra Requery? Given that it's the duplicate one
that's causing the problem, it seems to me that should be the
easiest thing to fix it.
 
D

David W. Fenton

It's hard to see, I know, with no color cues, but the requery in
the function was commented out. As the comment says, the bookmark
is supposed to insure that I'm on the proper record (not sure how
this works, just copied it).

But that makes no sense. Unless you've moved the record pointer in
the RecordsetClone, it will be on the same record as the form's
edit/display buffer. It's only if you've done .MoveNext/MoveFirst,
etc. or done a .FindFirst that you'd possibly have a difference in
current record between the RecordsetClone and the form's
edit/display buffers, so if you haven't done that, you don't need to
worry about which record you're on.
But I commented it out and I still got the same error. From
what you say it sounds like I might just have a corrupt form.
This has happened before. I have to copy all the controls and
code bit by bit into a new form..... sigh...

I don't think it's a corrupt form. I just think you're mucking about
in things that you don't need to bother about in order to accomplish
the task.
 
D

David W. Fenton

Nope, no subform. See response to your other post. Thanks for
confirming that this is unexpected behavior.

I'm not really confirming that.

I was trying to rescue your generalized delete function, but in
fact, I wouldn't use one. I think you should simplify and get rid of
the delete function and test for .NewRecord and do the deletion with
DoCmd.RunCommand if it's not a new record right inline in the same
code. That way, you don't have to worry about which form has the
focus.
 
D

David W. Fenton

ls_debug = me.currentrecord THIS WAS FOR SOME DEBUGGING

Ah, so you weren't doing anything that mucked about with the editing
process.

For what it's worth, .CurrentRecord isn't all that useful, since
there are no real "record numbers". It doesn't tell you anything
except the position within the current recordset.
Me.RecordsetClone.CurrentRecord and Me.CurrentRecord will return the
same value unless you navigate within the RecordsetClone with one of
the record moving commands (.MoveNext, etc.) or with .FindFirst.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top