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=TextBox1.Value or varValue=TextBox1.Column(0)

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

--

Bye

Dariusz My¶liwiec

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

[Richard Hollenbeck]

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

Dim groupSelection as Integer

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

With Me("cboSelectGroup")
.RowSource = "SELECT Groups.groupID, Groups.groupDescription,
Groups.courseCode " _
& "FROM Groups " _
& "WHERE (Groups.courseCode = ' & me!cboSelectCourse & ') " _
& "ORDER BY groupID"

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

groupSelection = me("cboSelectCourse")

End Sub

But the box still comes up blank.

"Gonty" <bl******@poczta.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=TextBox1.Value or varValue=TextBox1.Column(0)

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

--

Bye

Dariusz My¶liwiec

----------------------------------------------------------------------
E-mail: da***************@darekmysliwiec.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.courseCode = ' & me!cboSelectCourse & ') " _

with

& "WHERE (Groups.courseCode = '" & me!cboSelectCourse & "') " _

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.net> wrote in message
news:ti*******************@nwrddc02.gnilink.net...

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

Dim groupSelection as Integer

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

With Me("cboSelectGroup")
.RowSource = "SELECT Groups.groupID, Groups.groupDescription,
Groups.courseCode " _
& "FROM Groups " _
& "WHERE (Groups.courseCode = ' & me!cboSelectCourse & ') " _
& "ORDER BY groupID"

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

groupSelection = me("cboSelectCourse")

End Sub

But the box still comes up blank.

"Gonty" <bl******@poczta.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=TextBox1.Value or varValue=TextBox1.Column(0)

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

--

Bye

Dariusz My¶liwiec

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