Combo box row source (1 Viewer)

shapman

Registered User.
Local time
Today, 13:23
Joined
Aug 30, 2007
Messages
55
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
 

DrSnuggles

Well the hell is Hudson??
Local time
Today, 20:23
Joined
Sep 14, 2004
Messages
117
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??
 

RuralGuy

AWF VIP
Local time
Today, 14:23
Joined
Jul 2, 2005
Messages
13,826
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.
 

Simon_MT

Registered User.
Local time
Today, 20:23
Joined
Feb 26, 2007
Messages
2,177
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

Code:
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
 

RuralGuy

AWF VIP
Local time
Today, 14:23
Joined
Jul 2, 2005
Messages
13,826
I would just change the RowSource of the cboSubstatus cbo to:
Code:
"Select tblparmedchoices " & _
"FROM tblparmedchoices " & _
"WHERE tblparmedchoices.Statustype = Forms.MainFormName.cboStatustype " & _
"ORDER BY tblparmedchoices.Substatus;"
...using your MainFormName of course.
 

Users who are viewing this thread

Top Bottom