Hello all,
I apologize for the long text.
I wanted to create a database for work. Being completely new to microsoft office, in the last 2 months I have already spend hundreds of hours working on this, watching youtube videos. I even paid for 599cd.com tutorials which are great but I am poor.
I am almost there, but now am at a final hurdle.
I am trying to create a database that among other things includes people, organisations, events, .. which can be linked to each other.
Each of these components has their own mainform with several subforms attached in tabpages..
So, for instance, a person would have a mainform with his identity and then tabpages consisting of subforms. i.e.
-A subform "events", that shows what events he went to.
-A subform "organisations" which shows the organisations he belongs to.
And so on
But also the other way around, when I click on the "Events" mainform it has a: "people" subform which shows the people that attended, an "organisations" subform etc..
Of course when I add a person to an event, I want this to automatically add this event to the "events" subform on this person's mainform.
Where I have succeeded so far:
For linking different tables such as Events and Persons, i just took the easy road and created a junctiontable, a many to many relationship and a form based on the junctiontable. No query.
i.e.
I have a table Persons, a table events and a table organisations.
I have a Junctiontable Person_Event that has 4 fields:
1. Id (auto)
2. PersonID (number)
3. EventID (number)
4. Comment (short text)
+ many to many relationships: PersonTable -- Junctiontable -- EventTable.
I created 2 forms linked to this junctiontable:
A subform Person_Event;
A subform Event_person.
I basically just switched the taborder for PersonId and EventID on these different subforms. I then dragged these subforms into the respective tabpages on the mainform for Person and for Events. And lo and behold, it works exactly as I wanted: Acces automatically chooses the right Parent ([ID])and Child link([PersonID]).
-------
What doesn't work:
I now run into a problem that my primitive Acces brain can't handle:
On a person's mainform, I also badly need a subform "people" to link persons to each other.
Where before I got my ID's from 2 different tables (i.e. people and events), I now want to link 2 ID´s from the same table (people).
So far I used the same method as above:
I made a junctiontable People-People with 4 fields:
1. Id (auto)
2. Person1ID (number)
3. Person2ID (number)
4. Comment (short text)
made the relationship and then created a subform based on this table.
I will alway run into the same problem:
Example: I go to the mainform of Al Smith, open the subform "people" and I put Al Smith as Person1ID and Betty Doe as Person2ID.
As long as I am in Al Smith´s form I can see this relationship, there is however no trace of this relationship in Betty´s form. Although the relationship shows correctly in the junctiontable.
I figure this is because the parent-child link acces automatically makes made it so it only shows the relationships in which the person whose mainform I am viewing is person1ID. I know this because if I remove the parent-child link completely it will just show all the records in the junctiontable.
So a long text just to ask a simple question.
How can I make the subform show me all relationships in which Al Smith is person1ID and/or person2ID?
Is there a way to make this happen by just adding a certain Parent and Child link? Or am I going at this the wrong way and do I need to rebuild this subform a completely different way?
Thank you for your time, I very much appreciate any and all help.