Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2013
    Posts
    25

    Question Unanswered: Have a textbox assume a default value based on a combobox selection?

    Hi. This is my first post in this forum. I am a mechanical engineering student with a side-interest in programming but im barely in my first steps toward learning about it. I have a job which requires me to do stuff in access that i frequently have to learn on the spot. I hope i can, one day, contribute to this forum instead of just using it as a learning tool!

    So anyway, i have an access db that deals with quality testing. I have a table called "products" and another one "tests". Each product has a particular dimension, say "lenght", stored in a field in the products table.
    Now, when you test a product, its lenght might not coincide with the lenght it should have. So I made a form for the "tests" table and i want to, when i select the product in the combobox, have the lenght from the "products table" autofill the "measured lenght" value in the "tests" table AND show it on a textbox which the user can then use to change the default value in case the measurements gave a different lenght.
    Any help would be appreciated! and please, bear in mind that i haven't learned about macros yet so if it requires any coding, i'd need you to walk me through it! thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Basically, you have three simple options:

    1. Base the form you use on a query that combines both tables. That way, data from the table "products" will be immediately available and can be displayed in a bound TextBox control.

    2. Include the value (Length from the Products table) in the combo, then retrieve it in the AfterUpdate event of that combo. ComboBox controls can have several columns which can be visible or not and you can use the value of any column with a simple VBA procedure such as:
    Code:
    Private Sub Combo_Product_AfterUpdate()
        '
        ' Assume that the length is in the second column of the combo.
        ' Note: The first (leftmost) column is Column(0).
        '
        Me.Text_DefaultLength.Value = Me.Combo_Product.Column(1)
    
    End Sub
    3. Use a domain function to retrieve the requested value:
    Code:
    Private Sub Combo_Product_AfterUpdate()
        '
        ' Assume that the combo contains the ID of the product (ProductID)
        ' which is the primary key or a unique key in the table Products.
        '
        Me.Text_DefaultLength.Value = DLookUp("Length", "Products", "ProductID = " & Me.Combo_Product.Value)
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Sep 2013
    Posts
    25
    Quote Originally Posted by Sinndho View Post
    Basically, you have three simple options:

    1. Base the form you use on a query that combines both tables. That way, data from the table "products" will be immediately available and can be displayed in a bound TextBox control.

    2. Include the value (Length from the Products table) in the combo, then retrieve it in the AfterUpdate event of that combo. ComboBox controls can have several columns which can be visible or not and you can use the value of any column with a simple VBA procedure such as:
    Code:
    Private Sub Combo_Product_AfterUpdate()
        '
        ' Assume that the length is in the second column of the combo.
        ' Note: The first (leftmost) column is Column(0).
        '
        Me.Text_DefaultLength.Value = Me.Combo_Product.Column(1)
    
    End Sub
    3. Use a domain function to retrieve the requested value:
    Code:
    Private Sub Combo_Product_AfterUpdate()
        '
        ' Assume that the combo contains the ID of the product (ProductID)
        ' which is the primary key or a unique key in the table Products.
        '
        Me.Text_DefaultLength.Value = DLookUp("Length", "Products", "ProductID = " & Me.Combo_Product.Value)
    
    End Sub
    Remember, i need the user to be able to see the default value in a textbox, and then be able to change it if it doesn't match the value that the test gave him. Would those options be able to do that for me? I'll give it a shot while awaiting your reply thanks!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Options 2 and 3 surely will, perhaps with a test to assign the default value only when the textbox contains Null:
    Code:
    Private Sub Combo_Product_AfterUpdate()
        '
        ' Assume that the length is in the second column of the combo.
        ' Note: The first (leftmost) column is Column(0).
        '
        If IsNull(Me.Text_DefaultLength.Value) Then    
            Me.Text_DefaultLength.Value = Me.Combo_Product.Column(1)
        End If
    
    End Sub
    Have a nice day!

Tags for this Thread

Posting Permissions

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