I am trying to set the selections in a combo box to the fields in an ADO database. Have tried many different types of coding it, but this is the closest I got. Unfortunetly when the combo box is pulled down, it has nothing but blanks.

Below is the code I am currently using. I specificly want to display all the "Status" fields in the "Selection_Fields" table of the "TS99_v101" database in "cboStatus" combobox.


''''''''''''''''''''''''''''''''''''''''
Dim DBconnectionSF As New ADODB.Connection
Dim StatusSFrset As New ADODB.Recordset

Private Sub Form_Load()DBconnectionSF.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
      & "Data Source=" & App.Path & "\TS99_v101.mdb;Jet _
      OLEDB:Database Password=work"
StatusSFrset.Open "SELECT Status FROM [Selection_Fields]"_
       , DBconnectionSF, adOpenDynamic, adLockOptimistic

With StatusSFrset
    Do While Not .EOF
        cboStatus.AddItem (Status)
        .MoveNext
    Loop
End With
cboStatus.Refresh

End Sub

Thanks for any help!!

Hi

I'm going to correct your code for you persuming you use a normal combobox:

Dim DBconnectionSF As ADODB.Connection
Dim StatusSFrset As ADODB.Recordset
dim strQ As String

Private Sub Form_Load()

DBconnectionSF.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
      & "Data Source=" & App.Path & "\TS99_v101.mdb;Jet _
      OLEDB:Database Password=work"
strQ = "SELECT DISTINCT([Status]) FROM [Selection_Fields] ORDER BY Status ASC"

StatusSFrset.Open strq,DBconnectionSF, adOpenForwardOnly, adLockReadOnly

While StatusSFrset.EOF = False
 with cbostatus
   .AddItem trim$(rs!Status)
  end with
  StatusSFrset.MoveNext
wend

StatusSFrset.Close: Set StatusSFrset = nothing
DBconnectionSF.Close: Set DBconnectionSF = nothing

End Sub

Shout if you need more help

Helga Anagnostopoulos

Thanks Helga,
I copied your code exactly and am coming up with the error when the form loads:

Run-time error '424'
Object required

The debug highlights the following line:

.AddItem Trim$(rs!Status)

Sorry, but I really don't understand what the Trim$ of this line does. Any help would be greatly appreciated. Since I am a newbie with VB6, don't want to asume changing your code without knowing why it should work.

Also, is it required to use the strQ line instead of consolidating it like in my original code? Don't mind doing it, again just wanted to know how it works.

Thanks,
Tom

I have been messing with the code a little more and noticed something. With my original code, the combo actually had a blank line for each line in the database field. It seems that the info is getting to the recordset, but the combo box is not displaying the actual text as it goes through each field.

To test this, I ran the following code:

Private Sub Form_Load()
cboStatus.AddItem ("temp 1")

DBconnectionSF.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\TS99_v101.mdb;Jet _
OLEDB:Database Password=work"
StatusSFrset.Open "SELECT ([Status]) FROM [Selection_Fields] _
ORDER BY Status ASC", DBconnectionSF, adOpenForwardOnly, _
adLockReadOnly
With StatusSFrset
    Do While Not .EOF
        cboStatus.AddItem (Status)
        .MoveNext
    Loop
End With
DBconnectionSF.Close

cboStatus.AddItem ("temp 2")
End Sub

The result of the combobox pulldown was as follows:

--------------
temp 1






temp 2
--------------

Without the code refering to the database, it looks as follows:

--------------
temp 1
temp 2
--------------

Hope this makes sense!
Tom