Combo box - linking (not cascading)

[fraser_lindsay]

I tried to search but this wasn't the easiest to fit into a few short search terms.

I have a 'Profile' form with a tab control in which a user can select a 'hazard' from a combo list or press a button to add a new one (takes them to a control panel). This I can do. That goes into a table. That table is linked one to many on the parent.

The hazard control panel is a form that manages a table of hazards and two parameters linked to the hazard. Each hazard can have one or more events associated with it and each event has only frequency. These are selected by combo.

For example:

Noise Audiometry Annually



On my main 'Profile' form when the hazard is selected I want the associated rules set for that hazard to appear but in a format that cannot be changed by the user.

So perhaps they select the hazard from the drop down on the hazard subform for that profile and the hazard rules appear for each one in a text box rather than a combo, locked.

If the rule is changed in the control panel then this is reflected across all of the records which contain this particular hazard e.g. a change in frequency.


I'm really struggling in how to set this up to be honest.

 

[dkinley]

Okay .. I think I get it.

Let's say you had a table - call it tblHazards. In it you had 4 columns - HazardID, Type, Metric, Frequency.

So a typical record was like ...
147483 Noise Audiometry Annually

In tblProfile you have a foreign key for the tblHazard where you store the HazardID.

On the Profile form, you had a combo box and two text fields. The combo box was bound to the foreign key, HazardID. The combo box displayed the hazard: Noise and the other two text fields would autopopulate the remaining fields: Audiometry, Annually. This method will work if you were 110% sure the update would need to be for 100% of the records with no exceptions.

Now, you go to a form that was bound to tblHazard or into the table directly. You change Annually to Semi-Annually. So, when you open up any profile the word "Semi-Annually" is now shown instead of "Annually"?

Do I have that right? If so, then this can be done. You would only need to store the foreign key into tblProfile. The forms OnOpen event would need to call the combo boxes OnUpdate event to ensure the two text fields were populated with the correct information.

-dK

 

[fraser_lindsay]

dK, you are spot on.

So, following your guidance here is where I'm at:

I actually did have 'tblHazard' as you describe, but I had not added 'Hazard_ID' to my 'tblProfile'. I added this as a number field, but not a lookup.

Previously I was linking the primary key of this with the 'Hazard_Category' on 'tblHazards'. I deleted this and linked the 'Hazard_ID' primary key on the 'tblHazards' with the 'Hazard_ID' field on 'tblProfile' and enforced referential integrity and both cascades.

I have a form based on 'tblHazard' which is used to set the independent rules for each hazard i.e. hazard name, event and frequency. This form is now showing the profile form in a subform automatically, I assume because I enforced RI.

Now the bit I am currently stuck at is showing the hazard(s) on the main profile form and linking these one to many.

Guidance from some of the other very helpful guys has taught me how to create a nested subform using the form wizard, assuming my relationships are correct.

I have a template for a hazard subform which has the fields hazard (as a combo), two combos for event and frequency. I have made these last two invisible and added two text boxes linked to these which are locked.

What needs to happen is this subform needs to be nested into the hazard tab of the profile form to allow the user to add more than one hazard (each with unique rules) from the drop down on each master profile. Each hazard should automatically show the linked fields (in text boxes) of the rules for event and frequency.

Yes, you were correct, I was 110% sure that changing the rule would update all records holiding that specific hazard i.e. more of less frequent. This is in case there is a change in policy.

 

[fraser_lindsay]

Hi, I'm just trying to pick this up again after the holidays. Still haven't got it fully functional.

Would anyone be able to assist me, especially with dkinley's last suggestion:

If so, then this can be done. You would only need to store the foreign key into tblProfile. The forms OnOpen event would need to call the combo boxes OnUpdate event to ensure the two text fields were populated with the correct information.

Thanks

 

[jal]

Hi, I'm just trying to pick this up again after the holidays. Still haven't got it fully functional.

Would anyone be able to assist me, especially with dkinley's last suggestion:



Thanks

