×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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!


CODE --> vba

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 

RE: I think I need an ARRAY or a CONSTANT and loop?

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.

RE: I think I need an ARRAY or a CONSTANT and loop?

What is your database table structure/relationships?

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: I think I need an ARRAY or a CONSTANT and loop?

(OP)

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

RE: I think I need an ARRAY or a CONSTANT and loop?

(OP)

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.

RE: I think I need an ARRAY or a CONSTANT and loop?

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

CODE

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.

RE: I think I need an ARRAY or a CONSTANT and loop?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: I think I need an ARRAY or a CONSTANT and loop?

(OP)

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!

RE: I think I need an ARRAY or a CONSTANT and loop?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: I think I need an ARRAY or a CONSTANT and loop?

(OP)
Andy, this line that you have near the end:

WhoIsTheSuprev = strOut

Is that just another way of saying

CODE --> vba

Case Else
          Me.txtSupervisor = "" 

RE: I think I need an ARRAY or a CONSTANT and loop?

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 tblSupervisors may look like this:

ID    Supervisor
0     Not Specified
1     Glen Miller
2     Elvis Presley
3     Amadeus Mozart
 


---- Andy

There is a great need for a sarcasm font.

RE: I think I need an ARRAY or a CONSTANT and loop?

Hmmmmmmm? All musicians.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: I think I need an ARRAY or a CONSTANT and loop?

(OP)
Andy, good idea!

RE: I think I need an ARRAY or a CONSTANT and loop?

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


---- Andy

There is a great need for a sarcasm font.

RE: I think I need an ARRAY or a CONSTANT and loop?

...all de-composing.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: I think I need an ARRAY or a CONSTANT and loop?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: I think I need an ARRAY or a CONSTANT and loop?

Last but not Liszt...

combo

RE: I think I need an ARRAY or a CONSTANT and loop?

👍

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close