Dependent dropdowns

[Huber57]

All:

I would like to start by saying that I have reviewed every post I could find
on this site, a half dozen websites devoted to dependent dropdown lists and I
cannot get it to work ... so I am throwing myself on the mercy of the masses
to help me.

I have two tables. The first tracks brokerage firms and the other (in a
1-to-many relationship) tracks the brokers that work for each brokerage firm.
The two tables are linked by the PK in the brokerage firm table
(BrokerageNumber field) to the FK BrokerageNumber field in the broker table.

In my form I have two combo boxes. The first (titled Brokerage_Combo) has a
Row Source type of Table/Query and the Row Source is:

SELECT Brokerage_Firm_Table.BrokerageNumber, Brokerage_Firm_Table.Brokerage
FROM Brokerage_Firm_Table
ORDER BY Brokerage_Firm_Table.Brokerage;

I have also created (like the websites suggest) an event procedure in the
Brokerage_Combo box:
Private Sub Brokerage_Combo_AfterUpdate()
Me.Broker_Combo.Requery
End Sub

The second combo box (titled Broker_Combo) has a Row Source type of
Table/Query and the Row Source of:
SELECT Broker_Table.BrokerNumber, Broker_Table.BrokerLastName
FROM Broker_Table
WHERE
(((Broker_Table.BrokerageNumber)=[Forms]![Brokerage_Form]![Brokerage_Combo]))
ORDER BY Broker_Table.BrokerLastName;

I believe this is exactly how the examples online show it but it still does
not work.

Help please?!!

Thanks much in advance.

 

[Barry Andrew Hall]

Hi Huber,

Can you give me some more details. Do you get an error for example?

 

[Huber57]

There is no error. The firstdrop down shows the Brokerage Firms, but the
second dropdown shows nothing. I also get a pop up asking me to enter a
parameter value and "Forms!Brokerage_Form!Brokerage_Combo"

Hope that helps.

Hi Huber,

Can you give me some more details. Do you get an error for example?

All:

I would like to start by saying that I have reviewed every post I could
find
on this site, a half dozen websites devoted to dependent dropdown lists
and I
cannot get it to work ... so I am throwing myself on the mercy of the
masses
to help me.

I have two tables. The first tracks brokerage firms and the other (in a
1-to-many relationship) tracks the brokers that work for each brokerage
firm.
The two tables are linked by the PK in the brokerage firm table
(BrokerageNumber field) to the FK BrokerageNumber field in the broker
table.

In my form I have two combo boxes. The first (titled Brokerage_Combo) has
a
Row Source type of Table/Query and the Row Source is:

SELECT Brokerage_Firm_Table.BrokerageNumber,
Brokerage_Firm_Table.Brokerage
FROM Brokerage_Firm_Table
ORDER BY Brokerage_Firm_Table.Brokerage;

I have also created (like the websites suggest) an event procedure in the
Brokerage_Combo box:
Private Sub Brokerage_Combo_AfterUpdate()
Me.Broker_Combo.Requery
End Sub

The second combo box (titled Broker_Combo) has a Row Source type of
Table/Query and the Row Source of:
SELECT Broker_Table.BrokerNumber, Broker_Table.BrokerLastName
FROM Broker_Table
WHERE
(((Broker_Table.BrokerageNumber)=[Forms]![Brokerage_Form]![Brokerage_Combo]))
ORDER BY Broker_Table.BrokerLastName;

I believe this is exactly how the examples online show it but it still
does
not work.

Help please?!!

Thanks much in advance.

 

[box2003]

How have you delcared your brokeragenumber in the tables, string or number
data type?

There is no error. The firstdrop down shows the Brokerage Firms, but the
second dropdown shows nothing. I also get a pop up asking me to enter a
parameter value and "Forms!Brokerage_Form!Brokerage_Combo"

Hope that helps.

Hi Huber,

Can you give me some more details. Do you get an error for example?

