?
Solved

Linking database in comboboxes

Posted on 2003-03-05
11
Medium Priority
?
216 Views
Last Modified: 2010-05-01
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
0
Comment
Question by:sexy-sx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 

Expert Comment

by:sellison
ID: 8076014
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
 

Expert Comment

by:darrenowen
ID: 8076045
Are you linking the combo boxes to a database? If so show me the table layouts and I can show you the code.
0
 
LVL 11

Expert Comment

by:supunr
ID: 8076235
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sexy-sx
ID: 8076245
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
 
LVL 11

Expert Comment

by:supunr
ID: 8076269
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
 
LVL 11

Expert Comment

by:supunr
ID: 8076272
Ops...I forgoto to add the line

   rsCountryData.MoveNext
Loop
0
 

Author Comment

by:sexy-sx
ID: 8077379
what is rsCountryData??
0
 
LVL 1

Expert Comment

by:Computer101
ID: 8133927
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
 

Expert Comment

by:girish_its
ID: 8149824
hi,

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

bye.
0
 

Author Comment

by:sexy-sx
ID: 8155668
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
 
LVL 6

Accepted Solution

by:
Mindphaser earned 0 total points
ID: 8210660
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month9 days, 6 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question