Prev: Getting the prev record value
Next: Copy a currency value from one control to another
From: sg on 29 Apr 2010 11:09
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.
From: Dirk Goldgar on 29 Apr 2010 11:43
"sg" <sg(a)discussions.microsoft.com> wrote in message
news:6A20CA4D-478A-40E5-B0E1-5001E00EC229(a)microsoft.com...
>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.


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?

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

(please reply to the newsgroup)

From: sg on 29 Apr 2010 11:50
Perfect! Worked like a charm! Thank you so much for your quick response!

"Dirk Goldgar" wrote:

> "sg" <sg(a)discussions.microsoft.com> wrote in message
> news:6A20CA4D-478A-40E5-B0E1-5001E00EC229(a)microsoft.com...
> >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.
>
>
> 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?
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>
From: sg on 29 Apr 2010 13:56
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" wrote:

> Perfect! Worked like a charm! Thank you so much for your quick response!
>
> "Dirk Goldgar" wrote:
>
> > "sg" <sg(a)discussions.microsoft.com> wrote in message
> > news:6A20CA4D-478A-40E5-B0E1-5001E00EC229(a)microsoft.com...
> > >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.
> >
> >
> > 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?
> >
> > --
> > Dirk Goldgar, MS Access MVP
> > Access tips: www.datagnostics.com/tips.html
> >
> > (please reply to the newsgroup)
> >
From: Dirk Goldgar on 29 Apr 2010 14:38
"sg" <sg(a)discussions.microsoft.com> wrote in message
news:F42FC6B2-D9C7-4734-9699-F689DE1F0213(a)microsoft.com...
> 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!

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)

 |  Next  |  Last
Pages: 1 2
Prev: Getting the prev record value
Next: Copy a currency value from one control to another