Populating a combo box from SQL

[James Rasmussen]

I am trying to do this...

myCn.ConnectionString = "Provider=sqloledb;data source=server;initial
catalog=table;user id=user;password=password"
myCn.Open

sqlstr = "Select number from departments"

rs.Open sqlstr, myCn, adOpenDynamic
Me.Location_2.RowSource = rs
myCn.Close

All I get is a type mismatch from 'Me.Location_2.RowSource = rs'. What am I
missing here?

TIA
Jim

 

[Dan Artuso]

Hi,
A combo box expects a SQL statement as it's RowSource, not a recordset.
So try:
Me.Location_2.RowSource = sqlstr

 

[James Rasmussen]

Hi Dan,
Thanks, but when I do that I get Select number from departments(the value of
sqlstr) as the value. Any ideas as to what is missing now?

TIA
Jim

 

[Douglas J. Steele]

Actually, Access 2002 (and may 2000) allows you to set the recordset of the
form directly.

Try:

myCn.ConnectionString = "Provider=sqloledb;data source=server;initial
catalog=table;user id=user;password=password"
myCn.Open

sqlstr = "Select number from departments"

rs.Open sqlstr, myCn, adOpenDynamic
Set Me.Location_2.RecordSet = rs
myCn.Close

 

[Dan Artuso]

Hi Jim,
Not sure. I just tried this code with a combo box and it populates correctly:

Me.Combo12.RowSource = "Select ArticleId From Articles"

 

[Dan Artuso]

Hi Doug,
I knew that you could do that with a form, (honest <g>),
are you saying we can use a recordset for a combo as well?

 

[James Rasmussen]

I am using Access 2000 and it errors out with an invalid use of property.

Jim

 

[James Rasmussen]

I am using Access 2000. In case it matters.

TIA
Jim
And your code did'nt work

 

[James Rasmussen]

Yes it is. Should it be something else?
Thanks
Jim

 

[Douglas J. Steele]

Oops. Sorry. I'll just slink back to my remedial reading course now...

 

[Dan Artuso]

--
HTH
Dan Artuso, Access MVP

Oops. Sorry. I'll just slink back to my remedial reading course now...