Results 1 to 10 of 10
  1. #1
    LaserTaser is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    5

    Return values based on two combo box selections

    I have a created a form (the record source is the INVENTORY table) that will be used to find and update records as needed. There are two drop-downs on the form: Store and Fruit. A store and a fruit combination can only appear in the INVENTORY table once, and each store has all of the fruits. So, there is an entry for Albany – Apples, Albany – Bananas, Valdosta – Apples, Valdosta – Bananas, etc. The Store drop-down (cboStore) contains all of the stores and the fruit drop-down (cboFruit) contains all of the fruit. There are two text boxes on the form that list current stock (txctCurrentStock) and pending quantity (txtPendingQuantity) – both are stored in the INVENTORY table. What I am trying to do is make it so a user can select a store, then a fruit and have the corresponding current stock and pending quantity returned in the appropriate text boxes. The user MAY have to edit the stock quantities. I have a SAVE button on the form as well.

    I have used the following as a dlookup in the Control Source for the appropriate text box, but that only resulted in the first value being returned for the:



    Code:
     =DLookUp("CURRENTSTOCK","[INVENTORY]",("[STORE]='" & [Forms]![STOCK]![cboStore] & "'" And "[Fruit]='" & [Forms]![STOCK]![cboFruit] & "'"))

    I have also tried the following code in the AfterUpdate of the fruit combo box (at the time I was just focusing on the Current Stockvalue):

    Code:
    Private Sub cboFruit_AfterUpdate()
                Me.txtCurrentStock.Value = "Select [INVENTORY].[ CURRENTSTOCK] "& _
               "FROM [INVENTORY] " & _
               "WHERE [INVENTORY].[STORE] = '" & Me.cboStore.Value & "'AND [INVENTORY].[FRUIT] = '" & Me.cboFruit.Value & "' "
    EndSub
    In the INVENTORY table Store and Fruit are stored as Text. CurrentStock and PendingQuantity are stored as numbers.
    Can anyone shed any light on what I may be doing wrong?
    Last edited by LaserTaser; 07-30-2012 at 01:38 PM. Reason: typo

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The second method can't be done. The error in the first is a common one; remove the quotes in red:


    =DLookUp("CURRENTSTOCK","[INVENTORY]",("[STORE]='" & [Forms]![STOCK]![cboStore] & "'" And "[Fruit]='" & [Forms]![STOCK]![cboFruit] & "'"))
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    LaserTaser is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    5
    Quote Originally Posted by pbaldy View Post
    The second method can't be done. The error in the first is a common one; remove the quotes in red:


    =DLookUp("CURRENTSTOCK","[INVENTORY]",("[STORE]='" & [Forms]![STOCK]![cboStore] & "'" And "[Fruit]='" & [Forms]![STOCK]![cboFruit] & "'"))
    Works like a charm! Thanks, Paul.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help, and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    LaserTaser is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    5
    Thanks. Now I have hit another road block. The values of current stock and pending quantity are populating correctly, but because they use an expression in the Control Source to populate, the fields can't be edited. How can I get around this?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In inventory apps I've written, I never saved that type of thing. I calculate it from transactions. That said, the second section:

    http://allenbrowne.com/casu-14.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    LaserTaser is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    5
    Quote Originally Posted by pbaldy View Post
    In inventory apps I've written, I never saved that type of thing. I calculate it from transactions. That said, the second section:

    http://allenbrowne.com/casu-14.html

    I'm not sure I follow what was posted in that link. Here is the backstory behind current stock and pending quantity. The values are initially imported into the database. It was later discovered that the user does manual adjustments to the numbers and those numbers weren't always matching what was imported. So, the form will allow the user to see what was imported for the current stock and pending quantity. There will be only a few instances were the numbers have to be adjusted. There is no correlation between current stock and pending quantity (no calculations to be performed).

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The relevant section is "You want to store a calculated result anyway?". You place your DLookup() value into a textbox with code.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    LaserTaser is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    5
    Quote Originally Posted by pbaldy View Post
    The relevant section is "You want to store a calculated result anyway?". You place your DLookup() value into a textbox with code.
    Ok. So I got it to work. I created an additional textbox for the new current stock and used the following code:

    Code:
    Private Sub txtNewCurrentStock_AfterUpdate()
    Dim strSQL As String
    strSQL = "UPDATE [INVENTORY] SET CURRENTSTOCK = '" & Me.txtNewCurrentStock.Text & "' WHERE [Forms]![STOCK]![cboStore] = [item list].[Store] AND [Forms]![STOCK]![cboFruit] = [INVENTORY].[FRUIT]"
    DoCmd.RunSQL strSQL
    End Sub
    Paul, thanks for getting my mind thinking in the right direction.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 2
    Last Post: 06-19-2012, 08:30 AM
  2. Replies: 3
    Last Post: 04-09-2012, 10:16 AM
  3. Replies: 1
    Last Post: 05-25-2011, 08:37 AM
  4. Replies: 1
    Last Post: 03-27-2010, 06:13 AM
  5. Replies: 1
    Last Post: 08-26-2009, 10:45 AM

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