How to suppress the "no current record" error.

  • Thread starter Thread starter Jennifer Robertson
  • Start date Start date
J

Jennifer Robertson

I have a form for processing of permits. On this form is a subform for
audits.

I have the subform based on a query of the audits and sorted by date.

When the subform is loaded, it moves to the last audit for that permit
holder, thus displaying the most recent audit.

That all works fine.

There are cases where the permit is new and the permit holder has not been
audited yet. The subform is blank, which is good. However if I close the
form while viewing a permit which has no recordset for audits, I get the "no
current record" error message box. If I close the form while viewing a
permit which has a recordset for audits, I do not get the message.

The code is:

Private Sub Form_Current()

On Error Resume Next

If Me.Recordset.BOF <> Me.Recordset.EOF Then
Me.Recordset.MoveLast
End If
End Sub


How do I suppress this?
 
Thanks for the quick response RG.

The code as it is, works fine and does what it is supposed to do. If there
are records they are displayed and the most recent one is displayed (hence
movelast).

I've looked around online and your "if not" and my "<>" statements are
pretty equivalent. Unless I am missing something?

What I was asking for was how to suppress the popup message box that says
"No current record". We already know there will be times when there is no
current record, and it is not a problem for our process.

Hmmm.
 
are you running this code in the *mainform's* Current event procedure? if
so, it's looking at the mainform recordset, not the subform. if you're
running it in the *subform's* Current event, it's going to jump to the last
record in the subform every time you try to move to an *existing* record
that isn't the last record - seems like that would make it hard to edit
records, or even navigate the subform when there are more records than will
show in the subform window at one time.

something doesn't seem to be adding up here, but you might try running the
following code from the mainform's Current event procedure, as

With Me!SubformControlName.Form
If Not .NewRecord Then .RecordSet.MoveLast
End With

replace SubformControlName with the correct name of the subform control
*within the mainform* (not the name of the subform as you see it in the
database window).

hth
 
Hi hth,

The code is on the subform's on current event. The subform is for display
purposes only, the subform records are not to be edited. Just for the user
to see the details of the most recent audit. There is no need to move back
and forth through the subform's recordset.

For the permits (the main form) that do not have yearly audit yet (the
subform) there will be no record displayed in the subform. This is fine. It
alerts the user that there is no audit yet for this permit.

The user doesn't need a popup message saying that there is no current
record. THey already know it.

The question is how can I stop the popup box?

Jen
 
Hi RG,

I've tried the error handling suggestion. I still get the popup box only
when I close the form on a permit where there is no audit. Here is my code:

On the subform's On Current Event

Private Sub Form_Current()
On Error GoTo ErrorHandler
If Me.Recordset.BOF <> Me.Recordset.EOF Then
Me.Recordset.MoveLast
End If
ErrorHandler:
If Error = 3021 Then 'no current record
Resume Next
End If
End Sub

On the subform's On Close Event

Private Sub Form_Close()
On Error GoTo ErrorHandler
ErrorHandler:
If Error = 3021 Then 'no current record
Resume Next
End If
End Sub

On the main form's On Close Event

Private Sub Form_Close()
On Error GoTo ErrorHandler
ErrorHandler:
If Error = 3021 Then 'no current record
Resume Next
End If
End Sub


Its a sneaky error that doesn't like to be trapped! I'm sure its something
simple though.


ruralguy via AccessMonster.com said:
OK, then put error handling in the routine and trap the error.

Jennifer said:
Thanks for the quick response RG.

The code as it is, works fine and does what it is supposed to do. If there
are records they are displayed and the most recent one is displayed (hence
movelast).

I've looked around online and your "if not" and my "<>" statements are
pretty equivalent. Unless I am missing something?

What I was asking for was how to suppress the popup message box that says
"No current record". We already know there will be times when there is no
current record, and it is not a problem for our process.

Hmmm.
I believe your code should be:
If Not Me.Recordset.BOF AND Not Me.Recordset.EOF Then
Click to expand...
[quoted text clipped - 30 lines]
How do I suppress this?
Click to expand...
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
 
Nope. Still get the popup.

Pesky, pesky, pesky.....

ruralguy via AccessMonster.com said:
How about:
If Me.Recordset.RecordCount > 0 Then
Me.Recordset.MoveLast
End If


Jennifer said:
Hi RG,

I've tried the error handling suggestion. I still get the popup box only
when I close the form on a permit where there is no audit. Here is my
code:

On the subform's On Current Event

