CASE statement with two conditions 4

[misuser2k7]

I am trying to use CASE statement with two conditions. The issue I am experiencing is as follows.

I have a form (Form0) with two combo boxes and a command button. Values from both combo boxes are required to open other unique forms (one form for one pair of values from the two combo boxes) which are used for specific data entry requirements into specific tables after clicking a command button on the first form after selecting specific values from both combo boxes.

The CASE statement is used as shown below:

-----------------------------------------------------------------

Private Sub cmdButton1_click()

Select Case Me.Combobox1 And Me.Combobox2

Case “Value1FromCombobox1” And “Value2FromCombobox”
Docmd.Openform “Form1”

Case “Value2FromCombobox1” And “Value3FromCombobox”
Docmd.Openform “Form2”

.
.
.
.
.
End Select

End Sub
--------------------------------------------------------------------

But nothing happens when the command button is clicked i.e. second form does not open. I don’t get any errors either.

What is the correct syntax for using ‘And’ in the above CASE statement?

Thank you.

 

[MajP]

This makes no sense as written. I am guessing, maybe.

If not isnull(Me.Combobox1) And not isnull(Me.Combobox2) then 
  Docmd.Openform “Form1”
elseif not isnull(me.combobox2) and not isnull(me.combobox3) then
  Docmd.Openform “Form2”
end if

 

[misuser2k7]

Thank you MajP.

I need to use 40 combinations of two values from the two combo boxes.

And the above example should be

-------------------------------

Private Sub cmdButton1_click()

Select Case Me.Combobox1 And Me.Combobox2

Case “Value1FromCombobox1” And “Value2FromCombobox2”
Docmd.Openform “Form1”

Case “Value2FromCombobox1” And “Value3FromCombobox2”
Docmd.Openform “Form2”

.
.
.
.
.
End Select

End Sub
--------------------------------

I want to know if 'And' can be used in a CASE statement as I have used it. If not, I will try 'If-then-elseif-then' option.

Thank you.

 

[misuser2k7]

P.S. There are only two comboxes with multiple values in each combobox not multiple comboboxes.

 

[dhookom]

Sounds un-normalized if you have 40 different forms based on values from 2 combo boxes. If you really want to do this, I would consider creating a table with at least three columns. Two fields/columns would be unique values from combobox1 and combobox2. Another field/column would have the name of the form to open. You can use a DLookup() function to find the name of the form to open.



Duane
Hook'D on Access
MS Access MVP

 

[strongm]

>I want to know if 'And' can be used in a CASE statement as I have used it

Sure, but you need to set it up right.

You are looking for (x AND y) to be TRUE, so that's how we set up the select statement

[blue]Select Case True
    Case Combo1 = value1 and Combo2 = value2
    [green]' do stuff[/green]
    Case Combo1 = value1 and Combo2 = value3
    [green]' do stuff[/green]
    case ... [green]'etc[/green]
End Select[/blue]

Whether this is the right approach is, of course, a different matter ...

 

[Darrylles]

(This initially looked like you were (within the CASE options) checking DIFFERENT combo objects - I now see that you are not).
This is the format of a SELECT CASE...

SELECT CASE cmbValue1 & cmbValue2
   CASE "FredBloggs" : Do This
   CASE "JimSmith"   : Do That
   CASE "JackJones"  : Do T'other
   CASE ELSE
       ...
END SELECT

IF numeric values:

SELECT CASE cmbValue1 + cmbValue2
   CASE 5  : Do This
   CASE 10 : Do That
   CASE 15 : Do T'other 
   CASE ELSE
       ...
END SELECT

You 'acquire' a single VALUE, then you check that single VALUE against many value options.
What this is actually saying in English is:

I have a value.
In the CASE of this single value being X then do this.
In the CASE of this single value being Y then do that. etc

ATB,

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[dhookom]

Isn't anyone else concerned there are 40 different forms? This seems like it might be a trip down a long, winding road.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Isn't anyone else concerned there are 40 different forms?

No. You have already tried to protect the OP from him/her self and they stated they are going to go down this path anyways.

I am considering using the denormalized "chaotic" table because I have experienced problems in the past with linking multiple tables in a query (which becomes the recordsource for the data entry form) where one-to-one relationship is not possible as (to use the above example) one sales team has multiple interactions with the customer organization's division.

Sometimes lessons have to be learned the hard way, and this one will be increasingly painful.

The form design is a natural symptom of the denormalized "Tracking Table". This inefficient and labor intensive workaround is only the tip of the pain iceberg. Wait till they start tackling the reporting and queries. If they properly normalize the data they would need a single form most likely, but instead you end up with a Rube Goldberg machine.

We could assist the OP with a table design that would take minutes to build and a form design likely as fast. The OP's choice to go denormalized will cause them to spend likely tens or hundreds of hours more to initially build and the same amount to add changes or new features.

 

[misuser2k7]

MajP, Duane, StrongM and Darrylles,

Thank you for your inputs.

I decided to take a slightly different approach and make the second combo box “subordinate” to the first combo box as follows:

When a value or option is selected from the first combo box, the second combo box displays a set of values that are related to the selection in the first combo box.

The rowsources of the combo boxes are small, single column tables which can be updated easily with the changes that occur with time or changing situations.

I was making a big mistake in formulating the original issue that led to the need for having "simultaneous" values from the two combo boxes with the AND when in reality the second combo box values are driven by the selection in the first combo box.

Thank you all for your great suggestions. They are really very helpful in making me think in the right direction.

 

[MajP]

Sounds like your tables are more normalized which should make everything a lot simpler.

 

[dhookom]

misuser2k7,
Do you still have about 40 similar forms? If so, how are they different?

Duane
Hook'D on Access
MS Access MVP