All:

I would like to start by saying that I have reviewed every post I could
find
on this site, a half dozen websites devoted to dependent dropdown lists
and I
cannot get it to work ... so I am throwing myself on the mercy of the
masses
to help me.

I have two tables. The first tracks brokerage firms and the other (in a
1-to-many relationship) tracks the brokers that work for each brokerage
firm.
The two tables are linked by the PK in the brokerage firm table
(BrokerageNumber field) to the FK BrokerageNumber field in the broker
table.

In my form I have two combo boxes. The first (titled Brokerage_Combo) has
a
Row Source type of Table/Query and the Row Source is:

SELECT Brokerage_Firm_Table.BrokerageNumber,
Brokerage_Firm_Table.Brokerage
FROM Brokerage_Firm_Table
ORDER BY Brokerage_Firm_Table.Brokerage;

I have also created (like the websites suggest) an event procedure in the
Brokerage_Combo box:
Private Sub Brokerage_Combo_AfterUpdate()
Me.Broker_Combo.Requery
End Sub

The second combo box (titled Broker_Combo) has a Row Source type of
Table/Query and the Row Source of:
SELECT Broker_Table.BrokerNumber, Broker_Table.BrokerLastName
FROM Broker_Table
WHERE
(((Broker_Table.BrokerageNumber)=[Forms]![Brokerage_Form]![Brokerage_Combo]))
ORDER BY Broker_Table.BrokerLastName;

I believe this is exactly how the examples online show it but it still
does
not work.

Help please?!!

Thanks much in advance.

 

[Huber57]

In the Brokerage Table it is an AutoNumber. In the Broker Table it is a
Number data type.

How have you delcared your brokeragenumber in the tables, string or number
data type?

There is no error. The firstdrop down shows the Brokerage Firms, but the
second dropdown shows nothing. I also get a pop up asking me to enter a
parameter value and "Forms!Brokerage_Form!Brokerage_Combo"

Hope that helps.

Hi Huber,

Can you give me some more details. Do you get an error for example?


All:

I would like to start by saying that I have reviewed every post I could
find
on this site, a half dozen websites devoted to dependent dropdown lists
and I
cannot get it to work ... so I am throwing myself on the mercy of the
masses
to help me.

I have two tables. The first tracks brokerage firms and the other (in a
1-to-many relationship) tracks the brokers that work for each brokerage
firm.
The two tables are linked by the PK in the brokerage firm table
(BrokerageNumber field) to the FK BrokerageNumber field in the broker
table.

In my form I have two combo boxes. The first (titled Brokerage_Combo) has
a
Row Source type of Table/Query and the Row Source is:

SELECT Brokerage_Firm_Table.BrokerageNumber,
Brokerage_Firm_Table.Brokerage
FROM Brokerage_Firm_Table
ORDER BY Brokerage_Firm_Table.Brokerage;

I have also created (like the websites suggest) an event procedure in the
Brokerage_Combo box:
Private Sub Brokerage_Combo_AfterUpdate()
Me.Broker_Combo.Requery
End Sub

The second combo box (titled Broker_Combo) has a Row Source type of
Table/Query and the Row Source of:
SELECT Broker_Table.BrokerNumber, Broker_Table.BrokerLastName
FROM Broker_Table
WHERE
(((Broker_Table.BrokerageNumber)=[Forms]![Brokerage_Form]![Brokerage_Combo]))
ORDER BY Broker_Table.BrokerLastName;

I believe this is exactly how the examples online show it but it still
does
not work.

Help please?!!

Thanks much in advance.

 

[box2003]

Using my example, I have two combo boxes, combo9 and combo11
Form Name = frmDate

My tables are:

Brokerage (BID as Autonumber, Bname as text, PK = BID)
Broker (BrkrID as Autonumber, BID as number, BrkrLName as text, FK = BID)

For combo9 the following code is used:

