By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,399 Members | 1,028 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,399 IT Pros & Developers. It's quick & easy.

Referring to current record in a form

P: 3
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?
Aug 6 '07 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 635
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

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT itemsSubsubjects.ID, itemsSubsubjects.Subsubject FROM itemsSubjects INNER JOIN itemsSubsubjects ON itemsSubjects.Area = itemsSubsubjects.Area
  2. WHERE ItemsSubject.ID = [Forms]![YourFormName]![ID]
  3. OR [Forms]![YourFormName]![Subject] Is Null;
??

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

MTB
Aug 6 '07 #2

P: 3
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
Aug 8 '07 #3

P: 12
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)
Aug 8 '07 #4

Expert 100+
P: 635
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
Aug 8 '07 #5

P: 3
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?
Aug 9 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.