Updating through subform not functioning properly or as desired

[Guest]

I have a problem with a subform not updating with table information after the
initial input.

First, let me present the setup of the main form creation.

1. I have created a main form (Employee Training) using two tab pages on the
form.

2. The first tab (Employee Information) is used to collect data for the
primary employee table.

3. The second tab (Job Competency) has an embedded subform to collect
training that an employee has received and stores it in a Job Competency
table.

Here is the problem in the order experience.

1. Starting up the form and entering the employee information on first tab
works fine.

2. Change to second tab to enter training information (the Job Competency
table has a relationship to the Personnel table) by selecting the employee ID
from a ComboBox and it works fine.

3. Go back to first tab to enter a new employee and all is well.

4. Select the second tab again to update training information about this new
employee and the employee ID is not present in the drop ComboBox but [all]
previous are.

The workaround that I have is by entering one employee and closing the form
and reopening to enter a new employee. This defeats the objective of being
able to update the two tables through the use of one form with tab pages.

Looking forward to all replies on this issue.

Sincerely,

Tony D. Abel

 

[Guest]

The problem is that the combo box does not automatically requery itself after
you've added the new record.

You can use some event to requery it manually:

Me![YourComboBox].Requery

But if the main form's RecordSource is the Personnel table, and the
subform's LinkMasterFields and LinkChildFields are set to the shared field,
presumably EmployeeID, you wouldn't need to do this. All of the data
competency "child records" would be owned by the employee entered on tab 1.

Hope that helps.

Sprinks

 

[Guest]

Good day Sprinks.

I would like to thank you for providing assistance to my issue.

I try your suggestion in an OnClick function for the ComboBox in question. I
receive strange results. If I type in the first few numbers of the Employee
ID to find the newly entered employee it found the ID fine. When the ComboBox
lost focus as I begin to enter the Job Competency info the employee the
Employee ID number changed randomly to a employee already entered (Looking at
the Personnel table it had given the new employee job competency attributes
of the randomly selected employee). As I went back to the ComboBox to retype
in the new employee ID again the same sequence happens as describe previous.

-Tony

The problem is that the combo box does not automatically requery itself after
you've added the new record.

You can use some event to requery it manually:

Me![YourComboBox].Requery

But if the main form's RecordSource is the Personnel table, and the
subform's LinkMasterFields and LinkChildFields are set to the shared field,
presumably EmployeeID, you wouldn't need to do this. All of the data
competency "child records" would be owned by the employee entered on tab 1.

Hope that helps.

Sprinks

I have a problem with a subform not updating with table information after the
initial input.

First, let me present the setup of the main form creation.

1. I have created a main form (Employee Training) using two tab pages on the
form.

2. The first tab (Employee Information) is used to collect data for the
primary employee table.

3. The second tab (Job Competency) has an embedded subform to collect
training that an employee has received and stores it in a Job Competency
table.

Here is the problem in the order experience.

1. Starting up the form and entering the employee information on first tab
works fine.

2. Change to second tab to enter training information (the Job Competency
table has a relationship to the Personnel table) by selecting the employee ID
from a ComboBox and it works fine.

3. Go back to first tab to enter a new employee and all is well.

4. Select the second tab again to update training information about this new
employee and the employee ID is not present in the drop ComboBox but [all]
previous are.

The workaround that I have is by entering one employee and closing the form
and reopening to enter a new employee. This defeats the objective of being
able to update the two tables through the use of one form with tab pages.

Looking forward to all replies on this issue.

Sincerely,

Tony D. Abel

 

[Guest]

Tony,

Please post the following:

Main Form:
RecordSource property
The name of the primary key of the RecordSource

Subform (itself):
RecordSource property
The name of the primary key of the RecordSource
The name of the foreign key field corresponding to the primary key of the
main form's RecordSource

Subform (Control):
LinkMasterFields property
LinkChildFields property

ComboBox:
Its location (i.e., is it ON the subform? the page header?, etc.)
RowSource property
ControlSource property

Sprinks

Good day Sprinks.

I would like to thank you for providing assistance to my issue.

I try your suggestion in an OnClick function for the ComboBox in question. I
receive strange results. If I type in the first few numbers of the Employee
ID to find the newly entered employee it found the ID fine. When the ComboBox
lost focus as I begin to enter the Job Competency info the employee the
Employee ID number changed randomly to a employee already entered (Looking at
the Personnel table it had given the new employee job competency attributes
of the randomly selected employee). As I went back to the ComboBox to retype
in the new employee ID again the same sequence happens as describe previous.

-Tony

