Requery Issue

[Guest]

Hello Again,

I am saving data to a table and reloading it into a subform afterwards. The
problem is the "Requery" is loading data from the previous record instead of
the current record.

Q. How do I point to current record before issuing the ReQuery.

-Simon

 

[Graham Mandeno]

Hi Simon,

Please provide some more information.

What are you requerying? The main form? The subform? A control?

If it's a form (main or sub), what is the RecordSource of that form?

If it's a subform, what are the link fields?

When you say "the previous" record, do you mean the previous record on the
main form?

 

[Guest]

Hi Graham,

I'm doing the requery on a subform who's Record Source is a table named:
EmployeeCourseJoin

Following is the code being used:

Dim rs As Recordset, db As Database, sql As String
Dim SelectedItem As Variant
Dim ctl As Control
sql = "Select * From EmployeeCourseJoin"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
Set ctl = Me.[lstAdmin]

For Each SelectedItem In lstAdmin.ItemsSelected
rs.AddNew
rs![Training Courses_ID] = Me.lstAdmin.Column(0, SelectedItem)
rs![Employees_ID] = Me.cboEmp_Name.Value
rs("Emp_Name") = Me.cboEmp_Name.Column(1)
rs("Doc_Number") = Me.lstAdmin.Column(1, SelectedItem)
rs.Update
ctl.Selected(SelectedItem) = False
Next

rs.Close
Set db = Nothing
Set rs = Nothing

'Refresh the display
Me.sfrmAdmin.Requery
End Sub

Hi Simon,

Please provide some more information.

What are you requerying? The main form? The subform? A control?

If it's a form (main or sub), what is the RecordSource of that form?

If it's a subform, what are the link fields?

When you say "the previous" record, do you mean the previous record on the
main form?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello Again,

I am saving data to a table and reloading it into a subform afterwards.
The
problem is the "Requery" is loading data from the previous record instead
of
the current record.

Q. How do I point to current record before issuing the ReQuery.

-Simon

 

[Graham Mandeno]

Hi Simon

Sorry, but you still haven't answered two important questions:

1. What are the link fields between the main form and the subform?
I'm guessing that LinkMasterFields is cboEmpName and LinkChildFields is
Employees_ID. Is this correct?

2. What exactly do you mean by "the Requery is loading data from the
previous record instead of the current record"? Do you mean that the
subform shows the courses for a different employee from the one you have
displayed in the main form? Or does the main form change to another record?

Also, remember last week in my answer to you about junction tables I said:

Often the junction table contains only those two fields, but sometimes it
may be appropriate to store additional data there. This must happen ONLY
if
the data are attributes of the COMBINATION of the two foreign keys. For
example, the date enrolled, date completed, and final result are all
attributes of the combination of employee and course.

It appears that you are storing in your junction table the employee's name
and the Doc_Number. The Employee's name is an attribute of the employee,
not the junction, so it should not be in that table at all. Also, I suspect
the same applies to Doc_Number, which is an attribute of the course. Both
these fields are available by creating a query joining the three tables.

It is very rarely appropriate to copy fields from the one-side table into
the many-side table. One example where this is necessary is in an order
entry application, where the price of a product *at the time of the order*
needs to be recorded. In that sense, the price has become an attribute of
the *product ordered*, not just of the product.

Also, some comments on your code:

1. Instead of a "Select *" query, just open the table directly:
Set rs = db.OpenRecordset("EmployeeCourseJoin", dbOpenDynaset)

2. If you want to go to the trouble of setting a control variable to refer
to your listbox, then use it. Either replace all instances of "lstAdmin" or
"Me.lstAdmin" by "ctl" or (better) just refer to lstAdmin directly and get
rid of "ctl".

Answer back about the questions above and we'll try to resolve the Requery
problem.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I'm doing the requery on a subform who's Record Source is a table named:
EmployeeCourseJoin

Following is the code being used:

Dim rs As Recordset, db As Database, sql As String
Dim SelectedItem As Variant
Dim ctl As Control
sql = "Select * From EmployeeCourseJoin"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
Set ctl = Me.[lstAdmin]

