How to Set a combo Box's Row Source To A SQL Server Source? (1 Viewer)

deletedT

Guest
Local time
Today, 21:24
Joined
Feb 2, 2019
Messages
1,218
Is it possible at all?

The scenario:
In a Access 2019 FE with no linked table to it's SQL server BE, I want to set the row source of a combo box to a sql server table. Of course without having a linked table.


I know that I can set combo box's Row Source Type to List Value, read the data from table and add the items one by one.
But it's not convenient for me. I need to set the Row Source.

Thanks for any kind of advice.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:24
Joined
Feb 19, 2013
Messages
16,553
how are you reading the data from table? - if into a recordset, just assign the recordset to the combo

set rst=however you get your records
set mycombo.recordset=rst
 

deletedT

Guest
Local time
Today, 21:24
Joined
Feb 2, 2019
Messages
1,218
how are you reading the data from table?

By creating a direct sql to the table:
Code:
    ConnectToDB   ' connection sub
    sql = "SELECT " & TheseFields & " FROM " & myTable & " WHERE " & ThisFilter
       
    Set rs.ActiveConnection = db
    rs.LockType = adLockPessimistic
    rs.Open sql

just assign the recordset to the combo

set mycombo.recordset=rst
As far as I know, combo box doesn't have a Recordset property. It has a Rowsource property and it's a string.
Can you be more specific?

thank you.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:24
Joined
Feb 19, 2013
Messages
16,553
trust me, it has a recordset property, just try it. Not all properties are displayed in the property sheet
 

deletedT

Guest
Local time
Today, 21:24
Joined
Feb 2, 2019
Messages
1,218
trust me, it has a recordset property, just try it. Not all properties are displayed in the property sheet

I receive this error on the last line:


this is the mentioned function:
Code:
Public Function CreateRs(tbl As String, _
                                        Optional fltr As String = "", _
                                        Optional TheseFields As String = "*", _
                                        Optional ThisOrderBy As String = "") As adodb.Recordset
                                        
    Dim sql As String
'    On Error Resume Next
    ConnectToDB
    sql = "SELECT " & TheseFields & " FROM " & tbl & " WHERE " & fltr
    
        
    Set rs.ActiveConnection = db
    rs.LockType = adLockPessimistic
    rs.Open sql
    
    Set CreateRs = rs
        
End Function

If I test the recordset with this :
Code:
Do
        Debug.Print rs!User_ID
        rs.MoveNext
    Loop Until rs.EOF

a list of all users are printed out. So the recordset should be OK.

Is it because the recordset is ADO?
Any further advice?
Thanks again.
 

Attachments

  • 2019-03-15_9-43-27.jpg
    2019-03-15_9-43-27.jpg
    50.4 KB · Views: 560

deletedT

Guest
Local time
Today, 21:24
Joined
Feb 2, 2019
Messages
1,218
I changed my code and created a DAO recordset.
Everything is just fine now.

CJ_London, if you happened to be in Tokyo one day, I owe you a beer.
Just let me know. I'll set up a tour around :)
 

Users who are viewing this thread

Top Bottom