Two types of case statments

[Afrosheen via AccessMonster.com]

First of all thanks for reading my post.

I have two different types of case statements in two different forms.

Version 1 is based on a field called locat1.
Select Case locat1
Case "Ops"
strcnt = "ops"
End Select

Version 2 is based on a group box.
Select Case grpStaff.Value
Case 1
strcnt = "Ops"
End Select

The additional case statements for each follow suit.
What I'd like to do is to change either one so I can create a module where I
can access one type of case statement for both programs.

 

[Kardan via AccessMonster.com]

Hi Afrosheen

You could possibly do it like this.

Have a Variant parameter in the Sub declaration and then select the case
based on that, such as;

Sub MyProcedure(varValue as Variant)

Select Case varValue
Case "Ops", 1
strcnt = "ops"
End Select

End Sub

Then call the Sub like this;

Call MyProcedure locat1
or
Call MyProcedure grpStaff.Value

Let me know if this works

Regards

Richard

 

[Afrosheen via AccessMonster.com]

Hi and Thanks for the reply. Here is what I have. This is in a function of a
module that I call from the form. The line 220 is a combo box that displays
the results. I keep on getting "Argument not optional" when I run as is. When
I take the variant out and just put in the () then I get "Statements and
labels invalid between select case and first case."

Function deptSelection(varValue As Variant)
30 Select Case locat1
Case "Support Services", 1
40 strcnt = "Support Services"
50 Case "Admin Support", 2
60 strcnt = "Admin Support"
70 Case "Ops", 3
80 strcnt = "Ops"
90 Case "Unit 1", 4
100 strcnt = "Unit 1"
110 Case "Unit 2", 5
120 strcnt = "Unit 2"
130 Case "Unit 3", 6
140 strcnt = "Unit 3"
150 Case "Unit 4", 7
160 strcnt = "Unit 4"
170 Case "Food Services", 8
180 strcnt = "Food Service"
190 Case "OIC", 9
200 strcnt = "OIC"
210 End Select

220 cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & strcnt & "' " & _
"Order by tbl_assignment.unit;"


End Function

Hi Afrosheen

You could possibly do it like this.

Have a Variant parameter in the Sub declaration and then select the case
based on that, such as;

Sub MyProcedure(varValue as Variant)

Select Case varValue
Case "Ops", 1
strcnt = "ops"
End Select

End Sub

Then call the Sub like this;

Call MyProcedure locat1
or
Call MyProcedure grpStaff.Value

Let me know if this works

Regards

Richard

First of all thanks for reading my post.

[quoted text clipped - 15 lines]

What I'd like to do is to change either one so I can create a module where I
can access one type of case statement for both programs.

 

[Kardan via AccessMonster.com]

Hi

Correct code should be;

Function deptSelection(varValue As Variant) As String
Select Case varValue
Case "Support Services", 1
strcnt = "Support Services"
50 Case "Admin Support", 2
60 strcnt = "Admin Support"
70 Case "Ops", 3
80 strcnt = "Ops"
90 Case "Unit 1", 4
100 strcnt = "Unit 1"
110 Case "Unit 2", 5
120 strcnt = "Unit 2"
130 Case "Unit 3", 6
140 strcnt = "Unit 3"
150 Case "Unit 4", 7
160 strcnt = "Unit 4"
170 Case "Food Services", 8
180 strcnt = "Food Service"
190 Case "OIC", 9
200 strcnt = "OIC"
210 End Select

220 deptSelection = strcnt

End Function


Place the following in the calling procedure of the form;

cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & deptSelection(locat1) & "' " & _
"Order by tbl_assignment.unit;"

Do the same for your grpStaff.Value selection

Regards,

Richard

Hi and Thanks for the reply. Here is what I have. This is in a function of a
module that I call from the form. The line 220 is a combo box that displays
the results. I keep on getting "Argument not optional" when I run as is. When
I take the variant out and just put in the () then I get "Statements and
labels invalid between select case and first case."

Function deptSelection(varValue As Variant)
30 Select Case locat1
Case "Support Services", 1
40 strcnt = "Support Services"
50 Case "Admin Support", 2
60 strcnt = "Admin Support"
70 Case "Ops", 3
80 strcnt = "Ops"
90 Case "Unit 1", 4
100 strcnt = "Unit 1"
110 Case "Unit 2", 5
120 strcnt = "Unit 2"
130 Case "Unit 3", 6
140 strcnt = "Unit 3"
150 Case "Unit 4", 7
160 strcnt = "Unit 4"
170 Case "Food Services", 8
180 strcnt = "Food Service"
190 Case "OIC", 9
200 strcnt = "OIC"
210 End Select