The problem is that the combo box does not automatically requery itself after
you've added the new record.

You can use some event to requery it manually:

Me![YourComboBox].Requery

But if the main form's RecordSource is the Personnel table, and the
subform's LinkMasterFields and LinkChildFields are set to the shared field,
presumably EmployeeID, you wouldn't need to do this. All of the data
competency "child records" would be owned by the employee entered on tab 1.

Hope that helps.

Sprinks

I have a problem with a subform not updating with table information after the
initial input.

First, let me present the setup of the main form creation.

1. I have created a main form (Employee Training) using two tab pages on the
form.

2. The first tab (Employee Information) is used to collect data for the
primary employee table.

3. The second tab (Job Competency) has an embedded subform to collect
training that an employee has received and stores it in a Job Competency
table.

Here is the problem in the order experience.

1. Starting up the form and entering the employee information on first tab
works fine.

2. Change to second tab to enter training information (the Job Competency
table has a relationship to the Personnel table) by selecting the employee ID
from a ComboBox and it works fine.

3. Go back to first tab to enter a new employee and all is well.

4. Select the second tab again to update training information about this new
employee and the employee ID is not present in the drop ComboBox but [all]
previous are.

The workaround that I have is by entering one employee and closing the form
and reopening to enter a new employee. This defeats the objective of being
able to update the two tables through the use of one form with tab pages.

Looking forward to all replies on this issue.

Sincerely,

Tony D. Abel

 

[Guest]

Good day Sprinks.

Here is your request:

The name of the primary key of the RecordSource:
PersonnelID

Subform (itself):
RecordSource property
The name of the primary key of the RecordSource
The name of the foreign key field corresponding to the primary key of the
main form's RecordSource
JobCompetencyID
EmployeeID

Subform (Control):
LinkMasterFields property
LinkChildFields property
? to both

ComboBox:
Its location (i.e., is it ON the subform? the page header?, etc.)
RowSource property
ControlSource property
It is located in the Detail Section of the subform
SELECT Personnel.PersonnelID FROM Personnel;
EmployeeID

Sprink, I hope this provides you additional insight. I had a thought of
applying your first solution (![YourComboBox].Requery) to the On_Click method
of the tab page that the subform resides.

-Tony

Tony,

Please post the following:

Main Form:
RecordSource property
The name of the primary key of the RecordSource

Subform (itself):
RecordSource property
The name of the primary key of the RecordSource
The name of the foreign key field corresponding to the primary key of the
main form's RecordSource

Subform (Control):
LinkMasterFields property
LinkChildFields property

ComboBox:
Its location (i.e., is it ON the subform? the page header?, etc.)
RowSource property
ControlSource property

Sprinks

Good day Sprinks.

I would like to thank you for providing assistance to my issue.

I try your suggestion in an OnClick function for the ComboBox in question. I
receive strange results. If I type in the first few numbers of the Employee
ID to find the newly entered employee it found the ID fine. When the ComboBox
lost focus as I begin to enter the Job Competency info the employee the
Employee ID number changed randomly to a employee already entered (Looking at
the Personnel table it had given the new employee job competency attributes
of the randomly selected employee). As I went back to the ComboBox to retype
in the new employee ID again the same sequence happens as describe previous.

-Tony

The problem is that the combo box does not automatically requery itself after
you've added the new record.

You can use some event to requery it manually:

Me![YourComboBox].Requery

But if the main form's RecordSource is the Personnel table, and the
subform's LinkMasterFields and LinkChildFields are set to the shared field,
presumably EmployeeID, you wouldn't need to do this. All of the data
competency "child records" would be owned by the employee entered on tab 1.

Hope that helps.

Sprinks


:

I have a problem with a subform not updating with table information after the
initial input.

First, let me present the setup of the main form creation.

1. I have created a main form (Employee Training) using two tab pages on the
form.

2. The first tab (Employee Information) is used to collect data for the
primary employee table.

3. The second tab (Job Competency) has an embedded subform to collect
training that an employee has received and stores it in a Job Competency
table.

Here is the problem in the order experience.

1. Starting up the form and entering the employee information on first tab
works fine.

2. Change to second tab to enter training information (the Job Competency
table has a relationship to the Personnel table) by selecting the employee ID
from a ComboBox and it works fine.

3. Go back to first tab to enter a new employee and all is well.

4. Select the second tab again to update training information about this new
employee and the employee ID is not present in the drop ComboBox but [all]
previous are.

The workaround that I have is by entering one employee and closing the form
and reopening to enter a new employee. This defeats the objective of being
able to update the two tables through the use of one form with tab pages.

