Populating Combo Box after another combo box has been selected

G

Guest

Here is what i am trying to do.
I have Two combo boxes, cboPos and cboSec. In my form, i want cboSec to be
hidden, except for when the values "A Watch" "B Watch" or "C Watch" is chosen
from cboSec. Here is what i currently have:

cboSec Row Source:
SELECT Sections.[Section ID], Sections.Section FROM Sections;

cboPos Row Source:
SELECT Positionsbysection.Section, Positionsbysection.Position FROM
Positionsbysection;

cboSec AfterUpdate:
Private Sub CboSec_AfterUpdate(Cancel As Integer)
Select Case Me!CboSec
Case "A Watch", "B Watch", "C Watch" ' Or the code
that represents A, B, or C
With Me!CboPos
.Enabled = True
.Visible = True
End With
Me!CboPos.RowSource = "Select (Section, Position) FROM
PositionsBySection ORDER BY Position;"
Case Else
End Select
Me!CboPos.Requery
End Sub

I am currently receiving this error when i try to select something form
cboSec:
The Expression After Update you entered as the event property setting
produced the following error: Procedure declaration does not match the
description of event or procedure having the same name.

Please help me figure this out!!!
 
W

Wayne Morgan

A couple of things.

First, by the description of the error message, it appears you may have
entered "AfterUpdate" in the Event tab of the combo box's Properties. It
should actually read "[Event Procedure]" on the On After Update line.

Next, for cboSec you are returning 2 columns. The first is the Section ID
and the second is the Section Name. Usually, if the ID is used, then it is
the Bound Column. If that is the case, then the value of cboSec will be the
ID value, not the Section Name. In your Select Case statement, you are using
the Value of cboSec then checking the Case using the Section Name. If the ID
column is the Bound Column, these won't match. To solve that problem, change

Me!cboSec
to
Me!cboSec.Column(1)

The column index is zero based, so 0 is the first column, 1 the second, etc.

Also, in the case you have specified you make the second combo visible. What
if someone chooses Section A then changes their mind. I suspect you would
want to hide the second combo again. In the Case Else, set the second
combo's Visible back to False. It should also not be necessary to
Enable/Disable the second combo if you are hiding it. If it is hidden the
user can't do anything with it, whether or not it is enabled.
 
G

Guest

OK
First. [Event Procedure] is written in the After Update of cboSec.

Secondly... im confused where you are talking about the two columns.. I
added .column(1) in the select statement. I also changed the bound column to
2.

Third I added
Case Else
..visible = false..

Yet I am still receiving the same error. What could possibly be going wrong?
Wayne Morgan said:
A couple of things.

First, by the description of the error message, it appears you may have
entered "AfterUpdate" in the Event tab of the combo box's Properties. It
should actually read "[Event Procedure]" on the On After Update line.

Next, for cboSec you are returning 2 columns. The first is the Section ID
and the second is the Section Name. Usually, if the ID is used, then it is
the Bound Column. If that is the case, then the value of cboSec will be the
ID value, not the Section Name. In your Select Case statement, you are using
the Value of cboSec then checking the Case using the Section Name. If the ID
column is the Bound Column, these won't match. To solve that problem, change

Me!cboSec
to
Me!cboSec.Column(1)

The column index is zero based, so 0 is the first column, 1 the second, etc.

Also, in the case you have specified you make the second combo visible. What
if someone chooses Section A then changes their mind. I suspect you would
want to hide the second combo again. In the Case Else, set the second
combo's Visible back to False. It should also not be necessary to
Enable/Disable the second combo if you are hiding it. If it is hidden the
user can't do anything with it, whether or not it is enabled.

--
Wayne Morgan
MS Access MVP


Ty said:
Here is what i am trying to do.
I have Two combo boxes, cboPos and cboSec. In my form, i want cboSec to be
hidden, except for when the values "A Watch" "B Watch" or "C Watch" is
chosen
from cboSec. Here is what i currently have:

cboSec Row Source:
SELECT Sections.[Section ID], Sections.Section FROM Sections;

cboPos Row Source:
SELECT Positionsbysection.Section, Positionsbysection.Position FROM
Positionsbysection;

