Lookup Field in Form?

  • Thread starter Thread starter MBP
  • Start date Start date
M

MBP

I have the following fields on a form: Supervisor ( from
Table1, looks up on Supervisor table), Agent and some
input data fields. When I select supervisor Z, I want the
Agent field to populate, so that I only can choose agents
from the Supervisor Z table, which I set up-basically just
like a lookup field. I don't think I can do this with a
lookup field. Is there a way that I can write code behind
the form that will allow me to this. I'm not familiar with
code at all; but I am willing to try it out on a test
database to see what results I receive. Any help is
appreciated.
 
Not very clear on how you link agents to supervisors; do
you have a table where you specify that agents a,b,c
belong to supervisor Z, agents d,e,f to supervisor Y etc?
If that's the case then you do not need code! What you
have to do is change the agent text box on the form to a
combo box, and from properties/data/row source invoke the
query bulider, add the table in question, select the agent
and supervisor fields and in the supervisor field column,
first criteria line type [controLname] (e.g if the combo
box name on your form is agt, then type in [agt] -
including the square brackets!). Just make sure the
control name on your form is not the same as the field
name in the table, as this will confuse Access. Close the
query window and confirm to save the changes in the SQL
statement.
The only other thing you need to do, back in the form,
agent combo box > properties > event tab choose On Enter,
invoke macro builder and use action requery (specifying
the control name at the bottom part) so that you always
get the right agents, even if you've changed the
supervisor entry.

Good luck,

Nikos Y.
 
Back
Top