Populating combo box with DB entries

Hi. Can anyone tell me whats wrong with this code??

VB Code:

Private Sub Form_Activate()
  Dim stSQL As String
  
    stSQL = "SELECT DISTINCT [Focus_Area_of_System] FROM [List_of_Current_Practices_SQE]"
  With Adodc1
      .CommandType = adCmdText
      .RecordSource = stSQL
      .Refresh
      
     Do Until .Recordset.EOF
           .Recordset.MoveNext
           If .Recordset![Focus_Area_of_System] <> "" Then
              cboFocus_Area_of_System.AddItem Adodc1.Recordset![Focus_Area_of_System]
          End If
          
          Loop
  End With
  
  End Sub

It keeps deleting entries from my DB and doesn't populate the combo box at all. I was using DAO with this code before and it worked ok but im changing to ADO and now i'm fetting errors everywhere. Any ideas?
Cheers, Triona :p

Nothing jumped out at me. I assume you get the right resultset when you post the SQL statement in Access? FWIW, I'd ditch the bound controls - to include the ADO data control. I used to use them and found too many squirrely issues like yours. Just use the ADO objects and regular VB controls.

How is Galway this time of year?

Thanks for that, no result as yet though!
the SQL is workin fine in Access, i'm not well up on ADO so i reckon my mistake is there.....


Galway is rockin' ! :p

Could also be (b/c I don't remember exactly how Access handles nulls) that no entry <> evaluate to "". Blank is typically different than NULL. Try testing for NULL.

You're already using ADO - mostly.

Add a ref to MS ADO Data Objects 2.x Library (should already be there though due to the data control)

Then add the following:

VB Code:

Private Sub Form_Activate()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql As String
    Set cn = New ADODB.Connection
    With cn
        .Provider = 'paste the provider portion of the connection string 
    'from your ado control as a string ex: "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = 'paste the rest of the connection string
        .Open
    End With
    Set rs = New ADODB.Recordset
    sql = "SELECT DISTINCT [Focus_Area_of_System] FROM [List_of_Current_Practices_SQE]"
    rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly
    Do Until rs.EOF
        cboFocus_Area_of_System.AddItem rs!Focus_Area_of_System
        rs.MoveNext
    Loop
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

Sorted! Thanks a lot,
Triona :p