Selecting data in a data combo and updating a text box

Hi, people!

I'm trying to select data in a datacombo and automatically updating a text box.
I don't know how to do that only using the datacombo and textbox properties, so I'm trying to create a module.
Have added the following code to the "Change" event of my datacombo:
==================
Dim MyDb As Database
Dim Myset As Recordset
Set MyDb = "<my db name>"
Set Myset = MyDb.OpenRecordset("select Description from [My table] where COD = " & [<my datacombo name])
===================
How can I connect the result of this query to my text box?
I’m starting with VB now and I don’t even know if I am in the right direction.

Thanks in advance,
Roselene

Hi, Aaron!

Thanks for your attention and kindness!

You have used a lot of commands that I've never seen before. You module seems to be very complex and interesting.

I'm learning a lot with you SQL instruction!
Also have learnt the way of setting a form object= a field of the SQL instruction.

I'm sure you have already shown me the right direction, but, if you don't mind, I'd like to answer you one more question:
================================
Set Myset = MyDb.OpenRecordset("select Description from Equipments where MytableField = " & Mycomboname & ", dbOpenForwardOnly)")
==============================
The line above is causing a syntax error (missing operator)
Have tried a lot of changes and have been unsucessful so far.

Please, forgive my mistakes with the Language.
Thanks again and a very happy New Year!

Best Regards,
Roselene

Try:

Set Myset = MyDb.OpenRecordset("SELECT Description FROM Equipments WHERE MytableField = " & Mycomboname & ", dbOpenForwardOnly)

This tells it to retrieve a Recordset of Values where the MyTableField Value is Equal to the Value in MyComboName, if this is a String/Character Value you need to include Single Quotes, ie.

Set Myset = MyDb.OpenRecordset("SELECT Description FROM Equipments WHERE MytableField = '" & Mycomboname & "'", dbOpenForwardOnly)


------------------
Aaron Young
Analyst Programmer
[email protected]
[email protected]

If you're going to be using the Data Access Objects instead of the Data Control, I'd suggest dumping the Databound Controls too, here's an example of using the DAO with a Standard Combo and Textbox..

Add a Combobox and Textbox to your Form, also makea Reference to the Data Access Objects in the Reference Section, (Or just Add then Remove the Data Control).

Code:

---

Private oDB As Database

Private Sub Combo1_Click()
    Dim oRS As Recordset
    'Display the selected Books Author in the Textbox
    Set oRS = oDB.OpenRecordset("SELECT Author FROM Authors WHERE Au_ID = " & Combo1.ItemData(Combo1.ListIndex), dbOpenForwardOnly)
    Text1 = oRS("Author")
    oRS.Close
    Set oRS = Nothing
End Sub

Private Sub Form_Load()
    Dim oRS As Recordset
    'Fill the Combobox with the 1st 100 Book Titles
    Set oDB = Workspaces(0).OpenDatabase("Biblio.mdb", , True)
    Set oRS = oDB.OpenRecordset("SELECT TOP 100 Titles.Title,[Title Author].Au_ID FROM Titles,[Title Author] WHERE Titles.ISBN = [Title Author].ISBN", dbOpenForwardOnly)
    While Not oRS.EOF
        Combo1.AddItem oRS("Title")
        'Store the Authors ID in the ItemData
        Combo1.ItemData(Combo1.NewIndex) = oRS("Au_ID")
        oRS.MoveNext
    Wend
    If Combo1.ListCount Then Combo1.ListIndex = 0
    oRS.Close
    Set oRS = Nothing
End Sub

Private Sub Form_Unload(Cancel As Integer)
    oDB.Close
    Set oDB = Nothing
End Sub

---

------------------
Aaron Young
Analyst Programmer
[email protected]
[email protected]

Aaron,

I have tried the second option you have sent me (cause my value is a string) and at first it didn't work, even tough the syntax was right. So, I find out that besides the SQL instruction, there was another problem which was causing this message:
"run time error 13 type mismatch"

So, I was given an advice to change the position of the DAO object library to a position above the ADO Library (on the "Project" Menu, option "References").

I haven't got a clue on what happened when I did so, but now, it works.
I'm starting with VB now and have lots to learn.

Thanks very much for your precious help!
Still using your module to learn!

Best regards,
Roselene