How to Connect two Combo Boxes in a form?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with a combo box [Committees] which is tied to a table with (2)
fields. [CommID] and [Committee]. I ahve a second combo box [Combo51] which
I want to list the available positions fro each committee.

I have created a table [Committee Position Table] with three fields,
[CommPositionID], [CommPosition] and [CommID]. [CommPosition] and
[CommPositionID] both list the committee position available. [CommID] lists
the corresponding committee for each position.

To get [Combo51] to show only the available positions for the committee
selected in [Committees], I have created the following statement in the Row
Source property field for [Combo51]:

SELECT [Committee Position Table].CommPosition, [Committee Position
Table].CommID FROM [Committee Position Table] WHERE (((Committee Position
Table.CommID)=Forms![Committee Update Form]!Committees)) ORDER BY [Committee
Position Table].CommPosition;

It doesn't work. Please help!

Thanks,
Walt
 
Hi, Walt.

Requery Combo51 in the AfterEvent procedure of the Committees combo box:

Me![Combo51].Requery

Also, if you are storing the selected position in the form's underlying
table, it would be more efficient to store its numerical code,
CommPositionID, rather than the text, in a corresponding numerical field.
Also, you don't need the CommID field in the RowSource unless you're planning
to display it somewhere.

SELECT [Committee Position Table].[CommPositionID], [Committee Position
Table].CommPosition
FROM [Committee Position Table]
WHERE (((Committee Position Table.CommID)=Forms![Committee Update
Form]!Committees))
ORDER BY [Committee Position Table].CommPosition;

Set the BoundColumn property to 1 to store the value from the first column.
Set the ColumnWidths property to 0";x" where x is the width required to
display the widest Position. After selection, Access displays the first
non-zero-width column.

Hope that helps.
 
Three things I would check
1. The name of the form and combo are correct
2. On the after update event of the Committees combo box, you should run a
refresh on the second combo
Me.Combo51.requery
3. Is the CommID in the first combo, located as the first column. If it's on
the second column then you should use that
Forms![Committee Update Form]![Committees].column(1)
The column number start with 0
 
I have a form with a combo box [Committees] which is tied to a table with (2)
fields. [CommID] and [Committee]. I ahve a second combo box [Combo51] which
I want to list the available positions fro each committee.

I have created a table [Committee Position Table] with three fields,
[CommPositionID], [CommPosition] and [CommID]. [CommPosition] and
[CommPositionID] both list the committee position available. [CommID] lists
the corresponding committee for each position.

To get [Combo51] to show only the available positions for the committee
selected in [Committees], I have created the following statement in the Row
Source property field for [Combo51]:

SELECT [Committee Position Table].CommPosition, [Committee Position
Table].CommID FROM [Committee Position Table] WHERE (((Committee Position
Table.CommID)=Forms![Committee Update Form]!Committees)) ORDER BY [Committee
Position Table].CommPosition;

It doesn't work. Please help!

Thanks,
Walt
Click to expand...

Leave the Rowsource of Combo51 blank.

I assume CommID is a Number datatype.
Make sure the bound column of this combo box is 1 (CommID).

Code the AfterUpdate event of the Committee combo box:

Combo51.Rowsource = "SELECT [Committee Position Table].CommPosition,
[Committee Position Table].CommID FROM [Committee Position Table]
WHERE [Committee Position Table].CommID = " & Me![Committees] & "
ORDER BY [Committee Position Table].CommPosition;"

I notice you also left out brackets around the table name in the where
clause.. [ ]
WHERE (((Committee Position Table.CommID)=

and you don't need any of the parenthesis in the Where clause.
 
Hi, Sprinks

I tried to follow your directions, but it didn't work. Here's what I did:

I put the Me![Combo51].Requery in the AfteUpdate Event procedure.

I want to store the "text" of the position selected in the underlying table.
You are right as I don't display the CommID anywhere.

I changed the statement in [Combo51] Row Source property field as you
outlined.

This is what happens: When I try to change committees in [Committees] Combo
Box, I get a dialog box, "Enter Parameter Value", whch I don't understand.

When I click ]Combo51], I get the error message: the Record Source 'SELECT
[Committee Position Table].CommPositionID, [Committee Position
Table].CommPosition FROM [Committee Position Table] WHERE ..., specified on
this form or report does not exist." I don't understand as I checked and the
Table name is correct and the column names are correct?

Help!

Walt


Sprinks said:
Hi, Walt.

Requery Combo51 in the AfterEvent procedure of the Committees combo box:

Me![Combo51].Requery

Also, if you are storing the selected position in the form's underlying
table, it would be more efficient to store its numerical code,
CommPositionID, rather than the text, in a corresponding numerical field.
Also, you don't need the CommID field in the RowSource unless you're planning
to display it somewhere.

SELECT [Committee Position Table].[CommPositionID], [Committee Position
Table].CommPosition
FROM [Committee Position Table]
WHERE (((Committee Position Table.CommID)=Forms![Committee Update
Form]!Committees))
ORDER BY [Committee Position Table].CommPosition;

Set the BoundColumn property to 1 to store the value from the first column.
Set the ColumnWidths property to 0";x" where x is the width required to
display the widest Position. After selection, Access displays the first
non-zero-width column.

Hope that helps.




Walt said:
I have a form with a combo box [Committees] which is tied to a table with (2)
fields. [CommID] and [Committee]. I ahve a second combo box [Combo51] which
I want to list the available positions fro each committee.

I have created a table [Committee Position Table] with three fields,
[CommPositionID], [CommPosition] and [CommID]. [CommPosition] and
[CommPositionID] both list the committee position available. [CommID] lists
the corresponding committee for each position.

To get [Combo51] to show only the available positions for the committee
selected in [Committees], I have created the following statement in the Row
Source property field for [Combo51]:

SELECT [Committee Position Table].CommPosition, [Committee Position
Table].CommID FROM [Committee Position Table] WHERE (((Committee Position
Table.CommID)=Forms![Committee Update Form]!Committees)) ORDER BY [Committee
Position Table].CommPosition;

It doesn't work. Please help!

Thanks,
Walt
Click to expand...
Click to expand...
 
Hi, Fred

I tried your suggestions and it didn't work.

I left the Row Source fo Combo51 blank.

CommID is text spelling out the names of each of the committees and is the
third column of three with CommPositionID first and CommPosition second both
lis the exact same information.

I coded the AfterUpdate event of the Committee Combo box as you suggested.

When I click on the Committee Combo box, I get the message: MS Access can't
find the macro 'Combo51.' ?

When I click on Combo51, it's blank with no drop down list.

What am I doing wrong?

Walt




fredg said:
I have a form with a combo box [Committees] which is tied to a table with (2)
fields. [CommID] and [Committee]. I ahve a second combo box [Combo51] which
I want to list the available positions fro each committee.

I have created a table [Committee Position Table] with three fields,
[CommPositionID], [CommPosition] and [CommID]. [CommPosition] and
[CommPositionID] both list the committee position available. [CommID] lists
the corresponding committee for each position.

To get [Combo51] to show only the available positions for the committee
selected in [Committees], I have created the following statement in the Row
Source property field for [Combo51]:

SELECT [Committee Position Table].CommPosition, [Committee Position
Table].CommID FROM [Committee Position Table] WHERE (((Committee Position
Table.CommID)=Forms![Committee Update Form]!Committees)) ORDER BY [Committee
Position Table].CommPosition;

It doesn't work. Please help!

Thanks,
Walt
Click to expand...

Leave the Rowsource of Combo51 blank.

I assume CommID is a Number datatype.
Make sure the bound column of this combo box is 1 (CommID).

Code the AfterUpdate event of the Committee combo box:

Combo51.Rowsource = "SELECT [Committee Position Table].CommPosition,
[Committee Position Table].CommID FROM [Committee Position Table]
WHERE [Committee Position Table].CommID = " & Me![Committees] & "
ORDER BY [Committee Position Table].CommPosition;"