220 cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & strcnt & "' " & _
"Order by tbl_assignment.unit;"

End Function

Hi Afrosheen

[quoted text clipped - 29 lines]

 

[Afrosheen via AccessMonster.com]

Sorry about this. I cut and pasted your code and I'm still getting the
"Argument not optional" error. I'm calling the function by:

10 deptSelection

Hi

Correct code should be;

Function deptSelection(varValue As Variant) As String
Select Case varValue
Case "Support Services", 1
strcnt = "Support Services"
50 Case "Admin Support", 2
60 strcnt = "Admin Support"
70 Case "Ops", 3
80 strcnt = "Ops"
90 Case "Unit 1", 4
100 strcnt = "Unit 1"
110 Case "Unit 2", 5
120 strcnt = "Unit 2"
130 Case "Unit 3", 6
140 strcnt = "Unit 3"
150 Case "Unit 4", 7
160 strcnt = "Unit 4"
170 Case "Food Services", 8
180 strcnt = "Food Service"
190 Case "OIC", 9
200 strcnt = "OIC"
210 End Select

220 deptSelection = strcnt

End Function

Place the following in the calling procedure of the form;

cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & deptSelection(locat1) & "' " & _
"Order by tbl_assignment.unit;"

Do the same for your grpStaff.Value selection

Regards,

Richard

Hi and Thanks for the reply. Here is what I have. This is in a function of a
module that I call from the form. The line 220 is a combo box that displays

[quoted text clipped - 36 lines]

 

[Mike Painter]

Sorry about this. I cut and pasted your code and I'm still getting the
"Argument not optional" error. I'm calling the function by:

10 deptSelection

x= deptselection("1")
x= deptselection("support services")
would return "support services" in X

Correct code should be;

Function deptSelection(varValue As Variant) As String
Select Case varValue
Case "Support Services", 1
strcnt = "Support Services"
50 Case "Admin Support", 2
60 strcnt = "Admin Support"
70 Case "Ops", 3
80 strcnt = "Ops"
90 Case "Unit 1", 4
100 strcnt = "Unit 1"
110 Case "Unit 2", 5
120 strcnt = "Unit 2"
130 Case "Unit 3", 6
140 strcnt = "Unit 3"
150 Case "Unit 4", 7
160 strcnt = "Unit 4"
170 Case "Food Services", 8
180 strcnt = "Food Service"
190 Case "OIC", 9
200 strcnt = "OIC"
210 End Select

220 deptSelection = strcnt

End Function

Place the following in the calling procedure of the form;

cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & deptSelection(locat1) &
"' " & _ "Order by tbl_assignment.unit;"

Do the same for your grpStaff.Value selection

Regards,

Richard

Hi and Thanks for the reply. Here is what I have. This is in a
function of a module that I call from the form. The line 220 is a
combo box that displays

[quoted text clipped - 36 lines]

What I'd like to do is to change either one so I can create a
module where I can access one type of case statement for both
programs.

 

[Kardan via AccessMonster.com]

Hi

As I said, you should be using the code;

cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & deptSelection(locat1) & "' " & _
"Order by tbl_assignment.unit;"

You do need to pass the argument for the case select.

Regards

Richard

Sorry about this. I cut and pasted your code and I'm still getting the
"Argument not optional" error. I'm calling the function by:

10 deptSelection

Hi

[quoted text clipped - 44 lines]

 

[Afrosheen via AccessMonster.com]

Sorry Kardan. Here is my complete code.

Private Sub Form_Current()
10 deptSelection

20 cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & deptSelection(locat1) & "' " & _
"Order by tbl_assignment.unit;"
End Sub


Function deptSelection(varValue As Variant) As String
Select Case varValue
Case "Support Services", 1
strcnt = "Support Services"
50 Case "Admin Support", 2
60 strcnt = "Admin Support"
70 Case "Ops", 3
80 strcnt = "Ops"
90 Case "Unit 1", 4
100 strcnt = "Unit 1"
110 Case "Unit 2", 5
120 strcnt = "Unit 2"
130 Case "Unit 3", 6
140 strcnt = "Unit 3"
150 Case "Unit 4", 7
160 strcnt = "Unit 4"
170 Case "Food Services", 8
180 strcnt = "Food Service"
190 Case "OIC", 9
200 strcnt = "OIC"
210 End Select