Looking forward to all replies on this issue.

Sincerely,

Tony D. Abel

 

[Guest]

Although you didn't specify the subform's RecordSource, I assume it is
something like JobCompetencies.

By answering "?" to the LinkMasterFields and LinkChildFields properties, do
you mean they are currently blank, or that you couldn't find them? If the
latter, let me explain--when you embed a form onto another form as a subform,
a subform "control" is created in the main form. This control has its own
properties--the name of the form that has been embedded, the two linking
properties, and others. If you click the subform from the main form with the
Properties window displayed, you should be able to see these properties. If
you double-click on the subform control, you will actually be editing the
underlying form itself, and see ITS properties.

Based on what you've told me, there is a one-to-many link between Personnel
and JobCompetencies. Ownership of child records in the detail table should
be established by setting the LinkMasterFields property to PersonnelID and
the LinkChildFields to EmployeeID. This configuration will automatically
enter the PersonnelID of the parent record into any records created on the
subform--whenever you show a parent record, you will see all of the
associated child records. Since the ID is automatically entered, you don't
need nor want a combo box to choose it, so you can delete it from your
subform.

Hope that helps.
Sprinks

Good day Sprinks.

Here is your request:

The name of the primary key of the RecordSource:
PersonnelID

Subform (itself):
RecordSource property
The name of the primary key of the RecordSource
The name of the foreign key field corresponding to the primary key of the
main form's RecordSource
JobCompetencyID
EmployeeID

Subform (Control):
LinkMasterFields property
LinkChildFields property
? to both

ComboBox:
Its location (i.e., is it ON the subform? the page header?, etc.)
RowSource property
ControlSource property
It is located in the Detail Section of the subform
SELECT Personnel.PersonnelID FROM Personnel;
EmployeeID

Sprink, I hope this provides you additional insight. I had a thought of
applying your first solution (![YourComboBox].Requery) to the On_Click method
of the tab page that the subform resides.

-Tony

Tony,

Please post the following:

Main Form:
RecordSource property
The name of the primary key of the RecordSource

Subform (itself):
RecordSource property
The name of the primary key of the RecordSource
The name of the foreign key field corresponding to the primary key of the
main form's RecordSource

Subform (Control):
LinkMasterFields property
LinkChildFields property

ComboBox:
Its location (i.e., is it ON the subform? the page header?, etc.)
RowSource property
ControlSource property

Sprinks

Good day Sprinks.

I would like to thank you for providing assistance to my issue.

I try your suggestion in an OnClick function for the ComboBox in question. I
receive strange results. If I type in the first few numbers of the Employee
ID to find the newly entered employee it found the ID fine. When the ComboBox
lost focus as I begin to enter the Job Competency info the employee the
Employee ID number changed randomly to a employee already entered (Looking at
the Personnel table it had given the new employee job competency attributes
of the randomly selected employee). As I went back to the ComboBox to retype
in the new employee ID again the same sequence happens as describe previous.

-Tony

:

The problem is that the combo box does not automatically requery itself after
you've added the new record.

You can use some event to requery it manually:

Me![YourComboBox].Requery

But if the main form's RecordSource is the Personnel table, and the
subform's LinkMasterFields and LinkChildFields are set to the shared field,
presumably EmployeeID, you wouldn't need to do this. All of the data
competency "child records" would be owned by the employee entered on tab 1.

Hope that helps.

Sprinks


:

I have a problem with a subform not updating with table information after the
initial input.

First, let me present the setup of the main form creation.

1. I have created a main form (Employee Training) using two tab pages on the
form.

2. The first tab (Employee Information) is used to collect data for the
primary employee table.

3. The second tab (Job Competency) has an embedded subform to collect
training that an employee has received and stores it in a Job Competency
table.

Here is the problem in the order experience.

1. Starting up the form and entering the employee information on first tab
works fine.

2. Change to second tab to enter training information (the Job Competency
table has a relationship to the Personnel table) by selecting the employee ID
from a ComboBox and it works fine.

3. Go back to first tab to enter a new employee and all is well.

4. Select the second tab again to update training information about this new
employee and the employee ID is not present in the drop ComboBox but [all]
previous are.

The workaround that I have is by entering one employee and closing the form
and reopening to enter a new employee. This defeats the objective of being
able to update the two tables through the use of one form with tab pages.

Looking forward to all replies on this issue.

Sincerely,

Tony D. Abel

 

[Guest]

Sprinks,

The problem was in the LinkMasterFields and LinkChildFields properties not
having a value assigned.

