populating the combobox

[bsarika]

hi,
does the combobox in vb work the same as in access.
i mean in vb 6.0 i have a combobox ;i want to retrieve all values in a field from database using ado programming into combobox, based on the value i select in the combobox i want the corresponding values in the textbox.

i know how to connect to database using ado programming and retrieve data from databse, but i am unable to populate combobox with data from database only one value is displayed in it.

it's very urgent plz help me

[flukey2005]

hi,
does the combobox in vb work the same as in access.
i mean in vb 6.0 i have a combobox ;i want to retrieve all values in a field from database using ado programming into combobox, based on the value i select in the combobox i want the corresponding values in the textbox.

i know how to connect to database using ado programming and retrieve data from databse, but i am unable to populate combobox with data from database only one value is displayed in it.

it's very urgent plz help me

Once you have connected to your database, i assume an Access database and using the ADODB recordset

Private Sub Form_Load()
Do Until rs.EOF
List1.AddItem rs!FieldName
rs.MoveNext
Loop
End Sub

What this does is goes through each record in the recordset and adds it to the list box.

Hope this helps.

[bsarika]

thanks but it displays only true value in the listbox.


Private Sub Form_Load()
Set con = New Connection
Set rs = New Recordset
Set com = New Command
rs.CursorLocation = adUseServer
rs.LockType = adLockOptimistic
rs.CursorType = adOpenDynamic

With con
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=E:\swetha\finance.mdb;Persist Security Info=False"
.Open

End With
rs.Open "select * from ibs", con

Set Text1.DataSource = rs
Set Text2.DataSource = rs
Set List1.DataSource = rs


Text1.DataField = "No"
Text2.DataField = "Datewriten"
List1.DataField = "description"
rs.MoveFirst
Do Until rs.EOF
List1.AddItem (List1.DataField = "description")

rs.MoveNext
Loop

end sub

[manavo11]

Welcome to the forums.

I moved the thread from the Codebank. The codebank is only for posting code samples, not questions.

[si_the_geek]

Change this section of code:

List1.DataField = "description"
rs.MoveFirst
Do Until rs.EOF
  List1.AddItem (List1.DataField = "description")
  rs.MoveNext
Loop

to what flukey2005 suggested, ie:

Do Until rs.EOF
  List1.AddItem rs!description
  rs.MoveNext
Loop

[D43x]

I'm attempting something virtually identical except wanting to populate a ListBox not a Combo and i keep getting the error 'Invalid use of New Keyword"

Set con = New Connection

