Check number of subform records

[Jeff]

Hi All,

I have some code to check how many records a subform (in datasheet view)
has. The subform has a query as a source and master-child fields to link
to the mainform.

On the mainform I have a textbox that receives the number of records
from the code (On current event of subform).

The code works ok except when there are no records. The textbox shows
the same number as for the last mainform record.

Any ideas greatly appreciated,

Jeff C.

Code:

Set rsd = Me.Form.RecordsetClone

With rsd
.MoveLast
Rc = .RecordCount

If Rc > 0 Then
Me.Parent.Form.RecNo = rsd.RecordCount
'MsgBox Rc
Else
Me.Parent.Form.RecNo = "NR"
MsgBox Me.RecordsetClone.RecordCount
End If
End With
rsd.Close
Set rsd = Nothing

 

[TC]

AFAIK the MoveLast should fail with an error if the recordset does not
have any records. Not sure why that doesn't happen for you.

Here's what I'd do:

with Me.Form.RecordsetClone
if .bof and .eof
Me.Parent.Form.RecNo = "NR"
else
.movelast
Me.Parent.Form.RecNo = .RecordCount
endif
end with

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Jeff]

AFAIK the MoveLast should fail with an error if the recordset does not
have any records. Not sure why that doesn't happen for you.

Here's what I'd do:

with Me.Form.RecordsetClone
if .bof and .eof
Me.Parent.Form.RecNo = "NR"
else
.movelast
Me.Parent.Form.RecNo = .RecordCount
endif
end with

HTH,
TC (MVP Access)
http://tc2.atspace.com

Thanks, I've tried that, but no go!
I tried recreating the query using sql and it still won't work!

Thanks anyway,

Jeff C

 

[Jeff]

Thanks, I figured it out.

I tried putting it in all the events on the subform with no luck. So I
moved the code to the mainform's oncurrent event and bingo!!!

Could anyone explain why it didn't work on the subform when the
recordset is the same, only referenced from a different place?

Thanks

Jeff

 

[TC]

Looking at it again. I would normally say me.recordsetclone, not
me.form.recordsetclone. Not sure (off hand) if that would make any
difference.

Here's my suggestion: you haven't really solved the problem! - even
though you've got it working. Solving the problem would mean, finding
out why it did not work before, then fixing that (specifically). So
personally, I would move the code back where it was before, then do
some debugging to find out why it's not working. For example, if you
temporarily replace the textbox statements, with msgbox statements,
you'd soon see what was executing, & what was not.

I always advise: find out what is causing the error, then fix it. Do
not just make changes "on spec" - even if that fixes it!

HTH,
TC (MVP Access)
http://tc.atspace.com

 

[Jeff]

Looking at it again. I would normally say me.recordsetclone, not
me.form.recordsetclone. Not sure (off hand) if that would make any
difference.

Here's my suggestion: you haven't really solved the problem! - even
though you've got it working. Solving the problem would mean, finding
out why it did not work before, then fixing that (specifically). So
personally, I would move the code back where it was before, then do
some debugging to find out why it's not working. For example, if you
temporarily replace the textbox statements, with msgbox statements,
you'd soon see what was executing, & what was not.

I always advise: find out what is causing the error, then fix it. Do
not just make changes "on spec" - even if that fixes it!

HTH,
TC (MVP Access)
http://tc.atspace.com

I agree completely.

I have used the 'quickfix' solution and it's working.

I have copied the forms and am currently working on the original setup
to figure out why it wasn't working. Thanks for the advice and the
help. I'm still on the curvy part of the learning curve!

Have a great week,

Jeff C

 

[TC]

No probs. I like your viewpoint on the curvy part!

Post back here if you can't get the original code to work. I would,
however, start by changing me.form.recordsetclone to
me.recordsetclone.

Cheers,
TC (MVP Access)
http://tc2.atspace.com