Linking database in comboboxes

Hi,

I have 2 ComboBoxes and i want the changes from 1 combobox to affect the other.

E.g.
Combo 1 has country = australia, new zealand and singapore
Combo 2 has store number = 1 - 99

Stores 001, 002, 003, 004, 055 ect... are in Australia
Stores 064, 067, 071 are in New Zealand
Stores 065, 078, 097 are in Singapore

When the user choses Australia, combo2 will load only the store numbers associated with that country.

I only have 25 points, so when i get more point i will transfer it to you.

Thanks,
Bernie
sexy-sxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sellisonCommented:
Create an integer array for each country and assign the appropriate store numbers to each one.

In the ComboBox1_SelectedIndexChanged event handler, set the data source for combobox2 to the correct array based on the selection made.

Example:

Dim arrayAustalia() As Integer = {1, 2, 3, 4, 5}
Dim arrayNewZealand() As Integer = {6, 7, 8, 9, 10}
Dim arraySingapore() as Integer = {11, 12, 13, 14, 15}


Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)        

If( ComboBox1.SelectedText = "Australia") then ComboBox2.DataSource = arrayAustalia

If( ComboBox1.SelectedText = "New Zealand") then ComboBox2.DataSource = arrayNewZealand

If( ComboBox1.SelectedText = "Singapore") then ComboBox2.DataSource = arraySingapore

End Sub
0
darrenowenCommented:
Are you linking the combo boxes to a database? If so show me the table layouts and I can show you the code.
0
supunrCommented:
Try the code below...


Private CountryData(3) as String

Private Sub Combo1_Click()
     if (Combo1.Text = "Australia") then
          UpdateCombo2(0);
     elseif (Combo1.Text = "Nez Zealand") then
          UpdateCombo2(1);
     elseif (Combo1.Text = "Singapore") then
          UpdateCombo2(2);
     End If
End Sub

Private Sub UpdateCombo2(CountryID as long)
     Dim ItemsCount as long
     
     ' First item is the number of items in the list
     ItemsCount = Val(Split(CountryData(CountryID),"|")(0))
     ' Break each item by "|" and add to the list
     For i = 1 to itemsCount
          Combo2.AddItem Trim(Split(CountryData(CountryID),"|")(i))
     Next i
End Sub

Private Sub Form_Load()
     ' Initialize countrydata array.
     ' First item is number of items in the list
     CountryData(0) = "5|001|002|003|004|055" ' 5 items
     CountryData(1) = "3|064|067|071"     ' 3 items
     CountryData(2) = "3|065|078|097"     ' 3 items
End Sub

Good Luck!!!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sexy-sxAuthor Commented:
Yep the comboboxes are linked to a database.

the tables goes as follows:

StoreNumber(*)  Country       StoreName
001             Australia     Malvern
002             Australia     Geelong
064             New Zealand   Wairau Park
078             Singapore     Tampines

* Primary Key

So when the person selects Australia in the country combobox, the storenumber combobox will only display australian related store numbers.
And when a store number is selected, then a textbox will diplay the according store name.

Hope that made sense.
0
supunrCommented:
Try the code below...


Private Sub Combo1_Click()
     Private CountryName as string

     CountryName = Combo1.Text

     Combo2.Clear
     if (rsCountryData.RecordCount > 0)
          rsCountryData.MoveFirst     ' goto first record
     
          do while (not rsCountryData.EOF)
               if ("" & rsCountryData.Fields("Country").Value = CountryName) then
                    ' Add the store number to the list
                    Combo2.AddItem rsCountryData.Fields("StoreNumber").Value
               end if
          loop          
     End if
End Sub

two points...
1: get combo1.text to CountryName to reduce the overhead of reading text property of combo1.

2. "" & rsCountryData.Fields("Country").Value  => because if country value is NULL then VB fail trying to compare Null to a string.  This get rid of that error.

Good Luck!
0
supunrCommented:
Ops...I forgoto to add the line

   rsCountryData.MoveNext
Loop
0
sexy-sxAuthor Commented:
what is rsCountryData??
0
Computer101Commented:
A request for deletion has been made.  If no response or you feel this is in error, comment.  If no objection, I will delete in three days.

Computer101
E-E Admin
0
girish_itsCommented:
hi,

rsCountryData is the recordset of the type of connection u r opening.

bye.
0
sexy-sxAuthor Commented:
i have already sorted it out. i put in a adodc control and i user SQL commands to make them talk.

Private Sub Combo1_Change()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strcon As Connection
    Dim strSQL As String
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim strSQL3 As String
    Dim strSQL4 As String

   

    cn.Open ("Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=D:\stores.mdb")
    strSQL2 = "select storenumber, storename from sheet1 where storenumber = '"
    strSQL3 = "' order by storenumber;"
    strSQL = strSQL2 & Me.txtStoreNumber.Text & strSQL3
    rs.Open strSQL, cn, adOpenDynamic
    rs.MoveFirst
   
    Me.txtStoreName = rs.Fields("storename")
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
   
    text1.Refresh
    text1.SetFocus
End Sub
0
MindphaserCommented:
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.