Help making a sub datasheet for my database. (1 Viewer)

ConnorGiles

Strange Traveller
Local time
Today, 23:41
Joined
Aug 12, 2014
Messages
1,068
Hey Guys,

New project for next year after my database is implemented into the company.

For easy navigation through my database, I wish to create a sub datasheet in which would be used to sort through companies and their regions to find the site you would be looking for, then ultimately the plot number and the product.

I have never done this before and I have tried searching the forums to find an issue like mine (in terms of finding data which would suite mine) but no luck !

I have attached a Picture to show the basic way I would like this to be seen and searched through my database.

So I wish for it to cascade into separate sub-datasheets depending on which option you pick.

Any ideas on how I would start out achieving this?

And would this be a good idea to implement?

Thanks in advance!

Connor :D
 

Attachments

  • Subdatasheet design..png
    Subdatasheet design..png
    17.2 KB · Views: 58

pr2-eugin

Super Moderator
Local time
Today, 23:41
Joined
Nov 30, 2011
Messages
8,498
If you have tables properly normalized and relationship's established this would work just be very simple.

By simple looks,

One Company - Many Regions
One Region - Many Sites
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:41
Joined
Feb 19, 2013
Messages
12,745
It is a common requirement, surprised you can't find anything

providing your data is structured as one table for each subform simply do the following

1. Create a form for each table in continuous or datasheet view.

2. Create an unbound form and put each of these forms on the mainform as subform.

3. On the unbound form create an unbound text control for each subform (lets call them sf1, sf2 etc) which can then be hidden.

4. For each subform control, set the linkchild property to the field to the name of the field in the subform which links to the previous subform (for example in your attachment for the sites subform, this might be the regionFK which would link back to the region selected in the region subform.

5. For each subform control, set the linkmaster property to the name of the unbound text control relating to the previous subform (e.g. sf2)

6. For each of the unbound text controls, set the controlsource to name of the field in its related subform as follows - so say the regions subform control is called sfRegion, the controlsource for the sf2 would be

=SFRegion.form.RegionID
 

ConnorGiles

Strange Traveller
Local time
Today, 23:41
Joined
Aug 12, 2014
Messages
1,068
I've been trying to make one to many relationships in this sample database I just made - but it just wont work!

I've attached it - And it seems that my Subdatasheets are saying something is wrong here :p

They worked just a second ago. Then when I tried to take them out (reverse everything I did, They still appear there but show errors when I try clicking them).

Hellppppppp :D:banghead:
 

Attachments

  • Database.zip
    58.6 KB · Views: 24

ConnorGiles

Strange Traveller
Local time
Today, 23:41
Joined
Aug 12, 2014
Messages
1,068
It is a common requirement, surprised you can't find anything

providing your data is structured as one table for each subform simply do the following

1. Create a form for each table in continuous or datasheet view.

2. Create an unbound form and put each of these forms on the mainform as subform.

3. On the unbound form create an unbound text control for each subform (lets call them sf1, sf2 etc) which can then be hidden.

4. For each subform control, set the linkchild property to the field to the name of the field in the subform which links to the previous subform (for example in your attachment for the sites subform, this might be the regionFK which would link back to the region selected in the region subform.

5. For each subform control, set the linkmaster property to the name of the unbound text control relating to the previous subform (e.g. sf2)

6. For each of the unbound text controls, set the controlsource to name of the field in its related subform as follows - so say the regions subform control is called sfRegion, the controlsource for the sf2 would be

=SFRegion.form.RegionID

I shall try following this word for word - but before I do, would this be a starting point to making this? (my DB I linked)

Or would I just need one huge table consisting of everything?
 

pr2-eugin

Super Moderator
Local time
Today, 23:41
Joined
Nov 30, 2011
Messages
8,498
Here is your example..

 

Attachments

  • ConnorG.png
    ConnorG.png
    33.5 KB · Views: 153
  • Connor-Example.mdb
    420 KB · Views: 28

ConnorGiles

Strange Traveller
Local time
Today, 23:41
Joined
Aug 12, 2014
Messages
1,068
Also!

Would the ID's need to be entered each time or?
 

ConnorGiles

Strange Traveller
Local time
Today, 23:41
Joined
Aug 12, 2014
Messages
1,068
I mean when entering data into other tables, such as the one with the sites - region ID would need to be entered wouldn't it?

to be assigned to the right place.

same as company ID inside the region table.
 

pr2-eugin

Super Moderator
Local time
Today, 23:41
Joined
Nov 30, 2011
Messages
8,498
I mean when entering data into other tables, such as the one with the sites - region ID would need to be entered wouldn't it?

to be assigned to the right place.

same as company ID inside the region table.
Yes, but that is when CJ's method kicks in. If your design is set up right for the tables the form set up (as shown by CJ) would make this easier for you. So no you do not have to worry about ID's.
 

ConnorGiles

Strange Traveller
Local time
Today, 23:41
Joined
Aug 12, 2014
Messages
1,068
Ah, so I shall implement CJ's form set up and that should do the trick, thanks guys!
 

Users who are viewing this thread

Top Bottom