asked on

MS/ACCESS dropdown control selection depending on form fields.

Hi
I have a form where I have a dropdown control to help filling up (or correcting) a field [fRefSeccao]. The form contains 2 other fields, [fRefRegiao] and [fRefSector] that must work as filters for this dropdown so the user will only be able to see the [RefSeccao] list of records that match the [RefRegiao] and [RefSector] table fields linked to the [fRefRegiao] and [fRefSector] controls.

The form (in design mode) looks as below:

On the [fRefSeccao] source table, I do indicate a Select, looking like this:

SELECT qConfereSeccao.codRegiao, qConfereSeccao.codigoSector, qConfereSeccao.refSeccao, qConfereSeccao.nomeSeccao
FROM qConfereSeccao
WHERE (((qConfereSeccao.codRegiao)="13") AND ((qConfereSeccao.codigoSector)="12"));

and it works perfectly.

Now I want to replace the

WHERE (((qConfereSeccao.codRegiao)="13") AND ((qConfereSeccao.codigoSector)="12"));

by a dynamic search on the field forms [fRefRegiao] and [fRefSector], like

WHERE (((qConfereSeccao.codRegiao)= fRefRegiao) AND ((qConfereSeccao.codigoSector)= fRefSector));

but it does not work as it does not recognise them

How do I do it?

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Tsioumpris

In the attachment all i see is a part of your email window...can you give some more info

João serras-pereira

ASKER

Hi -

I am sorry to be slow on this one.
I am getting an error:

the statement is:

SELECT qConfereSeccao.codRegiao, qConfereSeccao.codigoSector, qConfereSeccao.refSeccao, qConfereSeccao.nomeSeccao
FROM qConfereSeccao WHERE (((qConfereSeccao.codRegiao) = [Forms]!fCorrigeSeccao]![fRefRegiao]) AND ((qConfereSeccao.codigoSector) = [Forms]![fCorrigeSeccao]![fRefSector]));

The question is on the field fRefSeccao:

and I am uploading the DB (it is really quite simple, with only one form, nothing else, no VBA code (except this table source for the fRefSeccao

thanks for helping
EstruturaPAIGC_01.accdb

João serras-pereira

ASKER

oops wrong upload of a screenshot...

John Tsioumpris

try this

SELECT qConfereSeccao.codRegiao, qConfereSeccao.codigoSector, qConfereSeccao.refSeccao, qConfereSeccao.nomeSeccao
FROM qConfereSeccao WHERE (((qConfereSeccao.codRegiao) = [Forms]![fCorrigeSeccao]![fRefRegiao]) AND ((qConfereSeccao.codigoSector) = [Forms]![fCorrigeSeccao]![fRefSector]));

Open in new window

you forgot a brace [

João serras-pereira

ASKER

Well ... It looks very close.

But I need to verify better as the output should be a bit different. Probably something wrong in the Tables.
I need to dash to a meeting and will come back after testing

John Tsioumpris

Well i worked with what you gave...probably we need some better info to assist you more

Gustav Brock

Use the form name:

SELECT 
    qConfereSeccao.codRegiao, 
    qConfereSeccao.codigoSector, 
    qConfereSeccao.refSeccao, 
    qConfereSeccao.nomeSeccao
FROM 
    qConfereSeccao
WHERE 
    (((qConfereSeccao.codRegiao) = [Forms]![fCorrigeSeccao]![fRefRegiao]) AND ((qConfereSeccao.codigoSector) = [Forms]![fCorrigeSeccao]![fRefSector]));

Open in new window

João serras-pereira

ASKER

Hi thanks! It is now really working!

Gustav Brock

Great!