cboSec AfterUpdate:
Private Sub CboSec_AfterUpdate(Cancel As Integer)
Select Case Me!CboSec
Case "A Watch", "B Watch", "C Watch" ' Or the code
that represents A, B, or C
With Me!CboPos
.Enabled = True
.Visible = True
End With
Me!CboPos.RowSource = "Select (Section, Position) FROM
PositionsBySection ORDER BY Position;"
Case Else
End Select
Me!CboPos.Requery
End Sub

I am currently receiving this error when i try to select something form
cboSec:
The Expression After Update you entered as the event property setting
produced the following error: Procedure declaration does not match the
description of event or procedure having the same name.

Please help me figure this out!!!
Click to expand...
Click to expand...
 
G

Guest

What exactly does the Me! mean? I think the problem must be in the after
event SQL coding.
 
D

Dan Artuso

Post the Procedure Declaration. It sounds as though you've modified it.
It should be something like:

Private Sub cboSec_AfterUpdate()

--
HTH
Dan Artuso, Access MVP


Ty said:
OK
First. [Event Procedure] is written in the After Update of cboSec.

Secondly... im confused where you are talking about the two columns.. I
added .column(1) in the select statement. I also changed the bound column to
2.

Third I added
Case Else
.visible = false..

Yet I am still receiving the same error. What could possibly be going wrong?
Wayne Morgan said:
A couple of things.

First, by the description of the error message, it appears you may have
entered "AfterUpdate" in the Event tab of the combo box's Properties. It
should actually read "[Event Procedure]" on the On After Update line.

Next, for cboSec you are returning 2 columns. The first is the Section ID
and the second is the Section Name. Usually, if the ID is used, then it is
the Bound Column. If that is the case, then the value of cboSec will be the
ID value, not the Section Name. In your Select Case statement, you are using
the Value of cboSec then checking the Case using the Section Name. If the ID
column is the Bound Column, these won't match. To solve that problem, change

Me!cboSec
to
Me!cboSec.Column(1)

The column index is zero based, so 0 is the first column, 1 the second, etc.

Also, in the case you have specified you make the second combo visible. What
if someone chooses Section A then changes their mind. I suspect you would
want to hide the second combo again. In the Case Else, set the second
combo's Visible back to False. It should also not be necessary to
Enable/Disable the second combo if you are hiding it. If it is hidden the
user can't do anything with it, whether or not it is enabled.

--
Wayne Morgan
MS Access MVP


Ty said:
Here is what i am trying to do.
I have Two combo boxes, cboPos and cboSec. In my form, i want cboSec to be
hidden, except for when the values "A Watch" "B Watch" or "C Watch" is
chosen
from cboSec. Here is what i currently have:

cboSec Row Source:
SELECT Sections.[Section ID], Sections.Section FROM Sections;

cboPos Row Source:
SELECT Positionsbysection.Section, Positionsbysection.Position FROM
Positionsbysection;

cboSec AfterUpdate:
Private Sub CboSec_AfterUpdate(Cancel As Integer)
Select Case Me!CboSec
Case "A Watch", "B Watch", "C Watch" ' Or the code
that represents A, B, or C
With Me!CboPos
.Enabled = True
.Visible = True
End With
Me!CboPos.RowSource = "Select (Section, Position) FROM
PositionsBySection ORDER BY Position;"
Case Else
End Select
Me!CboPos.Requery
End Sub

I am currently receiving this error when i try to select something form
cboSec:
The Expression After Update you entered as the event property setting
produced the following error: Procedure declaration does not match the
description of event or procedure having the same name.

Please help me figure this out!!!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

OK.. that worked. cboPos appears now when one of those are clicked... However
it is not filled with anything.. whats going on here?

Dan Artuso said:
Post the Procedure Declaration. It sounds as though you've modified it.
It should be something like:

Private Sub cboSec_AfterUpdate()

--
HTH
Dan Artuso, Access MVP


Ty said:
OK
First. [Event Procedure] is written in the After Update of cboSec.

Secondly... im confused where you are talking about the two columns.. I
added .column(1) in the select statement. I also changed the bound column to
2.

