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?