Lookup query filter not working as combo box row source

[Brian Goodheim]

I am learning Access by converting a flat-file database and am having a
problem using a filtered lookup query as a row source for a combo box field
in the primary table. The problem I am having is that the query does not
filter the data from the secondary table according to the criterea I am
trying to specify.

Could someone knowledgeable in the world of Access please advise me? Data
has been cleaned (no duplicates or nulls) and partially normalized
(secondary tables have been created, but redundant fields are still in the
primary table until I get this problem solved). Using Access 97, here is
the situation:

Primary table of job information is ASSIGNMENTS, containing job data and
rep-id and client-id pointers;

Secondary table of personnel contacts for each client is REPS, indexed on
rep-id and client-id (each contact record is associated with a single
client-id).

Tertiary table of client information is CLIENTS, containing additional
client data, indexed on client-id; this table is not directly referenced in
the problem query.

Problem: In my primary table ASSIGNMENTS, the rep-id field is bound to a
combo box which uses a filtered lookup query as the row source. In
datasheet mode, I want to see only the REPS associated with the client-id of
the current ASSIGNMENTS record, so I included Reps.client-id in the query
with Assignments.client-id as criterea. However, when the combo box
operates in the ASSIGNMENTS datasheet, all of the unique contacts from REPS
are displayed, not just for the client-id of the current record in
ASSIGNMENTS.

Question: What am I doing wrong? Is there a special syntax (like !?) to
indicate matching criterea is to come from the contents of a specific field
in the current record of the primary table? My filter seems to work only if
text criterea is specified. What is the syntax of the criterea line in
query builder to indicate I want to limit records displayed to
Assignments.client-id?

Thanks for any assitance.

-Brian Goodheim

 

[[MVP] S.Clark]

Post the SQL of your "filtered lookup query"? I really don't know what you
mean by this, so seeing it may help.

 

[Brian Goodheim]

Thanks for the reply, Steve. Here is the SQL generated by by the query
builder.

SELECT DISTINCT Reps.REPID, Reps.[rep-ln]
FROM Reps INNER JOIN Assignments ON Reps.CLIID = Assignments.CLIID
WHERE (((Reps.CLIID)=[assignments]![CLIID]))
ORDER BY Reps.[rep-ln];

I think the problem is that my criterea syntax for the Reps!CLIID field
(Assignments!CLIID) does not correctly instruct Access to use the contents
of the Client ID field of the current record as a filter against the Reps
table and the combo box is being populated with Reps records for every
client ID.

Also, as you can see, Access is picking up the CLIID and REPID relationships
established between the Assignments and Reps tables in the Access
'Relationships' builder.

Thanks for your time.

-Brian