Private Sub Form_Current()
On Error GoTo ErrorHandler
If Me.Recordset.BOF <> Me.Recordset.EOF Then
Me.Recordset.MoveLast
End If
ErrorHandler:
If Error = 3021 Then 'no current record
Resume Next
End If
End Sub

On the subform's On Close Event

Private Sub Form_Close()
On Error GoTo ErrorHandler
ErrorHandler:
If Error = 3021 Then 'no current record
Resume Next
End If
End Sub

On the main form's On Close Event

Private Sub Form_Close()
On Error GoTo ErrorHandler
ErrorHandler:
If Error = 3021 Then 'no current record
Resume Next
End If
End Sub

Its a sneaky error that doesn't like to be trapped! I'm sure its something
simple though.
OK, then put error handling in the routine and trap the error.
Click to expand...
[quoted text clipped - 18 lines]
How do I suppress this?
Click to expand...
Click to expand...
Click to expand...
 
well, if the user only needs to see the most recent audit, then i'd avoid
the issue entirely by pulling only the last audit for each permit; a Totals
query for the subform, doing GroupBy on each field (leave the primary key
field out of the query), and doing a Max instead of GroupBy on the date
field.

if you want older audits to show up in the subform, then i'd leave your
subform's RecordSource as is, except add an ORDER BY clause, sorting on the
date field in descending order, so the most recent audit is the first record
in the subform rather than the last.

in either solution above, get rid of the Current event code entirely.

hth
 
Hi hth,

Thanks for the input.

My question does not involve the on current event or anything about
displaying the most recent records. I'm good with what I have so far. It
works.

Regardless of how I set up my subform for displaying records, there WILL be
some cases where there is no record.

My question only involves trapping the "no current record" error, which I
can't seem to do. I've tried putting error trapping in every event on both
the form and the main form and can't quite seem to figure out when it is
popping up.

I'll keep plugging away.

Jen
 
Jennifer Robertson said:
I have a form for processing of permits. On this form is a subform for
audits.

I have the subform based on a query of the audits and sorted by date.

When the subform is loaded, it moves to the last audit for that permit
holder, thus displaying the most recent audit.

That all works fine.

There are cases where the permit is new and the permit holder has not been
audited yet. The subform is blank, which is good. However if I close the
form while viewing a permit which has no recordset for audits, I get the
"no current record" error message box. If I close the form while viewing a
permit which has a recordset for audits, I do not get the message.

The code is:

Private Sub Form_Current()

On Error Resume Next

If Me.Recordset.BOF <> Me.Recordset.EOF Then
Me.Recordset.MoveLast
End If
End Sub


How do I suppress this?
Click to expand...
 
Hi Jennifer,
you wrote
However if I close the
form while viewing a permit which has no recordset for audits, I get the
"no current record" error message box. If I close the form while viewing a
permit which has a recordset for audits, I do not get the message.
Click to expand...

When the form is closed, do you have any code that saves the current record?
Could you be getting the no current record error when your code tries to do
something with that non existent record?
Does the main form need to do something with that non existent record?

Maybe there is a routine where you need something like:

If Me.RecordsetClone.RecordCount >0 Then
Else
'code to stop that error
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Hi Jeanette,

I didn't put any code on the form to save the record, but I assume Access
does something like that when I close the form. That might be somewhere to
look.

Interestingly, when I added some error trapping code (which did squat) plus
a movefirst so the main form would go back to the first record (which by the
way DOES have a record on the subform) on the main form's On Close Event, I
still got the popup box. When I clicked ok, I saw the main form go back to
the first record just before it closed the form.

So I'm pretty sure its not popping up on the main forms On close event.

I'm also pretty sure its not popping up on the On current event of both the
main and subforms as the error never pops up while I"m surfing back and
forth thru records on both main and subforms.

Or I'm not using the correct code for error trapping.

I don't think I need to avoid the error. The error can run willy nilly. I
just need to avoid the annoying popup box that I have to click closed before
I can close the form.

To recapitulate the error, have two tables. One with 10 entries including
primary key. One with 5 entries (that correspond to 5 of the 10 entries in
the first table) and that links to the first via the primary key.

Make a main form using the first table as the recordsource. Subform using
second table as the record source. Link via primary key. If you flip through
the 10 records on the main form, you will notice that 5 of your records do
not have any records on the subform. (5 of your records will have a blank
subform)

Flip to a record on the main form which does have a record on the subform.
Then click the form closed.

