Help with Case Statement

[bryn30]

I have one ComboBox that is used to display reason codes. however, certian reson codes need to be displayed when other fields are equal.

example:
[Orderstatus] = 'pending NSC'
[productID] = 'IA' or 'DIA'
[exitStatus] = 'Move to pend-foc' or 'pend-info'

I need the [ReasonCode] to populate with the following; Missing Information
MACD Order Clarity
Invalid order

OR

[Orderstatus] = 'pending NSC'
[productID] = 'DSL'
[exitStatus] = 'Move to pend-foc' or 'pend-info'

I need the [ReasonCode] to populate with the following;
National Sales Form
Market cancel
Does not qualify

there are about 10 other combinations, this should get me enough to get started.
thanks in advance

 

[joelindquist]

I would make a table with 2 or more fields(productID,OrderStatus,ExitStatus,ReasonCode,) as a control table. Use all of the fields as a composite primary key. Either do this, or pull from a table you already have which contains all of the possible combinations.
Then write some VB code for the after update events of Orderstatus,productID,exitStatus. (Duplicate code, or call same module)

Inside the module, include in some form or another the following:
SQLstatement = "SELECT ReasonCode FROM tablename WHERE productID LIKE '" + me.productID + "' GROUP BY ReasonCode;"
Me.ReasonCode.RowSourceType = "Table/Query"
Me.ReasonCode.RowSource = SQLstatement
[/b]
This will update the list box with the values that the query returns. You shouldn't need to do a case statement if you can massage this to work. Also note that if all three vary, just build the query on all three fields. Let me know if you need more specific info. joelindquist@homelinx.com (I do not check this site that frequently)