implimenting select...Case

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

this is more or less a pure coding problem in VB

I have a 'StudentSearch' form which has many criteria, Fname, Lname,
University, Course etc which are put in through text boxes or drop down lists
and the results of which appear in a listbox called Results.

This works fine except that ive got 7 queries for the rowsource of Result
which are defined through the following If..else statement

<!--
Dim Search As String

'if a uni is picked and a course is not then change the type of query

If (Not IsNull(Me![UniID])) And IsNull(Me![Course]) Then
Search = "studentsearch2"
Else
'find all the students in a building
If (Not IsNull(Me![Building])) And IsNull(Me![Block]) And
IsNull(Me![Flat]) And IsNull(Me![Room]) Then
Search = "StudentSearch3"
Else
'find all the students ina block in a building
If (Not IsNull(Me![Building])) And (Not IsNull(Me![Block])) And
IsNull(Me![Flat]) And IsNull(Me![Room]) Then
Search = "StudentSearch4"
Else
'Find all the students in a flat in a block in a building
If (Not IsNull(Me![Building])) And (Not IsNull(Me![Block]))
And (Not IsNull(Me![Flat])) And IsNull(Me![Room]) Then
Search = "StudentSearch5"
Else
'impliment the default search
Search = "studentsearch"
End If
End If
End If
End If

With Me![results]
.RowSource = Search
.Requery
End With

-->

Getting silly isnt it?
Its going to get worse cos ive been told that further criteria will be
added and that the users will want to be able to search for students in ever
more imaginative combinations of criteria.
How can i change this to a Select..Case statement?

With much thanks,

Amit
 
This setup doesn't really lend itself to a Select Case syntax, as there
isn't a single field/data source that is being used. But perhaps using the
Switch function:

Search = Switch( _
Not IsNull(Me![UniID])) And IsNull(Me![Course]), _
"studentsearch2", _
Not IsNull(Me![Building])) And IsNull(Me![Block]) And _
IsNull(Me![Flat]) And IsNull(Me![Room]), _
"StudentSearch3", _
Not IsNull(Me![Building])) And (Not IsNull(Me![Block])) And _
IsNull(Me![Flat]) And IsNull(Me![Room]), _
"StudentSearch4", _
Not IsNull(Me![Building])) And (Not IsNull(Me![Block])) And _
(Not IsNull(Me![Flat])) And IsNull(Me![Room]), _
"StudentSearch5", _
1=1, _
"studentsearch" _
)


The above uses "1=1" as the "no other conditions apply" condition (the final
"Else" branch in your original code). Switch evaluates each condition, and
the first one that is true causes the result to be selected).
 
I'm not sure Select..Case will help you. Select..Case is used to check a
single object for multiple possible values like this:
Select Case strUserName
Case "George"
'do something if the user is George
Case "Mary", "Jane", "Sally"
'do something if the user is Mary, Jane or Sally
Case "James"
'do something if the user is James
Case Else
'do something if the user is anyone else
End Select
 
To use a Select Case construct in a situation like this, you'd have to
reverse the usual logic (which others have demonstrated) and write it
something like this ...

Select Case True
Case (Not IsNull(Me![UniID])) And IsNull(Me![Course])
Search = "studentsearch2"
Case (Not IsNull(Me![Building])) And IsNull(Me![Block]) And
IsNull(Me![Flat]) And IsNull(Me![Room])
Search = "studentsearch3"
etc., etc., etc.
End Select
 
Brendan Reynolds said:
To use a Select Case construct in a situation like this, you'd have to
reverse the usual logic (which others have demonstrated) and write it
something like this ...

Select Case True
Case (Not IsNull(Me![UniID])) And IsNull(Me![Course])
Search = "studentsearch2"
Case (Not IsNull(Me![Building])) And IsNull(Me![Block]) And
IsNull(Me![Flat]) And IsNull(Me![Room])
Search = "studentsearch3"
etc., etc., etc.
End Select
Click to expand...


Now this is what I call "thinking outside the box"! < g >
Neat.
 
That is indeed a nice idea that I'd never seen before.

Having said that, I don't think I'd approach the problem in the same way.
Deciding what query to use by looking at what fields the user has completed
is, I think, going to be confusing for them.... particularly if you're going
to be getting more complicated in the future. I'd prefer to go for
something along the lines of...

1) Have a combo/option group/whatever control that allows the user to pick
what type of search they want to do.
2) Use the after update event of that control to display the text
boxes/combos/whatever that are relevant for that search.
3) The query that you use is then decided by a straight select case
referring back to the choice made in 1)

That way, the user decides what exactly what it is that they're searching
for, and is then presented with the controls that they need to complete.
I'd suggest that that is way more user friendly.


Brendan Reynolds said:
To use a Select Case construct in a situation like this, you'd have to
reverse the usual logic (which others have demonstrated) and write it
something like this ...

Select Case True
Case (Not IsNull(Me![UniID])) And IsNull(Me![Course])
Search = "studentsearch2"
Case (Not IsNull(Me![Building])) And IsNull(Me![Block]) And
IsNull(Me![Flat]) And IsNull(Me![Room])
Search = "studentsearch3"
etc., etc., etc.
End Select
--
Brendan Reynolds (MVP)


in message news:[email protected]...
this is more or less a pure coding problem in VB

