Combo box, method o data member not found

[DebbieCoates]

Can anyone give me any idea as to why this isn't working, I just cant work it out and its driving me mad, or is there any other way to set a combo box?

is there a way to set the data source to somehow set to the cnn connection i have already set up, and then to the recordset?


Dim rsCountries As New ADODB.Recordset
rsCountries.Open sql, cnn, adOpenStatic, adLockReadOnly


With rsCountries
.MoveFirst
If Not .EOF Then
Do Until .EOF
CboCountries.AddItem rsCountries.Fields("Country")
.MoveNext
Loop
End If
End With


rsCountries.Close
Set rsCountries = Nothing

 

[ArtieChoke]

What line do you get the error on?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[DebbieCoates]

Actually I think I have got it now, I changed it to
Dim sql As String
sql = "select * from T_country"

Dim rsCountries As New ADODB.Recordset
rsCountries.Open sql, cnn, adOpenStatic, adLockReadOnly

With Me.CboCountries(0)
.Clear
Do
.AddItem rsCountries![CountryID]
rsCountries.MoveNext
Loop Until rsCountries.EOF
End With

This works fine, however, it only fills in one column of the combo box, can you not have multi columns in a combobox in vb6?

I tried this, but it just gives me extra rows rather than columns

Dim sql As String
sql = "select * from T_country"

Dim rsCountries As New ADODB.Recordset
rsCountries.Open sql, cnn, adOpenStatic, adLockReadOnly

With Me.CboCountries(0)
.Clear
Do
.AddItem rsCountries![CountryID]
.AddItem rsCountries![Country]
rsCountries.MoveNext
Loop Until rsCountries.EOF
End With

 

[ArtieChoke]

Nope, one column only.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[BobRodes]

Didn't I just answer this somewhere else?

 

[BobRodes]

Appears not. A fine answer it was, too. Must not have posted it.

A few things:

Dim rsCountries As ADODB.Recordset
Set rsCountries = New ADODB.Recordset
rsCountries.Open sql, cnn

These are improvements. Don't put New on the same line as Dim. You only need a firehose cursor, which is the default adOpenForwardOnly.

With Me.CboCountries(0)
    .Clear
    Do
    .AddItem rsCountries![CountryID]
    rsCountries.MoveNext
    Loop Until rsCountries.EOF
End With

Do you have an array of country combo boxes? If not, blank the value of the index property, and remove the (0) from your code.

Finally, I'm getting the feeling that you're trying to have the country name show in the combo box, and capture the country ID when the user selects a country name. If the ID is an integer value, you would use the ItemData property for this. If not, you create an array of IDs and put the array index in the ItemData property.

To use the ItemData property:

With cboCountries
    .Clear
    Do Until rsCountries.EOF 'your way will error out if there are no countries
        .AddItem rsCountries![CountryName]
        .ItemData(.NewIndex) = rsCountries![CountryID]
        rsCountries.MoveNext
    Loop
End With

HTH

Bob

 

[DebbieCoates]

your guess is right, i want to show the countries, but store the country id.

I've just tried your code. I keep getting an error on the .clear saying Method or data member not found, do you know why this is?

if i create an array, how can i see this? ie i wanted to test it out with afterupdate msgbox cboCountrie.value sort of thing, how would i go about this?

 

[BobRodes]

do you know why this is?
Do you have a combo box called cboCountries on your form? Why do you have the (0); do you for some reason need an array of Country combo boxes, which seems to me extremely unlikely?

What do you mean by "create an array" exactly? Like what I suggested? If so, give me an example of one of your country ids so I can get an idea of how to explain.

Thx

Bob

 

[DebbieCoates]

sorry, I have sorted the (0) out now, that was what was causing the problem.

you mentioned this line of code

.ItemData(.NewIndex) = rsCountries![CountryID]
what is it doing exactly? is it somehow saving the countryid in an array? or am I just wishfull thinking.

I am so used to using multicolumn combo boxes in Access to bound datasources, this is taking some getting my head around.

after i have selected an item from my combo box, is there such thing as an onchange event, just so i can see this in the debug window or something to know what value it is holding, as ideally i want to display the counry, but store the countryid to actually use

 

[DebbieCoates]

got it

Private Sub CboCountries_Click()
With CboCountries
MsgBox .ItemData(.ListIndex), vbInformation
End With
End Sub

Private Sub Form_Initialize()

Dim sql As String
sql = "select * from T_country"

Dim rsCountries As New ADODB.Recordset
rsCountries.Open sql, cnn, adOpenStatic, adLockReadOnly


With CboCountries
.Clear
Do Until rsCountries.EOF 'your way will error out if there are no countries
.AddItem rsCountries![Country]
.ItemData(.NewIndex) = rsCountries![CountryID]
rsCountries.MoveNext
Loop
End With


End Sub


Thanks for all your help

 

[BobRodes]

<what is it doing exactly? Is it somehow saving the countryid in an array?
As you seem to have figured out on your own, yes, basically, just as the List property is somehow saving the list strings in an array. So, there's a one-to-one correspondence between items in the List property and items in the ItemData property. The main difference is that ItemData has to be an Integer, and therefore has certain limitations beyond the most obvious one of not being able to contain a string. For example, if you are pulling a subset of, say, 10 members of a 50,000 record table, you could get a problem because integers don't go beyond 32,768. In any case where you can't store an ID directly to the ItemData property, you can always create an array, put the ID in the array, and store the offset of the array to the ItemData property. (Unless you have more than 32,768 selections in your ComboBox; I'd recommend against that of course.)

Finally, see faq222-6008 for reasons NOT to do this: [tt]Dim rsCountries As New ADODB.Recordset[/tt]. The more often you reference the recordset, the more of a performance hit you take.



Bob