Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

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

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Coniditionally expand subdatasheet in form

jcfraun (TechnicalUser)
2 Jul 10 7:00
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?
misscrf (TechnicalUser)
2 Jul 10 10:12
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.

misscrf

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

missinglinq (Programmer)
2 Jul 10 10:34

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!

misscrf (TechnicalUser)
2 Jul 10 10:36
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.

misscrf

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

MajP (TechnicalUser)
6 Jul 10 12:42
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

CODE

Private Sub Form_Current()
  ExpandRecord
End Sub

Public Sub ExpandRecord()
  'Collapse all records first
  Me.SubdatasheetExpanded = False
  'Set focus to a control
  Me.OrderID.SetFocus
  '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

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