Select cbo fill cbo

[greysquirl]

On my MDI I have two combo boxes, department and supervisor. I need code some code help. . . .

In Access I have two tables, [Department] & [Supervisor]. In VB want to be able to select a department and then have the second cbo reflect the supervisors for that department. How can I make it do that? Please be specific. Thank you.

[JonnyCab]

Are you at the stage where your combo boxes are filled with data from the linked tables?

How are you filling the combo boxes?

Are you using ADO or DAO?

[greysquirl]

The cbo's are currently being filled and are working properly, this is just something I wanted to add in.

Both boxes are retrieving the info they need from their own seperate tables in Access.

I'm using Adodc to make the boxes look at the tables.

[JonnyCab]

Sweet... in that case... this is how I do it.

' This is the on-click event for the combobox.
Private Sub cmbRCode_Click(Area As Integer)

If Area = 2 And cmbRCode.Text <> "" Then
With DataEnvironment1
.rscmdSCode.MoveFirst
.rscmdSCode.Find ("SortCode = '" & SQL("SortCode", "tblResource", "tblResource.ResourceCode", cmbRCode.Text) & "'")
Me.cmbSort.Text = .rscmdSCode!SortCode
End With
End If


The find method used above calls on a Function 'SQL' to get a value.

Public Function SQL(strField As String, strTable As String, strTest As String, strObject As String) As String
' ----------------------------------------------------------
' The 'SQL' Function is used to return a named fields contents from a record that
' ...matches the search criteria provided.
' Written : Jonny Wilson
' Date : May 28th 2000
' ----------------------------------------------------------

Dim strSQL As String
Dim rstTemp As ADODB.Recordset
Dim fld As ADODB.Field

' SQL field by variables sent from sub.
strSQL = "SELECT " & strField _
& " FROM " & strTable _
& " WHERE " & strTest & " = '" & strObject & "';"
' Identifies which ADO connection is being used for SQL.
Set rstTemp = DataEnvironment1.Connection1.Execute(strSQL, , adCmdText)

'fld = strField
For Each fld In rstTemp.Fields
SQL = fld.Value
Next

rstTemp.Close
Set rstTemp = Nothing