How to Nest Select Case?

[S Jackson]

I have built a search form. It contains an onClick event behind a Search
button that will open up a subform called [fsubResults] based on the
criteria entered into txtSearch. First, the user must make a selection
from:

Combo box No. 1 [cmboSearch] contains three choices:
1. Search Cases, brings up
- Combo Box No.2 [Typecmbo] containing four choices:
a. Case Name
b. DHS No.
c. Vendor
d. Action
- AND for each selection in [Typecmbo], the user can select
one of three option buttons [OptnCase]:
i. Open
ii. Closed
iii. All

2. Search LTCR Staff

3. Search Facility Attorneys

I started out with code to allow the user to select from [cmboSearch] and
then the Option group

Select cmboSearch.Value
Case "Search Cases"
Case "Search LTCR Staff"
Case "Search Facility Attorneys"

Under Case "Search Cases" I have set a stLinkCriteria , then an if statment
for the value of the OptnCase, and then built an strSQL string incorporating
the stLinkCriteria and the value of the OptnCase in the WHERE statement:

& "WHERE (((tblCaseInfo.CaseName)" & stLinkCriteria & ") And
((tblStatus.ClosedDate) Is Null)) " _

Then I decided to be brave and added the Typcmbo to allow the user to search
for a record using other values in addition to "CaseName." Selections from
the Typcmbo will change the WHERE statement of the SQL. How do I nest
Select Case statement? I would like to use Case Select for the Typecmbo and
then a third for the OptnCase.

I know I can build a thousand strSQL strings, but it seems to me there has
to be an easier way to do this.

TIA
S. Jackson

 

[Graham R Seach]

Select Case abc
Case 1
Select Case xyz
Case 1
Select Case fff
Case 1
Case 2
Case 3
End Select
Case 2
End Select
Case 2
Select Case xyz
Case 1
Select Case fff
Case 1
Case 2
Case 3
End Select
Case 2
End Select
Case 3
Select Case xyz
Case 1
Select Case fff
Case 1
Case 2
Case 3
End Select
Case 2
End Select
End Select

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[PC Datasheet]

Hi Shelly,

Look at the Switch and Choose functions in the Help file. They might be
something you can use. Another thought, some times it is more code economical to
work with what was not selected than to work with what was selected. Just
something to consider!

Did you get that secondexcellence award yet?

Steve

 

[S. Jackson]

:-D I come on here so much for help that I should probably share my awards
with y'all!

I have some really good books to read for Access VBA. Wished I had time to
get to them instead of trying to do everything on the fly.

I love doing this project though. I would like to go back to college for a
degree in computer science and someday do this for a living. I'd be broke
cuz I don't know a damn thing, but I'd be doing something I like!

Thanks bunches!

Shelly

Hi Shelly,

Look at the Switch and Choose functions in the Help file. They might be
something you can use. Another thought, some times it is more code economical to
work with what was not selected than to work with what was selected. Just
something to consider!

Did you get that secondexcellence award yet?

Steve

I have built a search form. It contains an onClick event behind a Search
button that will open up a subform called [fsubResults] based on the
criteria entered into txtSearch. First, the user must make a selection
from:

Combo box No. 1 [cmboSearch] contains three choices:
1. Search Cases, brings up
- Combo Box No.2 [Typecmbo] containing four choices:
a. Case Name
b. DHS No.
c. Vendor
d. Action
- AND for each selection in [Typecmbo], the user can select
one of three option buttons [OptnCase]:
i. Open
ii. Closed
iii. All

2. Search LTCR Staff

3. Search Facility Attorneys

I started out with code to allow the user to select from [cmboSearch] and
then the Option group

Select cmboSearch.Value
Case "Search Cases"
Case "Search LTCR Staff"
Case "Search Facility Attorneys"

Under Case "Search Cases" I have set a stLinkCriteria , then an if statment
for the value of the OptnCase, and then built an strSQL string incorporating
the stLinkCriteria and the value of the OptnCase in the WHERE statement:

& "WHERE (((tblCaseInfo.CaseName)" & stLinkCriteria & ") And
((tblStatus.ClosedDate) Is Null)) " _

Then I decided to be brave and added the Typcmbo to allow the user to search
for a record using other values in addition to "CaseName." Selections from
the Typcmbo will change the WHERE statement of the SQL. How do I nest
Select Case statement? I would like to use Case Select for the Typecmbo and
then a third for the OptnCase.

I know I can build a thousand strSQL strings, but it seems to me there has
to be an easier way to do this.

TIA
S. Jackson

 

[PC Datasheet]

Hi Shelly,

Do you have Beginning Access XXX VBA, Sussman and Smith, WROX? It's excellent
for learning VBA and very easy to read. Also the 2000 edition has a large
appendix of very valuable information - I put it right up there if not over the
Access Developer's Handbook by Getz! The next time you are looking for books, go
to BestBookBuys.com. My wife bought me several big time Access reference books
there for Christmas for under 10 bucks each as used. Don't let "used" mislead
you, one of the books came in the original publisher's box unopened!

