Filter Form based on Foreign Key Data

Pisteuo

Registered User.
Local time
Today, 13:32
Joined
Jul 12, 2009
Messages
72
I would like to create a continuous form that is filtered on data related to a foreign key.

The easiest way to explain my issue is to ask if there is a way to filter on a DLookup field.

Thank you for the help.
 
In order to filter on a DLookup field you would need to change the DLookup via code and setting the criteria there. And the question is - how would you filter using a DLookup as that only brings back the first value that matches the criteria?
 
You can filter on any field as long as it's in the Record Source.

Give us some more information.
 
vba, The data I would like to filter by is not in the record source.

Let me illustrate the issue:

tblCourse:
fldCourseID (Primary Key)
fldCoach

tblAssignment: ("Sub-Table" to tblCourse)
fldAssignmentID (Primary Key)
fldCourseID (Foreign Key)

The form is for editing tblAssignment records. I would like for the user to filter by Coach, which as you can see is included in tblCourse.

Bob, my intent is to create a DLookup field in the continuous form based on the foreign key which is part of the record source. I would then like to filter the records within the same continuous form by the data that is returned. The first returned value for each foreign key is perfect.

Is there a way to apply an sql statement in the filter by form that would accomplish this purpose?

Thank you again for helping guys.
 
You don't need a DLookup for this. Is this a Main Form / Sub Form set up? If it is, and it is listing Courses in the main form and then the assignments in the subform/subtable then you would not be able to filter by Coach as there is a relationship between fldCourseID which is not going to let you.

What you would need to do is to have a different form (or break the master/child links on the existing one) and use a record source which pulls the Assignment information and has the tblCourse in the query too and linked on fldCourseID but with criteria for fldCoach to get all of the fldCourseID's for that coach.

I hope that makes sense.
 
I did not know that an editable table can be based on a query record source.

I wanted to stay away from creating a subform to edit tblAssignment because I rely on list boxes to choose subform records for editing. This causes the user to interrupt the flow of work to navigate to the next records via the list box. However a query record source could fix this problem and allow continuous form navigation.

Let me know if I'm on the right track.
 
It appears that I cannot input data into a query recordsource, just edit data.
 
It appears that I cannot input data into a query recordsource, just edit data.
You can if it is set up correctly and using the right tables with the right relationships. If you have a one-to-many situation where you have both tables in the same query it isn't going to let you add records (I believe).

Your subform should have only the assignments table in its record source and the main form should have only the course table in its record source.
 
The query form is built and is accomplishing the purpose of my original post. Thank you for the guidance.

I have a question about updating the query form that I will post in a new thread.
 

Users who are viewing this thread

Back
Top Bottom