220 deptSelection = strcnt

End Function

The form will not even start because of the: Argument not optional.
If I "REM" the deptSelection on line 10 then the form will display/start. If
I leave it in then the error occures and the form will not display/start

I have it exactly as you gave me.

Sorry to keep on bugging you about this.
Thanks

Hi

As I said, you should be using the code;

cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & deptSelection(locat1) & "' " & _
"Order by tbl_assignment.unit;"

You do need to pass the argument for the case select.

Regards

Richard

Sorry about this. I cut and pasted your code and I'm still getting the
"Argument not optional" error. I'm calling the function by:

[quoted text clipped - 6 lines]

 

[Kardan via AccessMonster.com]

Hi

I can see why this is occurring now.

It is because you have the call in the Form Current event. This will be
called as the form is first loaded when locat1 would have no value. You
should have this call in the locat1 Change event.

Also do not forget the Me qualifier (Me.locat1)

Regards

Richard

Sorry Kardan. Here is my complete code.

Private Sub Form_Current()
10 deptSelection

20 cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & deptSelection(locat1) & "' " & _
"Order by tbl_assignment.unit;"
End Sub

Function deptSelection(varValue As Variant) As String
Select Case varValue
Case "Support Services", 1
strcnt = "Support Services"
50 Case "Admin Support", 2
60 strcnt = "Admin Support"
70 Case "Ops", 3
80 strcnt = "Ops"
90 Case "Unit 1", 4
100 strcnt = "Unit 1"
110 Case "Unit 2", 5
120 strcnt = "Unit 2"
130 Case "Unit 3", 6
140 strcnt = "Unit 3"
150 Case "Unit 4", 7
160 strcnt = "Unit 4"
170 Case "Food Services", 8
180 strcnt = "Food Service"
190 Case "OIC", 9
200 strcnt = "OIC"
210 End Select

220 deptSelection = strcnt

End Function

The form will not even start because of the: Argument not optional.
If I "REM" the deptSelection on line 10 then the form will display/start. If
I leave it in then the error occures and the form will not display/start

I have it exactly as you gave me.

Sorry to keep on bugging you about this.
Thanks

Hi

[quoted text clipped - 16 lines]

 

[Douglas J. Steele]

Line ten should not be there. It serves no useful purpose (Access thinks
it's an attempt to call the function, which is why you're getting the error)

I see nothing in Kardan's instructions saying that it should be there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry Kardan. Here is my complete code.

Private Sub Form_Current()
10 deptSelection

20 cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & deptSelection(locat1) & "' " & _
"Order by tbl_assignment.unit;"
End Sub


Function deptSelection(varValue As Variant) As String
Select Case varValue
Case "Support Services", 1
strcnt = "Support Services"
50 Case "Admin Support", 2
60 strcnt = "Admin Support"
70 Case "Ops", 3
80 strcnt = "Ops"
90 Case "Unit 1", 4
100 strcnt = "Unit 1"
110 Case "Unit 2", 5
120 strcnt = "Unit 2"
130 Case "Unit 3", 6
140 strcnt = "Unit 3"
150 Case "Unit 4", 7
160 strcnt = "Unit 4"
170 Case "Food Services", 8
180 strcnt = "Food Service"
190 Case "OIC", 9
200 strcnt = "OIC"
210 End Select

220 deptSelection = strcnt

End Function

The form will not even start because of the: Argument not optional.
If I "REM" the deptSelection on line 10 then the form will display/start.
If
I leave it in then the error occures and the form will not display/start

I have it exactly as you gave me.

Sorry to keep on bugging you about this.
Thanks

Hi

As I said, you should be using the code;

cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & deptSelection(locat1) & "' " &
_
"Order by tbl_assignment.unit;"

You do need to pass the argument for the case select.

Regards

Richard

Sorry about this. I cut and pasted your code and I'm still getting the
"Argument not optional" error. I'm calling the function by:

[quoted text clipped - 6 lines]