Steve

:-D I come on here so much for help that I should probably share my awards
with y'all!

I have some really good books to read for Access VBA. Wished I had time to
get to them instead of trying to do everything on the fly.

I love doing this project though. I would like to go back to college for a
degree in computer science and someday do this for a living. I'd be broke
cuz I don't know a damn thing, but I'd be doing something I like!

Thanks bunches!

Shelly

Hi Shelly,

Look at the Switch and Choose functions in the Help file. They might be
something you can use. Another thought, some times it is more code economical to
work with what was not selected than to work with what was selected. Just
something to consider!

Did you get that secondexcellence award yet?

Steve

I have built a search form. It contains an onClick event behind a Search
button that will open up a subform called [fsubResults] based on the
criteria entered into txtSearch. First, the user must make a selection
from:

Combo box No. 1 [cmboSearch] contains three choices:
1. Search Cases, brings up
- Combo Box No.2 [Typecmbo] containing four choices:
a. Case Name
b. DHS No.
c. Vendor
d. Action
- AND for each selection in [Typecmbo], the user can select
one of three option buttons [OptnCase]:
i. Open
ii. Closed
iii. All

2. Search LTCR Staff

3. Search Facility Attorneys

I started out with code to allow the user to select from [cmboSearch] and
then the Option group

Select cmboSearch.Value
Case "Search Cases"
Case "Search LTCR Staff"
Case "Search Facility Attorneys"

Under Case "Search Cases" I have set a stLinkCriteria , then an if statment
for the value of the OptnCase, and then built an strSQL string incorporating
the stLinkCriteria and the value of the OptnCase in the WHERE statement:

& "WHERE (((tblCaseInfo.CaseName)" & stLinkCriteria & ") And
((tblStatus.ClosedDate) Is Null)) " _

Then I decided to be brave and added the Typcmbo to allow the user to search
for a record using other values in addition to "CaseName." Selections from
the Typcmbo will change the WHERE statement of the SQL. How do I nest
Select Case statement? I would like to use Case Select for the Typecmbo and
then a third for the OptnCase.

I know I can build a thousand strSQL strings, but it seems to me there has
to be an easier way to do this.

TIA
S. Jackson

 

[PC Datasheet]

PS What dies IMAO stand for?

And by the way, my answer to that post meant 'If it ain't broken, don't fix it"


Steve

:-D I come on here so much for help that I should probably share my awards
with y'all!

I have some really good books to read for Access VBA. Wished I had time to
get to them instead of trying to do everything on the fly.

I love doing this project though. I would like to go back to college for a
degree in computer science and someday do this for a living. I'd be broke
cuz I don't know a damn thing, but I'd be doing something I like!

Thanks bunches!

Shelly

Hi Shelly,

Look at the Switch and Choose functions in the Help file. They might be
something you can use. Another thought, some times it is more code economical to
work with what was not selected than to work with what was selected. Just
something to consider!

Did you get that secondexcellence award yet?

Steve

I have built a search form. It contains an onClick event behind a Search
button that will open up a subform called [fsubResults] based on the
criteria entered into txtSearch. First, the user must make a selection
from:

Combo box No. 1 [cmboSearch] contains three choices:
1. Search Cases, brings up
- Combo Box No.2 [Typecmbo] containing four choices:
a. Case Name
b. DHS No.
c. Vendor
d. Action
- AND for each selection in [Typecmbo], the user can select
one of three option buttons [OptnCase]:
i. Open
ii. Closed
iii. All

2. Search LTCR Staff

3. Search Facility Attorneys

I started out with code to allow the user to select from [cmboSearch] and
then the Option group

Select cmboSearch.Value
Case "Search Cases"
Case "Search LTCR Staff"
Case "Search Facility Attorneys"

Under Case "Search Cases" I have set a stLinkCriteria , then an if statment
for the value of the OptnCase, and then built an strSQL string incorporating
the stLinkCriteria and the value of the OptnCase in the WHERE statement:

& "WHERE (((tblCaseInfo.CaseName)" & stLinkCriteria & ") And
((tblStatus.ClosedDate) Is Null)) " _

Then I decided to be brave and added the Typcmbo to allow the user to search
for a record using other values in addition to "CaseName." Selections from
the Typcmbo will change the WHERE statement of the SQL. How do I nest
Select Case statement? I would like to use Case Select for the Typecmbo and
then a third for the OptnCase.

I know I can build a thousand strSQL strings, but it seems to me there has
to be an easier way to do this.

TIA
S. Jackson

 

[S. Jackson]

L-M-A-O: "laughing my ass off"

:-D

PS What dies IMAO stand for?

And by the way, my answer to that post meant 'If it ain't broken, don't fix it"


Steve

:-D I come on here so much for help that I should probably share my awards
with y'all!

I have some really good books to read for Access VBA. Wished I had time to
get to them instead of trying to do everything on the fly.

