ADO: ComboBox mental disorder...

[666539]

There is one kind of combo box that populates automatically when you set 2 properties. There is a data bound combo box and list box. It has a rowsource property where you set the source of your data (recordsetname) and a listfield where you set the name of the field that the box is supposed to list.

You may have to switch the type of box combo box your using but the effort will pay for itself. Populating a combo box via code can be VERY time consuming if you have more than a couple of records. The rowsource and listfield properties can populate LOTS of records without slowing your application.

(I don't know if it will apply to your app but I usually set the style of the combo box to 2 and the matchentry to 1. That makes it really easy for the user to select data from the box.)

Thanks!
Can you give me an example of how to use this kind of combo box?

[HeSaidJoe]

I haven't used ADO or 2000 but the combo box you wnat is not the regular one. Go to Project\Components and add the Microsoft DataBound List Controls. Add the Combobox to your form.
In the properties you will see Database, recordfield, source, etc. Set these properties and you should be home free. ( I would set them in code as that way you can use App.Path if you plan on using your app elsewhere.

If in doubt, add the control to the form, select it, hit F1

Wayne

Thanks, I'll check it out and let you know.

Ok, I got the combo box on my form, and when I try to load a recordset into the combo I get an empty combo box:

DataCombo1.DataField = "Name"
DataCombo1.BoundColumn = "Name"
DataCombo1.ListField = "Name"
Set DataCombo1.RowSource = RS
Set DataCombo1.DataSource = RS

Any idea?

[666539]

What are you specifically trying to do with this combo box.

You have:

DataCombo1.DataField = "Name"
DataCombo1.BoundColumn = "Name"
DataCombo1.ListField = "Name"
Set DataCombo1.RowSource = RS
Set DataCombo1.DataSource = RS

I'm going to guess that you don't need to have the boundcolumn property set. Bound column "determines which column of a multicolumn list box or combo box is bound to the Value property of the control. Available at design time and run time." I don't think you need this one set.

I don't know the details of your app, but I'm not sure it makes sence to have the RS recordset set to both the rowsource and datasource properties, and the Name field set to both datafield and listfield properties.

The way I usually use the datacombo is for example, if you have a form that takes orders for a company. You only want the data entry person to be able to select from valid customer names. I'd set the datacombo.rowsource to the customers recordset, and the list field to customer number or customer name. This would only allow the datacombo box to show valid customers. Then you could set the datacombo.datasource property to the orders recordset and the datafield to the customer name or number or whatever you use to uniquely identify your customer. That would make sure that whatever valid record you chose from the customer table would be saved to the order table.

If you give some more specifics about your app we could give you more specific answers.

If this advice doesn't help out try doing some debugging to see what is actually in your recordset. It is possible that the reason you don't have anything in your combo box is because you didn't correctly connect to the database or becase you have no records.

The connection and the recordsets are ok, becuase when I set the style property of the combo box to 0, I got the first record in the combo box.

More details:
Let's say I have a recordset that looks like this:

'ID       Name               Address
'--       ----               -------
'456      John Kiwi          65th Barf St., NY
'123      John Doe           4th July Blvd., IL
'111      Another Name       ......
'010      Some other name    ......
'etc....

I want a combo box to display the names and for example, if a user clicks on a name I want to get the ID of the user.

[666539]

That is definatly something that sounds like all you'd have to do is set the rowsource property to the name of the recordset,and the listfield to the name field. You wouldn't have to set the datasource or datafield properties at all.

You'd have to code the changed event of the datacombo to do a find on the recordset and display it somewhere.

Ok, now I'm using this and I'm still getting a blank list:

DataCombo1.ListField = "Name"
Set DataCombo1.RowSource = RS

I'm sorry if I'm bugging you.

[666539]

Are you using the datacontrol or code? Sounds like code. It sounds like you're doing everything right. I can't imagine what's wrong. Try making a sample program with the data control. It's easier to bind to a control than to code. Try experimenting with the data control.

• I'm using:
  
  • DataCombo (An expansion of the DBCombo).
  • ADO 2.5 recordsets and a connection.
  • MS Jet 4.0.
  • An Access 2000 DB.
• Yes, I'm doing everything via code.
• I don't want to rewrite my whole app.
• I see the regular combo box also has the DataField, DataSource properties.

[666539]

If you've never used the dbcombo like this I'd sugest writing a sample app with the control. There is usually less room for error using the control.

Where are you setting the rowsource and listfield properties? Are you doing it in the form load event? I think some of the properties can just be set on the control. That could make it easier.

The reason I say you should write a sample program using the data control is because when you do that, all the properties of the datacontrol will be available when you set the properties of the data control. When you set the properties through code you have to make sure get everything typed in correctly. I don't think you will always get a run time error if you get it typed in wrong. I'm almost sure that the problem you're having is a typo.

You look like you have connected to the recordset properly. It's populated. You havn't gotten any design time syntax errors that have been picked up. It must be something where the design enviornment isn't picking up the error until it it run.

I had similar problems the first time I used code to do this. Most often it is some spelling mistake in the database field name.

I'd make a quick app with just a ADO data control and a db combo box. Set everything up and see if it works. Like I said, there is less room for error just using the control. Once you get that working with the control you'll know it's code problem rather than just something wrong with your VB installation.

[Edneeis]

Are you using the DataCombo (OLEDB) for use with ADO?
There are two one for DAO and one for ADO.
Are you sure RS is set the the recordsource?
If you can't bind to the control at design time it probably means you are using the DAO combo with an ADO recordset.

Edneeis:
I'm using the DataCombo with an ADO recordset. If I'm not mistaken this is the proper way.

666539:
I just found out something.
When I set the DataCombo's Style property to 2, the list is empty, but when it's 0, I see the value of the first record in the text of the combo!

[666539]

And you're sure you have the rowsource and listfield property set and not the datasource and datafield? If 1 record shows up it almost sounds like you have the wrong 2 properties set so that rather than showing the contents of the column in 1 database it is showing the contents of the current fields in the 1 column of the database.

This is my code:

Set DataCombo1.RowSource = RS
DataCombo1.ListField = "Name"

I tried switching the places of these two commands but it still doesn't work.

[666539]

I'm pretty stumped. Everything appears to be right. You have had it show the field from 1 record so it is communicating with the DB.

I'd still write a small sample app with just a ADO data control, a text box (just so you can see some data) and the combo box so you can see how it is supposed to work without worring about if it is coded correctly. Experiment with the different combo boxes.

Thnaks.

Do you know where I can find an example of the DataCombo control?
I don't want to write a sample app with the data control because I am using the ADODB library and not the control.

[DrewDog_21]

the only thing that i can think of here is that
you might want to check your recordset to be sure that it is
not empty. Put a Debug.Print RS.Recordcount statement
somewhere and be sure you have values in the recordset.
Everything else looks okay.

Well, my recordset is not empty (I got the one record, didn't I?).
What about the regular combo box's DataXXXXX properties?

[DrewDog_21]

kind of basic, but a start:
http://support.microsoft.com/support.../Q189/6/82.ASP

[666539]

http://vb.digitalroutes.co.uk/index.shtml?dbcombo

This is a link from the search page at http://www.vb-helper.com

I haven't looked at the code for the 2 programs that it returns but the descriptions look like the programs will help you out.

It STILL doesn't work.
What am I doing wrong?

Well, never mind. I'll just use the regular combo box with Populate subs.
Thanks A LOT for your time guys.

[chongo 2002]

try this code for the cbo. It is for dao but it can be converted.

With rs
        cbo.Clear
        cbo.Text = "Name"
        Dim o As Integer
        rs.MoveFirst
        For o = 1 To rs.RecordCount
        cbo.AddItem rs![Name]
        If o <> rs.RecordCount Then rs.MoveNext
        Next o
End With

Yes, I'm using that type of thing to populate my regular combo box.