function that passes the values

[Lieu]

Hi,

Can anyone please help me with creating a function that passes the value.

Two Access tables:

Table1 has two fields, Country and Id. And it only has two records in it, let say under Country we have US and Canada, and under Id we have 1 and 2 accordingly.

Table2 also has two fields, States and Id. and it has 64 records in it, let say under States we have all the name of the states in US and canada, and under id we have 1s and 2s.

now on form load, comb1 gets populated fron table1. What i would like to be able to do is to get cmb2 populate the name of the states that bases on the country that I've pick from cmb1.

In other words is. we need to pass the values of the id of the country that we have pick from cmb1. And use that to populate appropriate states to cmb2.

Thank you in advance

[chrisjk]

If I understand you right, you'd need to add some code to the click event of the first combo (the one with US & Canada in it) tha changes the recordsource of the second combo (the one with all the states) that would be along the lines of

Private Sub DataCombo1_Change()
 
Dim cn As Connection
Dim rs As Recordset
 
If Len(DataCombo1.BoundText) > 0 Then
    Set cn = New Connection
    
    With cn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\db1.mdb;Persist Security Info=False"
        .Open
    End With
 
    Set rs = New Recordset
    rs.Open "SELECT States.StateID, States.State FROM States WHERE States.CountryID=" & DataCombo1.BoundText & " ORDER BY States.State;", cn, adOpenStatic, adLockOptimistic
 
    Set DataCombo2.RowSource = rs
    DataCombo2.BoundColumn = "StateID"
    DataCombo2.ListField = "State"
    DataCombo2.Refresh
End If
 
End Sub
 
Private Sub Form_Load()
 
Dim cn As Connection
Dim rs As Recordset
 
Set cn = New Connection
 
With cn
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\db1.mdb;Persist Security Info=False"
    .Open
End With
 
Set rs = New Recordset
rs.Open "SELECT Countries.CountryID, Countries.Country FROM Countries ORDER BY Countries.Country;", cn, adOpenStatic, adLockOptimistic
 
Set DataCombo1.RowSource = rs
DataCombo1.BoundColumn = "CountryID"
DataCombo1.ListField = "Country"
DataCombo1.Refresh
 
End Sub

This requires to data combo boxes to work called DataCombo1 and DataCombo2. Don't forget to change the connection sources to reflect where your data source is.