[azadehv]

I have a table named : schoolInfo in access 2007! and it has two fields ( schName and mjrName) Now I'm trying to design a combo in visual basic 6 (cboMajors) which is related to the other combo (cboSchool) as a matter of fact I wanna have to cascading combo boxes! when I choose an Item in cboSchool the other combo should represents just the related majors for that school ( records with schName=x and mjrName=y)

Private Sub Form_Activate()

connection ' the Connection is a code in module contains codes are needed to make the connection between form and the database

fill_schools fill_majors

End Sub

Private Sub fill_schools() With rs

.Open "select DISTINCT schName from tblSchoolsInfo", cn, 2, 3

    Do While Not .EOF
    cboSchool.AddItem (.Fields(0))
    .MoveNext
Loop
End With rs.Close End Sub

Private Sub fill_majors()

With rs .Open "select DISTINCT mjrName from tblSchoolsInfo where schName= '" & Me.cboSchool & " '", cn, 2, 3

    Do While Not .EOF
    cboMajors.AddItem (.Fields(0))
    .MoveNext
Loop
End With 
End Sub

Now: the first combo gets correct values but the second one is completely Empty! could anybody help me please? thanks in advanced

This post has been edited by BobRodes: 03 February 2013 - 05:11 PM
Reason for edit:: Please use code tags in future.

[andrewsw]

The code to fill the majors should not run on activating, or first loading, the form, as the first combo-box is empty.

I would initially disable the 2nd combo-box and only enable it, and add items to it, once a value from the 1st combo-box is chosen.

There are guides to this process if you Google.

BTW Access combo-boxes have a Recordset (or RowSource, and RowSourceType) property that you can use rather than looping through a recordset.

This post has been edited by andrewsw: 02 February 2013 - 06:52 AM

[azadehv]

andrewsw, on 02 February 2013 - 06:48 AM, said:

The code to fill the majors should not run on activating, or first loading, the form, as the first combo-box is empty.

I would initially disable the 2nd combo-box and only enable it, and add items to it, once a value from the 1st combo-box is chosen.

There are guides to this process if you Google.

BTW Access combo-boxes have a Recordset (or RowSource, and RowSourceType) property that you can use rather than looping through a recordset.

Thnx Andrew!
But I Googled my problem n didn't find anything helpy! could u suggest me some links?
and is that realy important where did I create the database? access or somewhere else?
I put the fill_majors procedure call in cboSchool_change but it is empty yet!

[andrewsw]

Try this

http://msdn.microsof...office.12).aspx

Search "synchronize combo boxes" or "Access synchronize.. " etc - loads of results?!

Added:

With rs .Open "select DISTINCT mjrName from tblSchoolsInfo where schName= '" & Me.cboSchool & " '", cn, 2, 3

You've got an extra space at the end of your SQL statement.

This post has been edited by andrewsw: 02 February 2013 - 07:02 AM

[azadehv]

andrewsw, on 02 February 2013 - 06:59 AM, said:

Try this

http://msdn.microsof...office.12).aspx

Search "synchronize combo boxes" or "Access synchronize.. " etc - loads of results?!

Yeah! I have tried it b4 , but it didn't work as well! the second combo is still empty

[andrewsw]

I added to my previous post - you have an extra space..

If it still doesn't work then you'll need to set breakpoints and step through your code.

This post has been edited by andrewsw: 02 February 2013 - 07:05 AM

[azadehv]

andrewsw, on 02 February 2013 - 07:04 AM, said:

I added to my previous post - you have an extra space..

If it still doesn't work then you'll need to set breakpoints and step through your code.

ok! I fix the extra space and the second combo is still empty! now Im doing the search!
thanks Dear Andrew :)I will ask my questions again if I couldnt help the program anymore, is that ok?

[andrewsw]

azadehv, on 02 February 2013 - 07:15 AM, said:

andrewsw, on 02 February 2013 - 07:04 AM, said:

I added to my previous post - you have an extra space..

If it still doesn't work then you'll need to set breakpoints and step through your code.

ok! I fix the extra space and the second combo is still empty! now Im doing the search!
thanks Dear Andrew :)I will ask my questions again if I couldnt help the program anymore, is that ok?

Okay. But you should learn to debug and step through the code; there are tutorials on this site. It is an essential skill.

[azadehv]

andrewsw, on 02 February 2013 - 07:29 AM, said:

azadehv, on 02 February 2013 - 07:15 AM, said:

andrewsw, on 02 February 2013 - 07:04 AM, said:

I added to my previous post - you have an extra space..

If it still doesn't work then you'll need to set breakpoints and step through your code.

ok! I fix the extra space and the second combo is still empty! now Im doing the search!
thanks Dear Andrew :)I will ask my questions again if I couldnt help the program anymore, is that ok?

