By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,997 Members | 1,657 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,997 IT Pros & Developers. It's quick & easy.

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

P: n/a
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?
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
> 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/
-----------------------------------------------------------------------
Nov 13 '05 #2

P: n/a
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/
-----------------------------------------------------------------------

Nov 13 '05 #3

P: n/a
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/
-----------------------------------------------------------------------


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.