Run-time error 3021

  • Thread starter Thread starter sg
  • Start date Start date
S

sg

I have one form that opens another form to show related records. The 2nd
form has a custom record counter that runs the following code:

With Me.RecordsetClone
.MoveFirst
Me.RecCount = Me.CurrentRecord & " of " & .RecordCount
End With

I got the code from someone here on this forum and it works great as long as
the 2nd form has records in it. I originally ran the code in the oncurrent
event and also tried onload and afterupdate, but still get the same error.
When I debug, it highlights .MoveFirst. I tried taking that out, but then it
highlights Me.RecCount = Me.CurrentRecord & " of " & .RecordCount. From what
I can figure, it doesn't like that it can't find any record, but I don't know
how to fix it. I am using Access 2007.

Thanks in advance for your help.
 
sg said:
I have one form that opens another form to show related records. The 2nd
form has a custom record counter that runs the following code:

With Me.RecordsetClone
.MoveFirst
Me.RecCount = Me.CurrentRecord & " of " & .RecordCount
End With

I got the code from someone here on this forum and it works great as long
as
the 2nd form has records in it. I originally ran the code in the
oncurrent
event and also tried onload and afterupdate, but still get the same error.
When I debug, it highlights .MoveFirst. I tried taking that out, but then
it
highlights Me.RecCount = Me.CurrentRecord & " of " & .RecordCount. From
what
I can figure, it doesn't like that it can't find any record, but I don't
know
how to fix it. I am using Access 2007.

Thanks in advance for your help.
Click to expand...


It seems to me that the .MoveFirst should be .MoveLast, but either way you
need to allow for the possibility that the form has no records. Modifying
the posted code accordingly would give:

With Me.RecordsetClone
If .RecordCount <> 0 Then .MoveLast
Me.RecCount = Me.CurrentRecord & " of " & .RecordCount
End With

Does that work?
 
Well, I thought I was ok, but now I see that it shows one less record in the
record counter than what is really there. Once I move to the next record,
the record counter fixes itself, but I know this will be confusing to the
user. Any idea how to correct this? Thanks!
 
sg said:
Well, I thought I was ok, but now I see that it shows one less record in
the
record counter than what is really there. Once I move to the next record,
the record counter fixes itself, but I know this will be confusing to the
user. Any idea how to correct this? Thanks!
Click to expand...

Are you sure that it's not just the fact that you're on a new record that is
throwing you off? The RecordsetClone's RecordCount should show you the
records that have actually been saved, but when you move to a new blank
record, Access's CurrentRecord counts that blank record, even before it has
been saved.

If that's the problem, you can approach it a couple of ways. For example,
you can have the RecCount text box show "(new record)" instead of "n of m",
using code like this:

If Me.NewRecord Then
Me.RecCount = "(new record)"
Else
With Me.RecordsetClone
If .RecordCount <> 0 Then .MoveLast
Me.RecCount = Me.CurrentRecord & " of " & .RecordCount
End With
End If

Or you can make it behave like the built-in navigation buttons by (1)
removing the code from the form's Current event, (2) adding this code code
to the form's Load event:

Private Sub Form_Load()

With Me.RecordsetClone
If .RecordCount <> 0 Then .MoveLast
End With

End Sub

.... and (3) and changing RecCount to a calculated text box with this
ControlSource expression:

=[CurrentRecord] & " of " & RecordsetClone.RecordCount+Abs([NewRecord])

(Note: the above expression was entered all on one line, though the
newsreader may have broken it onto multiple lines.)
 
Thanks again for your quick response. I'll test out a couple of things and
see what's going on and what works best.

Dirk Goldgar said:
sg said:
Well, I thought I was ok, but now I see that it shows one less record in
the
record counter than what is really there. Once I move to the next record,
the record counter fixes itself, but I know this will be confusing to the
user. Any idea how to correct this? Thanks!
Click to expand...

Are you sure that it's not just the fact that you're on a new record that is
throwing you off? The RecordsetClone's RecordCount should show you the
records that have actually been saved, but when you move to a new blank
record, Access's CurrentRecord counts that blank record, even before it has
been saved.

If that's the problem, you can approach it a couple of ways. For example,
you can have the RecCount text box show "(new record)" instead of "n of m",
using code like this:

If Me.NewRecord Then
Me.RecCount = "(new record)"
Else
With Me.RecordsetClone
If .RecordCount <> 0 Then .MoveLast
Me.RecCount = Me.CurrentRecord & " of " & .RecordCount
End With
End If

Or you can make it behave like the built-in navigation buttons by (1)
removing the code from the form's Current event, (2) adding this code code
to the form's Load event:

Private Sub Form_Load()

With Me.RecordsetClone
If .RecordCount <> 0 Then .MoveLast
End With

End Sub

... and (3) and changing RecCount to a calculated text box with this
ControlSource expression:

=[CurrentRecord] & " of " & RecordsetClone.RecordCount+Abs([NewRecord])

(Note: the above expression was entered all on one line, though the
newsreader may have broken it onto multiple lines.)

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
Wanted to let you know that the first option you suggested for me worked
great. Sorry it took so long to reply - I've just now been able to work on
it. Thanks again for all your help.

Dirk Goldgar said:
sg said:
Well, I thought I was ok, but now I see that it shows one less record in
the
record counter than what is really there. Once I move to the next record,
the record counter fixes itself, but I know this will be confusing to the
user. Any idea how to correct this? Thanks!
Click to expand...

Are you sure that it's not just the fact that you're on a new record that is
throwing you off? The RecordsetClone's RecordCount should show you the
records that have actually been saved, but when you move to a new blank
record, Access's CurrentRecord counts that blank record, even before it has
been saved.

If that's the problem, you can approach it a couple of ways. For example,
you can have the RecCount text box show "(new record)" instead of "n of m",
using code like this:

If Me.NewRecord Then
Me.RecCount = "(new record)"
Else
With Me.RecordsetClone
If .RecordCount <> 0 Then .MoveLast
Me.RecCount = Me.CurrentRecord & " of " & .RecordCount
End With
End If

Or you can make it behave like the built-in navigation buttons by (1)
removing the code from the form's Current event, (2) adding this code code
to the form's Load event:

Private Sub Form_Load()

With Me.RecordsetClone
If .RecordCount <> 0 Then .MoveLast
End With

End Sub

... and (3) and changing RecCount to a calculated text box with this
ControlSource expression:

=[CurrentRecord] & " of " & RecordsetClone.RecordCount+Abs([NewRecord])

(Note: the above expression was entered all on one line, though the
newsreader may have broken it onto multiple lines.)

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
Back
Top