Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Linking database in comboboxes

Posted on 2003-03-05
11
Medium Priority
?
218 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
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
Technology Partners: 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month14 days, 23 hours left to enroll

578 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