3 Connected tables on 1 form

[skyrat]

A friend has asked me to bring somestructure in his old Access .mdb database , allrhough I have noexperience with Access. First I made a simple test-database with 3connected tables.
tbLand :
LandID Auto-increment Primary key
LandName Text
tbActivities
ActivID Auto-increment Primary key
LandID numeric
Activity text
tbFromTo
FromToID Auto-increment Primary key
ActivID numeric
StartDate Date
EndDate Date
and made the connexions
The Access Relations screen shows thatlbLand – tbActivities is a one to many relation and so istbActivities – tbFromTo
So for so good.
Then I dragged the 3 tables to 1 formand put some values in.
To test this I made a little program inDelphi XE7 using an ADO connection and got exactly what I wanted :clicking tbLand only showed the Activities connected with the clickedtbLand row etc.
In the Access database however I cannotget this to work ! I tried the OnClick option in SubForm subLand butnothing I tried worked ! The very primitive and complex way Accessuses subforms when you put 3 tables on 1 form makes it impossible forme. It should be simple to define a filter and set it on , but I justcannot find out how that filter should look !
Please help.
(I hope I used the right terms as theAccess database is in Dutch !)



Just tried to add a .zip file but it wouldn't upload !

[skyrat]

next try for a .zip file

[davegri]

Changed your table relationships to Many-to-Many and made new forms for same.
Don't know why your db was in .mdb format as you have Access 2010. The attached is .accdb
sky-davegri-v01.zip

[skyrat]

Thanks for that try , but it's absolutely NOT what we want. It's the very primitive way Access uses and I already tried it ! It's easy but very user unfriendly (the real user of the real database doesn't want it at all that way !!). The other file I sent by WeTransfer shows exactly the way it should look and work.