Third I added
Case Else
.visible = false..

Yet I am still receiving the same error. What could possibly be going wrong?
Wayne Morgan said:
A couple of things.

First, by the description of the error message, it appears you may have
entered "AfterUpdate" in the Event tab of the combo box's Properties. It
should actually read "[Event Procedure]" on the On After Update line.

Next, for cboSec you are returning 2 columns. The first is the Section ID
and the second is the Section Name. Usually, if the ID is used, then it is
the Bound Column. If that is the case, then the value of cboSec will be the
ID value, not the Section Name. In your Select Case statement, you are using
the Value of cboSec then checking the Case using the Section Name. If the ID
column is the Bound Column, these won't match. To solve that problem, change

Me!cboSec
to
Me!cboSec.Column(1)

The column index is zero based, so 0 is the first column, 1 the second, etc.

Also, in the case you have specified you make the second combo visible. What
if someone chooses Section A then changes their mind. I suspect you would
want to hide the second combo again. In the Case Else, set the second
combo's Visible back to False. It should also not be necessary to
Enable/Disable the second combo if you are hiding it. If it is hidden the
user can't do anything with it, whether or not it is enabled.

--
Wayne Morgan
MS Access MVP


Here is what i am trying to do.
I have Two combo boxes, cboPos and cboSec. In my form, i want cboSec to be
hidden, except for when the values "A Watch" "B Watch" or "C Watch" is
chosen
from cboSec. Here is what i currently have:

cboSec Row Source:
SELECT Sections.[Section ID], Sections.Section FROM Sections;

cboPos Row Source:
SELECT Positionsbysection.Section, Positionsbysection.Position FROM
Positionsbysection;

cboSec AfterUpdate:
Private Sub CboSec_AfterUpdate(Cancel As Integer)
Select Case Me!CboSec
Case "A Watch", "B Watch", "C Watch" ' Or the code
that represents A, B, or C
With Me!CboPos
.Enabled = True
.Visible = True
End With
Me!CboPos.RowSource = "Select (Section, Position) FROM
PositionsBySection ORDER BY Position;"
Case Else
End Select
Me!CboPos.Requery
End Sub

I am currently receiving this error when i try to select something form
cboSec:
The Expression After Update you entered as the event property setting
produced the following error: Procedure declaration does not match the
description of event or procedure having the same name.

Please help me figure this out!!!
Click to expand...
Click to expand...
Click to expand...
 
D

Dan Artuso

Hi,
There are 2 columns in your combo, right?
Which one do you think you're getting the value from with this statement:
Select Case Me.CboSec (use the dot operator, not !)

If you guessed Section ID, you're right.
Combos have a column property which enables you to get the value of any column.
The first column is Column(0) the second is Column(1) and so on.

I'll leave it to you to correct the statement :)


--
HTH
Dan Artuso, Access MVP


Ty said:
OK.. that worked. cboPos appears now when one of those are clicked... However
it is not filled with anything.. whats going on here?

Dan Artuso said:
Post the Procedure Declaration. It sounds as though you've modified it.
It should be something like:

Private Sub cboSec_AfterUpdate()

--
HTH
Dan Artuso, Access MVP


Ty said:
OK
First. [Event Procedure] is written in the After Update of cboSec.

Secondly... im confused where you are talking about the two columns.. I
added .column(1) in the select statement. I also changed the bound column to
2.

Third I added
Case Else
.visible = false..

Yet I am still receiving the same error. What could possibly be going wrong?
:

A couple of things.

First, by the description of the error message, it appears you may have
entered "AfterUpdate" in the Event tab of the combo box's Properties. It
should actually read "[Event Procedure]" on the On After Update line.

Next, for cboSec you are returning 2 columns. The first is the Section ID
and the second is the Section Name. Usually, if the ID is used, then it is
the Bound Column. If that is the case, then the value of cboSec will be the
ID value, not the Section Name. In your Select Case statement, you are using
the Value of cboSec then checking the Case using the Section Name. If the ID
column is the Bound Column, these won't match. To solve that problem, change

Me!cboSec
to
Me!cboSec.Column(1)

The column index is zero based, so 0 is the first column, 1 the second, etc.

