Hi,

I have been trying to make a combo box dependent on the previous combo box selection. Originally i had tried using separate tables for different selections in the first combo box, but efforts to get this working came to nothing. So I decided to have a single table for all of the dependent row source information.

the tables:
Tblparmedchoices (includes a column for status type and relevant substatus)

cboStatustype (first combo box, which the second's row source is dependant upon)

cboSubstatus (dependent combo box)


heres the code ive been trying:
Private Sub cboStatustype_AfterUpdate()

On Error Resume Next
cboSubstatus.RowSource = "Select tblparmedchoices " & _
"FROM tblparmedchoices " & _
"WHERE tblparmedchoices.Statustype = '" & cboStatustype.Value & "' " & _
"ORDER BY tblparmedchoices.Substatus;"

cboSubstatus.Requery
End Sub


help, this is central to my database working :-s

thanks in advance

In what way is it not working?
Does nothing happen . . error message?

You should put this in the on Click event anyway and capture the error not step over it.

Where's the connection & recordset objects?

Plus, call me crazy, but in the WHERE clause they are the same data types??
I.e You're not trying to reference and index and a description??

Is Statustype a text field? That is how you are referencing it. FYI, there is nothing dynamic about your SQL so you can leave the RowSource of the cboSubstatus ComboBox static and simply do a Me.cboSubstatus.Requery in the Enter event of the cboSubstatus cbo.

I use a Lookup Module so I'm a vague with Subs but

"WHERE tblparmedchoices.Statustype = '" & Me.cboStatustype.Value & "' " & _

Don't you need a "me" with a sub

in a Module you can say


Function cboStatustype_AfterUpdate()
On Error Resume Next

With CodeContextObject

With Screen.ActiveControl
.RowSource = "Select tblparmedchoices " & _
"FROM tblparmedchoices " & _
"WHERE tblparmedchoices.Statustype = '" & .cboStatustype.Value & "' " & _
"ORDER BY tblparmedchoices.Substatus;"

.cboSubstatus.Requery
End With
End Function



Simon

I would just change the RowSource of the cboSubstatus cbo to:
"Select tblparmedchoices " & _
"FROM tblparmedchoices " & _
"WHERE tblparmedchoices.Statustype = Forms.MainFormName.cboStatustype " & _
"ORDER BY tblparmedchoices.Substatus;"...using your MainFormName of course.

I posted an Access example in post #9 here (http://www.access-programmers.co.uk/forums/showthread.php?t=136049&highlight=combo).