selecting an alternative item from a combo box

  • Thread starter Thread starter David Gartrell
  • Start date Start date
D

David Gartrell

Hi there,

I don't know if anyone is able to help me but here goes...


I have a combo box that has a list of 6 products that the user can choose
from. Is there a way of setting the data base so that, if for example, the
user chooses the first item in the combo box, the database would
automatically substitute it for the 6th item in the combo box instead? This
is an order processing database so this may be necessary if the first item
is out of stock and the 6th item is provided as a substitute.

I hope someone is able to help. I hope i've made myself clear enough but if
anyone needs any more info then please do let me know.

Thanks

David
 
Sure, you can do that. You would need code in the After Update event of the
combo box or a function called from the After Update event to make that
determination and set the value. Here is some example code that would do
that. In the After Update event, call a function that has the logic in it
that returns either the original value passed if no change is required or the
new value to change it to.

Private Sub MyCombo_AfterUpdate()

Me.MyCombo = CheckForStock(Me.MyCombo)

End Sub

Private Function CheckForStock(strSelected) As String

If DLookUp("[ON_HAND]", "tblInventory", "[ITEM_CODE] = '" & _
strSelected & "'") >= Me.txtQrderQty Then
CheckForStock = strSelected
Else
CheckForStock = DLookup("[SUB_ITEM]", "tblInventory '" & _
strSelected & "'"
End If
End Function
 
Back
Top