Select which subform is shown as subdatasheet (1 Viewer)

[warp123]

Hello. I have the following issue and although I searched around for a solution it seems that I cannot find a working one.

The situation is like this.

I have a main form which has a subform (sub1) in datasheet view. Sub1 has 2 underlying subforms (sub2 and sub3).

When sub1 is in datasheet view it presents the underlying sub2 subform as a sudayasheet with plus sign on the left side by default.

I would like to be able to programmatically choose which one from sub2 and sub3 would be the subdatasheet, because now only sub2 is presented. It doesn't seem to have any property corresponding to the subdatasheet object name in order to assign it at run time so I am stuck there.

Any thoughts or solutions would be much appreciated..

 

[theDBguy]

Hi. Welcome to AWF!

I see this is your first post.

I believe the subdatasheet is dictated by the table's established relationship.

 

[warp123]

Correct. Both subforms sub2 and sub3 are linked with the same field with the parent sub1. They just present different details of the parent record.

 

[theDBguy]

Correct. Both subforms sub2 and sub3 are linked with the same field with the parent sub1. They just present different details of the parent record.

Can you post a screenshot of your form showing the subdatasheets open?

 

[warp123]

Can you post a screenshot of your form showing the subdatasheets open?

I am afraid I cannot post a screenshot at the moment because of non disclosure agreements I am involved with.

The case is as described above. One form, which contains 2 other subforms. When the main form is in form view the two subforms that are in datasheet view can be handled easily by seting the visible property to false accordingly.

If the parent form has to be in datasheet view, the first subform is presented as a subdatasheet, but I don't have any way to choose which one to present when hitting the plus sign.

Any help is appreciated.

 

[gemma-the-husky]

Can you not manage the display manually?
Is a sub-datasheet not simply a child form displayed as a datasheet?

design a mainform, and a subform, and link them based on the mutual PK/FK
Then you could either show both subforms simultaneously, or provide a button to flick from one to the other.

 

[warp123]

Can you not manage the display manually?
Is a sub-datasheet not simply a child form displayed as a datasheet?

design a mainform, and a subform, and link them based on the mutual PK/FK
Then you could either show both subforms simultaneously, or provide a button to flick from one to the other.

Yes the two subforms are child forms inside the main form, connected with the same Parent RecordID. When in form view everything works fine. When the parent form is in Datasheet view only the first child form is presented in the expand plus sign.

I havent found any way to programaticaly choose which one to present, according my needs.

When you say to manage the display manually, how do you propose to implement this?

Up until now I have come to the conclusion that access presents the subform according the Tab value. I shows the first ine that sees

 

[gemma-the-husky]

you have a master form
you have subform1 and subform2.
you link subfrom1 and subform2 to the master form.

As you select an item in the master form, the subforms automatically show the related data. A lot of us actually turn off the subdatasheets because you don't get enough control with them.

Although you can't automatically link a continuous master form to a subform, you can manually build the link quite easily.

You could put the whole lot inside a container form and add buttons to show/hide each subform. It's just a matter of designing the appearance you want. You might need some buttons, as you won't get them on a datasheet, but maybe you would be OK with all forms always visible.

 

[warp123]

you have a master form
you have subform1 and subform2.
you link subfrom1 and subform2 to the master form.

As you select an item in the master form, the subforms automatically show the related data. A lot of us actually turn off the subdatasheets because you don't get enough control with them.

Although you can't automatically link a continuous master form to a subform, you can manually build the link quite easily.

You could put the whole lot inside a container form and add buttons to show/hide each subform. It's just a matter of designing the appearance you want. You might need some buttons, as you won't get them on a datasheet, but maybe you would be OK with all forms always visible.

I am pretty familiar with connecting master form with child forms. This is not my problem in this case.

As I said the master form is not a continuous form. Is in datasheet view also, as well as the 2 subforms.

So in datasheet view access presents the plus sign on the left of the master form records which when pressed expands and shows the related records of the first subform only. There is no way that the plus sign expands to show 2 subforms.

 

[gemma-the-husky]

Yes, but you can easily design the form (data sheet) you want to show, and just add a command button to display it.

 

[warp123]

Yes, but you can easily design the form (data sheet) you want to show, and just add a command button to display it.

In which form would that button be?

 

[gemma-the-husky]

As you can't have a button on a data sheet, you could put the datasheets inside a container form and have the button there. You could include the options on the ribbon. You might be able to double click a row/cell on the datasheet (not sure whether you have events in a datasheet offhand). You could use a continuous from instead of a datasheet. You might put the subforms on pages of a tab control. Screen real estate is a precious commodity.

Maybe others can offer other thoughts

I do this with a little invoice management app I have.

I have a container form, with assorted command buttons.

On the left I have a datasheet of clients.
On the right I have two more data sheets - One on the RHS, top half is a contacts form, and one on the RHS, bottom half is an invoices form.
As I navigate to different clients, the (sub) forms on the right hand side automatically update.

I then have buttons on the LHS to open a normal form to Add A New Client or Edit A Client.
I also have buttons on the RHS to create a New Invoice, or Edit An Existing Invoice.
etc

 

[MajP]

The subdatasheetname property is a user created property of a tabledef or querydef. If it does not exist you must first create it. See example. Then you can programmatically change it

Change MS Access 2010 subdatasheet name default to [NONE]

Is there a way to set the default value for tables "Subdatasheet Name" to [None] instead of [Auto]?

stackoverflow.com

 

[warp123]

As you can't have a button on a data sheet, you could put the datasheets inside a container form and have the button there. You could include the options on the ribbon. You might be able to double click a row/cell on the datasheet (not sure whether you have events in a datasheet offhand). You could use a continuous from instead of a datasheet. You might put the subforms on pages of a tab control. Screen real estate is a precious commodity.

Maybe others can offer other thoughts

I do this with a little invoice management app I have.

I have a container form, with assorted command buttons.

On the left I have a datasheet of clients.
On the right I have two more data sheets - One on the RHS, top half is a contacts form, and one on the RHS, bottom half is an invoices form.
As I navigate to different clients, the (sub) forms on the right hand side automatically update.

I then have buttons on the LHS to open a normal form to Add A New Client or Edit A Client.
I also have buttons on the RHS to create a New Invoice, or Edit An Existing Invoice.
etc

The project I am working on is something similar and in fact is an invoicing app. The only way I found as a workaround is something similar with the one you describe. Putting the 3 datasheet forms in a container form and linking the subforms with an unbound textbox which has a data source a field from the main datasheet.

As I said it's just a workaround, and still I am wondering how could someone control which subform is presented as a subdatasheet inside the main datasheet.

 

[MajP]

am wondering how could someone control which subform is presented as a subdatasheet inside the main datasheet.

Did you not understand my explanation?

 

[ebs17]

In professional applications, the subdatasheet property is turned off because it slows down performance. Not actual subforms are a workaround, but the Subdatasheet property is, because it's only useful for simple, non-demanding applications.

These SubDatasheets are not forms, but direct access to tables with all associated drives, as if you were working directly in tables.

 

[gemma-the-husky]

In the clients form, I have this code in the current event.
I store the active client in a public variable, which I use in the query for the subforms.
I now know I could have used a couple of other linking methods, but this worked well enough. This was originally built over 10 years ago.

Private Sub Form_Current()
With gblData
    .gblselectedclient = ClientID 'stores the current ID
End With

Parent!sub_Contacts.Requery
Parent!sub_jobs.Requery
End Sub