I have a 'StudentSearch' form which has many criteria, Fname, Lname,
University, Course etc which are put in through text boxes or drop down
lists
and the results of which appear in a listbox called Results.

This works fine except that ive got 7 queries for the rowsource of Result
which are defined through the following If..else statement

<!--
Dim Search As String

'if a uni is picked and a course is not then change the type of query

If (Not IsNull(Me![UniID])) And IsNull(Me![Course]) Then
Search = "studentsearch2"
Else
'find all the students in a building
If (Not IsNull(Me![Building])) And IsNull(Me![Block]) And
IsNull(Me![Flat]) And IsNull(Me![Room]) Then
Search = "StudentSearch3"
Else
'find all the students ina block in a building
If (Not IsNull(Me![Building])) And (Not IsNull(Me![Block])) And
IsNull(Me![Flat]) And IsNull(Me![Room]) Then
Search = "StudentSearch4"
Else
'Find all the students in a flat in a block in a building
If (Not IsNull(Me![Building])) And (Not IsNull(Me![Block]))
And (Not IsNull(Me![Flat])) And IsNull(Me![Room]) Then
Search = "StudentSearch5"
Else
'impliment the default search
Search = "studentsearch"
End If
End If
End If
End If

With Me![results]
.RowSource = Search
.Requery
End With

-->

Getting silly isnt it?
Its going to get worse cos ive been told that further criteria will be
added and that the users will want to be able to search for students in
ever
more imaginative combinations of criteria.
How can i change this to a Select..Case statement?

With much thanks,

Amit
Click to expand...
Click to expand...
 
I agree! :-)

And of course, the 'Select Case True' trick, while not often seen in VBA, is
not my idea. I picked it up somewhere years ago, I can't remember where.

--
Brendan Reynolds (MVP)

Rob Oldfield said:
That is indeed a nice idea that I'd never seen before.

Having said that, I don't think I'd approach the problem in the same way.
Deciding what query to use by looking at what fields the user has
completed
is, I think, going to be confusing for them.... particularly if you're
going
to be getting more complicated in the future. I'd prefer to go for
something along the lines of...

1) Have a combo/option group/whatever control that allows the user to pick
what type of search they want to do.
2) Use the after update event of that control to display the text
boxes/combos/whatever that are relevant for that search.
3) The query that you use is then decided by a straight select case
referring back to the choice made in 1)

That way, the user decides what exactly what it is that they're searching
for, and is then presented with the controls that they need to complete.
I'd suggest that that is way more user friendly.


"Brendan Reynolds" <anonymous at discussions dot microsoft dot com> wrote
in
message news:[email protected]...
To use a Select Case construct in a situation like this, you'd have to
reverse the usual logic (which others have demonstrated) and write it
something like this ...

Select Case True
Case (Not IsNull(Me![UniID])) And IsNull(Me![Course])
Search = "studentsearch2"
Case (Not IsNull(Me![Building])) And IsNull(Me![Block]) And
IsNull(Me![Flat]) And IsNull(Me![Room])
Search = "studentsearch3"
etc., etc., etc.
End Select
--
Brendan Reynolds (MVP)


in message news:[email protected]...
this is more or less a pure coding problem in VB

I have a 'StudentSearch' form which has many criteria, Fname, Lname,
University, Course etc which are put in through text boxes or drop down
lists
and the results of which appear in a listbox called Results.

This works fine except that ive got 7 queries for the rowsource of Result
which are defined through the following If..else statement

<!--
Dim Search As String

'if a uni is picked and a course is not then change the type of
query

If (Not IsNull(Me![UniID])) And IsNull(Me![Course]) Then
Search = "studentsearch2"
Else
'find all the students in a building
If (Not IsNull(Me![Building])) And IsNull(Me![Block]) And
IsNull(Me![Flat]) And IsNull(Me![Room]) Then
Search = "StudentSearch3"
Else
'find all the students ina block in a building
If (Not IsNull(Me![Building])) And (Not IsNull(Me![Block])) And
IsNull(Me![Flat]) And IsNull(Me![Room]) Then
Search = "StudentSearch4"
Else
'Find all the students in a flat in a block in a building
If (Not IsNull(Me![Building])) And (Not IsNull(Me![Block]))
And (Not IsNull(Me![Flat])) And IsNull(Me![Room]) Then
Search = "StudentSearch5"
Else
'impliment the default search
Search = "studentsearch"
End If
End If
End If
End If

With Me![results]
.RowSource = Search
.Requery
End With

-->

Getting silly isnt it?
Its going to get worse cos ive been told that further criteria will be
added and that the users will want to be able to search for students in
ever
more imaginative combinations of criteria.
How can i change this to a Select..Case statement?

With much thanks,

Amit
Click to expand...
Click to expand...
Click to expand...
 
Thanks everyone,

I read them when i got into work this morning and have been looking to
impliment the solutions presented by Rob Oldfield, however im stuck on the
2nd part:

How do i get the texts boxes to appear, is this gonig to occur through
altering visiblity properties or is there a way to generate them, as
required, the way i would on a website using JSP?

The true select..case statement was ingenious!

Amit
 
You can only create new form controls at run time if you open the form in
design view, Amit, so usually it's better to create them at design time and
show/hide them at run time by toggling their Visible property.
 
Thank you again Brenden, I'v got a working model of this solution which ill
take forward.

with much regards and thanks

Amit
 
Back
Top