For Each SelectedItem In lstAdmin.ItemsSelected
rs.AddNew
rs![Training Courses_ID] = Me.lstAdmin.Column(0, SelectedItem)
rs![Employees_ID] = Me.cboEmp_Name.Value
rs("Emp_Name") = Me.cboEmp_Name.Column(1)
rs("Doc_Number") = Me.lstAdmin.Column(1, SelectedItem)
rs.Update
ctl.Selected(SelectedItem) = False
Next

rs.Close
Set db = Nothing
Set rs = Nothing

'Refresh the display
Me.sfrmAdmin.Requery
End Sub

Hi Simon,

Please provide some more information.

What are you requerying? The main form? The subform? A control?

If it's a form (main or sub), what is the RecordSource of that form?

If it's a subform, what are the link fields?

When you say "the previous" record, do you mean the previous record on
the
main form?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello Again,

I am saving data to a table and reloading it into a subform afterwards.
The
problem is the "Requery" is loading data from the previous record
instead
of
the current record.

Q. How do I point to current record before issuing the ReQuery.

-Simon

 

[Guest]

Graham,

1. The Link Master / Link Child fields are both Employees_ID
2. After the requery the subform shows data from the previous record. If I
navigate forward or backward it will then show the correct data.

-Simon

Hi Simon

Sorry, but you still haven't answered two important questions:

1. What are the link fields between the main form and the subform?
I'm guessing that LinkMasterFields is cboEmpName and LinkChildFields is
Employees_ID. Is this correct?

2. What exactly do you mean by "the Requery is loading data from the
previous record instead of the current record"? Do you mean that the
subform shows the courses for a different employee from the one you have
displayed in the main form? Or does the main form change to another record?

Also, remember last week in my answer to you about junction tables I said:

Often the junction table contains only those two fields, but sometimes it
may be appropriate to store additional data there. This must happen ONLY
if
the data are attributes of the COMBINATION of the two foreign keys. For
example, the date enrolled, date completed, and final result are all
attributes of the combination of employee and course.

It appears that you are storing in your junction table the employee's name
and the Doc_Number. The Employee's name is an attribute of the employee,
not the junction, so it should not be in that table at all. Also, I suspect
the same applies to Doc_Number, which is an attribute of the course. Both
these fields are available by creating a query joining the three tables.

It is very rarely appropriate to copy fields from the one-side table into
the many-side table. One example where this is necessary is in an order
entry application, where the price of a product *at the time of the order*
needs to be recorded. In that sense, the price has become an attribute of
the *product ordered*, not just of the product.

Also, some comments on your code:

1. Instead of a "Select *" query, just open the table directly:
Set rs = db.OpenRecordset("EmployeeCourseJoin", dbOpenDynaset)

2. If you want to go to the trouble of setting a control variable to refer
to your listbox, then use it. Either replace all instances of "lstAdmin" or
"Me.lstAdmin" by "ctl" or (better) just refer to lstAdmin directly and get
rid of "ctl".

Answer back about the questions above and we'll try to resolve the Requery
problem.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I'm doing the requery on a subform who's Record Source is a table named:
EmployeeCourseJoin

Following is the code being used:

Dim rs As Recordset, db As Database, sql As String
Dim SelectedItem As Variant
Dim ctl As Control
sql = "Select * From EmployeeCourseJoin"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
Set ctl = Me.[lstAdmin]

For Each SelectedItem In lstAdmin.ItemsSelected
rs.AddNew
rs![Training Courses_ID] = Me.lstAdmin.Column(0, SelectedItem)
rs![Employees_ID] = Me.cboEmp_Name.Value
rs("Emp_Name") = Me.cboEmp_Name.Column(1)
rs("Doc_Number") = Me.lstAdmin.Column(1, SelectedItem)
rs.Update
ctl.Selected(SelectedItem) = False
Next

rs.Close
Set db = Nothing
Set rs = Nothing

'Refresh the display
Me.sfrmAdmin.Requery
End Sub

Hi Simon,

Please provide some more information.

What are you requerying? The main form? The subform? A control?

If it's a form (main or sub), what is the RecordSource of that form?

If it's a subform, what are the link fields?

When you say "the previous" record, do you mean the previous record on
the
main form?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello Again,

I am saving data to a table and reloading it into a subform afterwards.
The
problem is the "Requery" is loading data from the previous record
instead
of
the current record.

Q. How do I point to current record before issuing the ReQuery.

-Simon

 

[Guest]