Try again. Flip to a record on the main form with does not have a record on
the subform. Then click the form closed. Here you should get the popup
error.

Still pluggin away.

Jen
 
The other way to track down this error message is to create a quick and
dirty new main form and subform. Don't do any formatting or layout work. Try
using the form wizard to create the 2 forms.
It's unlikely that the new forms will give the no current record error.
If you have the new form that doesn't give the error, then you can add to
this form the code from the previous form, one routine at a time.
It's like a process of elimination. Each time you add one code routine, test
the form. Eventually you should be able to pinpoint the thing that causes
the error.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
I already know what causes the error. Its the fact that no audit record
exists for that particular permit. The audit hasn't been done yet. The
person was assigned their permit recently and the office has not gotten
around to auditing them yet. It's a fact of the process. There will always
be cases of "no current record" in this database.

The fact that the error arises is not not not not not not not the problem.
Its in the handling of the error that is.

I really appreciate the time taken by you, hth and RG, but no one has
addressed my question. I don't know how much plainer I can be in asking my
question. How do I prevent the BOX from popping up. The error can occur
because there IS an error. I just don't want the popup box.

Sorry but I'm getting really frustrated with this problem and the fact that
I can't seem to get my point across to anyone LOL
 
We seem to be talking about different things - I am assuming that you want
to get rid of that silly error message that pops up.
It is likely that the only way to stop that silly message is to change
something in the way you have the forms setup, or change the thing in your
code that makes it popup. It could well be that no amount of error trapping
will get rid of that silly message box.

The process I described should enable you to get an understanding of what it
is about your form and subform that makes this error message popup.
I am not trying to get you to stop the error, merely to get an understanding
of what part of your form/code setup causes this error message to popup so
annoyingly.
It may be that you need to change something in your code, or in the way the
2 forms are setup that will stop that silly error message from popping up.
Starting with a new form subform setup where that silly message does not
popup is the jumping off point to discover how to get rid of that message.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
I think we were talking about different things. And its late, I'm frustrated
and pissy LOL

BUT

You mentioned something about Access updating the record on closing and
THAT's where the solution was. I googled "Cancel save on close" and found a
post by YOU! LOL

I did your suggestions in that post ie set allow edits/deletions/additions
to NO for the subform properties. And that did the trick!!!! I knew it was
something simple... just took me 12 hours and a partial brain hemorrhage to
get there ;)

Thanks for the inspiration

Jen
 
I just ran into this little problem myself and this is what fixed it for me,
hope it helps:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3021 Then Response = 0
End Sub
 
THANK YOU THANK YOU THANK YOU!!!

I've been looking into this (well, on and off) for the last year or
so, and finally the solution is there!! Apparently it was elsewhere
too but it is so submerged with stupid "Try and catch the error"
messages (DUH, what do you think was the first thing I tried to do!?!
@?) that I never found the real answer until now!

I'm so happy, thanks again ;)
 
Sadly this works when closing the form using the button, but if you
close it with Alt-F4 or the window's typical Close button, it still
does the "No Current Record" error.

If I trigger debugging, it breaks on the declaration of the on_unload
event. Though I could remove the Close button, I really don't like
this option.
 
Thank you!
I just had the same problem.
your solution works well!

thank you again



Courtney said:
I just ran into this little problem myself and this is what fixed it for me,
hope it helps:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3021 Then Response = 0
End Sub

"Jennifer Robertson" wrote:

> I have a form for processing of permits. On this form is a subform for
> audits.
>
> I have the subform based on a query of the audits and sorted by date.
>
> When the subform is loaded, it moves to the last audit for that permit
> holder, thus displaying the most recent audit.
>
> That all works fine.
>
> There are cases where the permit is new and the permit holder has not been
> audited yet. The subform is blank, which is good. However if I close the
> form while viewing a permit which has no recordset for audits, I get the "no
> current record" error message box. If I close the form while viewing a
> permit which has a recordset for audits, I do not get the message.
>
> The code is:
>
> Private Sub Form_Current()
>
> On Error Resume Next
>
> If Me.Recordset.BOF <> Me.Recordset.EOF Then
> Me.Recordset.MoveLast
> End If
> End Sub
>
>
> How do I suppress this?
>
>
>
Click to expand...
 
Courtney said:
I just ran into this little problem myself and this is what fixed it for me,
hope it helps:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3021 Then Response = 0
End Sub
Click to expand...
THANK YOU COURTNEY!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 
Back
Top