What I'd like to do is to change either one so I can create a module
where I
can access one type of case statement for both programs.

 

[Afrosheen via AccessMonster.com]

Me again.
I tried the on change event also and that didn't work. It still came up with
the error. Maybe what I'm trying to do is impossible. Should there be some
kind of dim statements added? I don't know I'm lost.

Thanks for the help

Hi

I can see why this is occurring now.

It is because you have the call in the Form Current event. This will be
called as the form is first loaded when locat1 would have no value. You
should have this call in the locat1 Change event.

Also do not forget the Me qualifier (Me.locat1)

Regards

Richard

Sorry Kardan. Here is my complete code.

[quoted text clipped - 47 lines]

 

[Afrosheen via AccessMonster.com]

Hi Doug thanks for popping in. Line 10 deptSelection calls the function.
Isn't that the way it should be? The case statements are in a module.

Thanks

Line ten should not be there. It serves no useful purpose (Access thinks
it's an attempt to call the function, which is why you're getting the error)

I see nothing in Kardan's instructions saying that it should be there.

Sorry Kardan. Here is my complete code.

[quoted text clipped - 65 lines]

 

[Douglas J. Steele]

No, it's your query that calls the function, not your form's Current event
itself.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug thanks for popping in. Line 10 deptSelection calls the function.
Isn't that the way it should be? The case statements are in a module.

Thanks

Line ten should not be there. It serves no useful purpose (Access thinks
it's an attempt to call the function, which is why you're getting the
error)

I see nothing in Kardan's instructions saying that it should be there.

Sorry Kardan. Here is my complete code.

[quoted text clipped - 65 lines]

where I
can access one type of case statement for both programs.

 

[Afrosheen via AccessMonster.com]

Thanks for replying. I guess I'm getting confused.

Here is the orginal code. Because the same basic code is in 3 forms, I wanted
a way where I could use the same code for all three forms in one location.
Two forms the case statement are strings. One is a number a group box. I was
trying to select the field: locat1, have it build [I guess] the query on that
and have the results display in the location combo box called: Location.

For example: If I choose the "Department" - locat1- as Unit 4 then the query
would be build and placed in the location combo box. So everything that is
associated with Unit 4 like B block, C Block and so on would be in the
location combo box.

Maybe I'm going about it wrong. I don't know. I guess what I'm trying to do
is build one combo box from another. Here is the orginal code and that works.

20 shft = Me.Shift
30 Select Case locat1 '.Value
Case "Support Services"
40 strcnt = "Support Services"
50 Case "Admin Support"
60 strcnt = "Admin Support"
70 Case "Ops"
80 strcnt = "Ops"
90 Case "Unit 1"
100 strcnt = "Unit 1"
110 Case "Unit 2"
120 strcnt = "Unit 2"
130 Case "Unit 3"
140 strcnt = "Unit 3"
150 Case "Unit 4"
160 strcnt = "Unit 4"
170 Case "Food Services"
180 strcnt = "Food Service"
190 Case "OIC"
200 strcnt = "OIC"
210 End Select

220 cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & strcnt & "' " & _
"Order by tbl_assignment.unit;"

I hope this helps.

Thanks

No, it's your query that calls the function, not your form's Current event
itself.

Hi Doug thanks for popping in. Line 10 deptSelection calls the function.
Isn't that the way it should be? The case statements are in a module.

[quoted text clipped - 12 lines]

 

[Douglas J. Steele]

All you need is

Private Sub Form_Current()

cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & deptSelection(locat1) & "' " & _
"Order by tbl_assignment.unit;"

End Sub

Your call to the function deptSelection is included in the SQL you're
assigning to the RowSource.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for replying. I guess I'm getting confused.

Here is the orginal code. Because the same basic code is in 3 forms, I
wanted
a way where I could use the same code for all three forms in one location.
Two forms the case statement are strings. One is a number a group box. I
was
trying to select the field: locat1, have it build [I guess] the query on
that
and have the results display in the location combo box called: Location.

For example: If I choose the "Department" - locat1- as Unit 4 then the
query
would be build and placed in the location combo box. So everything that is
associated with Unit 4 like B block, C Block and so on would be in the
location combo box.

Maybe I'm going about it wrong. I don't know. I guess what I'm trying to
do
is build one combo box from another. Here is the orginal code and that
works.