The real database was started in 1997 and although the owner updated Access to 2007 and later to 2010, he never upgraded the database to .accdb (and still doesn't want to either !)

[davegri]

OK. Next man up. I'm done.

[ArviLaanemets]

Something like in attached?

An advice. Don't rename forms subforms. Subform is entirely different object. It is a container, which has form as source. I.e. when you have a form Form1, and it has a subform based on Form2 in it, then there are in hierarchical order objects Form1 > Subform based on Form 2 > Form 2. Access has a bad habit to name subform it creates automatically whenever you drag a form into another with same name as form contained in subform. I myself rename the subform immediately after it is created (to avoid confusion am I referring to subform or to form it is based on).

[skyrat]

Thank you very,very,very much !! Your attachment does exactly what we want !!!
Now I'll have to understand what you did to be able to the same in the REAL db. So far I just dragged the 3 tables on 1 form. Is that not the way to do it ?

[ArviLaanemets]

It is not!

At first there are more tables, and table structures are different:
There are 2 registry tables - tblActivities (you missed this one) and tblLands. In those tables user registers lands and activities. Every entry is unique.
Then there are 2 relationship tables, where uset sets relationships between different information types.
Table tblLandActivities determines which acivities are occurring in which countries - this is determined by pair of foreign keys linked with primary keys of tblLands and tblActivities.
Table tblLandActivityPeriods is determining time periods (events) when an activity in country defined in tblLandActivities is occurring. User is defining there time period for foreign key linked with primary key in tblLandActivities.

Usually the form based on such tables includes a single form where user can select a land, and which has also a single subform, where user then can link an activity to selected land. And this subform has his own subform (the last one, which may be continuous), where the user can register time periods of different events for active activity in active land.

I used an unbound main form (so user could also have a possibility to register different activity types) with 2 unbound controls. Forms fLands Current event stores on one current LandID whenever different land is selected, and forms fLandActivities stores current LandActivityID in another one whenever another activity for active land is selected there. And those unbound controls are used as sources for LinkMasterFields of matching subforms. This allows to put all 3 forms as continuous subforms into main form and keep links between forms working.

Edit:

So far I just dragged the 3 tables on 1 form

I felt there was something strange with your form! I created main form and 3 continuous forms. And then dragged those 3 forms into main form. And I had to edit master fields for 2 subforms manually, as Microsofd does not know, that the master field can be linked to unbound control (at least it says it is not possible whenever you try to do it in wizard)

[skyrat]

Seeing the number of views for this post I'm sure you made a lot of people happy
Now it's time for me to try and understand what you've done, so I can use the ideas in the real database concerned. The one I attached was just a tryout ! It will certainly take some time (I'm 82) to completly understand but I'm sure I'll now get what I want. Thanks very much again !!

Before anything else I should explain my murdering the English language : I'm a 82 year old real Dutch Cheesehead and just trying to translate the things I do in my Dutch Access 2010 into English, so I can only hope you understand me.
Now I believe I understand the way you changed/added the tables, but I don't see how you make an "unbound form with 2 unbound controls". In Access Make --> Forms I see Form, Formdesign,Empty Form, Form Wizard andMore Forms (I hope I translated correctly). Where / how should I start

Selecting "Form" I get an empty form showing "frmMain1" at the top. Now what are the "2 unbound controls" you're talking about ?
All this is in your demo !

[ArviLaanemets]

The form fMain is the one where all is put together. It contains all other used controls and forms. Open it in design mode.

On form fMain is a tab control which has 2 pages. The 1st (leftmost) page is for subforms where you can register/select countries, and where you can link activities with active country and determine events for those activities. The 2nd is for subform where you can register different activities, so you can on 1st page select activities to be linked with countries.

With 2nd page it is simple. I created a continuous form based on table tblActivities, with primary key set invisible and label for primary key removed, activated in design mode 2nd page, and dragged the fActivities into it. Then clicked ONCE on freshly added subform - the SUBFORM was activated (when clicking twice, the form the subform is based on will be activated instead) - and in properties window (at right of form - when you don't see it, then activate it from menu, or from right-clig dropdown) changed the name of subform (sfActivities instead fActivities Access was giving). It was all for 2nd page.

On 1st page I placed 2 unbound text boxes and set for them the Visible property to False (they are placet at top right corner of page - just right of last subform on page). Then I created 3 continuous forms (all primary and foreign key fields hidden, and their labels deleted), and dragged all 3 forms into 1st page, dimensioned and aligned them there. And of-course renamed subforms for all 3 of them. Then I created Current events for forms fLands and fLandEvents to set values of unbound textboxes (You can see form events in 3rd page of roperties window, to see the event code, locate the event and click on "..." to right of it).

As last step, I activated subform sfLandEvents and set Master and Child field properties for it. And did same with sfLandEventPeriods (or whatever name I did give it - I'm at home now, and I don't have Office in my home computer.

And in tables, I defined additional unique indexes for all tables, so user can not enter 2 countries with same name, register same activity twice, link same activity twice to same country, or set several events for same activity in same country for exactly same period (to have an index which excludes overlaping periods is much harder task - I'm not sure a possible one at all).

[skyrat]

Wow , I'm overwhelmed by the amount of work you're doing for me !!!
However , when I select Form, I only get an empty form with a tab fMain1 in top and fMain under it, NO 2 pages! Maybe that is because your demo already has a Mainform ? Can Access 2010 only handle 1 Mainform in a database ?

[ArviLaanemets]

fMain was my main form - it was easier for me to create a new one instead to use your one. You can use your own - but before adding the tab control there, you have to delete subforms, or you have to transfer subforms from form to tab control page(s) - cut the subform, activate the page, paste.

Attached is a picture of fMain in design mode. To get tab control itself activated is somewhat tricky - you have to click at top border of it outside of pages (on right of page tabs). To insert a new tab control, you activate it from menu (between button and hyperlink controls). You can add additional pages to tab control from menu opened by right-click on tab control (Add pages).

[skyrat]

Sorry for being so stupid last night ! I misunderstood your "frmMain with 2 tab controls" (my lack of English probably). I tried everything to get a frmMain form with 2 tab controls on it. After a good sleep I understood that I had to put the 2 tabcontrols on the sheet myself !! And now I see in your new post that I'm right, so now I'll go on experimenting a bit, before trying to apply the new knowledge in the real database. Many thanks again and I hope I will not have to ask your help again !!!