Hi Graham,

Dude, I borrowed a line of code from another thread in this forum and the
ReQuery is now displaying the correct info. I inserted this line just before
the ReQuery line:

DoCmd.RunCommand acCmdSaveRecord

But your comments about duplicate fields in the EmployeeCourseJoin table
have me concerned.

It appears that you are storing in your junction table the employee's name
and the Doc_Number. The Employee's name is an attribute of the employee,
not the junction, so it should not be in that table at all.

Your correct about that. I'll remove the Emp_Name field from the table, but
I think I need to keep the Doc_Number field though. What do you think?

-Simon

Hi Simon

Sorry, but you still haven't answered two important questions:

1. What are the link fields between the main form and the subform?
I'm guessing that LinkMasterFields is cboEmpName and LinkChildFields is
Employees_ID. Is this correct?

2. What exactly do you mean by "the Requery is loading data from the
previous record instead of the current record"? Do you mean that the
subform shows the courses for a different employee from the one you have
displayed in the main form? Or does the main form change to another record?

Also, remember last week in my answer to you about junction tables I said:

Often the junction table contains only those two fields, but sometimes it
may be appropriate to store additional data there. This must happen ONLY
if
the data are attributes of the COMBINATION of the two foreign keys. For
example, the date enrolled, date completed, and final result are all
attributes of the combination of employee and course.

It appears that you are storing in your junction table the employee's name
and the Doc_Number. The Employee's name is an attribute of the employee,
not the junction, so it should not be in that table at all. Also, I suspect
the same applies to Doc_Number, which is an attribute of the course. Both
these fields are available by creating a query joining the three tables.

It is very rarely appropriate to copy fields from the one-side table into
the many-side table. One example where this is necessary is in an order
entry application, where the price of a product *at the time of the order*
needs to be recorded. In that sense, the price has become an attribute of
the *product ordered*, not just of the product.

Also, some comments on your code:

1. Instead of a "Select *" query, just open the table directly:
Set rs = db.OpenRecordset("EmployeeCourseJoin", dbOpenDynaset)

2. If you want to go to the trouble of setting a control variable to refer
to your listbox, then use it. Either replace all instances of "lstAdmin" or
"Me.lstAdmin" by "ctl" or (better) just refer to lstAdmin directly and get
rid of "ctl".

Answer back about the questions above and we'll try to resolve the Requery
problem.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I'm doing the requery on a subform who's Record Source is a table named:
EmployeeCourseJoin

Following is the code being used:

Dim rs As Recordset, db As Database, sql As String
Dim SelectedItem As Variant
Dim ctl As Control
sql = "Select * From EmployeeCourseJoin"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
Set ctl = Me.[lstAdmin]

For Each SelectedItem In lstAdmin.ItemsSelected
rs.AddNew
rs![Training Courses_ID] = Me.lstAdmin.Column(0, SelectedItem)
rs![Employees_ID] = Me.cboEmp_Name.Value
rs("Emp_Name") = Me.cboEmp_Name.Column(1)
rs("Doc_Number") = Me.lstAdmin.Column(1, SelectedItem)
rs.Update
ctl.Selected(SelectedItem) = False
Next

rs.Close
Set db = Nothing
Set rs = Nothing

'Refresh the display
Me.sfrmAdmin.Requery
End Sub

Hi Simon,

Please provide some more information.

What are you requerying? The main form? The subform? A control?

If it's a form (main or sub), what is the RecordSource of that form?

If it's a subform, what are the link fields?

When you say "the previous" record, do you mean the previous record on
the
main form?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello Again,

I am saving data to a table and reloading it into a subform afterwards.
The
problem is the "Requery" is loading data from the previous record
instead
of
the current record.

Q. How do I point to current record before issuing the ReQuery.

-Simon

 

[Graham Mandeno]

Hi Simon

Answers inline...

Hi Graham,

Dude, I borrowed a line of code from another thread in this forum and the
ReQuery is now displaying the correct info. I inserted this line just
before
the ReQuery line:

DoCmd.RunCommand acCmdSaveRecord

This worries me. Is this a new employee record you are just adding, which
has not yet been saved? If so, then it indicates you have not set
relational integrity between your Employees and EmployeeCourseJoin tables.
You should definitely do this, and you should therefore save the new
Employee record *before* adding the EmployeeCourseJoin records

