Referring to current record in a form

[mbraidy]

How can one refer to a field in a current row of a form in order to filter the Row Source of a linked Combo Box?

In each row itemsSubjects.ID, a foreign key, is set. I want to filter the RowSource of a Combo Box so it shows related fields only. The problem is that Subject should point to the value of the related field only.

My attempt is as follows:

SELECT DISTINCT itemsSubsubjects.ID, itemsSubsubjects.Subsubject FROM itemsSubjects INNER JOIN itemsSubsubjects ON itemsSubjects.Area = itemsSubsubjects.Area
WHERE Subject=itemsSubjects.ID
OR Subject Is Null;

Is there an object ( Object.Subject ) that can point to the related record in the form?

[MikeTheBike]

HI

How can one refer to a field in a current row of a form in order to filter the Row Source of a linked Combo Box?

In each row itemsSubjects.ID, a foreign key, is set. I want to filter the RowSource of a Combo Box so it shows related fields only. The problem is that Subject should point to the value of the related field only.

My attempt is as follows:

SELECT DISTINCT itemsSubsubjects.ID, itemsSubsubjects.Subsubject FROM itemsSubjects INNER JOIN itemsSubsubjects ON itemsSubjects.Area = itemsSubsubjects.Area
WHERE Subject=itemsSubjects.ID
OR Subject Is Null;

Is there an object ( Object.Subject ) that can point to the related record in the form?

Assuming this is a bound form and query is the RowSource for the Comb Control

Then pehaps the query should be somethink like

SELECT DISTINCT itemsSubsubjects.ID, itemsSubsubjects.Subsubject FROM itemsSubjects INNER JOIN itemsSubsubjects ON itemsSubjects.Area = itemsSubsubjects.Area
WHERE ItemsSubject.ID = [Forms]![YourFormName]![ID]
OR [Forms]![YourFormName]![Subject] Is Null;

??

You will also need Combo.Requery in the form On Curent Event.

MTB

[mbraidy]

Whenever I requery using this meethod all the Combo boxes are redefined. What I need is that the current record requery only the related combo box

[BoxTop]

SELECT DISTINCT itemsSubsubjects.ID, itemsSubsubjects.Subsubject FROM itemsSubjects INNER JOIN itemsSubsubjects ON itemsSubjects.Area = itemsSubsubjects.Area
WHERE Subject= Forms![NameOfYourForm]![itemsSubjects].Column(0)
OR Subject Is Null;

The column number should be set to the number of you ID column. Column numbers start with 0, so if you ID column is the second one in the list it would be Column(1)

[MikeTheBike]

Whenever I requery using this meethod all the Combo boxes are redefined. What I need is that the current record requery only the related combo box

Why do all the Combo Boxes redefine?

They will only redefine if you requery them somewhere!

One thought, do you mean you want the combo box to DISPLAY the record related to the one selected on the form?

MTB

[mbraidy]

Thank you guys for all the help you're offereing, Let me reexplain the problem

I am writing a school application. I have one table for classes, one for subjects(courses: Maths, english, etc), one for topics( Grammar, spelling, Algebra, etc). One common field between subjects and topics is Area(values can be: language, math, science, etc) and is used to map some topics to related subjects; e.g to restrict algebra to be assigned to, say, English. Or Spelling to Physics)
Now in the form(continuous), I choose the class, then the Subject. What I need next is to choose the topic. The drop down(Combo box) should be filtered and show only related topics (filtered using the common AREA field of the subjects and topics)
I tried many methods. If I refer to the field Forms!<...>!ID, it would be filtered however all the other topics field value disapear, however the values are still in the table. This confused end-users.
What I did is: I placed a text field above the combo box. I do not like this solution, even though it works.
So what do you think?