RowSource:
SELECT brokerage.bid, brokerage.bname
FROM brokerage;

Event procedure After Update

Me.Combo11 = ""
Me.Combo11.Requery


For combo 11 the following code is used:

RowSource:
SELECT broker.BrkrLName
FROM broker
WHERE broker.BID=[Forms]![frmDate]![combo9];


Table Values for Table Broker:

BrkrID BID BrkrLName
1 1 Johnson
2 1 Jones
3 1 Jackson
4 2 Rogers
5 3 Sams
6 3 Kurns

Table Values for Table Brokerage:

bid bname
1 Fidelity
2 American
3 Opp

Hope this example works for you.

In the Brokerage Table it is an AutoNumber. In the Broker Table it is a
Number data type.

How have you delcared your brokeragenumber in the tables, string or number
data type?

There is no error. The firstdrop down shows the Brokerage Firms, but the
second dropdown shows nothing. I also get a pop up asking me to enter a
parameter value and "Forms!Brokerage_Form!Brokerage_Combo"

Hope that helps.

:

Hi Huber,

Can you give me some more details. Do you get an error for example?


All:

I would like to start by saying that I have reviewed every post I could
find
on this site, a half dozen websites devoted to dependent dropdown lists
and I
cannot get it to work ... so I am throwing myself on the mercy of the
masses
to help me.

I have two tables. The first tracks brokerage firms and the other (in a
1-to-many relationship) tracks the brokers that work for each brokerage
firm.
The two tables are linked by the PK in the brokerage firm table
(BrokerageNumber field) to the FK BrokerageNumber field in the broker
table.

In my form I have two combo boxes. The first (titled Brokerage_Combo) has
a
Row Source type of Table/Query and the Row Source is:

SELECT Brokerage_Firm_Table.BrokerageNumber,
Brokerage_Firm_Table.Brokerage
FROM Brokerage_Firm_Table
ORDER BY Brokerage_Firm_Table.Brokerage;

I have also created (like the websites suggest) an event procedure in the
Brokerage_Combo box:
Private Sub Brokerage_Combo_AfterUpdate()
Me.Broker_Combo.Requery
End Sub

The second combo box (titled Broker_Combo) has a Row Source type of
Table/Query and the Row Source of:
SELECT Broker_Table.BrokerNumber, Broker_Table.BrokerLastName
FROM Broker_Table
WHERE
(((Broker_Table.BrokerageNumber)=[Forms]![Brokerage_Form]![Brokerage_Combo]))
ORDER BY Broker_Table.BrokerLastName;

I believe this is exactly how the examples online show it but it still
does
not work.

Help please?!!

Thanks much in advance.

 

[box2003]

In my example, brkrID is left in the first combo box, combo9, control bound
column is 1.

Using my example, I have two combo boxes, combo9 and combo11
Form Name = frmDate

My tables are:

Brokerage (BID as Autonumber, Bname as text, PK = BID)
Broker (BrkrID as Autonumber, BID as number, BrkrLName as text, FK = BID)

For combo9 the following code is used:

RowSource:
SELECT brokerage.bid, brokerage.bname
FROM brokerage;

Event procedure After Update

Me.Combo11 = ""
Me.Combo11.Requery


For combo 11 the following code is used:

RowSource:
SELECT broker.BrkrLName
FROM broker
WHERE broker.BID=[Forms]![frmDate]![combo9];


Table Values for Table Broker:

BrkrID BID BrkrLName
1 1 Johnson
2 1 Jones
3 1 Jackson
4 2 Rogers
5 3 Sams
6 3 Kurns

Table Values for Table Brokerage:

bid bname
1 Fidelity
2 American
3 Opp

Hope this example works for you.

In the Brokerage Table it is an AutoNumber. In the Broker Table it is a
Number data type.

How have you delcared your brokeragenumber in the tables, string or number
data type?

:

There is no error. The firstdrop down shows the Brokerage Firms, but the
second dropdown shows nothing. I also get a pop up asking me to enter a
parameter value and "Forms!Brokerage_Form!Brokerage_Combo"

Hope that helps.

:

Hi Huber,

Can you give me some more details. Do you get an error for example?


All:

I would like to start by saying that I have reviewed every post I could
find
on this site, a half dozen websites devoted to dependent dropdown lists
and I
cannot get it to work ... so I am throwing myself on the mercy of the
masses
to help me.

I have two tables. The first tracks brokerage firms and the other (in a
1-to-many relationship) tracks the brokers that work for each brokerage
firm.
The two tables are linked by the PK in the brokerage firm table
(BrokerageNumber field) to the FK BrokerageNumber field in the broker
table.

In my form I have two combo boxes. The first (titled Brokerage_Combo) has
a
Row Source type of Table/Query and the Row Source is:

SELECT Brokerage_Firm_Table.BrokerageNumber,
Brokerage_Firm_Table.Brokerage
FROM Brokerage_Firm_Table
ORDER BY Brokerage_Firm_Table.Brokerage;

I have also created (like the websites suggest) an event procedure in the
Brokerage_Combo box:
Private Sub Brokerage_Combo_AfterUpdate()
Me.Broker_Combo.Requery
End Sub

The second combo box (titled Broker_Combo) has a Row Source type of
Table/Query and the Row Source of:
SELECT Broker_Table.BrokerNumber, Broker_Table.BrokerLastName
FROM Broker_Table
WHERE
(((Broker_Table.BrokerageNumber)=[Forms]![Brokerage_Form]![Brokerage_Combo]))
ORDER BY Broker_Table.BrokerLastName;

I believe this is exactly how the examples online show it but it still
does
not work.

Help please?!!

Thanks much in advance.

 

[John W. Vinson]

In my form I have two combo boxes. The first (titled Brokerage_Combo) has a
Row Source type of Table/Query and the Row Source is:

SELECT Brokerage_Firm_Table.BrokerageNumber, Brokerage_Firm_Table.Brokerage
FROM Brokerage_Firm_Table
ORDER BY Brokerage_Firm_Table.Brokerage;

I have also created (like the websites suggest) an event procedure in the
Brokerage_Combo box:
Private Sub Brokerage_Combo_AfterUpdate()
Me.Broker_Combo.Requery
End Sub

The second combo box (titled Broker_Combo) has a Row Source type of
Table/Query and the Row Source of:
SELECT Broker_Table.BrokerNumber, Broker_Table.BrokerLastName
FROM Broker_Table
WHERE
(((Broker_Table.BrokerageNumber)=[Forms]![Brokerage_Form]![Brokerage_Combo]))
ORDER BY Broker_Table.BrokerLastName;

What are the Bound Column and Control Source properties for the two combos?
Are you just seeing blank combos, or does the Broker_Combo just not reflect
changes in the brokerage?

John W. Vinson [MVP]

 

[Huber57]

John,

THere is no control source for either combo box and both have a Bound Column
of 1.

Thanks!

In my form I have two combo boxes. The first (titled Brokerage_Combo) has a
Row Source type of Table/Query and the Row Source is:

SELECT Brokerage_Firm_Table.BrokerageNumber, Brokerage_Firm_Table.Brokerage
FROM Brokerage_Firm_Table
ORDER BY Brokerage_Firm_Table.Brokerage;

I have also created (like the websites suggest) an event procedure in the
Brokerage_Combo box:
Private Sub Brokerage_Combo_AfterUpdate()
Me.Broker_Combo.Requery
End Sub

The second combo box (titled Broker_Combo) has a Row Source type of
Table/Query and the Row Source of:
SELECT Broker_Table.BrokerNumber, Broker_Table.BrokerLastName
FROM Broker_Table
WHERE
(((Broker_Table.BrokerageNumber)=[Forms]![Brokerage_Form]![Brokerage_Combo]))
ORDER BY Broker_Table.BrokerLastName;