Im using vb6 / Access with ListBox / ADODC 6.0
Maybe I'm missing setting something crucial in the Properties? I've never had any success with ADO. Always same error. :-(

[Hack]

I'm attempting something virtually identical except wanting to populate a ListBox not a Combo and i keep getting the error 'Invalid use of New Keyword"

Set con = New Connection

Im using vb6 / Access with ListBox / ADODC 6.0
Maybe I'm missing setting something crucial in the Properties? I've never had any success with ADO. Always same error. :-(

Post all of your connection code.

[D43x]

Private Sub Form_Load()
Set con = New Connection
Set rs = New Recordset
Set com = New Command
rs.CursorLocation = adUseServer
rs.LockType = adLockOptimistic
rs.CursorType = adOpenDynamic
 
With con
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\db1.mdb;Persist Security Info=False"
.Open
 
End With
rs.Open "select * from Table1", con
 
Set Text1.DataSource = rs
Set Text2.DataSource = rs
Set Text3.DataSource = rs
Set List1.DataSource = rs
 
 
Text1.DataField = "Field1"
Text2.DataField = "Field2"
Text2.DataField = "Field3"
Do Until rs.EOF
  List1.AddItem rs!Description
  rs.MoveNext
Loop
End Sub

[Hack]

Ah, I see the problem. Try this connectino code instead. You don't have to use the same connection variables, but the syntax needs to be the same

Dim con As ADODB.Connection
Dim ConnString As String
 
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\db1.mdb;" & _
        "Persist Security Info=False"
 
Set con = New ADODB.Connection
con.ConnectionString = ConnString
con.Open ConnString

You have to declare them before you use them. Using New on a variable that has not yet been declared will result in the error that you received.

[D43x]

Here's how it looks now, but have a new error (below)...

Private Sub Form_Load()
Dim con As ADODB.Connection
Dim ConnString As String
 
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\db1.mdb;" & _
             "Persist Security Info=False"
 
Set con = New ADODB.Connection
con.ConnectionString = ConnString
con.Open ConnString
 
rs.Open "select * from Table1", con      'DEBUG - THE ERROR'S HERE
 
Set Text1.DataSource = rs
Set Text2.DataSource = rs
Set Text3.DataSource = rs
Set List1.DataSource = rs
 
Text1.DataField = "Field1"
Text2.DataField = "Field2"
Text2.DataField = "Field3"
Do Until rs.EOF
  List1.AddItem rs!Description
  rs.MoveNext
Loop
End Sub

Error:
Object required.

PS. Why is there "con" and "conn"? 2 separate variables?

[Hack]

Here's how it looks now, but have a new error (below)...

Private Sub Form_Load()
Dim con As ADODB.Connection
Dim ConnString As String
 
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\db1.mdb;" & _
             "Persist Security Info=False"
 
Set con = New ADODB.Connection
con.ConnectionString = ConnString
con.Open ConnString
 
rs.Open "select * from Table1", con      'DEBUG - THE ERROR'S HERE
 
Set Text1.DataSource = rs
Set Text2.DataSource = rs
Set Text3.DataSource = rs
Set List1.DataSource = rs
 
Text1.DataField = "Field1"
Text2.DataField = "Field2"
Text2.DataField = "Field3"
Do Until rs.EOF
  List1.AddItem rs!Description
  rs.MoveNext
Loop
End Sub

Error:
Object required.

I'm guessing that the object that is being required is rs. I seen no place where rs is being declared.

PS. Why is there "con" and "conn"? 2 separate variables?

Where is conn?

[Radjesh Klauke]

You forgot:
Dim rs As ADODB.RecordSet

[D43x]

Hack - "conn" part of connString var.
Klauke - thanks you solved one error, but now the same line give this error...

"Runtime 91 - Object variable or With block variable not set".

Is the object variable the name of the table inside the db or a field name?

[Radjesh Klauke]

Did you placed the ADODC1 control on the form? I think that is the problem.

[si_the_geek]

Radjesh Klauke is correct that you missed out the declaration of rs, but you also need to set the object (before the .Open line), eg:

Set rs = New ADODB.RecordSet

[D43x]

Could there be something else missing (or in the wrong order) as placing the "set rs." before the ".Open" still gives an error.

I've moved the lines about that often I don't know which error relates to what but there are 2 errors. The first relates to the data source not being found and highlights the following line:

con.Open ConnString

The second error relates to Object variable not being set.

Here's my code as it stands. Can someone place it in the correct order. Thanks.

Private Sub Form_Load()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset 'object
Dim ConnString As String
             
Set Text1.DataSource = rs
Set Text2.DataSource = rs
Set Text3.DataSource = rs
Set List1.DataSource = rs
 
Set con = New ADODB.Connection
con.ConnectionString = ConnString
con.Open ConnString
 
With con
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\db1.mdb;" & _
             "Persist Security Info=False"
 
End With
rs.Open "select * from Table1", con
 
Text1.DataField = "Field1"
Text2.DataField = "Field2"
Text2.DataField = "Field3"
 
Do Until rs.EOF
  List1.AddItem rs!Description
  rs.MoveNext
Loop
End Sub

[Radjesh Klauke]

i think the best solution in this ons is to post a piece of your database and coding in a zip-file. only if you want to ofcourse.

I'm not really sure what you are trying to accomplish and what really is going wrong.

Seems you also forgot to close the rs at the end of the code.

rs.Close
Set rs = Nothing

Using "On Error Goto" is also very important in Database Software developement. You should also consider that.

[Radjesh Klauke]

Here m8. Made you a quick example with comments. You see a Combobox and a Listbox loading data when starting the Form.

Special: ProgressBar while loading the Combobox...

Hope you can do something with it.

I will also make a tutorial with a search-function. Stay tuned.

[si_the_geek]

Ok.. you've completey destroyed the order of pretty much everything!!

You cannot set something to use a recordset ("Set ... = rs") until after the recordset has been opened, which can only be done (as mentioned in my last post) after you have Set it. It also cannot be opened until the connection is opened, which cannot be done until after you have told it where to connect to.

Here's an updated version of your code:

Private Sub Form_Load()
  'declare database object variables
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset 'object
Dim ConnString As String

   'initialise connection
Set con = New ADODB.Connection

   'open the connection
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\db1.mdb;" & _
             "Persist Security Info=False"
con.Open ConnString

  'initialise recordset  (this could be done with 'Set con = ' above if you want)
Set rs = New ADODB.Recordset

  'open recordset (using the connection)
rs.Open "select * from Table1", con

  'use recordset
Set Text1.DataSource = rs
Set Text2.DataSource = rs
Set Text3.DataSource = rs
'Set List1.DataSource = rs  'as you are filling it yourself, why do this?

Text1.DataField = "Field1"
Text2.DataField = "Field2"
Text3.DataField = "Field3"  'I presume this was supposed to be 3 ;)

Do Until rs.EOF
  List1.AddItem rs!Description
  rs.MoveNext
Loop
End Sub

[D43x]

Thanks guys (Radjesh and Si) - I'll let you know how I get on. Looking forward to the tutorial and the Find thing...

"'as you are filling it yourself, why do this?"

...well Si the idea is to have the List box populated with one field from the db, and when the field item is selected I wanted the fields associated with that record to populate the other textboxes. Perhaps I'm approaching this the wrong way, but until I get this first bit working...

Thanks again.

[Radjesh Klauke]

In that case your solution can be done much quicker ans easier. Your approach is not bad by the way. I'll make you an example/tutorial next week. It will be the same, but then with textboxes according the associated fields.

[Radjesh Klauke]

I just couldn't let it... hehehe
Here you go m8. With searchoption for Listbox and Combobox

[afshin_tt]

Thank you for nice program there I did look for the same solution for long time
But what I was looking for is a bit more than this nice program.
You must have seen sage program, if you need to fine a customer you start to type in a combobox, as soon as you start typing, a listbox opens up and shows all the names starting with the first letter you typed in not whole records in you database.
In other word it will sort the records of specified filed by what you enter to the combobox and show it in a listbox. Now I need to know that is it possible to ad this to your program if so, how?
Thank you.

[si_the_geek]

That's known as auto-complete, and has come up several times before.. I think that this thread has good solutions.

[afshin_tt]

Thank you si i got what i wanted

[afshin_tt]

I just couldn't let it... hehehe
Here you go m8. With searchoption for Listbox and Combobox

Hi Radjesh Klauke
There is a problem with your program which I could not go round it.
If you have a duplicated name in your database it only load the first on from database and ignores the rest.
Do you have a solution for it?

[si_the_geek]

Simply remove the word "DISTINCT" from the SQL statement, as it specifies to not show duplicates.

[afshin_tt]

Hi si
Thank you for the suggestion and it loads all the records now but if you chose any of the duplicated records in the list or the combobox the text boxes will only show the records for the first duplicated name in the database.
Any idea?

[Hack]

Hi si
Thank you for the suggestion and it loads all the records now but if you chose any of the duplicated records in the list or the combobox the text boxes will only show the records for the first duplicated name in the database.
Any idea?

Yes, this is correct as this is what it thinks you want it to do, so this makes sense.

If this first name is a duplicate then it will hit on the first instance it finds.

If this is not what you want, then you will have expand your criteria to include something that is unique about the individual record that you are after.

[afshin_tt]

In this case how do I view a table (in a datagrid or any other component that you think it is better to use) and double click on a record and have the text boxes felled with more info about that specific record.

[si_the_geek]

Just like Hack described for a combobox, you need to have a unique field (something that is guaranteed to be different for every row).

If the AccountNumber field is unique you can use that, otherwise you should add another field to the table which is unique - the easiest way to to make it AutoNumber (in Access) or Identity (most other database systems).

[afshin_tt]

Hi si the problem is that some times members forget their account number and the only way to find the member is to use the name "that’s what the members do not forget I hope".
I was wandered if you could help me with post number 30.
thank you.

[si_the_geek]

That doesn't matter I'm afraid - you still need something unique about the record.

You can search by the name, but to select a record to edit you need to have unique info (preferably a single field that is unique) to be able to get the correct row.


It seems to me that the example may not be the best starting position for you - it may be better to use the ADO Tutorial and Further Steps links in my signature, as they are a bit clearer in my opinion.

[afshin_tt]

hi si
I had to use a unique field to find records its now working thanks.

[ZoeWashburn]

Hi guys

I'm trying to do the same thing with three comboboxes on a form in my project. On the first one, the values are pulled from a lookup table that only has one field with the values I need in it. This one works fine. The other two need to pull data from tables with numeric ID fields and text name fields. I have both of these set up identically:

rstIssue.Open [tblACDCallIssue], cnn, adOpenForwardOnly, adLockReadOnly, adCmdTable
With Issue
Do While Not rstIssue.EOF
    rstIssue.MoveFirst
    .AddItem rstIssue.Fields("Issue")
    '.ItemData() = rstLEC.Fields("IssueID")
    rstIssue.MoveNext
Loop
rstIssue.Close
Set rstIssue = Nothing

My problem is, both of these fields either a) insist on populating the ID field instead of the text field, or b) refuse to populate anything at all, depending on what I do with the RowSource properties on the form design.

Any ideas?

[ZoeWashburn]

I got it fixed. yay!!

[Hack]

What did you do?

[ZoeWashburn]

What did you do?

Well, the one thing that screams at me the loudest is that I was a numbskull and forgot to initialize the recordset. Other than that, here's the code that I actually got to work:

Set rstIssue = New ADODB.Recordset
rstIssue.Open "tblACDCallIssue", cnn, adOpenForwardOnly, adLockReadOnly, adCmdTable
With Issue
rstIssue.MoveFirst
Do While Not rstIssue.EOF
    .AddItem rstIssue.Fields("Issue")
    rstIssue.MoveNext
Loop
End With
rstIssue.Close
Set rstIssue = Nothing

Wow... for once, I might actually be able to help somebody else!