Why does me.requery result in "no current record"
LAS
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
Ken Snell
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.
--
Ken Snell
http://www.accessmvp.com/KDSnell/
"LAS" <Fake...@Hotmail.com> wrote in message
news:i697sf$kju$1...@news.eternal-september.org...
LAS
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?
"Ken Snell" <kthsne...@ncoomcastt.renaetl> wrote in message
news:i69fs9$63u$1...@speranza.aioe.org...
David W. Fenton
news:i697sf$kju$1...@news.eternal-september.org:
> 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.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
David W. Fenton
news:i6bbqf$l29$1...@news.eternal-september.org:
> 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.
David W. Fenton
news:i697sf$kju$1...@news.eternal-september.org:
> 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.
LAS
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?
"David W. Fenton" <NoE...@SeeSignature.invalid> wrote in message
news:Xns9DEEB2591B9A2f9...@74.209.136.95...
LAS
now." I get it both on new records and pre-existing records. The old
function worked OK on pre-existing records.
"David W. Fenton" <NoE...@SeeSignature.invalid> wrote in message
news:Xns9DEEB33866F4Ef9...@74.209.136.95...
LAS
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?
"David W. Fenton" <NoE...@SeeSignature.invalid> wrote in message
news:Xns9DEEB2591B9A2f9...@74.209.136.95...
David W. Fenton
news:i6c35e$ltf$1...@news.eternal-september.org:
> 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.
David W. Fenton
news:i6c4vk$sgc$1...@news.eternal-september.org:
> 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.
David W. Fenton
news:i6c3lg$nst$1...@news.eternal-september.org:
> 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.
Well, is it perhaps the case that you're deleting records in a
subform?
For what it's worth, I'd never write a generic function to delete
records from forms. I've been programming Access since 1996, and
that's not something I've ever needed. It seems to me that the
attempt to generalize such a simple function is the cause of the
problem.
I just don't know enough about what your context is to say. But here
again, you're writing code for something I've never seen a need to
generalize into a function. Why you need to do that, I can't say.
LAS
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.
"David W. Fenton" <NoE...@SeeSignature.invalid> wrote in message
news:Xns9DEF9271E782Ef9...@74.209.136.94...
LAS
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...
"David W. Fenton" <NoE...@SeeSignature.invalid> wrote in message
news:Xns9DEF9351D267Df9...@74.209.136.94...
LAS
that this is unexpected behavior.
"David W. Fenton" <NoE...@SeeSignature.invalid> wrote in message
news:Xns9DEF93AB7720Ff9...@74.209.136.94...
David W. Fenton
news:i6e2t6$ru4$1...@news.eternal-september.org:
> 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.
David W. Fenton
news:i6e3gb$uiu$1...@news.eternal-september.org:
> 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.
David W. Fenton
news:i6e3hj$um5$1...@news.eternal-september.org:
> 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.
LAS
"David W. Fenton" <NoE...@SeeSignature.invalid> wrote in message
news:Xns9DF0975A03CF9f9...@74.209.136.99...
> "LAS" <Fake...@Hotmail.com> wrote in
> news:i6e2t6$ru4$1...@news.eternal-september.org:
>
>> 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?
ls_debug = me.currentrecord THIS WAS FOR SOME DEBUGGING
David W. Fenton
news:i6h22l$hm4$1...@news.eternal-september.org:
> "David W. Fenton" <NoE...@SeeSignature.invalid> wrote in message
> news:Xns9DF0975A03CF9f9...@74.209.136.99...
>> "LAS" <Fake...@Hotmail.com> wrote in
>> news:i6e2t6$ru4$1...@news.eternal-september.org:
>>
>>> 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?
>
> 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.