One more Select Case problem

[SElgin via AccessMonster.com]

I am trying to limit the number of items in a combo box list based on the
value of another combo box. Here is my non working code.

Private Sub StuLevel_BeforeUpdate(Cancel As Integer)

Select Case Me.StuInstrument

Case 1, 2
Me.StuLevel.ListRows = 11

Case 3, 5
Me.StuLevel.ListRows = 8

Case 4
Me.StuLevel.ListRows = 15

Case 6
Me.StuLevel.ListRows = 10

Case 7
Me.StuLevel.ListRows = 4

End Select

Me.StuLevel.Requery

End Sub


It is possible that I have this in the wrong event as well.
Any help will be appreciated.

TIA

Steve

 

[Stefan Hoffmann]

hi,

Put the code in the after update of the StuInstrument combo box. It is still
not limiting the list rows of the StuLevel box.

ListRows only defines the number of rows visible in the drop down list,
it does not limit by any means the selectable data.


mfG
--> stefan <--

 

[BruceM]

You need to build the Row Source according to some criteria. As Stefan and
I have pointed out, limiting the number of list rows affects only the
display. The number of rows will not change.

More description is needed. What do you mean by "books the student can be
in"? Are the instruments musical instruments, or what exactly? What are the
fields in tblLevels? Is this about instruction or text books for students
playing one instrument or another?

Remember, you can see your database. People reading your posts can not.
You need to describe the situation fully enough that a course of action can
be suggested.

Generally speaking, your After Update event in the first text box would be
something like:

Dim strCrit as String, strSQL as String
strCrit = Me.StuInstrument
strSQL = "SELECT [Field1], [Field2] FROM tblLevels " & _
"WHERE [Field1] = " & strCrit & _
" ORDER BY [Field2]"
Me.StuLevels.RowSource = strSQL

The code I posted is just an example of the syntax. It most likely has
nothing to do with your actual situation.

Hi Bruce

What I am trying to do is limit the choices the user can have in StuLevels
based on the value in StuInstrument. Essentially the levels are books the
student can be in and each instrument has a different number of books
available. Some instruments have the same number of books, thus the 1,2.

The row source for StuLevels is tblLevels.

Thanks

Steve

You would generally use the After Update event of the first combo box to
set
the Row Source of the second combo box, but in any case you would use an
event in almost any control except the one you want to change. The Before
Update event of the second combo box will not take place until you have
made
a selection in that combo box, so it is not a way to set the Row Source of
that combo box. Note that setting the number of List Rows will not change
the Row Source, but rather limit the number of rows initially displayed.
The scrolling arrow will still be there, along with all of the selections.
What specific change do you seek to make to the available selections in
the
second combo box? What is the Row Source of the second combo box?

I am trying to limit the number of items in a combo box list based on the
value of another combo box. Here is my non working code.

[quoted text clipped - 30 lines]

Steve

 

[SElgin via AccessMonster.com]

I apologise if the post was not clear enough. To answer the questions though.


I did not understand the list rows property. I thought it would limit it.

tblLevels has two fields LevelId which is PK autonumber and LevelDesc which
is text field. There are only 15 rows in this table. 14 books and Advanced

The instruments are musical instruments. tblInstruments has two fields,
InstrumentId PK autonumber and Instrument which is text and contains Violin,
Guitar etc.

All I am trying to do here is limit the number of rows the combo box displays.
If the instrument is Bass they only have 3 Levels (books) to pick from. If
it is Flute they have 14.

I am OK with building the SQL but I thought I would have to use a structure
like I did in order to allow for the varying number of books for each
instrument.

TIA for the help.

Steve

You need to build the Row Source according to some criteria. As Stefan and
I have pointed out, limiting the number of list rows affects only the
display. The number of rows will not change.

More description is needed. What do you mean by "books the student can be
in"? Are the instruments musical instruments, or what exactly? What are the
fields in tblLevels? Is this about instruction or text books for students
playing one instrument or another?

Remember, you can see your database. People reading your posts can not.
You need to describe the situation fully enough that a course of action can
be suggested.

Generally speaking, your After Update event in the first text box would be
something like:

Dim strCrit as String, strSQL as String
strCrit = Me.StuInstrument
strSQL = "SELECT [Field1], [Field2] FROM tblLevels " & _
"WHERE [Field1] = " & strCrit & _
" ORDER BY [Field2]"
Me.StuLevels.RowSource = strSQL

The code I posted is just an example of the syntax. It most likely has
nothing to do with your actual situation.

Hi Bruce

[quoted text clipped - 28 lines]