Full Version: M-to-m Tables - Form - Subform Relationship(s)
UtterAccess Forums > Microsoft® Access > Access Forms
jonpg_33
Click to view attachment
ello,
I've searched in the FORMS forum and found a few related threads to my questions below but I'm a little too dense to put it all together at this point since I'm still relatively green.
I have attached a screen view of my current table relationships. To recap the many-to-many relationship logic I have established:
a. I have a situation where a Subject, an individual person, (tbl_main_Subject) can be tied to many Incidents (tbl_main_Incident) and one incident can, on occasion, be tied to many subjects (individuals).
b. In turn, each incident can be tied to many types of merchandise, returned or purchased (tbl_main_Merchandise), and many gift cards (tbl_Main_GiftCard)...as well, one type of merchandise can appear in many incidents and one gift card can also be used in many incidents.
I'd like to have a tabbed form which includes the following tabs: Subject, Subject Detail, Incident, Merchandise & Gift Card. The goal is to select a Subject (via a combo box in the header of the Subject form) and then have the Subject, Subject Detail and Incident tabs linked directly to it so every time the Subject selection changes, the data contained on these three tabs changes with it. I have been able to figure this part out successfully by using the combo box and a version of the following code added to the Form current event. I used the Subject form as the primary form and then added the Subject Detail and Incident forms subforms to it.
Private Sub Form Current ()
Me.cbo_Subject.Value = Me.SubjectID
End Sub
However, here's my snafu: Each Incident is tied to different Merchandise and Gift Cards. Therefore, I need to be able to have the Merchandise and Gift Card tabs linked only to the Incident tab and not to the Subject tab directly even though the incident belongs to the Subject, i.e. when the Incident number for a Subject changes, the Merchandise and Gift Card tabs should change along with it since they are directly linked. Stated again, the selection of a Subject adjusts the Subject, Subject Detail and Incident tab information, and subsequently, the selection of any one of the Incidents on the Incident tab in turn adjusts the Merchandise and Gift Card tabs linked to the respective Incident. I have not been able to accomplish this. Is it possible with the table relationships I have established?
Also, another rookie question:
1. In order to have the junction tables properly populated, do I need to remove the table relationships, populate all of the tables with 2-3 different data entries directly into the respective tables, and then connect -the table relationships? (Can you point out a forum post to which I can refer that explains populating tables/junction tables correctly in many-to-many relationships?)
Thank you kindly for your direction and assistance.
jon
Daryl S
Jon -
It is difficult to manage several many-to-many relationships, but it can be done. I would suggest hiding the Merchandise and Gift Card tabs by default (set the .visible property to false). Then add code to the click event of the subform on your Incident tab that will make those two tabs visible and filter the Merchandise and Gift Card records by the selected IncidentID.
HAs for populating the junction tables, yes, you do need to populate the base tables first. For example, you must have an incident record and a subject record before you can add the subjectIncident junction record.
Hope that helps!
- Daryl
jonpg_33
Thank you for the response sir.
You noted: "Then add code to the click event of the subform on your Incident tab that will make those two tabs visible and filter the Merchandise and Gift Card records by the selected IncidentID."
Do you have a resource or example that could assist me in writing the code necessary for the above: "make those two tabs visible and filter the Merchandise and Gift Card records by the selected IncidentID".
jon
Daryl S
Jon -
o make your tabs visible in code, use something like this (but use your tab's name):
Me.PageName.Visible = True
To filter a subform, use code like this (again, use your form, field, and control names):
forms!mainformname!Merchandisesubformname.Form.Filter = "[IncidentID] = " & Me.IncidentID
forms!mainformname!GiftCardsubformname.Form.Filter = "[IncidentID] = " & Me.IncidentID
forms!mainformname!Merchandisesubformname.Form.FilterOn = True
forms!mainformname!GiftCardsubformname.Form.FilterOn = True
- Daryl
jonpg_33
10-4. I'll give that a shot and advise of the outcome. Thank you.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.