A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using the "Select Case..." function



 
 
Thread Tools Display Modes
  #1  
Old April 14th, 2010, 06:11 PM posted to microsoft.public.access.reports
Telesphore[_2_]
external usenet poster
 
Posts: 33
Default Using the "Select Case..." function

I am trying the following code in a TextBox of a Report.
We need to type the "Department of... " according to the different program a
student is registered.

Any suggestion wilol be appreciated.

Thank you




Function EquivalenceDepartment(strTypeDepartment As Variant) As String
Select Case strTypeDepartment

Case ProgBPh = Yes
EquivalenceDepartment = "Department of philosophy"
Case ProgMPh = Yes
EquivalenceDepartment = "Department of philosophy"
Case ProgCPh = Yes
EquivalenceDepartment = "Department of philosophy"
Case ProgBThIFTM = Yes
EquivalenceDepartment = "Department of theology"
Case ProgBThLatran = Yes
EquivalenceDepartment = "Department of theology"
Case ProgCTh = Yes
EquivalenceDepartment = "Department of theology"
Case ProgMThP = Yes
EquivalenceDepartment = "Department of pastoral"
Case ProgDESS = Yes
EquivalenceDepartment = "Department of pastoral"
Case ProgMDiv = Yes
EquivalenceDepartment = "Department of pastoral"
Case ProgCPF = Yes
EquivalenceDepartment = "Department of pastoral"
Case ProgCSPI = Yes
EquivalenceDepartment = "Department of pastoral"
Case ProgBA = Yes
EquivalenceDepartment = "Department of philosophy"
Case ProgAU = Yes
EquivalenceDepartment = "Other"
End Select
End Function

  #2  
Old April 14th, 2010, 07:42 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Using the "Select Case..." function

Telesphore wrote:
I am trying the following code in a TextBox of a Report.
We need to type the "Department of... " according to the different program a
student is registered.

Function EquivalenceDepartment(strTypeDepartment As Variant) As String
Select Case strTypeDepartment

Case ProgBPh = Yes
EquivalenceDepartment = "Department of philosophy"
Case ProgMPh = Yes
EquivalenceDepartment = "Department of philosophy"
Case ProgCPh = Yes
EquivalenceDepartment = "Department of philosophy"
Case ProgBThIFTM = Yes
EquivalenceDepartment = "Department of theology"
Case ProgBThLatran = Yes
EquivalenceDepartment = "Department of theology"
Case ProgCTh = Yes
EquivalenceDepartment = "Department of theology"
Case ProgMThP = Yes
EquivalenceDepartment = "Department of pastoral"
Case ProgDESS = Yes
EquivalenceDepartment = "Department of pastoral"
Case ProgMDiv = Yes
EquivalenceDepartment = "Department of pastoral"
Case ProgCPF = Yes
EquivalenceDepartment = "Department of pastoral"
Case ProgCSPI = Yes
EquivalenceDepartment = "Department of pastoral"
Case ProgBA = Yes
EquivalenceDepartment = "Department of philosophy"
Case ProgAU = Yes
EquivalenceDepartment = "Other"
End Select
End Function



It sort of looks like the Prog... are check boxes and you
want to translate them to text. If so, try using:

Select Case True
Case ProgBPh, ProgMPh, ProgCPh, ProgBA
EquivalenceDepartment = "Department of philosophy"
Case ProgBThIFTM, ProgBThLatran, ProgCTh,
EquivalenceDepartment = "Department of theology"
Case ProgMThP, ProgDESS, ProgMDiv, ProgCPF, ProgCSPI
EquivalenceDepartment = "Department of pastoral"
Case ProgAU
EquivalenceDepartment = "Other"
End Select


BUT, since you only have one text box, it appears that only
one of the check boxes can be selected. If that's correct,
you should be using an OptionGroup so more than one can not
be selected. In this case, the code would be very
different.

A warning is appropriate here. The text you are inserting
into the text box is data and data should not be hidden away
in code. Instead, it should be stored in a table so it can
be changed without having to modify the cade.

--
Marsh
MVP [MS Access]
  #3  
Old April 14th, 2010, 09:32 PM posted to microsoft.public.access.reports
Telesphore[_2_]
external usenet poster
 
Posts: 33
Default Using the "Select Case..." function

Thank you,

I learn something new with the instruction "Select Case True".

But I think the rest of the code will not work, since you have already said
"it appears that only one of the check boxes can be selected"..
Because a student can be registered in more than one program, so 2 or 3
check boxes, we will have to introduce a new field [Department] in the table
[tblIncriptionTable] so the that the secretary will have to type only one
department for each student.

Thank you again.


"Marshall Barton" a écrit dans le message de
...
Telesphore wrote:
I am trying the following code in a TextBox of a Report.
We need to type the "Department of... " according to the different program
a
student is registered.

Function EquivalenceDepartment(strTypeDepartment As Variant) As String
Select Case strTypeDepartment

Case ProgBPh = Yes

....

It sort of looks like the Prog... are check boxes and you
want to translate them to text. If so, try using:

