Check if text string exists

[Keypad]

Hi,

Using VB code, how do I use Select Case to do an action if a text string
exists in a field within a table. For example, I have a combo box named
DrinkTypes bound to a table named MixedDrinks:

Table name = MixedDrinks
Drink_ID PK
Drinks Type = Text

I want to test for text strings like "Martini, Bloody Mary, etc" using
Select Case then open a MsgBox with mixing instructions for that particular
drink. How do I code something like this in Access?

Thank you in advance!

 

[John W. Vinson]

Hi,

Using VB code, how do I use Select Case to do an action if a text string
exists in a field within a table. For example, I have a combo box named
DrinkTypes bound to a table named MixedDrinks:

Table name = MixedDrinks
Drink_ID PK
Drinks Type = Text

I want to test for text strings like "Martini, Bloody Mary, etc" using
Select Case then open a MsgBox with mixing instructions for that particular
drink. How do I code something like this in Access?

Thank you in advance!

It sounds like you're using inefficient tools - VBA Select Case, Message Box -
where a very simple query would be a lot more efficient (and easier to
implement). Is this intentional? Because it can be done, but I'm not sure that
you really want to do it the hard way!

 

[Keypad]

Hi John,

Since I don't know much, can you show me how it can be done both ways. I
want to learn as much as possible. Thanks in advance.

 

[John W. Vinson]

Hi John,

Since I don't know much, can you show me how it can be done both ways. I
want to learn as much as possible. Thanks in advance.

Please let us know where you're starting. We don't know anything about your
table structure or your level of Access knowledge beyond what we can guess
from your posts here.

You might want to take a look at some of the "getting started" info below if
you're new to Access: apologies if you're well beyond that and in fact seeking
a VBA solution for some good reason:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Keypad]

Hi John,

I found a easy solution in a different forum. I set up a combo box named
cboDrinks with the following query in the RowSource:

SELECT MixedDrinks.Drink_ID, MixedDrinks.Drinks FROM MixedDrinks ORDER BY
MixedDrinks.Drinks;

then I used the following Case statement in VB editor:

Select Case Me.cboDrinks.Column(1)
Case "Bloody Mary"
MsgBox "Bloody Mary"

Case "Whiskey Sour"
MsgBox "Whiskey Sour"
End Select

Now I am able to know what the user selected so now I can go ahead with the
business of completing everything. Thank you very much for your help!

 

[John W. Vinson]

Hi John,

I found a easy solution in a different forum. I set up a combo box named
cboDrinks with the following query in the RowSource:

SELECT MixedDrinks.Drink_ID, MixedDrinks.Drinks FROM MixedDrinks ORDER BY
MixedDrinks.Drinks;

then I used the following Case statement in VB editor:

Select Case Me.cboDrinks.Column(1)
Case "Bloody Mary"
MsgBox "Bloody Mary"

Case "Whiskey Sour"
MsgBox "Whiskey Sour"
End Select

Now I am able to know what the user selected so now I can go ahead with the
business of completing everything. Thank you very much for your help!

Unless you really want to interrupt the flow for the user by popping up a
Msgbox to which they must respond, I'd suggest instead just putting a textbox
on the form with a control source

=cboDrinks.Column(1)

or for that matter just hiding column 0.

You have never yet said WHY you are doing it this way. It's fine that you are,
but as I said, VBA code and Msgbox popups may not be the best way to get done
what you want done.