recordcount problem

[Savvoulidis Iordanis]

I use the following function to get the recordcount of a form/subform.

Public Function FRMRecordCount(afrm As Form) As Long
Dim rs As DAO.Recordset

FRMRecordCount = 0
If IsNull(afrm) Or IsNull(afrm.Recordset) Then Exit Function
afrm.RecordsetClone.MoveLast
FRMRecordCount = afrm.RecordsetClone.RecordCount
End Function

At first, it seems OK, but sometimes it does not work correctly.

When I call SQL commands that alter (add/delete) the underlying contents of
the form/subform, then after doing a requery on the form/subform and then
run the above function, I get the message: '3420: Object invalid or no
longer set'
on line :
afrm.RecordsetClone.MoveLast

Doesn't the requery reload the data based on the current recordsource of the
form, and if yes, then why the function crashes?
I call the function like this:

If FRMRecordCount(f1_subform.Form) = 0 Then
...
end if

where f1_subform is a subform control on the main form.

 

[Allen Browne]

Null applies to fields, not to forms or recordsets.

With DAO recordsets, you can just use this:


Public Function FRMRecordCount(afrm As Form) As Long
Dim rs As DAO.Recordset

Set rs = afrm.RecordsetClone

If rs.RecordCount > 0 Then
rs.MoveLast
FRMRecordCount = rs.RecordCount
End If

Set rs = Nothing
End Function

 

[Savvoulidis Iordanis]

Thanks for the reply Allen. I'll try it immediatelly

 

[Savvoulidis Iordanis]

Tried it. It doesn't work OK! In fact it works as I describe in my own
function.

1. First I use it on a subform. FRMRecordCount(Sub1.form). Works OK here.
2. Then I use some other function that executes SQL update/delete on the
data in Sub1 control
3. I requery the subform (tried 2 ways: Sub1.form.requery, and
Sub1.form.recordsource=Sub1.form.recordsource) to get a new set of data
4. When I try the function again, I get the message 'Run-time error 3420:
Object invalid or no longer set.'

I'am very disappointed. It seems I'm stuck for ever. I certainly want to use
this function, in order to be certain if a subform has records displayed
inside it, before I do other stuff (At least get a .currentrecord>0, but in
order to run .currentrecord you need at least 1 record, else you get a
runtime error!)
Is there another way to get the record count inside the subform at any time?
Even after a clean requery? Is it maybe a bug and is solved after applying
an access patch? I use A2000 (9.0.2720) and don't want to upgrade at this
particular moment.

TIA

 

[Max Smart]

Try this...

Dim intRecCount as Integer
intRecCount = Forms![MainForm]![Subform].Form.RecordsetClone.RecordCount

Max

 

[Allen Browne]

Hi. The MoveLast on the form's RecordsetClone should give the correct count.

Are these Access tables?
Or are they attached tables from SQL Server or Oracle or something else?
If they are Access tables, it should work.

It is quite possible that the service patches are a problem. The original
release of Access 2000 was a complete nightmare of interacting bugs, so if
you don't have at least Service Pack 1a, you are really on your own. I can't
think of a good reason not to apply SP3 for A2000 which at least makes the
software usable.

Even more importantly, you need to get the service pack for JET, the data
engine in Access. SP8 for JET 4 is available in the Downloads section at
support.microsoft.com

 

[Savvoulidis Iordanis]

They are access linked tables.
I downloaded office service pack 3, but when I run it, it displayed:

"The expected version of the product was not found on your system."

although I have installed Office 2000 Professional.
Should I first apply previous SPs or not?

Also, is there a difference berween calling sbfrm1.Requery and
sbfrm.Form.Requery?
Is the .requery the same as reapplying the current form's recordset property
on to itself?(sbfrm.form.recordsource=sbfrm.form.recordsource)

After the above requery, if I call the FRMRecordCount(sbfrm.form) function,
shouldn't I get the correct result again? (this is the problem I told you
about, after an underground SQL delete/update/insert on the sbfrm's data)

TIA

 

[Allen Browne]

You do need to apply at least SP1a to Office 2000 before you can apply SP3.
IIRC, you do not need to apply SP2.

Requerying the subform control should achieve the same result as requerying
the form within the control, but I can't guarantee the results will be the
same across all versions of Access.

Reassigning the RecordSource of a form will effectively achieve a requery,
but it could have other effects as well.

Neither requerying nor reassigning the recordsource should give you any
problem with the RecordCount though. Don't forget to apply the JET 4 SP8 as
well.

 

[Savvoulidis Iordanis]

Getting right down to it... Thanks

 

[Savvoulidis Iordanis]

No luck :-(

I applied the SP1a patch and then the SP3 patch, but nothing changed.

 

[Allen Browne]

Not sure what else to suggest. I don't see that behavior here.

 

[Savvoulidis Iordanis]

Thanks for your fast reply Allen.
I'm giving a little more eplanation about how my form is working:

I have the main form (unbound), which includes a subform (the master) and 3
other subforms below (the details).

In the master subform, I call a public form sub in the parent (the unbound)
form, to simulate the Form_Current event, when I change row in the master
subform. In this sub, I fill the detail subforms manually (everything is
done manually in my projects. I do not use Access's feature with linked
fields. I always execute sql to fill the subforms with the correct data. I
don't like getting all the data from the network and do filtering on the
client. Anyway the linked fields feature is not applicable between
subforms.).
Now, after I execute action sql on all subform's data, then I call requery
on all subforms to be sure everything is up to date.
Right after this, if I call FRMRecordCount, or just change row in the master
subform (which calls the public form sub), I get the error.
Even when I break inside FRMRecordCount, the recordset variable is empty
(object not set), although FRMRecordCount is called in the beginning and
works OK. Perhaps, it's something wrong in the sequence of the function
calls, but I don't really know.

If you want any more info, on anything please tell me so.

TIA

 

[Savvoulidis Iordanis]

Also, how can I get the real SP1a file?
What I downloaded from Microsoft, was a file download utility, which
downloaded the patch and then deleted it, after applying it.
I don't have it if I need it again (for my laptop).
TIA

 

[Allen Browne]

Info on how to download the fully downloadable update:
http://support.microsoft.com/default.aspx?kbid=276367

 

[Allen Browne]

As I said, I'm not sure what else to suggest.
You could try a DoEvents to let it catch up before the movelast.
Will have to leave it with you from here.

 

[Savvoulidis Iordanis]

Ok Allen,
I can understand how can some cases (like this) make get you tired, but in
case you are still interested, I noticed that the problem occures only after
a underground SQL delete on the subform's records. The form's recordset
seems unspecified, if I run FRMRecordCount afterwards. If I only insert or
update, then after the requery, if I run FRMRecordCount, there is no
problem!

Anyway, thanks again for your attention.