Load combobox from recordset

[pokeymonsc]

I want to load a combobox on my form from a table in another sqlserver database. I can code to open the connection, load the recordset and debug.print it. But I was told in my 'Accesseveloping Enterprise Applications' that I can't use a recordset as a source (to a form, table, query) and my rowsource for my combobox needs a source to fill the combobox during runtime. I tried to use

Me.cbxautofctitle.RowSource = "Select emp_id, first_name + ' ' + last_name from dbo_EmpNames where last_name is not null order by last_name" but the sql can't directly access the table w/o going through a recordset.

I thought of filling a temp table with data from the recordset and have the rowsource query use that table, but I haven't found a way of getting the data out of the recordset into the table. Does anyone have a way of getting the data out of the record set into a temp table OR a better method of doing what I'm trying to do? Or going direct: how do I reference 'Me.cbxautofctitle.RowSource =' directly to the data in the recordset?

Thanks in advance

 

[PHV]

Why not using a linked table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pokeymonsc]

Main reason is: To set up a linked table I would use an ODBC driver, which means that I would have to set manually up the link. Ultimately this ap is going to every desktop in our org and that's a lot of setup time and energy. With an ado connection I can code the connection, but I'm back to using recordsets and how to get the data out of the recordset and into a temp table or to the combobox row source direct.

 

[SJMcAbney]

Also, don't use the table as a recordsource for the combo - use a query of the table. This way you can sort the records as you wish.

Stewart J. McAbney | Talk History

 

[RoyVidar]

Not that I've used it much, but in 2002+ versions, I think you can assign a recordset directly to combos/lists (they have a recordset propertey to utilize, in stead of rowsource). Then use the RowSourceType of "table/query", and just:

[tt]set me("cboMyCombo").recordset = rs[/tt]

'nother thing for 2002+ versions, is that lists and combos have .AddItem/.RemoveItem methods (see help file).

Else you're left with either concatinating the rowsource

[tt]dim strSource as string
do while not rs.eof
strSource = strSource & rs.fields("firstfield").value & ";" & _
rs.fields("secondfield").value & ";" ' ...
rs.movenext
loop
me("cboMyCombo").rowsource = strSource[/tt]

or try the following (works with ADO, probably also DAO)

[tt]me("cboMyCombo").rowsource = rs.getstring(adclipstring,,";",";")[/tt]

For the two latter alternatives, remember the row source type needs to be "ValueList", also on Access 2000, the size limit is 2048 characters.

Roy-Vidar

 

[pokeymonsc]

Thanks, guys, I'll try these solutions in the morning and let you know how it goes. I do appreciate the answers.

 

[pokeymonsc]

Roy:

I tried the first method:

set me.cbxautofctitle.recordset = rstEmp and got the same runtime error as: me.cbxautofctitle.rowsource = rstEmp. e.g. it didn't work.

Then I tried the second with this code:

Dim strSource As String
Do While Not rstEmp.EOF
strSource = strSource & rstEmp.Fields(0).Value & ";" & _
rstEmp.Fields(1).Value & ";" & _
rstEmp.Fields(2).Value & ";" & _
rstEmp.Fields(3).Value & ";" & _
rstEmp.Fields(4).Value & ";"
rstEmp.MoveNext
Loop
Me("cbxautofctitle").RowSource = strSource

and the rowsource did indeed have the data. I'll have to admit that I didn't understand the third method, but a BIG star to you, thanks much.

http://www.tipmaster.com/images/2thumbsup.gif

http://www.tipmaster.com/images/2thumbsup.gif

PS: I can't seem to find where I nominate you for a star.

 

[PHV]

Simply click this link:
Thank RoyVidar
for this valuable post!

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244