D
daniel
I have a task which has been giving me grief; specifically, Access
would hang when I was trying to add or remove records from a subform
when using an SQL update query. Subsequent research and investigation
has revealed that the query forming the RecordSource for the subform is
not updateable - I'm hoping some kind soul can tell me why. I can add
new records to the query but not edit the existing records.
What I am trying to achieve is to have a form showing lists of courses
which have been allocated to a particular group (a group is an easy way
of tracking all the students who are allocated to the same bunch of
qualifications at the same time). I have one subform showing the list
of courses already allocated to the group (on the left of the main
form) and another subform showing the list of courses which are
available to be added. I have a button control in each subform; one
adds the course to the group list and the other returns the course to
the unallocated list.
The left subform is based on the query that is not updateable:
SELECT tCourses.CourseID, tCourses.StartDate, tCourses.Duration,
tCourses.GroupID, tGroups.GroupCode, tTrainingLocations.TLName,
tQualifications.QualificationCode, tQualifications.QualificationName,
tCourses.TrainingLocationID, tCourses.QualificationID
FROM tQualifications INNER JOIN (tTrainingLocations INNER JOIN (tGroups
INNER JOIN tCourses ON tGroups.GroupID = tCourses.GroupID) ON
tTrainingLocations.TrainingLocationID = tCourses.TrainingLocationID) ON
tQualifications.QualificationID = tCourses.QualificationID;
As you can see, there are four tables being referenced. A Course is
basically a dated instance of a qualification and the other items have
been explained or are obvious (I hope).
The query for the subform on the right is:
SELECT tProgrammes.ProgrammeID, tCourses.CourseID, tCourses.StartDate,
tTrainingLocations.TLName, tQualifications.QualificationCode,
tQualifications.QualificationName, IsNull([GroupID]) AS NotInGroup,
tCourses.GroupID
FROM tTrainingLocations INNER JOIN (tCourses INNER JOIN (tProgrammes
INNER JOIN tQualifications ON tProgrammes.ProgrammeID =
tQualifications.ProgrammeID) ON tCourses.QualificationID =
tQualifications.QualificationID) ON
tTrainingLocations.TrainingLocationID = tCourses.TrainingLocationID
WHERE (((tCourses.StartDate)>Date()) AND ((IsNull([GroupID]))=True));
My thinking is that by setting the GroupID of the record listed in this
right-hand side subform to the GroupID shown on the main form, I will
remove it from the list of unallocated courses and add it to the list
of courses which make up the group. By setting the GroupID to NULL in
the list of allocated courses, I will return the record to the list of
enrolled students. This basically works (when I used SQL to perform an
update query but did not requery the subforms, I verified that the data
was indeed being changed by closing and opening the main form - trying
to requery caused the application to hang), but if there is a better
way then please do not hesitate to suggest it.
My biggest problem is getting the first of these two queries to be
updateable. I would dearly love to finally eliminate this problem.
Regards,
Daniel.
would hang when I was trying to add or remove records from a subform
when using an SQL update query. Subsequent research and investigation
has revealed that the query forming the RecordSource for the subform is
not updateable - I'm hoping some kind soul can tell me why. I can add
new records to the query but not edit the existing records.
What I am trying to achieve is to have a form showing lists of courses
which have been allocated to a particular group (a group is an easy way
of tracking all the students who are allocated to the same bunch of
qualifications at the same time). I have one subform showing the list
of courses already allocated to the group (on the left of the main
form) and another subform showing the list of courses which are
available to be added. I have a button control in each subform; one
adds the course to the group list and the other returns the course to
the unallocated list.
The left subform is based on the query that is not updateable:
SELECT tCourses.CourseID, tCourses.StartDate, tCourses.Duration,
tCourses.GroupID, tGroups.GroupCode, tTrainingLocations.TLName,
tQualifications.QualificationCode, tQualifications.QualificationName,
tCourses.TrainingLocationID, tCourses.QualificationID
FROM tQualifications INNER JOIN (tTrainingLocations INNER JOIN (tGroups
INNER JOIN tCourses ON tGroups.GroupID = tCourses.GroupID) ON
tTrainingLocations.TrainingLocationID = tCourses.TrainingLocationID) ON
tQualifications.QualificationID = tCourses.QualificationID;
As you can see, there are four tables being referenced. A Course is
basically a dated instance of a qualification and the other items have
been explained or are obvious (I hope).
The query for the subform on the right is:
SELECT tProgrammes.ProgrammeID, tCourses.CourseID, tCourses.StartDate,
tTrainingLocations.TLName, tQualifications.QualificationCode,
tQualifications.QualificationName, IsNull([GroupID]) AS NotInGroup,
tCourses.GroupID
FROM tTrainingLocations INNER JOIN (tCourses INNER JOIN (tProgrammes
INNER JOIN tQualifications ON tProgrammes.ProgrammeID =
tQualifications.ProgrammeID) ON tCourses.QualificationID =
tQualifications.QualificationID) ON
tTrainingLocations.TrainingLocationID = tCourses.TrainingLocationID
WHERE (((tCourses.StartDate)>Date()) AND ((IsNull([GroupID]))=True));
My thinking is that by setting the GroupID of the record listed in this
right-hand side subform to the GroupID shown on the main form, I will
remove it from the list of unallocated courses and add it to the list
of courses which make up the group. By setting the GroupID to NULL in
the list of allocated courses, I will return the record to the list of
enrolled students. This basically works (when I used SQL to perform an
update query but did not requery the subforms, I verified that the data
was indeed being changed by closing and opening the main form - trying
to requery caused the application to hang), but if there is a better
way then please do not hesitate to suggest it.
My biggest problem is getting the first of these two queries to be
updateable. I would dearly love to finally eliminate this problem.
Regards,
Daniel.