Problem updating simple form 1

[topgeek]

I've got a really simple database consisting of three tables:

tblCourse
---------
CourseID (PK)
CourseName
CourseSupplier
CourseDuration

tblAttend
---------
CourseID
EmployeeID
CourseStart
CourseEnd

tblEmployee
-----------
EmployeeID (PK)
EmployeeDept
EmployeeFirst
EmployeeLast
EmployeeNumber
EmployeeTitle

I have linked the tables via their primary keys to their associated foreign keys in tblAttend.

I have created a main form with a subform using the Forms Wizard so that course details are shown against each employee in the subform.

I can enter employee data in the main form but when I try to enter data in the sub-form I get the error:

"Field cannot be updated"

I can then click on the OK button and enter data, however the data is not saved.

Can anyone assist please?

 

[SJMcAbney]

What is the query you have set as the RecordSource of the subform?

Stewart J. McAbney | Talk History

 

[topgeek]

Thank you very much for responding.

The Record Source is as follows:

SELECT tblCourses.CourseName, tblCourses.CourseSupplier, tblCourses.CourseDuration, tblAttend.CourseStart, tblAttend.CourseEnd, tblAttend.EmployeeID FROM tblCourses INNER JOIN tblAttend ON tblCourses.CourseID=tblAttend.CourseID;

 

[ssatech]

Stewart,

I put your (3) table data in a sample database I created:

Try this for record source for the subform:

SELECT tblAttend.CourseID, tblAttend.EmployeeID, tblAttend.CourseStart, tblAttend.CourseEnd, tblCourse.CourseName, tblCourse.CourseSupplier, tblCourse.CourseDuration
FROM tblCourse INNER JOIN tblAttend ON tblCourse.CourseID = tblAttend.CourseID;

Link Master & Child by EmployeeID

Put the field/control source 'EmployeeID' underneath the form footer of your form.

See if it works for you.

 

[topgeek]

Hi Stuart,

Thanks for the instructions.

Not sure how I "Link Master & Child by EmployeeID"

I just use the wizard to generate the sub-form

Thanks

 

[ssatech]

topgeek,

1.)Create a new form in design view. In the record source for this form - cut & paste this:

SELECT tblAttend.CourseID, tblAttend.EmployeeID, tblAttend.CourseStart, tblAttend.CourseEnd, tblCourse.CourseName, tblCourse.CourseSupplier, tblCourse.CourseDuration
FROM tblCourse INNER JOIN tblAttend ON tblCourse.CourseID = tblAttend.CourseID;

into the the recordsource of the form and call the form "subfrmCourses"

Go into designview and drop the fields into the form.
Go to view on the menubar above and check form header/footer
Drop the EmployeeID field below the form header/footer at the bottom of your form.

2.)Create another form called frmEmployees and use the tblEmployees as the recordsource.

3.)Use the the subform wizard to place a subform on the the frmEmployees form. Choose existing form and select subfrmCourses for you subform. It will ask you to define your link - choose the first one on the list.

Or just right-click on the subform for properties. Select Data and look for:

Link Child Fields.......
Link Master Fields......

Type in EmployeeID after each:

Link Child Fields.......EmployeeID
Link Master Fields......EmployeeID

Try it out.

 

[topgeek]

THat worked fine thanks Stuart.

Can you explain why the EmployeeID field is necessary in the subform footer?

THanks

 

[topgeek]

Hi Stuart,

I have been using the new database you suggested and added a coupe of forms. Everything seems to work OK, but I have changed the Course Name field to a combo in the subFrmCourses subform with a record source of tblCourses so users can select a previously entered cours. Unfortunately this just adds the same course name to the table tblCourse each time - obviously I only want one occurrence of each course in the table. Any idea how I can stop this,

Best Regards

 

[ssatech]

topgeek,
Why don't you create another table with course descriptions
call it tblCourseDescriptions and use this table as the recordsource for the combo.