20 shft = Me.Shift
30 Select Case locat1 '.Value
Case "Support Services"
40 strcnt = "Support Services"
50 Case "Admin Support"
60 strcnt = "Admin Support"
70 Case "Ops"
80 strcnt = "Ops"
90 Case "Unit 1"
100 strcnt = "Unit 1"
110 Case "Unit 2"
120 strcnt = "Unit 2"
130 Case "Unit 3"
140 strcnt = "Unit 3"
150 Case "Unit 4"
160 strcnt = "Unit 4"
170 Case "Food Services"
180 strcnt = "Food Service"
190 Case "OIC"
200 strcnt = "OIC"
210 End Select

220 cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & strcnt & "' " & _
"Order by tbl_assignment.unit;"

I hope this helps.

Thanks

No, it's your query that calls the function, not your form's Current event
itself.

Hi Doug thanks for popping in. Line 10 deptSelection calls the
function.
Isn't that the way it should be? The case statements are in a module.

[quoted text clipped - 12 lines]

where I
can access one type of case statement for both programs.

 

[Afrosheen via AccessMonster.com]

Thanks for the info and direction. I put the little dot for debug to check
the flow. What is now happening is that it will read the cboloc.RowSource
section then it goes to the deptSelection case statements. Pressing F8 takes
me through all the cases but doesn't stop on any of them. In turn it doesn't
fill the second combo box called location.

All you need is

Private Sub Form_Current()

cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & deptSelection(locat1) & "' " & _
"Order by tbl_assignment.unit;"

End Sub

Your call to the function deptSelection is included in the SQL you're
assigning to the RowSource.

Thanks for replying. I guess I'm getting confused.

[quoted text clipped - 58 lines]

 

[Afrosheen via AccessMonster.com]

Thanks again for all the help. I've got it finally working. I really
appreciate the help.

Thanks for the info and direction. I put the little dot for debug to check
the flow. What is now happening is that it will read the cboloc.RowSource
section then it goes to the deptSelection case statements. Pressing F8 takes
me through all the cases but doesn't stop on any of them. In turn it doesn't
fill the second combo box called location.

All you need is

[quoted text clipped - 15 lines]

 

[Afrosheen via AccessMonster.com]

If you still look at this, the problem now it is not reading the number after
the string in the case statement; Ex.

Function deptSelection(varValue As Variant) As String

10 Select Case varValue
Case "Support Services", 1
endi case

When I put a msgbox varValue in all it came up is with "Support Services" and
not the number. So when I creates the query based on the options box number
then it doesn't read it.

Sorry to keep on bugging you.

Thanks again for all the help. I've got it finally working. I really
appreciate the help.

Thanks for the info and direction. I put the little dot for debug to check
the flow. What is now happening is that it will read the cboloc.RowSource

[quoted text clipped - 7 lines]

 

[Mike Painter]

Sorry Kardan. Here is my complete code.

Private Sub Form_Current()
10 deptSelection

20 cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull]
" & _ "From tbl_assignment " & _
"Where tbl_assignment.locat = '" & deptSelection(locat1) & "' "
& _ "Order by tbl_assignment.unit;"
End Sub


Function deptSelection(varValue As Variant) As String
Select Case varValue
Case "Support Services", 1
strcnt = "Support Services"

220 deptSelection = strcnt

End Function

You are calling deptSelection in line 10 but it must take the form
deptSelection( SomeValue)
where SomeValue is 1,2,3, etc or a string.
(Since if you feed it a string, it returns thes ame value there is little
point calling it.)

 

[Afrosheen via AccessMonster.com]

Tahnks Mike for answering. I took out line ten and just have:

Private Sub Form_Current()

cboloc.RowSource = "Select tbl_assignment.locat, [unit],[pull] " & _
"From tbl_assignment " & _
"Where tbl_assignment.locat = '" & deptSelection(locat1) & "' " & _
"Order by tbl_assignment.unit;"

End Sub

Then the case statement in the function as above.

Sorry Kardan. Here is my complete code.

[quoted text clipped - 11 lines]

Case "Support Services", 1
strcnt = "Support Services"

220 deptSelection = strcnt

End Function

You are calling deptSelection in line 10 but it must take the form
deptSelection( SomeValue)
where SomeValue is 1,2,3, etc or a string.
(Since if you feed it a string, it returns thes ame value there is little
point calling it.)