Lookup Query--limiting values based on another field
Chris
(date 31 July). I have two tables, one that has
information about an organization and includes a field
called OrganizationID and another table for employees that
has an OrganizationID field, an EmployeeID field along
with other fields. The main form I have is based on the
Organization and the subform on it is based on the
Employee. I want to create a combo box that will show
only the employees who belong to the currently selected
Organization.
Mitch Kirschner replied to the Worth Bakers question
stating "build a form interface that employs a combobox
with its rowsource set to a query that's tied to the value
of clientid (which should be bound to a textbox on the
form).
In my case, I translate this to mean I need a combobox
with its rowsource set to a query thats tied tot he value
of OrganizationID. How do I do this?
Thanks
John Vinson
>In my case, I translate this to mean I need a combobox
>with its rowsource set to a query thats tied tot he value
>of OrganizationID. How do I do this?
You need to base the second combo on a query which uses the
first combo as a criterion. Create and save a query as the
RowSource for Combo2, using
=Forms![NameOfYourForm]![Combo1]
as a criterion.
Then, in the AfterUpdate event of Combo1, click the ... icon, select
Code Builder, and put one line of code between the Sub and End Sub
lines that Access will give you:
Private Sub Combo1_AfterUpdate()
Me!Combo2.Requery
End Sub
(changing the combo names to those in your form, of course).
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
Hap
I don't know if this will take care of all your problem,
but I'll give it a shot.
It sounds like you're 95% of the way there. You already
have the form/subform. I assume that currently you can
run the form and you toggle through the various
organizations and see the employees in that ID.
Next, the combo box. Place a combo box on the
organization form (make sure the wizard is turned on). On
the first wizard screen choose: "Find a record on my form
based on the value I selected in my combo box".
You may want to include both the OrganizationID as well as
OrganizationName (or whatever describes the Organization
if that is how it is identified by users).
I tend to put Name in first, then ID. A screen or two
later it asks me for which field uniquely identifies and I
then highlight ID. By putting Name in first I can, for
instance, start typing in "Hum.." and it will offer "Human
Resources" whereupon I hit tab and it brings up the
information. Since the subform is linked by ID, it should
change when the Organization form changes.
Viola!
Hap.
>.
>
Chris
First, let me thank you for your help. I am confident
that this is putting me on the right track.
I am not certain what you mean. If I understand
correctly, Combo1 is the combobox that contains the
OrganizationID. Combo2 is the combobox that when I select
an item in it, the rest of the form will reflect the data
for that record. If I undestand you (and it makes sense),
I need to base Combo2 on the contents of Combo1.
When you say,
Create and save a query as the
RowSource for Combo2, using
=Forms![NameOfYourForm]![Combo1]
I don't understand what query I am creating (the one that
combo1 is based on already? -- it is in the RowSource for
Combo1 in SQL format and does not have a corresponding
saved query name). Also, the RowSource for Combo2 already
has SQL code for the Lookup Query. Do I put the
=Forms![NameOfYourForm]![Combo1]
before whats in there and what syntax do I use to seperate
them if so?
Thanks again.
Chris
>.
>
John Vinson
>John,
>First, let me thank you for your help. I am confident
>that this is putting me on the right track.
>
>I am not certain what you mean.
At the moment, this situation is mutual... :-{)
>If I understand
>correctly, Combo1 is the combobox that contains the
>OrganizationID. Combo2 is the combobox that when I select
>an item in it, the rest of the form will reflect the data
>for that record. If I undestand you (and it makes sense),
>I need to base Combo2 on the contents of Combo1.
Well, Combo2's RowSource should reference Combo1 as a criterion in a
WHERE clause.
>When you say,
>Create and save a query as the
>RowSource for Combo2, using
>=Forms![NameOfYourForm]![Combo1]
>
>I don't understand what query I am creating (the one that
>combo1 is based on already? -- it is in the RowSource for
>Combo1 in SQL format and does not have a corresponding
>saved query name). Also, the RowSource for Combo2 already
>has SQL code for the Lookup Query. Do I put the
>=Forms![NameOfYourForm]![Combo1]
>before whats in there and what syntax do I use to seperate
>them if so?
I'm suggesting that you *CHANGE* the RowSource of Combo2. It needn't
be a stored query but if not, the SQL string should still have the
form reference. Perhaps you could post the current RowSource
properties of the two combos (and the form and control names) if this
isn't clear.