What are the Bound Column and Control Source properties for the two combos?
Are you just seeing blank combos, or does the Broker_Combo just not reflect
changes in the brokerage?

John W. Vinson [MVP]

 

[Huber57]

Box2003,

That works perfectly. I will try and see what I am doing wrong!

Thanks much for the clear, concise example!

In my example, brkrID is left in the first combo box, combo9, control bound
column is 1.

Using my example, I have two combo boxes, combo9 and combo11
Form Name = frmDate

My tables are:

Brokerage (BID as Autonumber, Bname as text, PK = BID)
Broker (BrkrID as Autonumber, BID as number, BrkrLName as text, FK = BID)

For combo9 the following code is used:

RowSource:
SELECT brokerage.bid, brokerage.bname
FROM brokerage;

Event procedure After Update

Me.Combo11 = ""
Me.Combo11.Requery


For combo 11 the following code is used:

RowSource:
SELECT broker.BrkrLName
FROM broker
WHERE broker.BID=[Forms]![frmDate]![combo9];


Table Values for Table Broker:

BrkrID BID BrkrLName
1 1 Johnson
2 1 Jones
3 1 Jackson
4 2 Rogers
5 3 Sams
6 3 Kurns

Table Values for Table Brokerage:

bid bname
1 Fidelity
2 American
3 Opp

Hope this example works for you.

In the Brokerage Table it is an AutoNumber. In the Broker Table it is a
Number data type.

:

How have you delcared your brokeragenumber in the tables, string or number
data type?

:

There is no error. The firstdrop down shows the Brokerage Firms, but the
second dropdown shows nothing. I also get a pop up asking me to enter a
parameter value and "Forms!Brokerage_Form!Brokerage_Combo"

Hope that helps.

:

Hi Huber,

Can you give me some more details. Do you get an error for example?


All:

I would like to start by saying that I have reviewed every post I could
find
on this site, a half dozen websites devoted to dependent dropdown lists
and I
cannot get it to work ... so I am throwing myself on the mercy of the
masses
to help me.

I have two tables. The first tracks brokerage firms and the other (in a
1-to-many relationship) tracks the brokers that work for each brokerage
firm.
The two tables are linked by the PK in the brokerage firm table
(BrokerageNumber field) to the FK BrokerageNumber field in the broker
table.

In my form I have two combo boxes. The first (titled Brokerage_Combo) has
a
Row Source type of Table/Query and the Row Source is:

SELECT Brokerage_Firm_Table.BrokerageNumber,
Brokerage_Firm_Table.Brokerage
FROM Brokerage_Firm_Table
ORDER BY Brokerage_Firm_Table.Brokerage;

I have also created (like the websites suggest) an event procedure in the
Brokerage_Combo box:
Private Sub Brokerage_Combo_AfterUpdate()
Me.Broker_Combo.Requery
End Sub

The second combo box (titled Broker_Combo) has a Row Source type of
Table/Query and the Row Source of:
SELECT Broker_Table.BrokerNumber, Broker_Table.BrokerLastName
FROM Broker_Table
WHERE
(((Broker_Table.BrokerageNumber)=[Forms]![Brokerage_Form]![Brokerage_Combo]))
ORDER BY Broker_Table.BrokerLastName;

I believe this is exactly how the examples online show it but it still
does
not work.

Help please?!!

Thanks much in advance.

 

[John W. Vinson]

There is no error. The firstdrop down shows the Brokerage Firms, but the
second dropdown shows nothing. I also get a pop up asking me to enter a
parameter value and "Forms!Brokerage_Form!Brokerage_Combo"

AHA. Is the form named Brokerage_Form in fact open? And is the Name property
of the combo box actually Brokerage_Combo? Do you get the error on opening the
form, or after you select a brokerage, or both?

John W. Vinson [MVP]