I notice you also left out brackets around the table name in the where
clause.. [ ]
WHERE (((Committee Position Table.CommID)=

and you don't need any of the parenthesis in the Where clause.
Click to expand...
 
I checked as you suggested and your 1. is OK. I inserted the
Me.Combo51.Requery in the Committees combox after update event.

3. The CommID is text spelling out the names of each of the committees and
is lociated in the third column. I changed the statement in the Row Source
for Combo51: WHERE ((([Committee Position Table].CommID)=Forms![Committee
Update Form]!Committees.column(2)))....

Wwhen I click on Combo51, I get the message box: "undefined function
'Forms![Committee Update Form]!Committees.column' in expression.

Don't know what I did wrong.

Walt

Ofer said:
Three things I would check
1. The name of the form and combo are correct
2. On the after update event of the Committees combo box, you should run a
refresh on the second combo
Me.Combo51.requery
3. Is the CommID in the first combo, located as the first column. If it's on
the second column then you should use that
Forms![Committee Update Form]![Committees].column(1)
The column number start with 0
--
I hope that helped
Good luck


Walt said:
I have a form with a combo box [Committees] which is tied to a table with (2)
fields. [CommID] and [Committee]. I ahve a second combo box [Combo51] which
I want to list the available positions fro each committee.

I have created a table [Committee Position Table] with three fields,
[CommPositionID], [CommPosition] and [CommID]. [CommPosition] and
[CommPositionID] both list the committee position available. [CommID] lists
the corresponding committee for each position.

To get [Combo51] to show only the available positions for the committee
selected in [Committees], I have created the following statement in the Row
Source property field for [Combo51]:

SELECT [Committee Position Table].CommPosition, [Committee Position
Table].CommID FROM [Committee Position Table] WHERE (((Committee Position
Table.CommID)=Forms![Committee Update Form]!Committees)) ORDER BY [Committee
Position Table].CommPosition;

It doesn't work. Please help!

Thanks,
Walt
Click to expand...
Click to expand...
 
It doesn't make sense that the its the third column in the Row source of the
combo, in the first post you said that the table has only two fields, so it
can be either column 0 or 1.

--
I hope that helped
Good luck


Walt said:
I checked as you suggested and your 1. is OK. I inserted the
Me.Combo51.Requery in the Committees combox after update event.

3. The CommID is text spelling out the names of each of the committees and
is lociated in the third column. I changed the statement in the Row Source
for Combo51: WHERE ((([Committee Position Table].CommID)=Forms![Committee
Update Form]!Committees.column(2)))....

Wwhen I click on Combo51, I get the message box: "undefined function
'Forms![Committee Update Form]!Committees.column' in expression.

Don't know what I did wrong.

Walt

Ofer said:
Three things I would check
1. The name of the form and combo are correct
2. On the after update event of the Committees combo box, you should run a
refresh on the second combo
Me.Combo51.requery
3. Is the CommID in the first combo, located as the first column. If it's on
the second column then you should use that
Forms![Committee Update Form]![Committees].column(1)
The column number start with 0
--
I hope that helped
Good luck


Walt said:
I have a form with a combo box [Committees] which is tied to a table with (2)
fields. [CommID] and [Committee]. I ahve a second combo box [Combo51] which
I want to list the available positions fro each committee.

I have created a table [Committee Position Table] with three fields,
[CommPositionID], [CommPosition] and [CommID]. [CommPosition] and
[CommPositionID] both list the committee position available. [CommID] lists
the corresponding committee for each position.

To get [Combo51] to show only the available positions for the committee
selected in [Committees], I have created the following statement in the Row
Source property field for [Combo51]:

SELECT [Committee Position Table].CommPosition, [Committee Position
Table].CommID FROM [Committee Position Table] WHERE (((Committee Position
Table.CommID)=Forms![Committee Update Form]!Committees)) ORDER BY [Committee
Position Table].CommPosition;

It doesn't work. Please help!

Thanks,
Walt
Click to expand...
Click to expand...
Click to expand...
 
I must not have explained properly, so here goes again. The combo box
[Committees] Row Source is the table [Committees], which has two fields,
CommID and Committee. Both are text data type and are the names of the
committees. The statement is the Row Sourct fo the committees combo box is:
SELECT [Committees].CommID, [Committees].Committee FROM [Committees];

I want the second combo box [Combo51] to display only the committee
positions that partain to the committee displayed in the committees combo box.

The other table, [Committee Position Table] has three field, CommPosition
(text, CommPosition (text) and CommID. The CommPositionID and CommPosition
fields are indentical and contain the committee positions. The CommID field
is the name of the committees and is the same as the CommID field in the
committees table.

I tried again by changing the where statement for the row source for Combo51
to WHERE ((([Committee Position Table].CommID=Forms![Committee Update
Form!Committees.column(1))) .... Unfortunately this didn't work.

I hope this more complete explaination will help.

Thanks,

Walt


Ofer said:
It doesn't make sense that the its the third column in the Row source of the
combo, in the first post you said that the table has only two fields, so it
can be either column 0 or 1.

--
I hope that helped
Good luck


Walt said:
I checked as you suggested and your 1. is OK. I inserted the
Me.Combo51.Requery in the Committees combox after update event.

3. The CommID is text spelling out the names of each of the committees and
is lociated in the third column. I changed the statement in the Row Source
for Combo51: WHERE ((([Committee Position Table].CommID)=Forms![Committee
Update Form]!Committees.column(2)))....

Wwhen I click on Combo51, I get the message box: "undefined function
'Forms![Committee Update Form]!Committees.column' in expression.

Don't know what I did wrong.

Walt

Ofer said:
Three things I would check
1. The name of the form and combo are correct
2. On the after update event of the Committees combo box, you should run a
refresh on the second combo
Me.Combo51.requery
3. Is the CommID in the first combo, located as the first column. If it's on
the second column then you should use that
Forms![Committee Update Form]![Committees].column(1)
The column number start with 0
--
I hope that helped
Good luck


:

I have a form with a combo box [Committees] which is tied to a table with (2)
fields. [CommID] and [Committee]. I ahve a second combo box [Combo51] which
I want to list the available positions fro each committee.

I have created a table [Committee Position Table] with three fields,
[CommPositionID], [CommPosition] and [CommID]. [CommPosition] and
[CommPositionID] both list the committee position available. [CommID] lists
the corresponding committee for each position.

To get [Combo51] to show only the available positions for the committee
selected in [Committees], I have created the following statement in the Row
Source property field for [Combo51]:

SELECT [Committee Position Table].CommPosition, [Committee Position
Table].CommID FROM [Committee Position Table] WHERE (((Committee Position
Table.CommID)=Forms![Committee Update Form]!Committees)) ORDER BY [Committee
Position Table].CommPosition;

It doesn't work. Please help!

Thanks,
Walt
Click to expand...
Click to expand...
Click to expand...
 
The SQL look fine, check the properties of combo Committees, Does the
ColumnCount Property is 2 and not 1?

--
I hope that helped
Good luck


Walt said:
I must not have explained properly, so here goes again. The combo box
[Committees] Row Source is the table [Committees], which has two fields,
CommID and Committee. Both are text data type and are the names of the
committees. The statement is the Row Sourct fo the committees combo box is:
SELECT [Committees].CommID, [Committees].Committee FROM [Committees];

I want the second combo box [Combo51] to display only the committee
positions that partain to the committee displayed in the committees combo box.

The other table, [Committee Position Table] has three field, CommPosition
(text, CommPosition (text) and CommID. The CommPositionID and CommPosition
fields are indentical and contain the committee positions. The CommID field
is the name of the committees and is the same as the CommID field in the
committees table.

I tried again by changing the where statement for the row source for Combo51
to WHERE ((([Committee Position Table].CommID=Forms![Committee Update
Form!Committees.column(1))) .... Unfortunately this didn't work.

I hope this more complete explaination will help.

Thanks,

Walt


Ofer said:
It doesn't make sense that the its the third column in the Row source of the
combo, in the first post you said that the table has only two fields, so it
can be either column 0 or 1.

--
I hope that helped
Good luck


Walt said:
I checked as you suggested and your 1. is OK. I inserted the
Me.Combo51.Requery in the Committees combox after update event.

3. The CommID is text spelling out the names of each of the committees and
is lociated in the third column. I changed the statement in the Row Source
for Combo51: WHERE ((([Committee Position Table].CommID)=Forms![Committee
Update Form]!Committees.column(2)))....

Wwhen I click on Combo51, I get the message box: "undefined function
'Forms![Committee Update Form]!Committees.column' in expression.

Don't know what I did wrong.

Walt

:

Three things I would check
1. The name of the form and combo are correct
2. On the after update event of the Committees combo box, you should run a
refresh on the second combo
Me.Combo51.requery
3. Is the CommID in the first combo, located as the first column. If it's on
the second column then you should use that
Forms![Committee Update Form]![Committees].column(1)
The column number start with 0
--
I hope that helped
Good luck


:

I have a form with a combo box [Committees] which is tied to a table with (2)
fields. [CommID] and [Committee]. I ahve a second combo box [Combo51] which
I want to list the available positions fro each committee.

I have created a table [Committee Position Table] with three fields,
[CommPositionID], [CommPosition] and [CommID]. [CommPosition] and
[CommPositionID] both list the committee position available. [CommID] lists
the corresponding committee for each position.

To get [Combo51] to show only the available positions for the committee
selected in [Committees], I have created the following statement in the Row
Source property field for [Combo51]:

SELECT [Committee Position Table].CommPosition, [Committee Position
Table].CommID FROM [Committee Position Table] WHERE (((Committee Position
Table.CommID)=Forms![Committee Update Form]!Committees)) ORDER BY [Committee
Position Table].CommPosition;

It doesn't work. Please help!

Thanks,
Walt
Click to expand...
Click to expand...
Click to expand...
 
Back
Top