cascading list boxes for query

R

Robert Painter

Hi
have grabbed code for cascading list/cbo boxes and adapted to hopefully
suit.
lstCategory from tblcategories.categorydescription
lstSkill from tblskill.skilldescription

using the following code:

Private Sub lstCategory_AfterUpdate()
Dim strCateg As String
Dim varSelected As Variant

If Me.lstCategory.ItemsSelected.Count > 0 Then
For Each varSelected In Me.lstCategory.ItemsSelected
strCateg = strCateg & "'" & Me.lstCategory.ItemData(varSelected) & "',
"
Next varSelected
strCateg = Left(strCateg, Len(strCateg) - 2)
Me.LstSkill.RowSource = "SELECT SkillDescription " & _
"FROM tblSkills " & _
"WHERE tblCategory IN (" & strCateg & ") " & _
"ORDER BY SkillDescription"
End If

I cannot get rowsource populated hence nothing showing in lstSkill

Any ideas of what i am doing wrong.

Have managed to cascade but done another way but lst only refreshes when
focused.

Robert
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put a BreakPoint at the line

Me.LstSkill.RowSource = "SELECT SkillDescription " & _

Run the code & step thru to the End If. Then type the following in the
Debug window:

? lstSkill.RowSource

This will show if the RowSource has accepted the SELECT query.

If the query is in the RowSource, then copy the SQL statement from the
Debug window and paste it into a QueryDef's SQL View & run the query.
Debug any errors the query throws up. When the query works replace the
SQL string in the VBA routine w/ the working query's SQL string, if
necessary.

Usually it is not necessary, but, you could add a .Requery to the
ListBox after the RowSource is populated:

lstSkill.Requery

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZWiLYechKqOuFEgEQJOugCgjDI2MowDR8yT96zCox3FdXGUNxQAoNKC
7ghFRdr52NDEft3hTVPOBBGE
=UNvI
-----END PGP SIGNATURE-----
 
R

Robert Painter

Thanx for the assistance. Have managed to now get it working correctly..
once again thankyou

Robert
 
Top