Help with a Case Statement 1

[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

 

[DougP]

Let me get this strait.

On a form you have textboxes with information in them,
depending on what's in those textboxes, you want "ONLY" certain reason codes to be available in the combo box and nothing else to choose from????

Or do you want to Multi-Select the reason codes in the combo out of a larger list.

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[bryn30]

hey DougP,
Thanks for the help on this.
I want "ONLY" certain reason codes to be available in the combo box and nothing else to choose from.

 

[JoeMiller]

Make a Reason code table (tblReasonCode), have the productID as one field and the Reasons as the other field. Here's how the table could look with the sample data you've provided:

[tt]
ProductID ReasonCode
========= ==========
IA Missing Information
IA MACD Order Clarity
IA Invalid Order
DIA Missing Information
DIA MACD Order Clarity
DIA Invalid Order
DSL National Sales Form
DSL Market Cancel
DSL Does Not Qualify
[/tt]

Now the combo box can use your ProductID field on the form to look at ReasonCode table and get the appropriate selections for the current ProductID. The select statement for the combo source is this:

[tt]
SELECT ReasonCode FROM tblReasonCode WHERE ProductID = [Forms]![MyFormName]![ProductID];
[/tt]

Then the last thing you need to do is issue a requery of the combo box to make sure that the correct reason codes are always displayed. So in the OnEnter event of the combo box (cboReasonCode) put this code:

cboReasonCode.Requery

HTH Joe Miller
joe.miller@flotech.net

 

[bryn30]

Thanks Joe
It works!!!

PS. ok I am curious what are the pros/cons to using case statements on a form opposed to building tables and queries to get the same data?

 

[JoeMiller]

Number of possibilities basically, I don't like to use a select case (personally) if I have more than 5 options. That's a personal preference though, others don't have issues with it.

For your situation I would never have done a Case statement because it's not the right kind of solution. I try to stay out of VBA as much as possible and let tables/queries do the work because Access is optimized for that. Joe Miller
joe.miller@flotech.net