Subform show only relevant records (selection from list)

[hunoob]

Hi there Everyone! Althought I still did not get any answer to my previous question (post: http://www.access-programmers.co.uk/forums/showthread.php?t=212420) I am already having another problem.

I have a list from which I can select the records. With the help of the VBA the fields fields on the form populate different data belonging to that record.

My problem is when there are multiple data belonging to the same record. Please see the file attached. I have three tables: Risk, Assessment and Controls. There is a list box on the left which contains the risks. When I click on one of the risks the Assessment tab displays the impact and likelihood amounts belonging to that record (VBA code). But one risk can have many controls. And here is my problem. On the Controls tab I have a query displayed which shows the control points. This subform shows all the Controls, however I would like to show only those which are belonging to the selected risk on the listbox. How could I do that?
Thank you in advance and have a nice weekend.

 

[spoole]

Im not sure, but cant you get round this by setting up a relationship between them using the relationship manager?

 

[hunoob]

This is my problem... How could I set the relationship between the list and the subform? This is my question as well. How could I get the subform to 'realize' which record is selected on the list, so it will show only those controls which are related to the selected risk.

As I am quite new to access I really do not know what and how to do...

 

[spoole]

Ok I think I see.

You need to base a query on what is selected in the list and display the results on a subform? If so firstly create a query with all the info you need from each table and set up the relationships. Then use this as an example to use the value in your selected list box as a parameter for the query (how to reference a value on form and use it in a query) http://www.techonthenet.com/access/queries/filter1.php

Then create a subform based on the query. You will have to requery the subform though everytime you select a value from the list box.

*Im not sure if its a combo box or list box that this will ONLY with.

 

[spikepl]

before you do anything else, read up on normalization: http://support.microsoft.com/kb/283878 or http://en.wikipedia.org/wiki/Database_normalization

Your tables are not structured properly.

Further the ID's should be autonumbers. You do have that in your data, but you then link your tables via textfields. That will be 1. inefficient, and 2 cause grief down the road.

Also, a one-to-many relation is normally set up like this:

tblParentTable
-------------
ParentTableID (autonumber)


tblChildTable
-----------
ChildTableID (Primary Key - autonumber)
ParentTableID (ForeignKey)

The relations are managed through these keys only (and those are normally not for human consumption)

Before you reassess your tables, then it will not be a good idea to procede.

 

[hunoob]

Hi Spikepl!

I have textfiled for the IDs because I want to have IDs which look like R1, R2....R99 and so on. Those are the IDs..

Spoole.. your link is good however it is about using parameters. What I would like is a list and selecting by clicking.

 

[spikepl]

That is not the way to do things, but if you want to live with the consequences down the road then be my guest. You have been warned.

 

[hunoob]

Hi Spikepl!

I changed the IDs and normalized the tables as you advised. However, my problem still persists. On the control tab I see all the controls, and not only those which belong to the selected risk on the listbox. Please help me to proceed further. (Please find attached the new Example file)
Thank you in advance!

 

[spikepl]

Ok the tables look much better now.

Your subform is based on SELECT Assessment.RiskID, Assessment.Likelihood, Assessment.Impact
FROM Assessment;

which grabs all it can find.


You need to add a clause to your query WHERE AssementRiskID = Forms!Form1!List0

1. Name all controls something meaningful. Otherwise you'll very quickly run sour in stuff like textbox35 and List17
2. To add a criterion to a query, open the query in the query designer, put cursoer in the relevant Criterion field, and right-click on Build. This will let you navigate to the control in question and fill in the correct syntax.

 

[spikepl]

And in the AfterUpdate event of the List0, you need to requery the subform, for your newest selection to show up

 

[hunoob]

Hi Spikepl!

Thank you very much for your help! I already learned a lot! However (as I am a beginner) I still have problems.

The question is: "And in the AfterUpdate event of the List0, you need to requery the subform, for your newest selection to show up"... what do you mean by I need to requery the subform?

I feel that I am very close now to the solution... thanks for you! Please help me in the above questions! Thank you very much!

 

[hunoob]

Hi spikepl!

Finally I managed and got it working! Thank you so much for your help! Thank you!!!

 

[spikepl]

Good to hear, thx for the feed-back.