Okay. But you should learn to debug and step through the code; there are tutorials on this site. It is an essential skill.

u mean I should use the F8 key? n Run the program step by step? I did it the rs variable in "fill_majors" procedure has completely NOTHING!

[andrewsw]

Obtain the text of the SQL statement before opening the recordset, and display it to make sure it is correct:

Dim sSql As String
sSql = "select DISTINCT mjrName from tblSchoolsInfo where schName= '" & Me.cboSchool & "'"
MsgBox sSql

Me.cboSchool probably doesn't return the selected text. I haven't checked this (I ain't got VB6 currently). But you probably need something like:

cboSchool.List(cboSchool.ListIndex)

to retrieve the name of the school.

This post has been edited by andrewsw: 02 February 2013 - 10:29 AM

[azadehv]

andrewsw, on 02 February 2013 - 10:26 AM, said:

Obtain the text of the SQL statement before opening the recordset, and display it to make sure it is correct:

Dim sSql As String
sSql = "select DISTINCT mjrName from tblSchoolsInfo where schName= '" & Me.cboSchool & "'"
MsgBox sSql

Me.cboSchool probably doesn't return the selected text. I haven't checked this (I ain't got VB6 currently). But you probably need something like:

cboSchool.List(cboSchool.ListIndex)

to retrieve the name of the school.

Hooray! the second combo now represents correct data
thanks thanks thanks andrew

[andrewsw]

You are welcome

[BobRodes]

azadehv, I'm glad you got your combobox working, but the reason that you had the problem in the first place is because you need to set your Style property to 2, vbComboDropdownList. (I'm assuming that you aren't intending the user to be able to type in any school he wants to if he doesn't find it on your list.) This is the reason that your code didn't work until Andrew gave you his suggestion. The point is that the Text property is the default property for ComboBox. The default for the combo box is to be able to type your own values in the top of the box, which is why you have that funny text box at the top of it (you do, don't you?). And if the Style is either 0 or 1, then the Text property returns whatever you type in the box. Since you didn't type anything in the top box, you didn't get any values.

cboSchool.LIst(cboSchool.ListIndex) and cboSchool.Text are equivalent if the Style property is set to 2. I can't remember any time that I've used any other style of combo box in an application, and I generally use Text instead of the longer property to get the selected value.

andrewsw, on 02 February 2013 - 08:04 AM, said:

I added to my previous post - you have an extra space..

If it still doesn't work then you'll need to set breakpoints and step through your code.

Andrew, since the extra space isn't contained in the string, it makes no difference.

SELECT mjrName from Schools where SchoolName = 'Lincoln'
SELECT mjrName from Schools where SchoolName= 'Lincoln'
SELECT mjrName from Schools where SchoolName='Lincoln'

are equivalent as far as SQL syntax is concerned. On the other hand,

SELECT mjrName from Schools where SchoolName = ' Lincoln'

is different, of course.

This post has been edited by BobRodes: 03 February 2013 - 05:43 PM

[andrewsw]

The space was within the quotes (post #1):

'" & Me.cboSchool & " '", cn, 2, 3

produces 'StTrinians '

[azadehv]

BobRodes, on 03 February 2013 - 05:29 PM, said:

azadehv, I'm glad you got your combobox working, but the reason that you had the problem in the first place is because you need to set your Style property to 2, vbComboDropdownList. (I'm assuming that you aren't intending the user to be able to type in any school he wants to if he doesn't find it on your list.) This is the reason that your code didn't work until Andrew gave you his suggestion. The point is that the Text property is the default property for ComboBox. The default for the combo box is to be able to type your own values in the top of the box, which is why you have that funny text box at the top of it (you do, don't you?). And if the Style is either 0 or 1, then the Text property returns whatever you type in the box. Since you didn't type anything in the top box, you didn't get any values.

cboSchool.LIst(cboSchool.ListIndex) and cboSchool.Text are equivalent if the Style property is set to 2. I can't remember any time that I've used any other style of combo box in an application, and I generally use Text instead of the longer property to get the selected value.

andrewsw, on 02 February 2013 - 08:04 AM, said:

I added to my previous post - you have an extra space..

If it still doesn't work then you'll need to set breakpoints and step through your code.

Andrew, since the extra space isn't contained in the string, it makes no difference.

SELECT mjrName from Schools where SchoolName = 'Lincoln'
SELECT mjrName from Schools where SchoolName= 'Lincoln'
SELECT mjrName from Schools where SchoolName='Lincoln'

are equivalent as far as SQL syntax is concerned. On the other hand,

SELECT mjrName from Schools where SchoolName = ' Lincoln'

is different, of course.

Bob! That was your favour, yes I did! I did use that funny text box and now I will delete it, I didn't mention the Style property values and thanks