Subsubform -- How do I programmatically show and hide fields?

[MarkK]

FWIW, it's unusual to have a subform on a subform on a subform. When you look at actual workflow in a computer system, a user typically performs a task, usually repetitively, like entering a new order, that is most commonly concerned with a thing and its first level of child related things. An Order has OrderDetails, a Class has Students, a Company has Employees, a Job might have JobSections, or JobExpenses.

Once you drill down to the third level, make a new main form. So yeah, a Student has many classes, but if the main form is Class, and the subform is Student, don't add Classes as a nested sub-sub-form of the student subform. Make a Student main form with Classes subform. See what I mean? It's a different workflow. Give it a different interface.

That third level is too deep. A bridge too far. IMO.

 

[painterz]

Hi Mark,

Thanks for your insight. I need to see all of the info at once. My main form is address (this is a neighborhood database), my subform is family (this is new, since we are getting multiple families per address), my sub-subform is people (which used to be at the subform level. I need to show the people belonging to each family). In addition to the sub-subform People (I actually called this Neighbors when I first started but People is more fitting) there are two additional subforms to this sub-subform--one for phone numbers and the other for notes.

I've been out of this for a long time. I can't see any other way for me to display all of this on one screen other than nesting forms. I thought the tab control would have been the slicker solution and tried it but since it didn't have a record source or master/child links I ditched it. Each tab would have represented a family. The subform on the tab would have been the people within the family.

Do you have any ideas why my sub-subform won't load? The main form is set to single form, the subform is set to datasheet, and the sub-subform is set to single form. All are set to visible yet I'm not able to see my sub-subform when I load my forms.

Thanks

 

[gemma-the-husky]

OK. Here's an example

Check the code on the two buttons on the main form

I assume this is what you are trying to do.
Open frmCustomer

The buttons on this form change a property of two fields on the sub sub form.

note that the expression setting the reference to the subform uses the names of the sub form controls (suborders and suborderlines) and not the names of the forms themselves.

I set the frm reference, and used with syntax, for ease, but I could have coded the whole thing directly.

 

[painterz]

Hi Dave,

Your example is basically what I'm looking for. CJ, MarkK, and JDraw got me around the code and I think it worked but I can't see my sub-subform. Thank you for showing me the correct syntax for the WITH statement!

Working with your subform, I set it to datasheet view, and the sub-subform no longer showed. So now I know what my problem is but I liked the look of the datasheet. Since you're okay with nesting forms, do you know how I can make my first subform look like a datasheet and not a form. The Family subform (first subform) should list all of the family names and nothing else so I really don't need a form.

Thank you!

 

[CJ_London]

datasheets and continuous forms can't have subforms although the system can be tricked for the latter.

consider having an unbound main form with a datasheet subform (a bit like the split form access provides).

To link from the datasheet subform to your other subforms have a hidden unbound text control on the main form, populated by code in the datasheet current event and referenced in the other subform(s) linkmaster property

 

[painterz]

??? Sorry CJ, it's already past wine o'clock here and I'm a little lost with your very elegant solution. I'm going to have to break it down. The unbound main form and unbound text control are throwing me off. I'll give it a go and let you know.

Thanks!

 

[painterz]

Hi CJ,

I can't get my head around this. My main form is for the Address. It links to the Family subform on AddressID. The Family subform links to the People sub-subform on FamilyID.

If I make my main form unbound, how do I get my AddressID to link to the subform? I'm sure you've figured out already, I'm extremely rusty on my coding.

My current solution was to change the subform from datasheet to single form and use a list box to show the families. The list box is based on a query with the criteria of the AddressID. However, I'm having trouble linking to the FamilyID in the query results. It's the first column and is what my list box is bound to. I don't seem to be able to change the sub-subform to the next family since I can't click on it.

Am I even on the right track? I think clarity has left me tonight.

Thanks!

 

[CJ_London]

If I make my main form unbound, how do I get my AddressID to link to the subform?

reread my post, last paragraph

 

[painterz]

Hi CJ,

If I understand your last paragraph correctly, the datasheet (subform) would be driving the main and sub-subform. The Main form needs to be the driver of everything. The main form is the address and the address is at the top of the hierarchy.

If I've misunderstood, would you please break it down for me?

Thanks!

 

[CJ_London]

what version of access are you using?

Also, we have reached the stage where I can no longer guess what it is you are trying to achieve.

Please supply a screenshot of your relationships, and a clear and simple description of a) what you are trying to do (e.g. user selects a value from a combobox called helloworld and these fields (a,b,c) from these tables (1,2,3) is displayed on the form) and b) some example data from each of the tables that are referenced.

