Cascading Combo Boxes

[stfcTerryA]

cboProject
cboProjectModel
cboProjectCard

cboProject - looks up projects in a table (tblProjects)
cboProjectModel - looks up data in a value list ("DM";"EM";"FM";"TEST")

I want the cboProjectCard to filter the options by what has been selected in
cboProject and cboProjectModel

The first section works fine as in Project = " & Me.cboProject & _

but the second part produces a pop up box with the value i selected from
cboProjectModel as the label above the blank field. Example: If i selected
FM it would show FM above the text entry field of the pop up box. Now If i
then type in DM into the text entry field of the pop up box FM is shown as
what was selected in the cboProjectModel however the cboProjectsCard lists
the options for the DM.

Below is the code i'm using to do this - what am i doing wrong?


Private Sub cboProjectModel_AfterUpdate()

Me.cboProjectCard.RowSource = "SELECT Card FROM" & _
" tblProjectCards WHERE Project = " & Me.cboProject & _
" AND Model = " & Me.cboProjectModel & _
" ORDER BY ProjectCard"

Me.cboProjectCard = Me.cboProjectCard.ItemData(0)

End Sub

Cheers


Terry

 

[Rod Plastow]

Hi Terry,

I am a little confused by your explanation as you need to be a little more
specific about to which pop up box (combo box?) you are referring and what
you mean by the 'label above the blank field.' However let's try and get you
moving in the correct direction.

Your SQL looks OK except you 'SELECT Card ...' and then '... ORDER BY
ProjectCard' Is this what you mean? Are Card and ProjectCard both columns
on the table?

I would tend to use the BeforeUpdate event for the first two combo boxes and
control the availability of the third box from these event procedures.
Here's a suggestion for the code; the code could be placed in a separate sub
procedure to avoid duplication.

If IsNull(Me.cboProject) then
Me.cboProject.SetFocus
Me.cboProjectCard.Enabled = False
ElseIf IsNull(Me.cboProjectModel)
Me.cboProjectModel.SetFocus
Me.cboProjectCard.Enabled = False
Else
Me.cboProjectCard.Enabled = True
Me.cboProjectCard.RowSource = <whatever>
Me.cboProjectCard.Requery
Me.cboProjectCard = Me.cboProjectCard.ItemData(0)
Me.cboProjectCard.SetFocus
End If

This may be more elaborate than you need but at least you are in full control.

I thought that changing the rowsource triggered a requery but on reviewing
some of my code I see I have always included a requery of the combo box
immediately after changing the rowsource. Hence there is a requery statement
in the above.

Rod

 

[stfcTerryA]

Hi Rod,

Thanks for that, the pop up box is a enter parameter value box but the
parameter it specifying it needs (as in the text above the use entry field)
is the value (one of the option from the combo box value list, either DM, FM,
TEST etc) I then enter the value DM and it filters the cboProjectCard buy
that model.

Its as if it doesnt see what i selected in the combo box as the value it's
looking for but the name of the parameter.

I thought where i had specified ORDER BY ProjectCard was the reason for the
pop up box but no such luck, yes it should have read ORDER BY Card

Have looked at your code and understood most of it but what do you mean when
you typed <whatever>


Cheers


Terry

 

[Rod Plastow]

Hi Terry,

<whatever> means insert your SQL here.

Check the property sheets for both cboProject and cboProjectModel to ensure
that the correct column is the bound column - usually column 1.

The following example SQL assumes that the bound value in cboProject is
numeric and the bound value in cboProjectModel is alpha - as determined from
your value list. If cboProject is also alpha then apply the same construct
to the first variable.

"SELECT Card FROM tblProjectCards WHERE Project = " & Me.cboProject & _
" AND Model = '" & Me.cboProjectModel & _
"' ORDER BY Card"

Look very closely and you will see an extra single quote just before and
just after the cboProjectModel expression. These are necessary because
cboProjectModel is a string variable and needs to be enclosed with quotes in
the resulting SQL. You can use two double quotes instead of one single quote
and the relevant portion then looks like

" AND Model = """ & Me.cboProjectModel & """ ORDER BY Card"

Rod

PS The single quotes go inside the explicit text, so they occur to the left
and the right of the double quotes respectively - expanded for clarity only

" AND Model = ' " & Me.cboProjectModel & " ' ORDER BY Card"

 

[stfcTerryA]

Hi Rod

Many thanks problem resolved!! It was the quotes

Do have another issue don't know if you can help naturally i'm trying to
have the cboProjectCard box filtered with a list of options that match
criteria in combo 1 and 2. Now works great thanks - problem is the
cboProjectCard gives results like KE0199985A or KE0201453B which aren't very
user friendly so what i was then trying to do is populate a txtBox with the
ProjectCardName once a selection has been made.

Me.txtProjectCardName = "SELECT CardName FROM" & _
" tblProjectCards WHERE Card = " & Me.cboProjectCard

This as i'm sure your aware prints exact what i've written above without the
"" and fills in quite nicely the project card number.

Any help greatly appreciated

Many thanks


Terry

 

[Rod Plastow]

Hi Terry,

Pleased to hear things are moving forward.

Your latest problem is as before, alphanumeric variables. The SQL should be:

Me.txtProjectCardName = "SELECT CardName FROM" & _
" tblProjectCards WHERE Card = '" & Me.cboProjectCard & "'"

HOWEVER ...

You can solve your requirement using only the combo box. Alter the row
source for cboProjectCard to be:

"SELECT Card, CardName FROM tblProjectCards WHERE Project = " &
Me.cboProject & _
" AND Model = '" & Me.cboProjectModel & _
"' ORDER BY Card"

While in the Property Sheet make sure that, under the Data tab, Bound Column
is 1 and that, under the Format tab, Column Count is 2 and Column Widths is
0;2 (you may have to play with the second number as the units will be your
default Windows units). Remember that if you use this technique the true
value of cboProjectCard is Card despite the fact it shows CardName.

Rod

 

[stfcTerryA]

Hi Rod

Hope you had a good weekend, mine was spent replacing a floor hence now in a
bit of pain. Oh well, at least its back to the desk job; ok i have tried
putting the &cboProjectCard in quotes as suggest but the text in the txtBox
still prints as the code i've typed in.

SELECT CardName FROM tblProjectCards WHERE Card = 'KE0199-01n'

I'm assuming this is because i can not add a .RowSource to a txt box as in
Me.txtProjectCardName.RowSource hence it see the value my code after the
first " as text.

I had thought of using a combo box and hidding it then having the text box
print what was written in the combo box but imagine there must be a correct
way of doing it.

Normally i would have opted for your second suggestion but the reason i
havent is the layout of my form would be thrown out by having the name of the
cards displayed as the text is so long. So currently i have the Project card
label then combo box and the text box fits nicely under both.

Many thanks

Terry

 

[stfcTerryA]

Hi Rod

Solved it!! So simple if only i knew what i was doing, used the column()
option.

Private Sub cboProjectCard_Change()

Me.txtProjectCardName = Me.cboProjectCard.Column(1)

End Sub

Cheers



Terry