May I please have some assistance creating VB code in Access 2010, Windows 7. I have a main form labeled frm.Sponsors, the primary key (long Integer) is the field is [MSF_RERP]. I have another form that I wish to create a linkage from frm.Sponsors to frm.Coaches. frm.Coaches has a successful subform labeled frm.CoachAssignment. The primary key (long Integer) field that links frm.Coaches to frm.CoachAssignment is [MSF_Coach]. There are about 50 sponsor records in frm.Sponsors. Each sponsor has at least one record or Coach in frm.Coaches. Each Coach has at least one record (Coach assigned) to one sponsor in frm.CoachAssignment. Coaches (frm.Coaches) may be employed by multiple sponsors at one time. I have created relationships in my database: tbl.sponsor.MSF_RERP one-to-many tbl.CoachAssignment.MSF_RERP and tbl.CoachAssignment.MSF_Coach one-to-many tbl.Coaches.MSF_Coach. As you can see field [MSF_Coach] is not in frm.Sponsors this is my dilemma because I don’t know how to link main frm.Sponsors to subform frm.Coaches and then to subsubform frm.CoachAssignment. In case it may not be clear, the desired effect when the end-user opens the frm.Coach from frm.Sponsors it should only display coaches that are employed by the Sponsor [MSF_RERP].
Create link from main to a Sub of Subform (1 Viewer)
- Thread starter S_Preston
- Start date
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Tomorrow, 02:04
- Joined
- May 7, 2009
- Messages
- 19,242
you specify a Condition when you open
the Coaches Form from Sponsors Form.
Both have MSF_REF field so you set the
condition there.
If [MSF_REF] is a number:
DoCmd.OpenForm FormName:="Coaches Form", WhereCondition:="[MSF_REF] = " & Me.[MSF_REF]
If [MSF_REF] is string:
DoCmd.OpenForm FormName:="Coaches Form", WhereCondition:="[MSF_REF] = " & Chr(34) & Me.[MSF_REF] & Chr(34)
the Coaches Form from Sponsors Form.
Both have MSF_REF field so you set the
condition there.
If [MSF_REF] is a number:
DoCmd.OpenForm FormName:="Coaches Form", WhereCondition:="[MSF_REF] = " & Me.[MSF_REF]
If [MSF_REF] is string:
DoCmd.OpenForm FormName:="Coaches Form", WhereCondition:="[MSF_REF] = " & Chr(34) & Me.[MSF_REF] & Chr(34)
Hello arnelgp, thank you for taking the time to read and reply to my post. I'm not sure how things got miscommunicated if it is my fault I'm sorry. The primary key /numeric string in frm.Sponsors is [MSF_RERP], not [MSF_REF]. The primary key / numeric string in frm.RiderCoaches is [MSF_Coach], this field is in not in frm.Sposnors. The numeric string field that ties sub form frm.Coaches and sub sub form frm.CoachAssignment is [MSF_Coach]. The string field [MSF_RERP] is in both frm.Sponsors and frm.CoachAssignment. I hope this clarifies my inquiry.
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Tomorrow, 02:04
- Joined
- May 7, 2009
- Messages
- 19,242
Hi!
it is indeed confusing. let us try this.
create a query that will be the record source
of your form FormCoaches:
SELECT tblCOACHES.*
FROM tblCOACHES WHERE MSF_COACH IN (
SELECT MSF_COACH FROM tblCOACHASSIGNMENT WHERE tblCOACHASSIGNMENT.[MSF_RERP]=FORMS![FRMSPONSOR]![MSF_RERP])
can you follow? or did i follow?
tblCoaches there is you coaches table (without MSF_RERP).
the mediator between COACHES and SPONSORS is tblCoachAssignment.
it is indeed confusing. let us try this.
create a query that will be the record source
of your form FormCoaches:
SELECT tblCOACHES.*
FROM tblCOACHES WHERE MSF_COACH IN (
SELECT MSF_COACH FROM tblCOACHASSIGNMENT WHERE tblCOACHASSIGNMENT.[MSF_RERP]=FORMS![FRMSPONSOR]![MSF_RERP])
can you follow? or did i follow?
tblCoaches there is you coaches table (without MSF_RERP).
the mediator between COACHES and SPONSORS is tblCoachAssignment.
Arnelgp thanks for your prompt reply!
I have created the query you suggested in SQL view as you suggested. Your last inquiry is accurate.
For clarification purposes:
Tbl.Sponsors has [MSF_RERP] doesn't have [MSF_Coach] both are long integer.
Tbl.Coach has [MSF_Coach] doesn't have [MSF_RERP] both are long integer
Tbl.CoachAssignment has BOTH [MSF_RERP] and [MSF_Coach] both are long integer.
I have created the query you suggested in SQL view as you suggested. Your last inquiry is accurate.
For clarification purposes:
Tbl.Sponsors has [MSF_RERP] doesn't have [MSF_Coach] both are long integer.
Tbl.Coach has [MSF_Coach] doesn't have [MSF_RERP] both are long integer
Tbl.CoachAssignment has BOTH [MSF_RERP] and [MSF_Coach] both are long integer.
Arnelgp thanks, the query is functioning. However new caveat; frm.Coach is a single form view, and the subfrm.coachassignment linked to frm.coach is datasheet view. A single coach may be employed by several sponsors at the same time. I would assume we modify the SQL query we just created to open the frm.Coach?
- Steve
- Steve
Arnelgp - frm.Coach is a single form view, and the subfrm.coachassignment linked to frm.coach is datasheet view. One to many relationship. The frm.coach (single form view) displays the coach information name, address, certifications, etc. The frm.coachassignment (datasheet view) lists all the sponsors the one coach is employed by; [MSF_RERP], [MSF_Coach], [SponsorName], [Date of hire], [Date of fire]. A single coach may be employed by several sponsors at the same time.
The query that you created when launched from the frm.sponsor displays in datasheet format all of the coaches employed by the sponsor past and present.
- Steve
The query that you created when launched from the frm.sponsor displays in datasheet format all of the coaches employed by the sponsor past and present.
- Steve
Arnelgp thank you for your assistance!
With some perseverance and stubborn determination I was able to solve the problem. I’m going to share this with everyone because I’ve never encountered this type of second hand association before between forms.
1/1/2018 14:43 paraphrasing “Arnelgp wrote create SQL query with the code below”
SELECT COACHES.*
FROM COACHES
WHERE MSF_COACH IN (
SELECT MSF_COACH FROM COACH_ASSIGNMENT WHERE COACH_ASSIGNMENT.[MSF_RERP]=FORMS![SPONSORs]![MSF_RERP]);
After I created this SQL query, I saved it as qry-rcrca. Then I modified my Navigation settings in the current database: opened the File menu, Options, Current Database, Navigation, Navigation Options, Table and Related Views, place a check in the box labeled “Show hidden objects”.
On my main form frm.Sponsors I created VBA command button to open frm.Coaches this has the subfrm.Coach_Assignment attached. If you recall the there is no direct link of field that ties both frm.Sponsors to frm.Coaches. There is a link field [MSF_RERP] between frm.Sponsors and subfrm.Coach_Assignment. That is why Arnelgp had me create the SQL query that I named qry-rcrca.
Private Sub Coaches_Click()
Application.SetHiddenAttribute acQuery, "qry-rcrca", True
DoCmd.OpenForm "coaches", acNormal, , , acFormEdit
End Sub
I set qry-rcrca to be a hidden attribute query in my database because before I did so the query would open behind the frm.Coaches. I experimented a few ways to open the query but now have it visible and I decided to go with this solution. If you have frm.Sponsors open, and click the command button to open frm.Coaches; the qry-rcrca selects the current me.[MSF_RERP] on the screen and displays the same me.[MSF_RERP] on the subfrm.Coaches_Assignment. In the past to accomplish this task would have created a tertiary or fourth field that would tie the tables together like a customer number for example.
With some perseverance and stubborn determination I was able to solve the problem. I’m going to share this with everyone because I’ve never encountered this type of second hand association before between forms.
1/1/2018 14:43 paraphrasing “Arnelgp wrote create SQL query with the code below”
SELECT COACHES.*
FROM COACHES
WHERE MSF_COACH IN (
SELECT MSF_COACH FROM COACH_ASSIGNMENT WHERE COACH_ASSIGNMENT.[MSF_RERP]=FORMS![SPONSORs]![MSF_RERP]);
After I created this SQL query, I saved it as qry-rcrca. Then I modified my Navigation settings in the current database: opened the File menu, Options, Current Database, Navigation, Navigation Options, Table and Related Views, place a check in the box labeled “Show hidden objects”.
On my main form frm.Sponsors I created VBA command button to open frm.Coaches this has the subfrm.Coach_Assignment attached. If you recall the there is no direct link of field that ties both frm.Sponsors to frm.Coaches. There is a link field [MSF_RERP] between frm.Sponsors and subfrm.Coach_Assignment. That is why Arnelgp had me create the SQL query that I named qry-rcrca.
Private Sub Coaches_Click()
Application.SetHiddenAttribute acQuery, "qry-rcrca", True
DoCmd.OpenForm "coaches", acNormal, , , acFormEdit
End Sub
I set qry-rcrca to be a hidden attribute query in my database because before I did so the query would open behind the frm.Coaches. I experimented a few ways to open the query but now have it visible and I decided to go with this solution. If you have frm.Sponsors open, and click the command button to open frm.Coaches; the qry-rcrca selects the current me.[MSF_RERP] on the screen and displays the same me.[MSF_RERP] on the subfrm.Coaches_Assignment. In the past to accomplish this task would have created a tertiary or fourth field that would tie the tables together like a customer number for example.
Similar threads
