populating a combobox using vba

[ArielZusya]

I've got a form that has a two combo boxes (cmbType and cmbStage). I want to
populate cmbStage based on the value selected in cmbType. cmbType points to
the field tblMain.Type which is a lookup to tblType.ID_Type and tblType.Type.
There are four types in tblType. I figure I need code in my onload for the
form and in my afterupdate for cmbType. I tried the following code in each:

Me.cmbStage.RowSourceType = "Value List"

If Me.cmbType.Value = 1 Then
Me.cmbStage.RowSource = "Pre;Group"
ElseIf Me.cmbType.Value = 2 Then
Me.cmbStage.RowSource = "Pre;Individual;Group"
ElseIf Me.cmbType.Value = 3 Then
Me.cmbStage.RowSource = "Pre;Individual;Group"
ElseIf Me.cmbType.Value = 4 Then
Me.cmbStage.RowSource = "Pre;Group"
End If
Me.cmbStage.ControlSource = "Stage"

Instead of providing me either

Pre
Group

or

Pre
Individual
Group

in cmbStage I get:

[tblStage].[Stage] FROM tblStage ORDER BY [ID_Stage]

and when selected I get an error about the value not being valid for the
field. The reason the value wouldn't be valid is that the value isn't a
number and the field (a lookup) is a number. I then commented out the

Me.cmbStage.ControlSource = "Stage"

line and now the combo box only shows me an unintended list: if my list
contained two values, it only shows me the second value; if it contained
three values, it only shows me the second value and then a blank value after
that. What am I doing wrong? Ultimately I'd like to be able to store the
result in tblStage.Stage which means I need to have a number with my value
and I'd like the number to be 1 for pre, 2 for Individual, and 3 for group.
Your help is greatly appreciated. Thanks!

 

[ArielZusya]

Guess I spoke too soon... I'm just having an off day... so it occured to me
that I'm making this far too complex... I just did this as a query and
everything worked perfectly. confidence issues I guess. In case anyone else
reads this and wants to know how I ended up doing it:

Dim stSQLStringer As String
Dim stSQLStarter As String
Dim stSQLMiddle As String
Dim stSQLEnder As String

stSQLStarter = "SELECT tblStage.ID_Stage, tblStage.Stage FROM tblStage "
stSQLMiddle = "WHERE (((tblStage.ID_Stage)=1 Or (tblStage.ID_Stage)=3)) "
stSQLEnder = "ORDER BY tblStage.ID_Stage;"

If Me.cmbType.Value = 1 Then
stSQLStringer = stSQLStarter & stSQLMiddle & stSQLEnder
ElseIf Forms!frmDescriber!cmbType.Value = 2 Then
stSQLStringer = stSQLStarter & stSQLEnder
ElseIf Forms!frmDescriber!cmbType.Value = 3 Then
stSQLStringer = stSQLStarter & stSQLEnder
ElseIf Forms!frmDescriber!cmbType.Value = 4 Then
stSQLStringer = stSQLStarter & stSQLMiddle & stSQLEnder
End If

Me.cmbStage.RowSource = stSQLStringer

worked like a charm.

 

[Marshall Barton]

Guess I spoke too soon... I'm just having an off day... so it occured to me
that I'm making this far too complex... I just did this as a query and
everything worked perfectly. confidence issues I guess. In case anyone else
reads this and wants to know how I ended up doing it:

Dim stSQLStringer As String
Dim stSQLStarter As String
Dim stSQLMiddle As String
Dim stSQLEnder As String

stSQLStarter = "SELECT tblStage.ID_Stage, tblStage.Stage FROM tblStage "
stSQLMiddle = "WHERE (((tblStage.ID_Stage)=1 Or (tblStage.ID_Stage)=3)) "
stSQLEnder = "ORDER BY tblStage.ID_Stage;"

If Me.cmbType.Value = 1 Then
stSQLStringer = stSQLStarter & stSQLMiddle & stSQLEnder
ElseIf Forms!frmDescriber!cmbType.Value = 2 Then
stSQLStringer = stSQLStarter & stSQLEnder
ElseIf Forms!frmDescriber!cmbType.Value = 3 Then
stSQLStringer = stSQLStarter & stSQLEnder
ElseIf Forms!frmDescriber!cmbType.Value = 4 Then
stSQLStringer = stSQLStarter & stSQLMiddle & stSQLEnder
End If

Me.cmbStage.RowSource = stSQLStringer

Definitely better than using a value list.

You can simplify the code a little by replacing the If logic
with:

Select Case Me.cmbType.Value
Case 1, 3
stSQLStringer = stSQLStarter & stSQLMiddle & stSQLEnder
Case 2, 4
stSQLStringer = stSQLStarter & stSQLEnder
End Select

 

[ArielZusya]

Ooh... I like that. I'm going to do that. Thanks for your suggestion. I am
constantly amazed at just how much I've learned by asking questions in this
newsgroup. Thanks!

 

[sara]

I am pretty much trying to achieve what you did. I used the tips you gave and
now i am not getting any errors( which is a big step forward ). In my case
i am using the selected value of a list in the where clause of the SQL
statement used to populate the combobox. I am pasting the code below (which
is pretty much a cut and paste of your code)

Public Sub List1_Click()

Dim ID As Integer
Dim stSQLStringer As String
Dim stSQLStarter As String
Dim stSQLMiddle As String
Dim stSQLEnder As String

ID = List1.Column(0) ' This works fine to give me the correct ID

stSQLStarter = "SELECT NewProcessPortfolio.Megaprocess,
NewProcessPortfolio.ParentProcessNumber, NewProcessPortfolio.ProcessNumber,
IndustryToolToNewProcessJunction.Weight FROM NewProcessPortfolio INNER JOIN
IndustryToolToNewProcessJunction ON
NewProcessPortfolio.ProcessID=IndustryToolToNewProcessJunction.ProcessID"
stSQLMiddle = "WHERE IndustryToolToNewProcessJunction.AppID=ID"
stSQLEnder = "ORDER BY NewProcessPortfolio.Megaprocess"
stSQLStringer = stSQLStarter & stSQLMiddle & stSQLEnder
Combo25.RowSourceType = "Table/Query"
Combo25.RowSource = stSQLStringer

End Sub