Populate a combo box

How do you populate a combo box with results from a SQL query that is saved in a recordset?
Eg.
Set rstResult = db.OpenRecordset(strSQL)
' Loop the Recorset and fill the combo box
Do While Not rstResult.EOF
' what goes here?
rstResult.MoveNext
Loop
rstResult.Close

Thanks
Dilani

When populating a combo box in this manner you have to
build the rowSource as a string value with the RowSource
with the RowSourceType = 'Value List'.
For a single column dropdown list, the code would be along
the lines of

Set rstResult = db.OpenRecordset(strSQL)
' Loop the Recorset and fill the combo box
strRowSource = ""
Do While Not rstResult.EOF
strRowSource = strRowSource & rstResult!column & ";"
rstResult.MoveNext
Loop
rstResult.Close
comboBox.RowSource = Left(strRowSource,
Len(strRowSource)-1)

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
How do you populate a combo box with results from a SQL
query that is saved in a recordset?
Eg.
Set rstResult = db.OpenRecordset(strSQL)
' Loop the Recorset and fill the combo box
Do While Not rstResult.EOF
' what goes here?
rstResult.MoveNext
Loop
rstResult.Close

Thanks
Dilani
.

Thanks for the help.

Is there any other way of doing it? Because when I have a long result in the recordset, I get a run time error 2176. It says the Characters for this property is too long needs to be 255 or 2048.

Any other suggestions?

Thanks
Dilani


----- Gerald Stanley wrote: -----

When populating a combo box in this manner you have to
build the rowSource as a string value with the RowSource
with the RowSourceType = 'Value List'.
For a single column dropdown list, the code would be along
the lines of

Set rstResult = db.OpenRecordset(strSQL)
' Loop the Recorset and fill the combo box
strRowSource = ""
Do While Not rstResult.EOF
strRowSource = strRowSource & rstResult!column & ";"
rstResult.MoveNext
Loop
rstResult.Close
comboBox.RowSource = Left(strRowSource,
Len(strRowSource)-1)

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
How do you populate a combo box with results from a SQL
query that is saved in a recordset?
Eg.
Set rstResult = db.OpenRecordset(strSQL)
' Loop the Recorset and fill the combo box
Do While Not rstResult.EOF
' what goes here?
rstResult.MoveNext
Loop
rstResult.Close
Thanks
Dilani
.

Sure. Simply set the Combo Box' Row Source to the SQL
statement, e.g.,

SELECT YourTable.Field1, YourTable.Field2 FROM YourTable
ORDER BY YourTable.Field1;

HTH
Kevin Sprinkel

-----Original Message-----
How do you populate a combo box with results from a SQL
query that is saved in a recordset?
Eg.
Set rstResult = db.OpenRecordset(strSQL)
' Loop the Recorset and fill the combo box
Do While Not rstResult.EOF
' what goes here?
rstResult.MoveNext
Loop
rstResult.Close

Thanks
Dilani
.

Not really. There is no AddItem method with the combo box
in Access. The 'normal' mode is to use a SQL statement as
the RowSource. The Value List alternative is usually for
short lists only.

Gerald Stanley MCSD
-----Original Message-----
Thanks for the help.

Is there any other way of doing it? Because when I have a
long result in the recordset, I get a run time error 2176.
It says the Characters for this property is too long needs
to be 255 or 2048.

Any other suggestions?

Thanks
Dilani


----- Gerald Stanley wrote: -----

When populating a combo box in this manner you have to
build the rowSource as a string value with the RowSource
with the RowSourceType = 'Value List'.
For a single column dropdown list, the code would be
along
the lines of

Set rstResult = db.OpenRecordset(strSQL)
' Loop the Recorset and fill the combo box
strRowSource = ""
Do While Not rstResult.EOF
strRowSource = strRowSource &
rstResult!column & ";"
rstResult.MoveNext
Loop
rstResult.Close
comboBox.RowSource = Left(strRowSource,
Len(strRowSource)-1)

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
How do you populate a combo box with results from a SQL
query that is saved in a recordset?
Eg.
Set rstResult = db.OpenRecordset(strSQL)
' Loop the Recorset and fill the combo box
Do While Not rstResult.EOF
' what goes here?
rstResult.MoveNext
Loop
rstResult.Close
Thanks
Dilani
.

.