Populate a dropdown box using vba

[aman]

Hi guys

I have a frontend built in Access 2010 and backend in Access 2003. Now I am writing the code to populate a drop down box but it doesn't work

Any suggestions will be much appreciated.

Private Sub RequesteeName_Change()
Dim strText, strFind As String
strText = Me.RequesteeName.Text
If Len(Trim(strText)) > 0 Then
                    
strsql = "SELECT Name, Dept FROM tblElevateEmployee where Name like '" & strtext & "*' ORDER BY Name;"
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data source=J:\abs\Pennywell\» Case Trackers\DO NOT USE 2010 test version\2003 Backend Database.mdb;"
Set rs = CreateObject("ADODB.Recordset")
rs.Open strsql, cn, adOpenKeyset, adLockOptimistic
[COLOR=red]RequesteeName.RowSource = rs.fields(0)
[/COLOR]End If
End Sub

 

[jdraw]

A couple of suggestions, not necessarily related to your specific issue:

Use Option Explicit in your vba to force Dimming of variables

Dim strText, strFind As String does NOT Dim these variables as String

You must explicitly Dim variables to assign data type, otherwise the variable is a Variant

Dim strText, strFind As String

is the same as
Dim strText as Variant, strFind as String

 

[Kiwiman]

Howzit

Does this work

DIm strSQL as string

strSQL = "SELECT Name, Dept FROM tblElevateEmployee where Name like '" & strtext & "*' ORDER BY Name;"

With me.yourcombobox
    .rowsource = strsql
    .requery
End With

 

[vbaInet]

By the way, you really have this in your source path?

"Data source=J:\abs\Pennywell\[COLOR=Red]» [/COLOR]Case Trackers\DO NOT USE 2010 test version\2003 Backend Database.mdb;"

 

[aman]

Kiwiman, This code won't work as I am writing the code in Access 2010 that will populate data from Access 2003 database to the dropdown box.

So we have to use recordset and once it is opened then i can't figure out how to populate it.

strsql = "SELECT Name, Dept FROM tblElevateEmployee where Name like '" & strtext & "*' ORDER BY Name;"
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data source=J:\abs\Pennywell\» Case Trackers\DO NOT USE 2010 test version\2003 Backend Database.mdb;"
Set rs = CreateObject("ADODB.Recordset")
rs.Open strsql, cn, adOpenKeyset, adLockOptimistic

I hope anyone can help me out in this.

Thanks

 

[aman]

I am writing the following code. It doesn't give me any error message but doesn't even populate the dropdown box.

Private Sub RequesteeName_Change()
Dim strText, strFind As String
strText = Me.RequesteeName.Text
If Len(Trim(strText)) > 0 Then
                    
strsql = "SELECT Name FROM tblElevateEmployee where Name like '" & strText & "*' ORDER BY Name;"
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data source=J:\abs\Pennywell\» Case Trackers\DO NOT USE 2010 test version\2003 Backend Database.mdb;"
Set rs = CreateObject("ADODB.Recordset")
rs.Open strsql, cn, adOpenKeyset, adLockOptimistic
Set RequesteeName.Recordset = rs
Me.RequesteeName.Dropdown
End If
End Sub

Any help would be much appreciated.

Thanks