Results 1 to 8 of 8
  1. #1
    riversr54 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    4

    Active Text Box

    I've created a very simple little database that consists of two tables for the purposes of learning. I'm trying to figure out how to make a text box on a form, update it's contents based on a user selection in a combo box. I have a Products table:

    Click image for larger version. 

Name:	Acc01.JPG 
Views:	35 
Size:	17.6 KB 
ID:	39843

    And an Orders table:

    Click image for larger version. 

Name:	Acc02.JPG 
Views:	32 
Size:	13.9 KB 
ID:	39844

    My Orders form has a text box and a Combo box that contains a list of the products:

    Click image for larger version. 

Name:	Acc03.JPG 
Views:	32 
Size:	10.0 KB 
ID:	39845
    The combo box provides the list of products correctly. I want the Text Box to display the product description when the user picks a product. I've played around for 2-3 hours with Select commands and with DLookup, but I can't seem to find the right combination to make it work. Please help me with the proper command and where it goes (Control Source, AfterUpdate Event, etc)

    Regards,



    riversr54

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The form is bound to a table
    the text box is bound to a field via property: CONTROL SOURCE

    If you see #Type, it means you prob. have a calculation in the box thus the user cannot enter data.
    if you want the user to pick an item from the combo , then run this snip of code to put it there:
    Code:
    sub cboBox_afterupdate()
      txtBox = cboBox
    end sub


    but you wouldn't need both for a 1 to 1 entry. The combo box alone can serve the purpose of the text box.
    it has a source data , the list to choose from: ROW SOURCE
    and the target field:
    CONTROL SOURCE

    but having a combo and text box lets you fill in more. The combo box can have 2 columns : ProdName , Description
    then fill the textbox with the description and the combo has the prod code.

    Code:
    sub cboBox_afterupdate()
      txtBox = cboBox.column(1)
    end sub


    Note: in vb , the columns begin with 0.
    the combo has 2 columns,
    set the bound column,
    set the visibility of the columns too, via column widths.

  3. #3
    riversr54 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    4

    Lost

    Thanks for the quick reply, but you really lost me there. I need to know what 'query' or DLookup I would use to populate the text box with the product description from the product table. And I assume I would attach said query to the AfterUpdate even of the text box. I don't see anywhere in your answer that does that. What did I miss?

    Quote Originally Posted by ranman256 View Post
    The form is bound to a table
    the text box is bound to a field via property: CONTROL SOURCE

    If you see #Type, it means you prob. have a calculation in the box thus the user cannot enter data.
    if you want the user to pick an item from the combo , then run this snip of code to put it there:
    Code:
    sub cboBox_afterupdate()
      txtBox = cboBox
    end sub


    but you wouldn't need both for a 1 to 1 entry. The combo box alone can serve the purpose of the text box.
    it has a source data , the list to choose from: ROW SOURCE
    and the target field:
    CONTROL SOURCE

    but having a combo and text box lets you fill in more. The combo box can have 2 columns : ProdName , Description
    then fill the textbox with the description and the combo has the prod code.

    Code:
    sub cboBox_afterupdate()
      txtBox = cboBox.column(1)
    end sub


    Note: in vb , the columns begin with 0.
    the combo has 2 columns,
    set the bound column,
    set the visibility of the columns too, via column widths.

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    The rowsource of your combo should be "Select prodID,prodName,prodDesc from tblProducts. (or whatever your products table name is)
    The column count would be 3
    the column widths would be 0,2,0

    doing this has the description in the 3rd column of the combo box which would be referred to as the 2nd column because the columns are zero based (0,1,2)
    the columns with a zero width are hidden so you only see the prodName in the combo.

    In the after update event of the combo you would have
    Code:
    sub cboBox_afterupdate()
         txtBox = cboBox.column(2)
    end sub
    which takes the hidden column(2) of the combo and inserts the value into the textbox.

  5. #5
    riversr54 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    4
    Thanks but I still want it to do more.
    I have the combo box working, it displays a drop down list of product names where the user can select one. When I build the combo box, I choose for it to include both productID and productName, but to only display product names in the drop down. That part works.

    What I want is to then take the productID associated with the selected name and to use that ID number to build a query which will query the products table for the product description and display it in an unrelated text box. Is that possible?


    Quote Originally Posted by moke123 View Post
    The rowsource of your combo should be "Select prodID,prodName,prodDesc from tblProducts. (or whatever your products table name is)
    The column count would be 3
    the column widths would be 0,2,0

    doing this has the description in the 3rd column of the combo box which would be referred to as the 2nd column because the columns are zero based (0,1,2)
    the columns with a zero width are hidden so you only see the prodName in the combo.

    In the after update event of the combo you would have
    Code:
    sub cboBox_afterupdate()
         txtBox = cboBox.column(2)
    end sub
    which takes the hidden column(2) of the combo and inserts the value into the textbox.

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    heres an example of what RanMan and I are talking about.

    There is no need to build a query or use a dlookup as you can include the description in the rowsource of the combo, hide it from displaying in the combo, and use that for the textbox

    rivers.zip

  7. #7
    riversr54 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    4
    That did the trick. I think I was making it more complicated that it has to be.

    Thank you very much.

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I think I was making it more complicated that it has to be.
    We've all been there.
    Good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Clear active text box with using VBA.
    By KPAW in forum Programming
    Replies: 4
    Last Post: 03-06-2019, 06:06 PM
  2. Replies: 4
    Last Post: 05-26-2018, 03:02 PM
  3. Yes/No Field, when active print text in a report
    By crobaseball in forum Access
    Replies: 2
    Last Post: 04-09-2014, 11:08 PM
  4. Replies: 21
    Last Post: 01-21-2014, 05:04 PM
  5. Replies: 1
    Last Post: 01-11-2014, 12:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums