Datasheet Forms & Subforms?

[djclinton15]

I have many Datasheet Forms that has Datasheet Forms within them. The issue I’m having is the I wish for certain Datasheet Forms to open up with the sub-forms collapse, while I would want other Datasheet Forms to open up with the sub-forms expanded. Now, I’m sure many will suggest for me to close out sub-form I want expanded leave the sub-form expanded when closed. And, close out sub-form I want collapse leave the sub-form collapse when closed. Are there reasons why some sub-forms opens up collapse & other sub-forms will open expanded?

Any advice/suggestion would be greatly appreciated! Thanks in advance!

[John_G]

I don't know what you mean by "collapse" - Access doesn't have such a feature - but what you can do is make the main form control containing the subform visible or not visible as required. Would that work for you?

[jwhite]

Is it possible you are opening a table directly, and at top left of that table clicking the "+" button to open the related records to that table? If that is the case, table datasheets and subdatasheets are not designed for complex data-entry. You need to design a form with subforms for your data-entry. See http://allenbrowne.com/bug-09.html for a detailed review of this "feature" and how to remove the subdatasheets.

[Missinglinq]

This doesn't only apply to opening Tables, directly...when you have Subforms on Datasheet View Forms, the Record on the MainForm appear with a (+) (Plus Sign). To view the Subform(s) for a given Record you click on the (+). In order for all of the Subforms to be dropped down when your MainForm opens, in the MainForm use this code

Private Sub Form_Load()
  Me.SubdatasheetExpanded = True
End Sub

To have them appear 'collapsed,' set it as

Me.SubdatasheetExpanded = False

Linq ;0)>

[djclinton15]

Thanks to everyone for responding to my thread!

Ling, much thanks! You understood & provided with exactly what I was looking for.

[CJ_London]

As a side comment, the + only appears on the parent when a one to many relationship (i.e. enforce referential integrity) has been defined. And if a parent has more than one child table, only one table can be shown, this can be changed by manually editing the Subdatasheet Name property of a query or with code in a datasheet form

[djclinton15]

Private Sub Form_Load()
Me.SubdatasheetExpanded = True
End Sub
Me.SubdatasheetExpanded = False

The above code resolved my issue, however now it prohibits me to have the For open in a particular field. This code no longer works. I get this error message saying, ‘The expression you entered in the property setting produced the following error message: :Ambiguous name detected: Form_Load”
Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
Me.ArtistsIDcbx.SetFocus
End Sub

[davegri]

I think that's trying to tell you that you have more than one sub named Form_Load in the same scope (form).

[Missinglinq]

That's exactly the problem...you can only have one sub named Form_Load! You have to put all of your code inside a single Form_Load:

Private Sub Form_Load()
  Me.SubdatasheetExpanded = True
  DoCmd.GoToRecord , , acNewRec
  Me.ArtistsIDcbx.SetFocus
End Sub

Be sure to delete the second sub!

Linq ;0)>

[djclinton15]

ling, this code worked perfectly for 5-days until today. I don't understand nor can figure out what (if any) changes to disrupt the code. This has me so mad & frustrated!

Private Sub Form_Load()
Me.SubdatasheetExpanded = True
DoCmd.GoToRecord , , acNewRec
Me.ArtistsIDcbx.SetFocus
End Sub

[John_G]

What line does the code fail on and what is the error message? I see nothing obvious in the code.