Populating a combo box with values depending on another Drop Down

[Guest]

I have a drop down list that contains all the setions in my company from a
table called Sections. The top three sections are A, B and C. Now if one of
these first three items is selected from the drop down list, cboSection, I
want the combobox cboPosition to populate with all the records of Section and
Position from the table PositionsbySection. How can i go about doing this?

 

[Guest]

Hi, Ty.

In the AfterUpdate event of first combo box, set the Row Source property of
the 2nd combo box, and requery it:

Me!ComboBox2.RowSource = "Select (field1, field2, etc.) FROM
PositionsBySection WHERE PositionsBySection.SectionID = Me!ComboBox1 ORDER BY
PositionDescription;"

Me!ComboBox2.Requery

Hope that helps.
Sprinks

 

[Guest]

OK, I dont know if was was 100% clear on the last post, but i only want the
second combo box to populate if one of the first three records is selected
from the first drop down. There are more than three records, however. If it
is possible, i would even like the second combo to hide and only disply if
one of the first three records which are called "A", "B", and "C" are
selected.

 

[Guest]

Hi, Ty.

No, sorry, I missed that nuance.

I think I would set the Visible and Enabled properties of the 2nd CB in
design view to No, then turn them on if the user picks one of the three
values. Alternatively, you can turn them off if the user selects a different
item, but you'll then have to write an On Current event procedure so that
they turn On on the next record.

Here, I think Select..Case is more compact than If..Then:

Select Case Me!CBox1
Case A,B,C ' Or the code that represents A, B, or C
With Me!CBox2
.Enabled = True
.Visible = True
End With
Me!CBox2.RowSource = "Select (field1, field2, etc.) FROM
PositionsBySection
WHERE PositionsBySection.SectionID = Me!CBox1
ORDER BY PositionDescription;"
Case Else
End Select

 

[Guest]

Well whats the code to retreive three specific rows in a table?

Also, where do the select case statements go?

 

[Guest]

This is what i have in my code builder, but nothing seems to happen when i
select one of the items. I beleive this is because its not recognizing the A,
B, C retreival part.

Private Sub CboSec_AfterUpdate(Cancel As Integer)
Select Case Me!CboSec
Case A, B, C ' 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 WHERE PositionsBySection.Section =" & Me!CboSec & "ORDER
BY Position;"
Case Else
End Select

End Sub

 

[Guest]

The Select Case statement should be placed in the first Combo Box'
AfterUpdate event procedure. You should put quotes around "A", "B", and "C".
You should also requery the combo box as I described in my earlier post but
omitted from the last.

I'm not sure I understand what you're asking by retrieving three specific
rows in a table. In general, selecting rows from a table uses an SQL
statement, specifying the selection criteria in the WHERE clause.

In the code below, if the user selects either "A", "B", or "C", the second
combo box displays ALL records from the PositionDescription table where the
SectionID (or whatever you've called the foreign key to the Sections table)
equals the value selected in the first combo box.

If that doesn't answer your question, please provide additional detail on
what you're interested in doing.

Hope that helps.
Sprinks

 

[Guest]

ok.. this is what is all im asking. If one of the first three items in cboSec
is selected, cboPos should appear and it should be filled with all records in
PositionBySection. So whats wrong here?

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

 

[Guest]

You need quotes around text values to identify them as string literals. My
apologies.

Case "A", "B", "C"
.....

P.S. If this doesn't resolve it, you must be storing a different column in
the combo box. Check the Bound Column to identify it, and change the Case
clause accordingly to this column's value in the 3 rows you're interested in.

HTH
Sprinks

 

[Guest]

Oh yeh, Bound Column of CboSec says 1

You need quotes around text values to identify them as string literals. My
apologies.

Case "A", "B", "C"
....

P.S. If this doesn't resolve it, you must be storing a different column in
the combo box. Check the Bound Column to identify it, and change the Case
clause accordingly to this column's value in the 3 rows you're interested in.

HTH
Sprinks

 

[Guest]

Two things.
1) The Row Source of CboPos is
SELECT Positionsbysection.Section, Positionsbysection.Position FROM
Positionsbysection;

Should that be in there?

2) I'm not too sure what you said further about what the problem could be,
because i have the A B and C in quotes

 

[Guest]

One final note. When i open the form and click on cbo sec its fine until i
select an item. then an error comes up that says:
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.

 

[Guest]

Hi, Ty.

Sorry I couldn't respond to you earlier; I had to myself research what can
cause this error. I think you must have modified the sub routine in some
way; Access can't associate the subroutine itself with the event. Probably
the easiest way to test that theory is to open the procedure and copy its
body (everything but the Sub and End Sub lines) to the Clipboard, paste it to
a Word file as a backup, then delete the entire procedure, and start over.
Access will create the proper Sub and End Sub lines for you again, and you
can paste the body between them.

If that does not solve your problem, cut and paste the entire procedure to a
new post.

Sprinks