Populating combo box with DB entries

[caitriona]

Hi. Can anyone tell me whats wrong with this 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

[Briantcva]

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?

[caitriona]

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' !

[Briantcva]

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:

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

[caitriona]

Sorted! Thanks a lot,
Triona