Determining number of subform records 1

[whoschad]

Ladies and gentlemen:

I have a form with a continuous subform linked to it which contains two controls, side by side. What I would like for Access to do is to not display any scroll bars on the subform unless the subform contains over 8 records linked to the particular record the main form is displaying (in which case vertical scroll bars should show up).
What I have set up right now is this code under the Main Form’s ‘Current’ event (abridged):

strSQL = "SELECT subrecord FROM subtable WHERE id = " & Nz(Me.txtTextbox.Value, 0)
        
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
        
If rs.RecordCount > 8 Then ...

It works okay, but my question is: Do I need to run the extra query at all, or is there an easier way of determining how many records a subform contains (a property or something)? It seems to me that Access already ran a similar query to determine the records to show, and I’m just doing it again.

BONUS QUESTION: Is there a way to add vertical scroll bars to a subform by using VBA code in the main form? There doesn’t appear to be.

This code:

Me![Subform].ScrollBars = 2

Does not work in the main form, but if you place it in the subform like this:

Me.ScrollBars = 2

It does work. It’s not that big a deal to me, because I just have the main form call a procedure in the subform, but I’d kinda like to have it all in the main form. Is this possible?

Thanks for the time.

 

[PHV]

Perhaps something like this ?
Me![Subform control name].Form.ScrollBars = 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[whoschad]

Thanks PHV. I tried your correct syntax, but it still didn't work. I suppose I'll content myself with calling the procedure in the subform. I'm running Access 2003 on Windows XP Pro if that makes any difference.

Any thoughts on my other question?

 

[whoschad]

PHV,

I tried your code again and it did work. I don't know what I was thinking. Thanks.

Also, instead of using my long code in the beginning, I just used the Subform's recordset object. My problem was not putting ".form" after my subform name.

Go figure.