I did as you suggested and now the control functions as expected. I had to
leave the ComboBox in for the ability to assign one Employee (ID) to multiple
competencies in [many] departments.

I deeply appreciate your time and effort with assisting me on this problem.
Great job Sprinks

Have a great day.

-Tony

Although you didn't specify the subform's RecordSource, I assume it is
something like JobCompetencies.

By answering "?" to the LinkMasterFields and LinkChildFields properties, do
you mean they are currently blank, or that you couldn't find them? If the
latter, let me explain--when you embed a form onto another form as a subform,
a subform "control" is created in the main form. This control has its own
properties--the name of the form that has been embedded, the two linking
properties, and others. If you click the subform from the main form with the
Properties window displayed, you should be able to see these properties. If
you double-click on the subform control, you will actually be editing the
underlying form itself, and see ITS properties.

Based on what you've told me, there is a one-to-many link between Personnel
and JobCompetencies. Ownership of child records in the detail table should
be established by setting the LinkMasterFields property to PersonnelID and
the LinkChildFields to EmployeeID. This configuration will automatically
enter the PersonnelID of the parent record into any records created on the
subform--whenever you show a parent record, you will see all of the
associated child records. Since the ID is automatically entered, you don't
need nor want a combo box to choose it, so you can delete it from your
subform.

Hope that helps.
Sprinks

Good day Sprinks.

Here is your request:

The name of the primary key of the RecordSource:
PersonnelID

Subform (itself):
RecordSource property
The name of the primary key of the RecordSource
The name of the foreign key field corresponding to the primary key of the
main form's RecordSource
JobCompetencyID
EmployeeID

Subform (Control):
LinkMasterFields property
LinkChildFields property
? to both

ComboBox:
Its location (i.e., is it ON the subform? the page header?, etc.)
RowSource property
ControlSource property
It is located in the Detail Section of the subform
SELECT Personnel.PersonnelID FROM Personnel;
EmployeeID

Sprink, I hope this provides you additional insight. I had a thought of
applying your first solution (![YourComboBox].Requery) to the On_Click method
of the tab page that the subform resides.

-Tony

Tony,

Please post the following:

Main Form:
RecordSource property
The name of the primary key of the RecordSource

Subform (itself):
RecordSource property
The name of the primary key of the RecordSource
The name of the foreign key field corresponding to the primary key of the
main form's RecordSource

Subform (Control):
LinkMasterFields property
LinkChildFields property

ComboBox:
Its location (i.e., is it ON the subform? the page header?, etc.)
RowSource property
ControlSource property

Sprinks

:

Good day Sprinks.

I would like to thank you for providing assistance to my issue.

I try your suggestion in an OnClick function for the ComboBox in question. I
receive strange results. If I type in the first few numbers of the Employee
ID to find the newly entered employee it found the ID fine. When the ComboBox
lost focus as I begin to enter the Job Competency info the employee the
Employee ID number changed randomly to a employee already entered (Looking at
the Personnel table it had given the new employee job competency attributes
of the randomly selected employee). As I went back to the ComboBox to retype
in the new employee ID again the same sequence happens as describe previous.

-Tony

:

The problem is that the combo box does not automatically requery itself after
you've added the new record.

You can use some event to requery it manually:

Me![YourComboBox].Requery

But if the main form's RecordSource is the Personnel table, and the
subform's LinkMasterFields and LinkChildFields are set to the shared field,
presumably EmployeeID, you wouldn't need to do this. All of the data
competency "child records" would be owned by the employee entered on tab 1.

Hope that helps.

Sprinks


:

I have a problem with a subform not updating with table information after the
initial input.

First, let me present the setup of the main form creation.

1. I have created a main form (Employee Training) using two tab pages on the
form.

2. The first tab (Employee Information) is used to collect data for the
primary employee table.

3. The second tab (Job Competency) has an embedded subform to collect
training that an employee has received and stores it in a Job Competency
table.

Here is the problem in the order experience.

1. Starting up the form and entering the employee information on first tab
works fine.

2. Change to second tab to enter training information (the Job Competency
table has a relationship to the Personnel table) by selecting the employee ID
from a ComboBox and it works fine.

3. Go back to first tab to enter a new employee and all is well.

4. Select the second tab again to update training information about this new
employee and the employee ID is not present in the drop ComboBox but [all]
previous are.

The workaround that I have is by entering one employee and closing the form
and reopening to enter a new employee. This defeats the objective of being
able to update the two tables through the use of one form with tab pages.

Looking forward to all replies on this issue.

Sincerely,

Tony D. Abel