Results 1 to 5 of 5

Thread: Selecting data in a data combo and updating a text box

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Dec 1999
    Location
    Itabirito,Minas Gerais, Brazil
    Posts
    79

    Post

    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

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Dec 1999
    Location
    Itabirito,Minas Gerais, Brazil
    Posts
    79

    Post

    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

  3. #3
    Guru Aaron Young's Avatar
    Join Date
    Jun 1999
    Location
    Red Wing, MN, USA
    Posts
    2,176

    Post

    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
    aarony@redwingsoftware.com
    ajyoung@pressenter.com


  4. #4
    Guru Aaron Young's Avatar
    Join Date
    Jun 1999
    Location
    Red Wing, MN, USA
    Posts
    2,176

    Post

    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
    aarony@redwingsoftware.com
    ajyoung@pressenter.com


  5. #5

    Thread Starter
    Lively Member
    Join Date
    Dec 1999
    Location
    Itabirito,Minas Gerais, Brazil
    Posts
    79

    Post

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width