select from combo box label shows relevant word from DB

[Maypen]

Hi guys.I would be grateful for some help. I am new to VB and i am writing a program which translates English words into various languages (I have a different form for each language). On the form is a combo box, a label and a datacontrol.I have the combo box populated with the English words. When i choose a word i want say, the French equivalent to appear in the label.
I know how to populate the combo box but i am lost how to ensure that if i choose "Hello" then "Bonjour" will appear
in the label. The database is MS Access 2000. Fields are English,French,Spanish etc.First record is "Hello", Then "Bonjour" etc. Please try to explain fully (syntax if possible) as i am finding it quite hard. Thanks in advance for any advise you can provide.

 

[zemp]

Assuming that you are using ADO to connect to your database and have created a proper connection, you can do something similar to the following in the combo box click event.

Private Sub Combo1_Click()
Dim l_strSQL As String
Dim rs As New ADODB.Recordset

On Error GoTo handler

l_strSQL = "SELECT [French] FROM [Table1] WHERE [English]= '" & combo1.text & "';"
rs.Open l_strSQL, Conn, adOpenForwardOnly, adLockReadOnly
If not (rs.EOF and rs.BOF) Then
rs.movefirst
Label1.caption = rs![French]
End If
Set rs = nothing
Exit Sub

handler:
If Err.Number <> 3021 Then '// No current record.
msgbox Err.Number & &quot;: &quot; Err.Description
End If
End Sub

Make sure that your control names are correct and that your table and field names are correct. 'Conn' is the open ADO connection object. If you do not have one open you can look at the Vb help files, msdn or search this forum for an example of how to open one.

I hope thatn this gets you started. Post again if you have questions or are stuck. Thanks and Good Luck!

zemp

 

[zemp]

You can find sample connections at

http://www.able-consulting.com/ADO_Conn.htm.

Link was provided by johnwm in a previous thread. Thanks and Good Luck!

zemp

 

[Maypen]

Thank you for your quick reply Zemp.The problem is that i am using DAO data control. Below is the code I am using to Populate the Combo box.How do i change the code you've posted to DAO. Sorry if this seems green but i am a newbie.


Private Sub Form_Load()

Set db = DBEngine.OpenDatabase(&quot;C:\My Documents\Mann Travel.mdb&quot
Set rs = db.OpenRecordset(&quot;select English from Phrase1&quot

Combo1.Clear

If rs.RecordCount <> 0 Then
Do While Not rs.EOF
Combo1.AddItem rs(&quot;English&quot
rs.MoveNext
Loop
End If

End Sub

Thanks in advance for your help.