Also, in the case you have specified you make the second combo visible. What
if someone chooses Section A then changes their mind. I suspect you would
want to hide the second combo again. In the Case Else, set the second
combo's Visible back to False. It should also not be necessary to
Enable/Disable the second combo if you are hiding it. If it is hidden the
user can't do anything with it, whether or not it is enabled.

--
Wayne Morgan
MS Access MVP


Here is what i am trying to do.
I have Two combo boxes, cboPos and cboSec. In my form, i want cboSec to be
hidden, except for when the values "A Watch" "B Watch" or "C Watch" is
chosen
from cboSec. Here is what i currently have:

cboSec Row Source:
SELECT Sections.[Section ID], Sections.Section FROM Sections;

cboPos Row Source:
SELECT Positionsbysection.Section, Positionsbysection.Position FROM
Positionsbysection;

cboSec AfterUpdate:
Private Sub CboSec_AfterUpdate(Cancel As Integer)
Select Case Me!CboSec
Case "A Watch", "B Watch", "C Watch" ' Or the code
that represents A, B, or C
With Me!CboPos
.Enabled = True
.Visible = True
End With
Me!CboPos.RowSource = "Select (Section, Position) FROM
PositionsBySection ORDER BY Position;"
Case Else
End Select
Me!CboPos.Requery
End Sub

I am currently receiving this error when i try to select something form
cboSec:
The Expression After Update you entered as the event property setting
produced the following error: Procedure declaration does not match the
description of event or procedure having the same name.

Please help me figure this out!!!
Click to expand...
Click to expand...
Click to expand...
 
W

Wayne Morgan

"Me" is Visual Basic shorthand for the form or report that the code is
running on. For example, if the form is called frmMyForm and the code is in
this form's code module (whether for a form event or the event of a control
on the form) then you could refer to a control on the form using

Forms!frmMyForm!ctlMyControl

This is how you would also refer to a control on another form. However, VB
will let you shorten this to just Me!ctlMyControl if you are speaking about
the form the code is on. "Me" is not recognized in SQL or even in the
Control Source of a calculated control on a form or report, just in the VB
code attached to a form or report.
 
W

Wayne Morgan

Ty,

In your previous message you stated that you added ".Column(1)" to the
select statement and also changed the Bound Column to 2. Only one of these
should be needed, but it should continue to work with both.

In your original post, you stated:
cboSec Row Source:
SELECT Sections.[Section ID], Sections.Section FROM Sections;
Click to expand...

This SQL statement is retrieving the [Section ID] and Section fields from
the table Sections. The combo box will use these in the order you have them
listed in the SQL. So [Section ID] would be in column 1 and Section would be
in column 2. You can adjust the width of these columns on the Format tab of
the combo box's Properties. The first visible column (width > 0) will be
what is displayed in the textbox portion of the combo box after a selection
is made. The value of the combo box will come from the Bound Column, which
if defined as 1 would be the value in [Section ID]. If you need the value
from a different column, you can redefine the Bound Column or just specify
which column you want the value from. In this case "cboSec.Column(1)" would
give you the selected value from the column filled by Section.
PositionsBySection ORDER BY Position;"<<

It appears that Access doesn't like the parenthesis around the field names.
Try

Me!CboPos.RowSource = "Select Section, Position FROM PositionsBySection
ORDER BY Position;"
or
Me!CboPos.RowSource = "Select PositionsBySection.Section,
PositionsBySection.Position FROM PositionsBySection ORDER BY
PositionsBySection.Position;"

--
Wayne Morgan
Microsoft Access MVP
Ty said:
OK
First. [Event Procedure] is written in the After Update of cboSec.

Secondly... im confused where you are talking about the two columns.. I
added .column(1) in the select statement. I also changed the bound
column to
2.

Third I added
Case Else
.visible = false..
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Populating a combo box with values depending on another Drop Down 12
Access Control on subform / combo box requesting parameter 0
Cascading Combo Box - help needed please. 6
Populating a combo box depending on the selection of another combo 1
Prompted for a value for combo box value 1
Using a combo box as a filter 1
Combo box not showing field 6
Limit content of combo boxes 1

Top