Combo Box With Database

[Jaseem Ahmed]

i have connected combo box with data base...
i have called a field in combo box...
but i dont know how to made selection..
means when i click on items that are in combo box from database, the text box should change value according to the selection of combobox.

Option Explicit
Private con As New ADODB.Connection
Public rs As New ADODB.Recordset

Private Sub Form_Activate()
Dim con As New ADODB.Connection


con.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ISIS_157;Data Source=JASEEMAHMED"
con.Open
Set rs = New ADODB.Recordset
rs.Open "SELECT * from EQUIPMENT", con, adOpenStatic, adLockOptimistic

Do While Not rs.EOF = True
Combo1.AddItem rs.Fields("Itemcode") 

Text1.Text = rs!Description
rs.MoveNext
Loop
End Sub

i have 2 fields in database, one is item code, other is description, now i have 10 item codes and its description.
i want to show item code in combo box, and its discription in text box.
when combobox value changes, the text box should also change its description, acording to the itemcode, which is saved in database.

[Pgmer]

I have a question for you..
Why your assigning the text1.text=rs!Description in loop?

you want to use the vb6 syntax or pure vb.net code?

why cant u assign dataset to combobox with datasorce property and set value meber and display member property of combo?

[Jaseem Ahmed]

why cant u assign dataset to combobox with datasorce property and set value meber and display member property of combo?

i didnnt understand, what u want to say..?
i want to use pure vb 6 code..
and i was confuse on this statement..
text1.text=rs!Description..
i am just trying to show the dscription of itemcode, which is in combo box..i tried this statement but didnt worked..

[debasisdas]

follow these steps.

1. populate the combo only (not the text box) from the recordset.

2. to populate the text --capture the value from combo and pass the same back to DB using SQL. Fetch the Description and display in a textbox.

[Pgmer]

Yes debasis, i asked the same to jaseem.
No need to assign the value to textbox in loop.

cant we get the data into dataset and assign that dataset to combo with value meber and display member? and in selected index changed event we can get the value member of that index and show up in textbox???

[debasisdas]

value member and display member are not available in vb 6.

[Jaseem Ahmed]

how can i capture the value from combo, where as i have just kept the codes in combo box, not their description, the description of codes is kept in another field...
can u plz pass an example?

[debasisdas]

when you select the combo
pass the code into another SQL and fetch the description and display in textbox.

[Pgmer]

if he is using vb6 code then why cant he try like this?

combobox1.Items.Add(New VB6.ListBoxItem(rs.fields("code").ToString(), rs.fields("Description").ToString() ))

and the he can access the data using vb6.getitemdata. why to make call to server each time? its expensive i guess..

[debasisdas]

This is VB 6.0 code ?

[Pgmer]

sorry we need to add Imports Microsoft.VisualBasic.Compatibility this reference i think.
but jahseem aheamd is not clear he is using vb.net or vb6 code..

[debasisdas]

If you go through the very 1st post of this thread you will understand what code we are discussing here .

Here the code under discussion is Purely VB 6.0 only.

We are not going to .net and not trying to make that backward compatible.

[Jaseem Ahmed]

i m very clear.....and i have already told....that i using Vb6 code.

[Pgmer]

ohh ok I am very sorry i think i have not read it properly before replying.
sorry debasis and sorry jaseem.

[AndreRet]

@Jaseem -

Use the combo box List index number to set the recordset to that record -

Dim xIndex As Integer

XIndex = Combo1.ListIndex

Rs.AbsolutePosition = xIndex

Text1.Text = rs!Description

This will display the record as was selected from the combo.:)

[Jaseem Ahmed]

should i use this code in combobox_change event? or at another place??

[AndreRet]

In the combo click event. Just add code for your recordset "Rs".

[Jaseem Ahmed]

there is a still problem Andre...
all the itemcodes are working fine...but the item which is on top in combo, is not working...it is Errorring "Arguments Are of the wrong type,are out of acceptable range,or are in confilict with one another" and pointing to "rs.AbsolutePosition = xIndex"..

[debasisdas]

alternate method:

Use the item data property of the Combo Box.

populate the combo like this

Combo1.AddItem rs("itemcode")
Combo1.Itemdata(Combo1.NewIndex) = rs("description")

and display the Itemdata property in the textbox.

[Jaseem Ahmed]

whre should i use this code?
and how to assign textbox a discription?
text1.text=combo1.itemdata(combo1.newindex)????

[Jaseem Ahmed]

type mismatch error...
Combo1.Itemdata(Combo1.NewIndex) = rs("description")

[debasisdas]

In form laod

Combo1.AddItem rs("itemcode")
Combo1.Itemdata(Combo1.NewIndex) = rs("description")

on combo_click

text1.text = combo1.itemdata(combo1.ListIndex)

[Jaseem Ahmed]

type mismatch error...
Combo1.Itemdata(Combo1.NewIndex) = rs("description")
still same Error...

[AndreRet]

My apologies, I just gave you a sample on my code. You need to allow for 0 (zero) in the listindex. Try the following modified code under the combo click event -

Dim xIndex As Integer
 
XIndex = Combo1.ListIndex

If xIndex = 0 Then'Combo's first line... 
Rs.AbsolutePosition = 1
Else
Rs.AbsolutePosition = xIndex - 1
End If
 
Text1.Text = rs!Description

This should load the correct record, and will show record 1 if index is 0

[Jaseem Ahmed]

now the same error is coming on second item code..

rs.AbsolutePosition = xIndex - 1
in this line..
and if i m just writting
rs.AbsolutePosition = xIndex
then it shows same value on code 1 and 2...

[Jaseem Ahmed]

it is working fine with +1

rs.AbsolutePosition = xIndex + 1

[Jaseem Ahmed]

Once AGain Bundle of Thanksssssssssssssssssssss....

[AndreRet]

As always, a pleasure. Glad you could get the final tweaking sorted by using + 1.:)