Opening another form at a specific record, based on current form and record (1 Viewer)

tdriscoll

New member
Local time
Today, 14:33
Joined
Apr 13, 2017
Messages
5
Hello,

I have a 3-tier database (grandparent, parent and child), which I would like the end user to be able to navigate using command buttons.

Currently, I know how to add command buttons using the event builder that can open another form, and at a specific record. So far so good.

However; there are 6 grandparents, 9 parents, and approx. 40 children. Not all parents are related to all the grandparents, and not all children are related to all the children. My current buttons display on a form for all records, including those which do not relate to the record the button will open.

I have set up the many-to-many relationships between each generation/tier.

From the grandparent/parent forms, I would like to have command buttons allowing you to go the next level down, to the parent/child related to the grandparent/parent record displayed.

As I scroll between records, I would like for these buttons to stay the same, but their attached events to change.

I'm imagining this should be possible somehow using the relationship join tables and/or record ID numbers.

If this downward journey is possible, it follows I could go up the family tree in a similar manner.

For Example: From Grandparent 1, I want to be able to open the parent form at records 1 or 3. From Grandparent 2, I want to be able to open the parent form at records 2 or 3. The grandparent form would then only need 2 or 3 buttons.

Likewise, from Parent Form record 1, I would like to be able to go the Child form, and open records 1, 2, 3, 4, or 5 etc.
From Parent record 9, I would like to be able to open Child form, record 35, 36, 37, or 38....

Many thanks in advance,

Tom
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:33
Joined
Jul 9, 2003
Messages
16,244
From your description of what you are doing it is difficult to advise you. It's better if you provide an overall picture of your problem. From the limited information you have given I would point you towards using subforms...
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:33
Joined
Jul 9, 2003
Messages
16,244
What is a Subform?

Well, it's nothing special, it's just a standard everyday form.

What does Subform mean then?

When you drag and drop a form on to the form you are editing, MS Access automatically embeds the form you are placing in a Subform/subreport control.

What's a Subform/subreport control?

It stores editable information controling how the records of the form and Subform should interact.

Subforms are nothing special, it's this practicality invisible intermediary control, the "Subform Window" which makes an ordinary form a Subform.

Interesting to Note:-
The original form isn't really "in" the "Subform Window", it's actually a copy of the form. This copy is termed "an instance" in programming terms. This leads to some interesting possibilities, for example, you can effectively put the same form on many forms, or many times on the same form. You can open it, and use it as a standalone form.

I created an example called the "Time Management Matrix" Which I can post and blog about if anyone is interested.

Sent from my SM-G925F using Tapatalk
 

tdriscoll

New member
Local time
Today, 14:33
Joined
Apr 13, 2017
Messages
5
Hi Gizmo,

I've attached an image of how I am planning out this database.

Is the idea of a button with variable events in this layout feasible?

I will also look into sub-forms to see if that could be the answer.
 

Attachments

  • db_plan.PNG
    db_plan.PNG
    22.8 KB · Views: 96

isladogs

MVP / VIP
Local time
Today, 14:33
Joined
Jan 14, 2017
Messages
18,186
Just to say, if you succeed with this project, it could make a useful addition to the sample databases section when complete
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:33
Joined
Jul 9, 2003
Messages
16,244
I'm not getting anything from the image you supplied.

What do I mean by that?

Well usually when somebody has a question it's usually about a problem that has been presented on the forum before. Many of us have been here for 10 years or more! So we've seen most things.

Having said that we also have our own little foibles and our own areas of expertise, hence it is possible someone will look at your Image and immediately know what you want. However I don't. In other words it's not saying to me, oh! it's that problem again, and this is the solution.

I would suggest you explain exactly what you are trying to do in layman's terms.

Imagine someone who knew nothing about fire stations decided to build one. They submit a planning application with a garage for the fire engines on one side of the plot and a building to quarter the firefighters on the other side of the plot. Someone who knows about fire stations says, no don't do it like that put the firefighters above the garage and have a big long pole for the firefighters to slide down!

A better approach at getting an answer would be:-
I'm building a fire station and I think the quarters should be here and the garage should be there. What do you think?
 
Last edited:

tdriscoll

New member
Local time
Today, 14:33
Joined
Apr 13, 2017
Messages
5
Ok, I suppose my question boils down to:

Can a button on a form have different outcomes depending on the record being viewed?

In layman's terms, it might translate as:

I have a list of people, and know what each of their favourite hobbies are. I want to be able to go from the person, to their favourite hobby. How might I do this?

Something like: from one button on the people form, open the hobbies form, at the appropriate record. The difficulty comes in the button linking to a different hobbies record from each people record.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:33
Joined
Jul 9, 2003
Messages
16,244
What are your tables like?

Show field types and some example records for each of your tables

Sent from my SM-G925F using Tapatalk
 

static

Registered User.
Local time
Today, 14:33
Joined
Nov 2, 2015
Messages
823
I have a list of people, and know what each of their favourite hobbies are. I want to be able to go from the person, to their favourite hobby. Who might I do this?

I think you've answered your own question. You have lists.

List/combo controls hold data and can perform actions based on the selected value(s).
 

Users who are viewing this thread

Top Bottom