I love doing this project though. I would like to go back to college for a
degree in computer science and someday do this for a living. I'd be broke
cuz I don't know a damn thing, but I'd be doing something I like!

Thanks bunches!

Shelly

Hi Shelly,

Look at the Switch and Choose functions in the Help file. They might be
something you can use. Another thought, some times it is more code economical to
work with what was not selected than to work with what was selected. Just
something to consider!

Did you get that secondexcellence award yet?

Steve


I have built a search form. It contains an onClick event behind a Search
button that will open up a subform called [fsubResults] based on the
criteria entered into txtSearch. First, the user must make a selection
from:

Combo box No. 1 [cmboSearch] contains three choices:
1. Search Cases, brings up
- Combo Box No.2 [Typecmbo] containing four choices:
a. Case Name
b. DHS No.
c. Vendor
d. Action
- AND for each selection in [Typecmbo], the user can select
one of three option buttons [OptnCase]:
i. Open
ii. Closed
iii. All

2. Search LTCR Staff

3. Search Facility Attorneys

I started out with code to allow the user to select from

[cmboSearch]
and

then the Option group

Select cmboSearch.Value
Case "Search Cases"
Case "Search LTCR Staff"
Case "Search Facility Attorneys"

Under Case "Search Cases" I have set a stLinkCriteria , then an if statment
for the value of the OptnCase, and then built an strSQL string incorporating
the stLinkCriteria and the value of the OptnCase in the WHERE statement:

& "WHERE (((tblCaseInfo.CaseName)" & stLinkCriteria & ") And
((tblStatus.ClosedDate) Is Null)) " _

Then I decided to be brave and added the Typcmbo to allow the user

to
search

for a record using other values in addition to "CaseName."

Selections
from

the Typcmbo will change the WHERE statement of the SQL. How do I nest
Select Case statement? I would like to use Case Select for the

Typecmbo
and

then a third for the OptnCase.

I know I can build a thousand strSQL strings, but it seems to me

there
has

to be an easier way to do this.

TIA
S. Jackson

 

[S. Jackson]

I have the Access 2000 VBA Developer's Handbook series by Susann Novalis. I
haven't gotten to far into it and I am already lost. I am definitely going
to check out the book you recommended.

Oh, I also have a copy of Access 97 Developer's Handbook by O'brien, Pogge
and White - got it for $3.50.

Shelly

Hi Shelly,

Do you have Beginning Access XXX VBA, Sussman and Smith, WROX? It's excellent
for learning VBA and very easy to read. Also the 2000 edition has a large
appendix of very valuable information - I put it right up there if not over the
Access Developer's Handbook by Getz! The next time you are looking for books, go
to BestBookBuys.com. My wife bought me several big time Access reference books
there for Christmas for under 10 bucks each as used. Don't let "used" mislead
you, one of the books came in the original publisher's box unopened!

Steve

:-D I come on here so much for help that I should probably share my awards
with y'all!

I have some really good books to read for Access VBA. Wished I had time to
get to them instead of trying to do everything on the fly.

I love doing this project though. I would like to go back to college for a
degree in computer science and someday do this for a living. I'd be broke
cuz I don't know a damn thing, but I'd be doing something I like!

Thanks bunches!

Shelly

Hi Shelly,

Look at the Switch and Choose functions in the Help file. They might be
something you can use. Another thought, some times it is more code economical to
work with what was not selected than to work with what was selected. Just
something to consider!

Did you get that secondexcellence award yet?

Steve


I have built a search form. It contains an onClick event behind a Search
button that will open up a subform called [fsubResults] based on the
criteria entered into txtSearch. First, the user must make a selection
from:

Combo box No. 1 [cmboSearch] contains three choices:
1. Search Cases, brings up
- Combo Box No.2 [Typecmbo] containing four choices:
a. Case Name
b. DHS No.
c. Vendor
d. Action
- AND for each selection in [Typecmbo], the user can select
one of three option buttons [OptnCase]:
i. Open
ii. Closed
iii. All

2. Search LTCR Staff

3. Search Facility Attorneys

I started out with code to allow the user to select from

[cmboSearch]
and

then the Option group

Select cmboSearch.Value
Case "Search Cases"
Case "Search LTCR Staff"
Case "Search Facility Attorneys"

Under Case "Search Cases" I have set a stLinkCriteria , then an if statment
for the value of the OptnCase, and then built an strSQL string incorporating
the stLinkCriteria and the value of the OptnCase in the WHERE statement:

& "WHERE (((tblCaseInfo.CaseName)" & stLinkCriteria & ") And
((tblStatus.ClosedDate) Is Null)) " _

Then I decided to be brave and added the Typcmbo to allow the user

to
search

for a record using other values in addition to "CaseName."

Selections
from

the Typcmbo will change the WHERE statement of the SQL. How do I nest
Select Case statement? I would like to use Case Select for the

Typecmbo
and

then a third for the OptnCase.

I know I can build a thousand strSQL strings, but it seems to me

there
has

to be an easier way to do this.

TIA
S. Jackson