i'm new programmer. can u help me , how to retrieve data from database(access) and display in combo box. the value in combo box will automatic display into text box
example:
dtbs=office.mdb
table name= item
field name=itemID,itemName
itemID=M1; itemName=modem
itemID=M2; itemName=repeater

i want this combo box display all itemID in combo box. if the user choose/click, M1, text box will display 'modem' automatically....
how to do like that???plzz help me.....

1. open the recordset only for itemID
2. in a for loop add the items to combobox
3. open another recordset for itemname by passing itemID in where clause at runtime dynamically by selecting the itmes from combobox

i'm new programmer. can u help me , how to retrieve data from database(access) and display in combo box. the value in combo box will automatic display into text box
example:
dtbs=office.mdb
table name= item
field name=itemID,itemName
itemID=M1; itemName=modem
itemID=M2; itemName=repeater

i want this combo box display all itemID in combo box. if the user choose/click, M1, text box will display 'modem' automatically....
how to do like that???plzz help me.....

think thats the complete code for you....take a combo box and a textbox...

set properties :-

combox box : name --> cboItem
style --> 2- dropdown list
textbox : name --> txtname

add this reference in the project --> Microsoft Activex Data Object <version no.> Library.......the version no. depends on the version of ms office u have installed.....take the highest one for better performance....

here we go...

Option Explicit

Dim gcn As New ADODB.Connection

Private Sub cboItem_Click()
Dim rs As New ADODB.Recordset

With rs
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .CursorType = adOpenDynamic
    .Open "select itemname from [item] where itemid='" & cboItem.List(cboItem.ListIndex) & "'", gcn

    txtname.Text = IIf(.RecordCount = 0, "", IIf(IsNull(!itemname), "", !itemname))
End With

If rs.State = adStateOpen Then rs.Close
Set rs = Nothing
End Sub

Private Sub Form_Load()
On Error GoTo err1

gcn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\sample.mdb;" & _
                    "Persist Security Info=False"
gcn.Open

Call PopulateCombo

Exit Sub

err1:
    Err.Clear
    MsgBox "Could not open database.", vbCritical, "Error"
    If gcn.State = adStateOpen Then gcn.Close
    Set gcn = Nothing
End Sub

Private Sub PopulateCombo()
Dim rs As New ADODB.Recordset

rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.CursorType = adOpenDynamic
rs.Open "select itemid from [item] order by itemid", gcn

With cboItem
    .Clear
    .AddItem "Select an Item Id"
    .ListIndex = 0
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        While Not rs.EOF()
            .AddItem rs!itemid
            rs.MoveNext
        Wend
        .ListIndex = 1
    End If
End With

If rs.State = adStateOpen Then rs.Close
Set rs = Nothing
End Sub

Private Sub Form_Unload(Cancel As Integer)
If gcn.State = adStateOpen Then gcn.Close
Set gcn = Nothing

End
End Sub

please let me know if this works out for you....for any more questions feel free to ask...

regards
Shouvik

This article has been dead for over six months. Start a new discussion instead.