accessing the value of the link child in a subform

[Wierdbeard65]

Hi,

Application is a simple (!) instructor scheduling app.

I have a form which is designed to allow me to select which instructors are
qualified to teach which courses. I have three tables. One with all the
instructors, one with all the courses and then a link table.

Ok, on my main form, I can select an instructor and then on a sub-form I get
a list of the courses that instructor can teach. Each record in the subform
is represented by a listbox to allow the course to be selected.

The problem is that all the courses are selectable in each record's list,
whereas I only want those courses to appear that are not already selected
elsewhere (in part to prevent duplicate entries.

I have the query for my listbox data sorted, but I need to refer to the
value of the link field, but don't know how to reference it.

At the moment, the query looks like:
Select Course.CourseID, Course.CourseTitle from Course
Order by Course.CourseTitle

When I want to have something like:
Select Course.CourseID, Course.CourseTitle from Course
Where Course.CourseID not in (Select Qualification.CourseID from
Qualification where Qualification.InstructorID = <linkfield>)
Order by Course.CourseTitle

How do I reference the linkfield to get the current instructor?

TIA

 

[Alex Dybenko]

Hi,
"on my main form, I can select an instructor" - you can use this control,
combobox or listbox, like this:
Select Course.CourseID, Course.CourseTitle from Course
Where Course.CourseID not in (Select Qualification.CourseID from
Qualification where Qualification.InstructorID =
Froms!MyForm!MyInstructorControl )
Order by Course.CourseTitle

also do not forget to add code to requery course listbox after changing
instructor

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Wierdbeard65]

Thanks for the response.

When I say I can select an instructor, I do so by selecting the record, not
using a control. The instructorID is then the linkMaster in the parent and
the LinkChild in the subform. When I am editing the subform, it's just a
form, so the link fields aren't there :-(

So, I need to grab the ID of the currently selected record in the parent
form. I thought this was the purpose of the link fields?

 

[Alex Dybenko]

Hi,
if that case you can use Froms!MyForm!instructorID
where MyFrom is a name of your parent form

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com