U
unhinged
G'day,
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.
What I am trying to achieve is to have a form showing lists of students
who have been allocated to a group of courses (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 students already allocated to the group (on the left of the main
form) and another subform showing the list of students enrolled in the
programme to which the courses belong. I have a button control in each
subform; one adds the student to the group list and the other returns
the student to the programme 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 enrolled students and add it to the list of
allocated students. By setting the GroupID to NULL in the list of
allocated students, 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), 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.
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.
What I am trying to achieve is to have a form showing lists of students
who have been allocated to a group of courses (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 students already allocated to the group (on the left of the main
form) and another subform showing the list of students enrolled in the
programme to which the courses belong. I have a button control in each
subform; one adds the student to the group list and the other returns
the student to the programme 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 enrolled students and add it to the list of
allocated students. By setting the GroupID to NULL in the list of
allocated students, 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), 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.
