I think I need an ARRAY or a CONSTANT and loop? 1

[Pudsters]

I have an Access Form, my code functions, but needs serious help! I need to assign a group of employees in Department 160 to their own special group with their own Supervisor, based on their employee number that shows up in Me.txtEmpNumber. How can I make this code easy to maintain as it will be used in multiple places and employees come and go?
I would like to only have to change the employee #'s in one place!

 Case "SOUTH"
      Me.txtSupervisor = EMAIL_SUPERVISOR_SOUTH       
          
       Case "CENTRAL"
          Me.txtSupervisor = EMAIL_SUPERVISOR_CENTRAL
       
       Case "NORTH"       
          If Me.txtEmpNumber = 395 Or Me.txtEmpNumber = 634 Or Me.txtEmpNumber = 749 Or Me.txtEmpNumber = 1050 _
             Or Me.txtEmpNumber = 1100 Or Me.txtEmpNumber = 1108 Or Me.txtEmpNumber = 1109 Then
                Me.txtSupervisor = EMAIL_SUPERVISOR_NORTH_SALES
            Else
                Me.txtSupervisor = EMAIL_SUPERVISOR_NORTH_SERVICE
          End If
          
       Case Else
          Me.txtSupervisor = ""
    End Select

 

[mintjulep]

You are working with a DATABASE.

This DATA belongs in tables in the database.

Then you look things up.

You should have a table for employees.

One of the attributes for each employee should be who their supervisor is.

 

[SkipVought]

What is your database table structure/relationships?

I need to assign a group of employees in Department 160 to their own special group with their own Supervisor

So given Dept = 160 then do all employees in that Dept get assigned to this special group? Or is this group of employees used as a basis for making selections to go to this special group?

What is the Field Name to which the special group will be assigned or is this assignment to another Department?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Pudsters]

Yes you are right, I should just add a Supervisor field to the employee table. That would solve all of my problems! The only thing is that would require the person adding new employees to have to know which supervisor to assign and then make the mouse click. But I guess that is the way to go. THANKS

 

[Pudsters]

Skip and mintjulep,

Actually I wanted to do this without requiring anyone to select the supervisor, because when an employee is first entered into the system, the girl doing the entry might not know which supervisor to choose.

In Department 160, we currently have around 12 employees. 5 of them have a Supervisor and the other 7 (listed above, identified by their employee #), have a different supervisor. I know it should really be changed to two separate departments, but that would require a lot of changes company wide, and we would prefer not to do that. So, if you look at my code, it works fine. It is just a pain if and when we add or remove employees.

 

[Andrzejek]

If you have your heart set on to have your code with hard-coded values,
how about setting a little Public Function:

Public Function WhoIsTheSuprev(ByRef strRegion As String, ByRef intEmplNo As Integer) As String
Dim strOut As String

Select Case strRegion
    Case "SOUTH"
        strOut = EMAIL_SUPERVISOR_SOUTH       
    Case "CENTRAL"
        strOut = EMAIL_SUPERVISOR_CENTRAL
    Case "NORTH"   
        Select Case intEmplNo
            Case 395, 634, 749, 1050, 1100, 1108, 1109 
                strOut = EMAIL_SUPERVISOR_NORTH_SALES
            Case Else
                strOut = EMAIL_SUPERVISOR_NORTH_SERVICE
        End Select    
End Select 

WhoIsTheSuprev = strOut 

End Function

And that will "make this code easy (?) to maintain as it will be used in [called from] multiple places "


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Well here’s what it appears to be: Pay me now or pay me later.

You don’t have Supervisor in your db because, “when an employee is first entered into the system, the girl doing the entry might not know which supervisor to choose.”

So you haven’t gone to the “bother” of having to eventually get the proper data into a properly designed database (cuz that’s too much time and effort, “a lot of changes company wide”) and now you’ve got a problem that’s gonna take time and effort to sort out.

Like I stated before, pay me now or pay me later. Often the later payment is more costly than getting it right to begin with.

So don’t you have a form for maintaining an employee’s status? SomeBODY goes in and changes a bunch of records in accordance with a Change Request. You don’t write code to do this kind of update as a matter or course.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Pudsters]

Andy, the public function looks like the perfect fix for my current situation.

And

Skip, you are absolutely right! We need to bite the bullet, make the proper changes and update our employee table, create another department, and update all of our queries and reports to reflect the new department.

Thank you both!

 

[SkipVought]

What I would suggest is to have a Table into which you could load the Employee Numbers and then read that table into the code that Andy posted above. Then that code could be used over and over by simply replacing the values in that table.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Pudsters]

Andy, this line that you have near the end:

WhoIsTheSuprev = strOut

Is that just another way of saying

Case Else
          Me.txtSupervisor = ""

 

[Andrzejek]

Yes, it is.

“when an employee is first entered into the system, the girl doing the entry might not know which supervisor to choose.” - then make the default value of the Employee's supervisor 0, and your [tt]tblSupervisors[/tt] may look like this:

[pre]
ID Supervisor
0 Not Specified
1 Glen Miller
2 Elvis Presley
3 Amadeus Mozart
[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Hmmmmmmm? All musicians.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Pudsters]

Andy, good idea!

 

[Andrzejek]

Glen Miller - dead. Elvis Presley - dead (no matter what other people say). Amadeus Mozart - dead.
And I don't feel that well, either......




---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

...all de-composing.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Franz Liszt-DEAD!
It was my conviction that over his lifetime, Franz Liszt compiled a compendium of sundry composers' compositions. Musical connoisseurs, therefore, lust for the last of Liszt's lists, lest Liszt's lists be lost.

BYT tonight I’m eating the last of the töltött káposzta I made.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Last but not Liszt...

combo

 

[SkipVought]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]