Please do not submit an example db, if it can't be described, it can't be done

 

[painterz]

Hi CJ,

I'm using Access 2007. I've attached the screen shot of my nested forms.

I have a db of a neighborhood--so the address drives the db. Usually one family lives at the address but recently I have multiple families at the same address.

My new form should show the address, the families at the address, and allow me to update, add, or delete the people living within the family.

I'm currently working on listbox in my Family subform (master/child = AddressID) based on a query of families with the AddressID from the current record. I want to use the list box to pass the FamilyID to sub-subform People as the master/child link. I haven't had success and apparently my listbox needs to be based on an updatable query. I just want to grab the FamilyID of the family name the user has chosen so I can display the people.

Now, after explaining all of this--I'm not sure how I add a family when a house is sold. I guess it would be a pop up screen with the basic info requested--address, first and last name. I'll work on that once I get this working.

Thanks!

 

[painterz]

To link from the datasheet subform to your other subforms have a hidden unbound text control on the main form, populated by code in the datasheet current event and referenced in the other subform(s) linkmaster property

Hi CJ,

I'm still having trouble w my list box. I revisited your earlier post and have a question. Is the sub-subform located in the datasheet subform? Since a datasheet can't have a subform, I'm assuming it's off to the side and set with a a master/child link to the unbound text box.

If that's the case, I've already done something similar and I ditched it bc I had the same problem of when a new family moves into a house. When I posted this issue another user made a suggestion and that was what led me to the nested subforms.

At this point I'm stuck.

Thanks

 

[MarkK]

when a new family moves into a house.

What is the purpose of your system? Do you deliver social services to families or civil services to addresses? I don't understand your overall purpose.

 

[CJ_London]

Sorry, can't help unless you provide the info requested

Please supply a screenshot of your relationships, and a clear and simple description of a) what you are trying to do (e.g. user selects a value from a combobox called helloworld and these fields (a,b,c) from these tables (1,2,3) is displayed on the form) and b) some example data from each of the tables that are referenced.

the screen shot shows where you want to end up - a bit like saying 'here's picture of a spaceship - how do I build one?'

 

[painterz]

Hi Mark,

I keep the records for the HOA of two neighborhoods. The records are used for the neighborhood directory, distribution of the neighborhood newsletter which includes announcing children's milestones (with their parents permission), and also teen jobs. The records are also used for the Board for pool tag registration, proxies, and emergency contact. To provide this info, I have to keep track of who lives at the address.

I don't release any info beyond the neighborhood and the data is for the neighborhood's use only. Beyond public info, I don't collect any private information and the residents choose what they want listed in the directory, if anything.

Thanks

 

[painterz]

Hi CJ,

I apologize, I thought I'd given the info requested since the db wasn't requested. I've attached the screen shot and the underlying query for the Neighbors (People) subform. I have no relationships defined among the tables but I've included the primary tables.

For the user, I want to be able to see the people within the family at an address and update/add/delete any info. I'll add a button to delete the people/family and to add people/family. I search using a double click function on the address, first name, last name, and phone number. Since a screenshot is a snapshot, I don't know how to show a sample of the data if the db isn't wanted. How do I provide a sample of the data?

Thanks

 

[CJ_London]

How do I provide a sample of the data?

data is usually confidential, so make it up unless you are happy to provide live data - easiest way is to put some in excel, one worksheet per table

 

[painterz]

Wouldn't it be easier for me to attach my test db? The data is already made up.

 

[painterz]

I've got 9 tables plus the underlying queries for the forms, subforms, and combo boxes. I'v attached my db.

I have tried using a datasheet outside of the subform but I wasn't able to pass the FKs to tblFamily (Neighborhood Input Form).

I'm currently working with Copy of Neighborhood Input Form, which is what will load when the db is opened. My list box for the Family subform (single form view) is based on an updatable query but I'm not able to select different families and I'm not able to update the people in the family I can see.

 

[jdraw]

painterz,

For clarity:

How exactly are tblAddress, tblFamily, tblPeople, tblPhone and tblNotes related in your "business"?

A description of each of these entities in simple terms would help. Just enough detail/info to clearly distinguish one entity from all others -- eg. People from a Family.

For example, some detail/rationalization of these may help

A Family may consist of 1 or more People
A People(person) is part of 1 or more Family(s) (or only 1 Family??)
A People(Person) lives at 1 and only 1 Address (???)
A Phone is associated to 1 or more People(person)
A Note can relate to 0,1 or many People
A Note can relate to 0,1 or many Family(s)
A Note can relate to 0,1 or many Addresses????