Opening different subforms based on main form ID

[AndyMark]

I have a main form which is linked and opens through the Employee ID. The
main form has a Tab control to allow the Employee to update several subforms
(trade skills, surveys, etc) most of which are generic and linked through the
Employee ID, no problems here. BUT the trade skills are specific and vary in
terms of number of fields according to trade group classification. The
separate trade skills subforms exist.
My Question: how can I automatically open the specific Employee trade skills
subform when the main form is opened.

 

[Klatuu]

The form that will be displayed in a subform control is defined in the
SourceObject property of the subform control. You can set that value
programmatically as needed.

 

[AndyMark via AccessMonster.com]

Thank you Klaatu but I need to be more exact.

Right now the Employee has to select their ‘Department’ to open the required
subform using a combo box, on the main form, here is the code:

Private Sub cboDepartment_AfterUpdate()
Me!Child0.SourceObject = Me!cboDepartment
End Sub

Is it possible to remove the combo box function but get the same result; open
the correct ‘Department’ subform - filtered to the Employee ID when opening
the main form. If so, please explain. I can not seem to link them all
together.

Thank you in advance.

The form that will be displayed in a subform control is defined in the
SourceObject property of the subform control. You can set that value
programmatically as needed.

I have a main form which is linked and opens through the Employee ID. The
main form has a Tab control to allow the Employee to update several subforms

[quoted text clipped - 4 lines]

My Question: how can I automatically open the specific Employee trade skills
subform when the main form is opened.

 

[AndyMark via AccessMonster.com]

Klatuu
sorry for the delay.

In answer to your questions
1) Yes the dept code is on employee record
2) It is necessarty to have a different subform for each department, each
department has it's own unique set of fields which in turn provide an
evalution report.

Here are my current steps
employee selects their name from a drop down menu - enter
employee record opens (linked by ID) on the main form
The main form as an Tab control with 5 separate subforms
subforms 1 to 4 - general organizational information (linked by ID)

subform 5 - specific skills based information, it is this form that I can
not control correctly.
I wish to open the appropriate department subform tied to the employee ID.
At the moment all the departments record are shown. What am I missing here.

Any help will be appreciated

AndyMark

Is the department defined in your employee record?
If so, then you can filter the subform's record source based on the value of
the department field in the employee record.

If I am reading correctly, you have a different subform for each department?
Is that necessary?

Thank you Klaatu but I need to be more exact.

[quoted text clipped - 22 lines]

 

[Klatuu]

To display only the selected department, you can use the subform control's
Link Master Fields and Link Child Fields properties to limit to records to
one department.

--
Dave Hargis, Microsoft Access MVP

Klatuu
sorry for the delay.

In answer to your questions
1) Yes the dept code is on employee record
2) It is necessarty to have a different subform for each department, each
department has it's own unique set of fields which in turn provide an
evalution report.

Here are my current steps
employee selects their name from a drop down menu - enter
employee record opens (linked by ID) on the main form
The main form as an Tab control with 5 separate subforms
subforms 1 to 4 - general organizational information (linked by ID)

subform 5 - specific skills based information, it is this form that I can
not control correctly.
I wish to open the appropriate department subform tied to the employee ID.
At the moment all the departments record are shown. What am I missing here.

Any help will be appreciated

AndyMark

Is the department defined in your employee record?
If so, then you can filter the subform's record source based on the value of
the department field in the employee record.

If I am reading correctly, you have a different subform for each department?
Is that necessary?

Thank you Klaatu but I need to be more exact.

[quoted text clipped - 22 lines]

My Question: how can I automatically open the specific Employee trade skills
subform when the main form is opened.

 

[AndyMark via AccessMonster.com]

Klatuu

I'm back on the case, the problem appears to centre on link fields, here is
what I've found:
I can create the form based on the employee table with fields Name,
Department & Employee ID
in turn I've added a tab control with a subform that displays the
departmental skills based subforms linked to the Employee ID field. This
works very well and I can scroll through each employee and the appropriate
skills based subform and employee data is displayed, great so far. So for
example, Employee #124 is a Mechanic and his trade skills are formatted and
displayed. Quite different from Employee #217 who is an Admin Assistant and
her approriate trade skills.

The problem starts when I open the main form through a Employee Name combo
box (linked by Employee ID). Although it correctly opens to the right main
form employee record and the appropriate department skills based subform it
does not display the dedicated employee data but freezes at the first
departmental record. So Employee #124 Mechanic subform is displayed but the
trade skills are not tied to his ID but to the first Mechanic's (#34) record.
It seem that by adding the combo box control to select the employee record as
in turn delinked the subform data record. What is the next step to close this
loop.

AndyM

To display only the selected department, you can use the subform control's
Link Master Fields and Link Child Fields properties to limit to records to
one department.

Klatuu
sorry for the delay.

[quoted text clipped - 32 lines]

 

[Klatuu]

The behaviour you are experiencing is caused by a strange little Access
quirk. Even though the Access development team's answer was "because it has
always been that way", when you load a form, the subform loads first, so it
will not know that you have opened the form to a specific record necessarily.

I don't know what method you are using to move to the selected record, but
here is what I would try (in order)

1. Try using the Where argument of the OpenForm method to open the main
form using the employee's primary key value.

2. Try adding a requery of the subform in the form's load event.

3. Try putting the requery in the form's current event.


--
Dave Hargis, Microsoft Access MVP

Klatuu

I'm back on the case, the problem appears to centre on link fields, here is
what I've found:
I can create the form based on the employee table with fields Name,
Department & Employee ID
in turn I've added a tab control with a subform that displays the
departmental skills based subforms linked to the Employee ID field. This
works very well and I can scroll through each employee and the appropriate
skills based subform and employee data is displayed, great so far. So for
example, Employee #124 is a Mechanic and his trade skills are formatted and
displayed. Quite different from Employee #217 who is an Admin Assistant and
her approriate trade skills.

The problem starts when I open the main form through a Employee Name combo
box (linked by Employee ID). Although it correctly opens to the right main
form employee record and the appropriate department skills based subform it
does not display the dedicated employee data but freezes at the first
departmental record. So Employee #124 Mechanic subform is displayed but the
trade skills are not tied to his ID but to the first Mechanic's (#34) record.
It seem that by adding the combo box control to select the employee record as
in turn delinked the subform data record. What is the next step to close this
loop.

AndyM

To display only the selected department, you can use the subform control's
Link Master Fields and Link Child Fields properties to limit to records to
one department.

Klatuu
sorry for the delay.

[quoted text clipped - 32 lines]

My Question: how can I automatically open the specific Employee trade skills
subform when the main form is opened.