linking data between dbcombo boxes

[DKasler]

I have 3 dbcombo boxes on my form. If the user selects something from box one... I want it to sort box2 and have box2 only display info pertaining to the choice made in box 1, then when they choose something from box2 I want it to sort box3 and have box 3 only display info pertaining to the choice made in box 2


DBCombo1 Choice -------> DBCombo2 [Choices related to DBCombo1] -------> DBCombo3 [Choices related to DBCombo2].

I tried to figure it all out on my own but alas, I could not come up with a means of pulling it off.

[dglienna]

I do something like that, where I use the LostFocus event of the first combobox, which loads the second combo, and as soon as something is clicked in the second combo, a third combo is loaded with items that are particular to the first two combos.

You could run a query to load the combos. If they are bound, then you would have to change the record.

[DKasler]

Well i tried running a query... but I couldnt exactly figure out how to get the data from the query to the dbcombo boxs listfield property... That and the row source property MUST be set at design time, not runtime (as far as I know).

Here is the query i was trying to use

Dim sConnect As String
    Dim sSQL As String
    Dim dfwConn As ADODB.Connection
 
    ' set strings
    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\supplies\supplies.mdb;Persist Security Info=False"
    sSQL = "select ProdID,Items,Type,Supplier from Products"
    
    ' open connection
    Set dfwConn = New ADODB.Connection
    dfwConn.Open sConnect
 
    ' create a recordset using the provided collection
    Set datprimaryRS = New ADODB.Recordset
    datprimaryRS.CursorLocation = adUseClient
    datprimaryRS.Open sSQL, dfwConn, adOpenForwardOnly, adLockReadOnly
 
    DBCombo2.RowSource = datprimaryRS

[dglienna]

I found this, and it may help you:

Option Explicit
 
Private Sub cboStudent_Click(Area As Integer)
 Dim Y As Single
 Dim StrQuery1 As String
 
 txtStudNo.Text = cboStudent.BoundText
 
 StrQuery1 = "select teacher_name & ', ' & teacher_first as t_name,teacher_name,teacher_no from teacher inner join stud_teach on teacher.teacher_no = stud_teach.st_teacher_no where stud_teach.st_student_no = " & Val(cboStudent.BoundText)
 datTeacher.RecordSource = StrQuery1
 datTeacher.Refresh
 
End Sub
Private Sub Form_Load()
Dim file_name As String
Dim strQuery As String
 
    file_name = App.Path
    If Right$(file_name, 1) <> "\" Then file_name = file_name & "\"
    file_name = file_name & "Example.mdb"
 
    datTeacher.DatabaseName = file_name
    datStudent.DatabaseName = file_name
 
 strQuery = " select teacher_name & ', ' & teacher_first as t_name,teacher_name,teacher_no from teacher order by teacher_name"
 datTeacher.RecordSource = strQuery
 datTeacher.Refresh
 
End Sub

[DKasler]

yea... I think im going to have to figure something else out, because I have no idea what that code you posted does.

[dglienna]

Did you try out the code? It works, and shows how to use the DE with a combo box.

[DKasler]

whoops. Sorry I didnt see that attachment when I first looked. Thanks DG

[cybersandokan]

Waht about programming this without using bound controls? I heard bound controls are devil, is it true ? can we do this same code without the data control.

Cybersandokan