Results 1 to 4 of 4
  1. #1
    seanst8579 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    14

    Conditionally populating text box from combo box value

    Been trying to figure out how to do this without luck.

    I've got a subform bound to a wholesale order table which contains a (bound) combo box pulling values from a table of available items. Among the things in this table are prices of the item at certain buyer quantity thresholds, e.g., a field named WSPrice5 that gives the price of of each unit when the buys five, WSPrice6 which gives the price per item when they purchase six, etc. I need to do two things:

    When a user selects an item from the combo box, it needs to automatically populate the List Price text box with the value of that item's USListPrice field from the available items table and, most importantly, save it to the CurrentListPrice field in the wholesale table. I know how to make the value display in the text box, but I don't know how to get it to save and it's important that it does (list prices change from time to time, so we need a record of its value at the time of sale).

    More importantly, I need to conditionally populate another text box based on 1) the item selected in the combo box and 2) the quantity entered by the user (and save the value to the wholesale order table as well). So for example, if the user selects item 3 and enters a quantity of 7, it would display/save item 3's WSPrice7 value in the Discounted Price text box and save it to the DiscountPrice field. One additional complication is that one of the WSPrice values corresponds to the discount price on orders of 10 or more units, so it needs to grab an item's WSPrice10 price if the quantity entered is 10, 11, 12 or any other larger number.



    Any help would be appreciated.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not too clear on what your actual form and table structure is, but if you want to display a value from a different column in your combo:

    assuming combo (called say cboItems) has rowsource of say

    SELECT ItemPK, ItemdDesc, ItemPrice FROM tblItems ORDER BY ItemDesc

    this is three columns.

    1. You bind the first column to the ItemFK field in your form table - think you are already doing this
    2. you hide the first column (set column widths property to 0) - the combo will now display the ItemDesc
    3. For the price - if you are not saving it in your form table then have an unbound textbox with a controlsource of
    =[cboItems].[Column](2)

    or
    4. For the price - if you want to save it in your form table put the following code in your cboItems AfterUpdate Event

    me.txtListPrice=[cboItems].Column(2)

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    your list box cannot fill the price, the list box can only enter the Item .
    you will need a function to do the price lookup in 2 places:
    1 at listbox_afterupdate event. I will guess the Qty default = 1.
    2 at the Qty_afterupdate event. the user can change qty and it will need another price check.

    the query "qsPriceSort" sorts by client/Part/Qty so the lookup will hit the 1st one that qualifies
    Code:
    function GetPrice(pvClientID, pvPart, pvQty)
       GetPrice= dlookup("[price]","qsPriceSort","[ClientID]=" & pvClientID & " and [Part#]='" & pvPart & "' and [Qty]<=" & pvQty) & ""    
    end function
    
    sub listbox_afterupdate()
      txtPrice = GetPrice(txtClient, txtPart, txtQty)
    end sub
    
    sub Qty_afterupdate()
      txtPrice = GetPrice(txtClient, txtPart, txtQty)
    end sub

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It sounds like your wholesale order table has fields something like (this is a spreadsheet design)
    Code:
    ProdID, WSPrice1, WSPrice2, WSPrice3, WSPrice4, WSPrice5, WSPrice6, WSPrice7, ..... , WSPrice10
    If so, You might want to stop and step back a bit. You do not have a normalized table.

    A better (database) design would be something like

    Code:
    ProdID, WSPrice, WSQty

    See:
    The Problem of Repeated Columns http://rogersaccessblog.blogspot.com...d-columns.html
    What Is Normalization, Part I http://rogersaccessblog.blogspot.com...on-part-i.html
    Normalizing Repeated Columns: Single Repeated Column(Part1) http://rogersaccessblog.blogspot.com...simple-pc.html

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

Similar Threads

  1. Populating Text box after selection from Combo Box
    By coach32 in forum Programming
    Replies: 3
    Last Post: 03-19-2014, 10:15 PM
  2. Replies: 2
    Last Post: 08-16-2012, 10:02 PM
  3. Wrap text conditionally
    By devcon in forum Reports
    Replies: 6
    Last Post: 06-12-2012, 12:32 AM
  4. Replies: 15
    Last Post: 09-18-2010, 01:19 PM
  5. Replies: 3
    Last Post: 06-29-2010, 12:08 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