How to set Row Source in Combo Box in VBA based on a variable?

[Richard Hollenbeck]

I want to have a second combo box have its selection options based on a
selection made in a first combo box. The first one gathers data from a
table, and the second one should gather data from a related table depending
on the selections of the first. Using the wizard will give me a specific
field but will not have a WHERE clause. Is there some code to indicate that
the row source is built a variable in the sub set by the first combo box?

[Gonty]

> I want to have a second combo box have its selection options based on a

selection made in a first combo box. The first one gathers data from a
table, and the second one should gather data from a related table depending on the selections of the first. Using the wizard will give me a specific
field but will not have a WHERE clause. Is there some code to indicate that the row source is built a variable in the sub set by the first combo box?

Dim varValue

varValue=TextBo x1.Value or varValue=TextBo x1.Column(0)

TextBox2.RowSou rce="SELECT * FROM varTABLE WHERE v = " & varValue

--

Bye

Dariusz My¶liwiec

----------------------------------------------------------------------
E-mail: da************* **@darekmysliwi ec.biz
WWW: http://www.darekmysliwiec.biz/
-----------------------------------------------------------------------

[Richard Hollenbeck]

Here's what I have so far:
Private Sub cboSelectGroup_ Change()

Dim groupSelection as Integer

Me("cboSelectCo urse").Enabled = False
' cboSelectCourse , used in this With statement, is the first name of the
first combo box

With Me("cboSelectGr oup")
.RowSource = "SELECT Groups.groupID, Groups.groupDes cription,
Groups.courseCo de " _
& "FROM Groups " _
& "WHERE (Groups.courseC ode = ' & me!cboSelectCou rse & ') " _
& "ORDER BY groupID"

.RowSourceType = "Table/Query"
.BoundColumn = 1
.ColumnCount = 3
.ColumnWidths = "0in;.05in;.05i n"
.ListRows = 8
End With

groupSelection = me("cboSelectCo urse")

End Sub

But the box still comes up blank.

"Gonty" <bl******@poczt a.onet.pl> wrote in message
news:cd******** *@news.onet.pl. ..

I want to have a second combo box have its selection options based on a
selection made in a first combo box. The first one gathers data from a
table, and the second one should gather data from a related table depending

on the selections of the first. Using the wizard will give me a specific field but will not have a WHERE clause. Is there some code to indicate

that

the row source is built a variable in the sub set by the first combo

box?
Dim varValue

varValue=TextBo x1.Value or varValue=TextBo x1.Column(0)

TextBox2.RowSou rce="SELECT * FROM varTABLE WHERE v = " & varValue

--

Bye

Dariusz My¶liwiec

----------------------------------------------------------------------
E-mail: da************* **@darekmysliwi ec.biz
WWW: http://www.darekmysliwiec.biz/
-----------------------------------------------------------------------

[Larry Linson]

No wonder. What you intended to refer to a control is actually imbedded in
the text string, so the value isn't being substituted. It's a good idea to
check such strings that you build by using Debug.Print or a MsgBox to
validate that you got what you intended.

Replace:

& "WHERE (Groups.courseC ode = ' & me!cboSelectCou rse & ') " _

with

& "WHERE (Groups.courseC ode = '" & me!cboSelectCou rse & "') " _

and then verify the generated string before executing it, so you aren't
trying to work in the dark.

Larry Linson
Microsoft Access MVP
"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in message
news:ti******** ***********@nwr ddc02.gnilink.n et...

Here's what I have so far:
Private Sub cboSelectGroup_ Change()

Dim groupSelection as Integer

Me("cboSelectCo urse").Enabled = False
' cboSelectCourse , used in this With statement, is the first name of the first combo box

With Me("cboSelectGr oup")
.RowSource = "SELECT Groups.groupID, Groups.groupDes cription,
Groups.courseCo de " _
& "FROM Groups " _
& "WHERE (Groups.courseC ode = ' & me!cboSelectCou rse & ') " _
& "ORDER BY groupID"

.RowSourceType = "Table/Query"
.BoundColumn = 1
.ColumnCount = 3
.ColumnWidths = "0in;.05in;.05i n"
.ListRows = 8
End With

groupSelection = me("cboSelectCo urse")

End Sub

But the box still comes up blank.

"Gonty" <bl******@poczt a.onet.pl> wrote in message
news:cd******** *@news.onet.pl. ..

I want to have a second combo box have its selection options based on a selection made in a first combo box. The first one gathers data from a table, and the second one should gather data from a related table

depending

on the selections of the first. Using the wizard will give me a specific field but will not have a WHERE clause. Is there some code to

indicate that

the row source is built a variable in the sub set by the first combo

box?

Dim varValue

varValue=TextBo x1.Value or varValue=TextBo x1.Column(0)

TextBox2.RowSou rce="SELECT * FROM varTABLE WHERE v = " & varValue

--

Bye

Dariusz My¶liwiec

----------------------------------------------------------------------
E-mail: da************* **@darekmysliwi ec.biz
WWW: http://www.darekmysliwiec.biz/
-----------------------------------------------------------------------