Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Coniditionally expand subdatasheet in form

Coniditionally expand subdatasheet in form

Assume I have a form (Form_x) with a subform (Form_x_subform).  Both are in datasheet view.  They are linked by Field_z.  I only want the subform (i.e., subdatasheet) to expand automatically if it has data in it.  If the subform is empty, I do not want it to expand.  

How can I do this?

RE: Coniditionally expand subdatasheet in form

do you have the detail section set to can grow and can shrink yes?

Click the square box in the design view of the form in the top left corner, on the properties box, go to all and then on the form click on the Detal section.

You can set these values for the form header/footer and detail section.

I set that for most controls as well, but that doesn't have any effect in datasheet view.


It is never too late to become what you could have been ~ George Eliot

RE: Coniditionally expand subdatasheet in form

In point of fact little in the way of conditionally formatting works in Datasheet View, it is what it is.

And Can Grow/Can Shrink, although it appears in Form Design View, doesn't work on forms, only on reports, unless something has changed since version 2003, and I've seen no reports of such.   

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

RE: Coniditionally expand subdatasheet in form

have you consider making it invisible if there are no subrecords?  using the visible property may be a way to help accomplish some of what you want.  Getting into setting dimensions based on the amount of records is always possible, but you would probably need to measure the form size based on every amount of records.

ie. if the record count is 1 then make the form this size, 2 that size and so on.  Could get cumbersome if you have say 1,000 recs lol.


It is never too late to become what you could have been ~ George Eliot

RE: Coniditionally expand subdatasheet in form

I believe everyone misread the OP's post.
The only way I know to do this is using sendkeys. I know of no property to expand a single record.  

This will expand the current record if the current record has subrecords.

Here is an example


Private Sub Form_Current()
End Sub

Public Sub ExpandRecord()
  'Collapse all records first
  Me.SubdatasheetExpanded = False
  'Set focus to a control
  'Check to see if there are child records
  If hasRecords(Nz(Me.OrderID)) Then
    'expand current record
    SendKeys "+^{down}"
  End If
End Sub

Public Function hasRecords(OrderID As Long) As Boolean
  If Not DCount("orderID", "[Order Details]", "OrderID = " & OrderID) = 0 Then
     hasRecords = True
  End If
End Function

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close