Select Case True
Case ProgBPh, ProgMPh, ProgCPh, ProgBA
EquivalenceDepartment = "Department of philosophy"
Case ProgBThIFTM, ProgBThLatran, ProgCTh,
EquivalenceDepartment = "Department of theology"
Case ProgMThP, ProgDESS, ProgMDiv, ProgCPF, ProgCSPI
EquivalenceDepartment = "Department of pastoral"
Case ProgAU
EquivalenceDepartment = "Other"
End Select


BUT, since you only have one text box, it appears that only
one of the check boxes can be selected. If that's correct,
you should be using an OptionGroup so more than one can not
be selected. In this case, the code would be very
different.

A warning is appropriate here. The text you are inserting
into the text box is data and data should not be hidden away
in code. Instead, it should be stored in a table so it can
be changed without having to modify the cade.

--
Marsh
MVP [MS Access]


  #4  
Old April 14th, 2010, 11:01 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Using the "Select Case..." function

How do you want to display multiple departments on the form?
Maybe using a subform instead of all those check boxes??

Seems like you need a many-many junction table to keep track
of which students are in which departments.

I don't understand what you are saying about adding a field
to tblIncriptionTable??
--
Marsh
MVP [MS Access]


Telesphore wrote:
I learn something new with the instruction "Select Case True".

But I think the rest of the code will not work, since you have already said
"it appears that only one of the check boxes can be selected"..
Because a student can be registered in more than one program, so 2 or 3
check boxes, we will have to introduce a new field [Department] in the table
[tblIncriptionTable] so the that the secretary will have to type only one
department for each student.

"Marshall Barton" écrit
Telesphore wrote:
I am trying the following code in a TextBox of a Report.
We need to type the "Department of... " according to the different program
a student is registered.

Function EquivalenceDepartment(strTypeDepartment As Variant) As String
Select Case strTypeDepartment

Case ProgBPh = Yes

...

It sort of looks like the Prog... are check boxes and you
want to translate them to text. If so, try using:

Select Case True
Case ProgBPh, ProgMPh, ProgCPh, ProgBA
EquivalenceDepartment = "Department of philosophy"
Case ProgBThIFTM, ProgBThLatran, ProgCTh,
EquivalenceDepartment = "Department of theology"
Case ProgMThP, ProgDESS, ProgMDiv, ProgCPF, ProgCSPI
EquivalenceDepartment = "Department of pastoral"
Case ProgAU
EquivalenceDepartment = "Other"
End Select


BUT, since you only have one text box, it appears that only
one of the check boxes can be selected. If that's correct,
you should be using an OptionGroup so more than one can not
be selected. In this case, the code would be very
different.

A warning is appropriate here. The text you are inserting
into the text box is data and data should not be hidden away
in code. Instead, it should be stored in a table so it can
be changed without having to modify the cade.

  #5  
Old April 15th, 2010, 01:12 AM posted to microsoft.public.access.reports
Telesphore[_2_]
external usenet poster
 
Posts: 33
Default Using the "Select Case..." function


"Marshall Barton" wrote ...
How do you want to display multiple departments on the form?
Maybe using a subform instead of all those check boxes??
Seems like you need a many-many junction table to keep track
of which students are in which departments.
I don't understand what you are saying about adding a field
to tblIncriptionTable??


In the tblInscription I will add the [Departement] field with these 3
values: "Philosophy";"Theology";"Pastoral" in the combo box. So that the
secretary will write only one of these 3 values through the frmInscription
for each student. She will keep on checking the programm boxes.



  #6  
Old April 15th, 2010, 03:42 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Using the "Select Case..." function

Telesphore wrote:
"Marshall Barton" wrote ...
How do you want to display multiple departments on the form?
Maybe using a subform instead of all those check boxes??
Seems like you need a many-many junction table to keep track
of which students are in which departments.
I don't understand what you are saying about adding a field
to tblIncriptionTable??


In the tblInscription I will add the [Departement] field with these 3
values: "Philosophy";"Theology";"Pastoral" in the combo box. So that the
secretary will write only one of these 3 values through the frmInscription
for each student. She will keep on checking the programm boxes.



What combo box???

If a student can be in more that one department, how can one
field keep track of more than one thing?

--
Marsh
MVP [MS Access]
  #7  
Old April 17th, 2010, 08:12 PM posted to microsoft.public.access.reports
Telesphore[_2_]
external usenet poster
 
Posts: 33
Default Using the "Select Case..." function

Thanks and sorry to answer lately.

You are right, about the problem of multiple departments. It will be easier
to reduce the multiple programst to one. The departments are only 3 of them,
meanwhile the programs are 14. So the the secretary will decide the
department to choose.

Thank you again.

"Marshall Barton" a écrit dans le message de
...
Telesphore wrote:
"Marshall Barton" wrote ...
How do you want to display multiple departments on the form?
Maybe using a subform instead of all those check boxes??
Seems like you need a many-many junction table to keep track
of which students are in which departments.
I don't understand what you are saying about adding a field
to tblIncriptionTable??


In the tblInscription I will add the [Departement] field with these 3
values: "Philosophy";"Theology";"Pastoral" in the combo box. So that the
secretary will write only one of these 3 values through the frmInscription
for each student. She will keep on checking the programm boxes.



What combo box???

If a student can be in more that one department, how can one
field keep track of more than one thing?

--
Marsh
MVP [MS Access]


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:38 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.