Frankly I don't see why you are having a problem. When the profile form opens it should automatically pull up the existing data in the Hazards table which should be current.

The only way I could imagine non-current data would be if the profile form stays open (and thus doesn't start up again with current data) during and after the Hazards-form update. If that's the case, you might need to add code to the Hazards form which updates the profile form - or, alternatively, put that code in the profile form's On_Activate event (which should hopefully fire when the user switches back from the hazards form to the profile form).

 

[fraser_lindsay]

Well I'm clearly doing something wrong as I'm not really getting what I want.

I either have a problem in that my fields won't link, or if I fix that in my relationships then I can't create a nested subform of many hazards on one profile due to the change in relationships. I can't seem to have both.

I've been at this all afternoon and made no progress at all.

The combo box and text fields on the profile form doesn't work alone as I need multiple rows for each hazard on each profile. That's why I thought a nested subform would be best.

 

[jal]

Well I'm clearly doing something wrong as I'm not really getting what I want.

I either have a problem in that my fields won't link, or if I fix that in my relationships then I can't create a nested subform of many hazards on one profile due to the change in relationships. I can't seem to have both.

I've been at this all afternoon and made no progress at all.

The combo box and text fields on the profile form doesn't work alone as I need multiple rows for each hazard on each profile. That's why I thought a nested subform would be best.

There are probably 2 or 3 different ways to do this. A nested subform should be fine.

So let's assume you have one form called Mainform and a subform which displays the profile info. You'll need a cboHazardID which is bound to the HazardID. When the user selects a different hazardID from the cboHazardId, this should fire the cbo's AfterUpdate event. In that code block you can repopulate the profile form. To do so, use code like this:

Me.subform1.Form.RecordSource = "SELECT * FROM Profiles WHERE HazardId = " & cboHazardId
Me.subform1.Requery

You'll need to make sure the subform's filtering is turned off (because "filter" would mean another WHERE clause which I already provided)

 

[jal]

Now, you go to a form that was bound to tblHazard or into the table directly. You change Annually to Semi-Annually. So, when you open up any profile the word "Semi-Annually" is now shown instead of "Annually"?

If he is reading you right, this too would be the same idea. Here too, you will use the textBox's AfterUpdate event to place code that causes the profile subform to requery, just like I said above.

Your earlier explanations sounded very complicated, but at the moment it doesn't look so bad. I mean, we're not talking 45 different tables here. You apparently just want the user to select an ID# from one table (called Hazards) and then display the related records from another table (called Profiles).

 

[dkinley]

You apparently just want the user to select an ID# from one table (called Hazards) and then display the related records from another table (called Profiles).

Exactamundo to what Jal posted.

Glad you bumped this message - I've been out-of-pocket for a couple of weeks. If you could and wouldn't mind posting a copy of the db? I am in a better position to take a gander at it now.

-dK

 

[fraser_lindsay]

Thanks for you responses guys. I was working it last night and after much messing around went back to basics and tried to retrace my steps and try again in smaller chunks. I have setup up the nested subform properly now i.e. I have multiple hazards linked to one profile, that works. So now I need to re-link my combo's again and get that to work.

I had a problem with parent/child relationships which was messing my subform up on previous attempts (see previous frustration).

I think I was possibly trying to much at once and maybe my explanation was a tad too complicated.

I have been working on something else today but I'll give this a bash tonight as per your responses and then report back. If I still can't get it to do it I'll upload my database.

 

[fraser_lindsay]

Ok, I appear to have been successful except I managed it without using your code. Not intentionally, I kind of fell into it as I was resetting the tables and relationships.

I had gone back through each step, rechecked my relationships and created another intermediate table. Then linked the relevant fields in a one to one relationship.

I can add multiple 'hazards' to a profile using the subform and each hazard is linked to additional fields in a one to one. I can edit the rules for the hazards in another table and form and these replicate through to each profile.

So I seem to have achieved what I wanted by following some of your prompts. Thanks very much.

 

[dkinley]

Awesome ... I think you hit it spot on. Good luck with your project!

-dK