problem using related combo boxes in access 2007 database programming

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 1515 Views - Last Post: 08 February 2013 - 12:24 PM Rate Topic: -----

#1 azadehv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-February 13

problem using related combo boxes in access 2007 database programming

Posted 02 February 2013 - 06:38 AM

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.


Is This A Good Question/Topic? 0
  • +

Replies To: problem using related combo boxes in access 2007 database programming

#2 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3822
  • View blog
  • Posts: 13,544
  • Joined: 12-December 12

Re: problem using related combo boxes in access 2007 database programming

Posted 02 February 2013 - 06:48 AM

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

Was This Post Helpful? 1
  • +
  • -

#3 azadehv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-February 13

Re: problem using related combo boxes in access 2007 database programming

Posted 02 February 2013 - 06:57 AM

View Postandrewsw, 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!
:(
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3822
  • View blog
  • Posts: 13,544
  • Joined: 12-December 12

Re: problem using related combo boxes in access 2007 database programming

Posted 02 February 2013 - 07:02 AM

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

Was This Post Helpful? 1
  • +
  • -

#5 azadehv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-February 13

Re: problem using related combo boxes in access 2007 database programming

Posted 02 February 2013 - 07:03 AM

View Postandrewsw, 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
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3822
  • View blog
  • Posts: 13,544
  • Joined: 12-December 12

Re: problem using related combo boxes in access 2007 database programming

Posted 02 February 2013 - 07:04 AM

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

Was This Post Helpful? 1
  • +
  • -

#7 azadehv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-February 13

Re: problem using related combo boxes in access 2007 database programming

Posted 02 February 2013 - 07:15 AM

View Postandrewsw, 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?
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3822
  • View blog
  • Posts: 13,544
  • Joined: 12-December 12

Re: problem using related combo boxes in access 2007 database programming

Posted 02 February 2013 - 07:29 AM

View Postazadehv, on 02 February 2013 - 07:15 AM, said:

View Postandrewsw, 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.
Was This Post Helpful? 1
  • +
  • -

#9 azadehv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-February 13

Re: problem using related combo boxes in access 2007 database programming

Posted 02 February 2013 - 09:51 AM

View Postandrewsw, on 02 February 2013 - 07:29 AM, said:

View Postazadehv, on 02 February 2013 - 07:15 AM, said:

View Postandrewsw, 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!
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3822
  • View blog
  • Posts: 13,544
  • Joined: 12-December 12

Re: problem using related combo boxes in access 2007 database programming

Posted 02 February 2013 - 10:26 AM

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

Was This Post Helpful? 1
  • +
  • -

#11 azadehv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-February 13

Re: problem using related combo boxes in access 2007 database programming

Posted 02 February 2013 - 11:23 AM

View Postandrewsw, 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! :bananaman: the second combo now represents correct data
thanks thanks thanks andrew :)
Was This Post Helpful? 0
  • +
  • -

#12 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3822
  • View blog
  • Posts: 13,544
  • Joined: 12-December 12

Re: problem using related combo boxes in access 2007 database programming

Posted 02 February 2013 - 11:33 AM

You are welcome :)
Was This Post Helpful? 1
  • +
  • -

#13 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: problem using related combo boxes in access 2007 database programming

Posted 03 February 2013 - 05:29 PM

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.

View Postandrewsw, 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

Was This Post Helpful? 1
  • +
  • -

#14 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3822
  • View blog
  • Posts: 13,544
  • Joined: 12-December 12

Re: problem using related combo boxes in access 2007 database programming

Posted 03 February 2013 - 07:21 PM

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

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

produces 'StTrinians '
Was This Post Helpful? 1
  • +
  • -

#15 azadehv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-February 13

Re: problem using related combo boxes in access 2007 database programming

Posted 04 February 2013 - 06:52 AM

View PostBobRodes, 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.

View Postandrewsw, 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 :)
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2