If both your link field properties are set to "Employees_ID" then the
records in your subform should be those linked to the Employees_ID control
on your main form. Is it possible that this holds a different value from
that in your cboEmp_Name combo box?

But your comments about duplicate fields in the EmployeeCourseJoin table
have me concerned.


Your correct about that. I'll remove the Emp_Name field from the table,
but
I think I need to keep the Doc_Number field though. What do you think?

It depends what Doc_Number is. It is obviously an attribute of a course, as
it's present in the Courses table. It should only be copied to the junction
table if (a) you need to record a "snapshot" of its value at the time that
particular student enrolled for the course, or (b) if its value may be
changed for that particular enrolment record.

If neither of these conditions apply, then it should not be present in the
junction table.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

-Simon

Hi Simon

Sorry, but you still haven't answered two important questions:

1. What are the link fields between the main form and the subform?
I'm guessing that LinkMasterFields is cboEmpName and LinkChildFields is
Employees_ID. Is this correct?

2. What exactly do you mean by "the Requery is loading data from the
previous record instead of the current record"? Do you mean that the
subform shows the courses for a different employee from the one you have
displayed in the main form? Or does the main form change to another
record?

Also, remember last week in my answer to you about junction tables I
said:

Often the junction table contains only those two fields, but sometimes
it
may be appropriate to store additional data there. This must happen
ONLY
if
the data are attributes of the COMBINATION of the two foreign keys.
For
example, the date enrolled, date completed, and final result are all
attributes of the combination of employee and course.

It appears that you are storing in your junction table the employee's
name
and the Doc_Number. The Employee's name is an attribute of the employee,
not the junction, so it should not be in that table at all. Also, I
suspect
the same applies to Doc_Number, which is an attribute of the course.
Both
these fields are available by creating a query joining the three tables.

It is very rarely appropriate to copy fields from the one-side table into
the many-side table. One example where this is necessary is in an order
entry application, where the price of a product *at the time of the
order*
needs to be recorded. In that sense, the price has become an attribute
of
the *product ordered*, not just of the product.

Also, some comments on your code:

1. Instead of a "Select *" query, just open the table directly:
Set rs = db.OpenRecordset("EmployeeCourseJoin", dbOpenDynaset)

2. If you want to go to the trouble of setting a control variable to
refer
to your listbox, then use it. Either replace all instances of "lstAdmin"
or
"Me.lstAdmin" by "ctl" or (better) just refer to lstAdmin directly and
get
rid of "ctl".

Answer back about the questions above and we'll try to resolve the
Requery
problem.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I'm doing the requery on a subform who's Record Source is a table
named:
EmployeeCourseJoin

Following is the code being used:

Dim rs As Recordset, db As Database, sql As String
Dim SelectedItem As Variant
Dim ctl As Control
sql = "Select * From EmployeeCourseJoin"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
Set ctl = Me.[lstAdmin]

For Each SelectedItem In lstAdmin.ItemsSelected
rs.AddNew
rs![Training Courses_ID] = Me.lstAdmin.Column(0, SelectedItem)
rs![Employees_ID] = Me.cboEmp_Name.Value
rs("Emp_Name") = Me.cboEmp_Name.Column(1)
rs("Doc_Number") = Me.lstAdmin.Column(1, SelectedItem)
rs.Update
ctl.Selected(SelectedItem) = False
Next

rs.Close
Set db = Nothing
Set rs = Nothing

'Refresh the display
Me.sfrmAdmin.Requery
End Sub

:

Hi Simon,

Please provide some more information.

What are you requerying? The main form? The subform? A control?

If it's a form (main or sub), what is the RecordSource of that form?

If it's a subform, what are the link fields?

When you say "the previous" record, do you mean the previous record on
the
main form?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello Again,

I am saving data to a table and reloading it into a subform
afterwards.
The
problem is the "Requery" is loading data from the previous record
instead
of
the current record.

Q. How do I point to current record before issuing the ReQuery.

-Simon

 

[Guest]

Hi Graham,

Is this a new employee record you are just adding, which
has not yet been saved? If so, then it indicates you have not set
relational integrity between your Employees and EmployeeCourseJoin tables.

