INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

CASE statement with two conditions

CASE statement with two conditions

(OP)

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.

RE: CASE statement with two conditions

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

CODE -->

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 

RE: CASE statement with two conditions

(OP)

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.

RE: CASE statement with two conditions

(OP)

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

RE: CASE statement with two conditions

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

RE: CASE statement with two conditions

>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

CODE --> VBA

Select Case True
    Case Combo1 = value1 and Combo2 = value2
    ' do stuff
    Case Combo1 = value1 and Combo2 = value3
    ' do stuff
    case ... 'etc
End Select 

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

RE: CASE statement with two conditions

(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...

CODE -->

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

IF numeric values:

CODE -->

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.

RE: CASE statement with two conditions

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

RE: CASE statement with two conditions

Quote:

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.

Quote:

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.

RE: CASE statement with two conditions

(OP)

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.

RE: CASE statement with two conditions

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

RE: CASE statement with two conditions

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

Duane
Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close