how to retrieve data

[snta]

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.....

[cguan_77]

check out this link

http://en.allexperts.com/q/Visual-Basic-1048/retrieve-data-search-form.htm

[snta]

check out this link

http://en.allexperts.com/q/Visual-Basic-1048/retrieve-data-search-form.htm

this example for text box,but now i using combo box (style drop down list).
how to do that???

[debasisdas]

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

[snta]

thank you very much for your help

[choudhuryshouvi]

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 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

[choudhuryshouvi]

it seems that u have already benefited.....have u got ur answer...

if yes then please mark this thread as SOLVED