Yes about it being a new employee record, and No about the relational
integrity between Employees and EmployeeCourseJoin tables. In the
relationships popup all the boxes are checked "Enforce Referential Integrity,
Cascade Update and Cascade Delete".

If both your link field properties are set to "Employees_ID" then the
records in your subform should be those linked to the Employees_ID control
on your main form.

EmployeeCourseJoin has two foreign keys "Training Courses_ID and
Employees_ID".
Does this mean I need to change the Link Parent / Link Child fields to match
these Foreign Keys?

It depends what Doc_Number is. It is obviously an attribute of a course, as
it's present in the Courses table. It should only be copied to the junction
table if (a) you need to record a "snapshot" of its value at the time that
particular student enrolled for the course, or (b) if its value may be
changed for that particular enrolment record.

The Doc_Number field serves as a reference of each course assigned to an
employee. Adjacent to the Doc_Number field I have another field used to input
a date for when the course is completed. So in the subform it looks like this:

Doc Number Date Completed
ADM-001 11/21/04
ADM-002 02/15/05


The EmployeeCourseJoin table has these fields:
Training Courses_ID ( Primary Key from Training Courses table )
Employees_ID ( Primary Key from Employees table )
Date_Completed
Doc_Number

The Main table has these fields:
Main_ID ( Primary Key )
Employees_ID ( FK from Employees table )
Departments_ID ( FK from Departments table )
Managers_ID ( FK from Managers table )

So far though, it's all working pretty well without any error messages. But
as always, expert opinions and suggested changes are always welcome. And
believe me, it's all very much appreciated.

-Simon

Hi Simon

Answers inline...

Hi Graham,

Dude, I borrowed a line of code from another thread in this forum and the
ReQuery is now displaying the correct info. I inserted this line just
before
the ReQuery line:

DoCmd.RunCommand acCmdSaveRecord

This worries me. Is this a new employee record you are just adding, which
has not yet been saved? If so, then it indicates you have not set
relational integrity between your Employees and EmployeeCourseJoin tables.
You should definitely do this, and you should therefore save the new
Employee record *before* adding the EmployeeCourseJoin records

If both your link field properties are set to "Employees_ID" then the
records in your subform should be those linked to the Employees_ID control
on your main form. Is it possible that this holds a different value from
that in your cboEmp_Name combo box?

But your comments about duplicate fields in the EmployeeCourseJoin table
have me concerned.


Your correct about that. I'll remove the Emp_Name field from the table,
but
I think I need to keep the Doc_Number field though. What do you think?

It depends what Doc_Number is. It is obviously an attribute of a course, as
it's present in the Courses table. It should only be copied to the junction
table if (a) you need to record a "snapshot" of its value at the time that
particular student enrolled for the course, or (b) if its value may be
changed for that particular enrolment record.

If neither of these conditions apply, then it should not be present in the
junction table.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

-Simon

Hi Simon

Sorry, but you still haven't answered two important questions:

1. What are the link fields between the main form and the subform?
I'm guessing that LinkMasterFields is cboEmpName and LinkChildFields is
Employees_ID. Is this correct?

2. What exactly do you mean by "the Requery is loading data from the
previous record instead of the current record"? Do you mean that the
subform shows the courses for a different employee from the one you have
displayed in the main form? Or does the main form change to another
record?

Also, remember last week in my answer to you about junction tables I
said:

Often the junction table contains only those two fields, but sometimes
it
may be appropriate to store additional data there. This must happen
ONLY
if
the data are attributes of the COMBINATION of the two foreign keys.
For
example, the date enrolled, date completed, and final result are all
attributes of the combination of employee and course.

It appears that you are storing in your junction table the employee's
name
and the Doc_Number. The Employee's name is an attribute of the employee,
not the junction, so it should not be in that table at all. Also, I
suspect
the same applies to Doc_Number, which is an attribute of the course.
Both
these fields are available by creating a query joining the three tables.

It is very rarely appropriate to copy fields from the one-side table into
the many-side table. One example where this is necessary is in an order
entry application, where the price of a product *at the time of the
order*
needs to be recorded. In that sense, the price has become an attribute
of
the *product ordered*, not just of the product.

Also, some comments on your code:

1. Instead of a "Select *" query, just open the table directly:
Set rs = db.OpenRecordset("EmployeeCourseJoin", dbOpenDynaset)

