RecordsetClone.RecordCount producing Invalid Argument error

[Seth Schrock]

I'm trying to get the record count of a subform. Based on what I found online, I should be able to use the RecordsetClone.RecordCount property to get this value. However, I get error 3001, Invalid argument when I try.

The following code works in the immediate window:

?me.sfrmHosts.Form.Name
sfrmHosts

This code fails with the above error:

?me.sfrmHosts.Form.RecordsetClone.RecordCount

????????
(Hair being pulled out)

I originally tried with .Recordset.RecordCount, but I got the same error with that. What is wrong?

[twinnyfo]

Seth,

Try the following:

    Dim rstClone As Recordset
    Set rstClone = Me.sfrmHosts.Form.RecordsetClone
    rstClone.MoveLast
    Debug.Print rstClone.RecordCount

You have to declare the recordsetclone first.

[Seth Schrock]

I now fail on the

Set rst = Me.sfrmHosts.Form.RecordsetClone

line with the same error, Invalid Argument.

[twinnyfo]

Seth,

Where are you putting the code for this count? I just created a form/subform and it worked perfectly. I have it in the OnCurrent Event of the main form.

Does the Subform currently have a record source (I know, dumb question). This just seems strange.

Worst case, re-create the forms and copy and paste all the controls and code. Sometimes kick-starting from scratch will give different results--you know how finicky Access can be....

[Seth Schrock]

I have a combo box in the header of the parent form. I use it's After_Update event to run this code. The subform does have a record source (a querydef).

I'll give starting from scratch a try. Unfortunately this is a complicated form with lots of code to move :(

[Seth Schrock]

After starting over, I'm now getting a different error on the same line of code: Object variable or With block variable not set. I'm not using any With/End With, nor is there a variable being used. I tried it both with setting the Recordset variable and without and neither worked. This is so strange.

[twinnyfo]

Try this, jsut to see if we are looking in the wrong place.

Create a new form. Create another form (subform) which has a table as a record source. Don't make it fancy.

Recreate our current situation and see if the code will work. According to theory, whether you have a Table or a QueryDef, the RecordsetClone should work. But, let's see if this will work with a table. Then, maybe we can see some other light at the end of tht tunnel.

[Seth Schrock]

I created the simplest form/subform that I could with a button to trigger the code.

MsgBox Me.Hosts.Form.Recordset.RecordCount

I tried with both Parent and Child forms based on tables and on QueryDefs. Both tries worked perfectly. I guess I'm just going to have to start completely over without copying anything over and try it that way. It probably wouldn't hurt to rethink what I'm doing anyway.

So strange.

[twinnyfo]

Have you also tried with the RcordsetClone? (although you've just proven that the clone is not required.

I feel your pain. Obviously there is "something" going on in your form/subform that doesn't like the record counting. I am sure you have must less hair left by now....

:-/

[Seth Schrock]

I just tried with the RecordsetClone and it worked with the QueryDefs.

Thankfully, pulling on my hair didn't cause any to come out so I have the same number right now. We will see about later.......

[twinnyfo]

Any ideas what the issue was? Very strange....

[Seth Schrock]

I'm just guessing that the main form is corrupted. I was still using the simple test form, so I don't have anything definitive.