Database problem

[tigger]

Hi there. I need some help in linking my Access database file to my program. I'm using a combo box which contains a list of names. But i have no idea on how to retrieve the database that consists the particulars of these names.

[debasisdas]

Can you please specify wha exactly is your requirment.

do u want to retrive/display the details ,when u change the name

or u want to fill the combo itself by retriving data from the database.

Please do post back.

[shiju uthaman]

if you are populating the combo directly other than from database you may experience some problems. First, populate the combo with names directly from database using the following procedure

private sub populatenames(cmb as combobox)
    dim cn as new adodb.connection
    dim rec as new adodb.recordset
 onerror goto errhandler
    cn.connectionstring="DSN="nameofdsn"
    cn.open
 rec.open "select name from tbl",cn,adopendynamic,adlockoptimistic
   cmb.clear
    while rec.eof<>true
      cmb.additem rec.fields(0)
      rec.movenext
    wend
rec.close
set rec=nothing
cn.close
set cn=nothing
errhandler:
     if err.number<>0 then
         if err.number=3021 then
           msgbox "No such name")
        else
           msgbox err.description
         end if
     end if

end sub


call this procedure in the form activate event

and on the click event write the following code

dim cn as new adodb.connection
dim rec as new adodb.recordset
dim strsql as string
cn.connectionstring="DSN=dsnname"
strsql="select * from tblname where name=' "  & comboname.text & "'"
cn.open
rec.ope strsql,cn,adopendynamic,adlockoptimistic

 ' bind your code to the necessary controls

text1.text=rec.fields(0)
text2.text=rec.fields(1)


rec.close
set rec=nothing
cn.close
set cn=nothing

' be careful to check the status of the connection object before opening

[tigger]

Thanks for the reply. My database contains 10 names and their threshold values. When i select a name and click a "Present tone" button, it should show a smile if it corresponds to the threshold values in the database. I don't know how to link my database file to the program.

[nairda]

Thanks for the reply. My database contains 10 names and their threshold values. When i select a name and click a "Present tone" button, it should show a smile if it corresponds to the threshold values in the database. I don't know how to link my database file to the program.

Hi tigger,
First, it's better for you to get your combobox's list directly from your database. It means that every time your database changed (e.g: added new names or deleted a name), the combobox will be updated automatically.

To link your combobox to your database, you can use these codes:
Private Sub Form_Load()
Dim conec As New ADODB.Connectio n
Dim rs As New ADODB.Recordset

conec.Connectio nString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source ='" & App.Path & "\STUDENTS.mdb' "
conec.CursorLoc ation = adUseClient
conec.Open

rs.Open "select STUDENTNAME from STUDENTDATA", conec, adOpenKeyset, adLockOptimisti c
If rs1.EOF = False Then
Do Until rs1.EOF
Combo1.AddItem rs!KD_SALES
rs.MoveNext
Loop
rs.Close
End If
End Sub

And to link your form to your database and show the data that match the name you've choosed in the textbox:
Private Sub Combo1_Click() '--> show the data straight away after you choose a name from combo1
Dim conec As New ADODB.Connectio n
Dim rs2 As New ADODB.Recordset

conec.Connectio nString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source ='" & App.Path & "\STUDENTS.mdb' "
conec.CursorLoc ation = adUseClient
conec.Open

rs2.Open "select * from STUDENTDATA where STUDENTNAME = '" & Combo1.Text & "'", conec, adOpenKeyset, adLockOptimisti c
If rs2.EOF = False Then
Text1.Text = rs2!STUDENTNAME
Text5.Text = rs2!STUDENTADDR ESS
rs2.MoveNext
rs2.Close
End If
End Sub

The bold text is used for show data from your database to textboxes. You may change it if you want to do other actions.

I hope it helps.
Nairda

[tigger]

Hey thanks.. But i encountered a problem opening the database at load event. I typed "conec.Open " but there's an error saying invalid password. Is it got to do with me adding a password in my access database?