2. If you want to go to the trouble of setting a control variable to
refer
to your listbox, then use it. Either replace all instances of "lstAdmin"
or
"Me.lstAdmin" by "ctl" or (better) just refer to lstAdmin directly and
get
rid of "ctl".

Answer back about the questions above and we'll try to resolve the
Requery
problem.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Hi Graham,

I'm doing the requery on a subform who's Record Source is a table
named:
EmployeeCourseJoin

Following is the code being used:

Dim rs As Recordset, db As Database, sql As String
Dim SelectedItem As Variant
Dim ctl As Control
sql = "Select * From EmployeeCourseJoin"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
Set ctl = Me.[lstAdmin]

For Each SelectedItem In lstAdmin.ItemsSelected
rs.AddNew
rs![Training Courses_ID] = Me.lstAdmin.Column(0, SelectedItem)
rs![Employees_ID] = Me.cboEmp_Name.Value
rs("Emp_Name") = Me.cboEmp_Name.Column(1)
rs("Doc_Number") = Me.lstAdmin.Column(1, SelectedItem)
rs.Update
ctl.Selected(SelectedItem) = False
Next

rs.Close
Set db = Nothing
Set rs = Nothing

'Refresh the display
Me.sfrmAdmin.Requery
End Sub

:

Hi Simon,

Please provide some more information.

What are you requerying? The main form? The subform? A control?

If it's a form (main or sub), what is the RecordSource of that form?

If it's a subform, what are the link fields?

When you say "the previous" record, do you mean the previous record on
the
main form?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello Again,

I am saving data to a table and reloading it into a subform
afterwards.
The
problem is the "Requery" is loading data from the previous record
instead
of
the current record.

Q. How do I point to current record before issuing the ReQuery.

-Simon

 

[Graham Mandeno]

Hi Simon

Inline...

Hi Graham,



Yes about it being a new employee record, and No about the relational
integrity between Employees and EmployeeCourseJoin tables. In the
relationships popup all the boxes are checked "Enforce Referential
Integrity,
Cascade Update and Cascade Delete".

Good to see you are using relational integrity properly

However, if this is a new employee record that has not yet been saved, I
cannot see how you have been allowed to save related records with FK values
related to a PK which does not yet exist. (Did that make sense?

In any case, the SaveRecord should happen *before* you start adding related
records to the EmployeeCourseJoin table.

EmployeeCourseJoin has two foreign keys "Training Courses_ID and
Employees_ID".
Does this mean I need to change the Link Parent / Link Child fields to
match
these Foreign Keys?

No no no... LinkChildFields should be "Employees_ID" (the FK *field* in the
subform's RecordSource) and LinkMasterFields should be the name of the PK
field in your main form's RecordSource OR the name of a *control* on your
main form whose value is an existing PK value. For example, it's common to
have an unbound combo box with its RowSource based on your parent table as
the LinkMasterFields.

The Doc_Number field serves as a reference of each course assigned to an
employee. Adjacent to the Doc_Number field I have another field used to
input
a date for when the course is completed. So in the subform it looks like
this:

Doc Number Date Completed
ADM-001 11/21/04
ADM-002 02/15/05

OK, so it seems that Doc_Number is the name of the course. Right? In
which case, it should NOT be copied to the junction table. Let's say that
ADM-001 has a Training_Courses_ID of 99. The course with ID=99 will always
be ADM-001, and will never be anything other that ADM-001, so "ADM-001"
should not be stored in more that one location in the database.

On the other hand, Date_Completed is an attribute of the junction, because
different employees will complete ADM-001 on different dates and the saam
employee might complete ADM-002 on a different date from ADM-001. It is
therefore correct for that field to be in the junction table.

The EmployeeCourseJoin table has these fields:
Training Courses_ID ( Primary Key from Training Courses table )
Employees_ID ( Primary Key from Employees table )
Date_Completed
Doc_Number

The Main table has these fields:
Main_ID ( Primary Key )
Employees_ID ( FK from Employees table )
Departments_ID ( FK from Departments table )
Managers_ID ( FK from Managers table )

I don't quite see the point if this table, but that's another story

So far though, it's all working pretty well without any error messages.
But
as always, expert opinions and suggested changes are always welcome. And
believe me, it's all very much appreciated.

Glad to be able to help!