Want to alter code with out screwing up

[Chey]

I have this code
Me.[Label167].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Label168].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Travel Description].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Facilities visiting].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[For LS].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Mary].Visible = Nz(Me.[Licensing_Specialist_III], False)
Me.[Nita].Visible = Nz(Me.[Associate_Coordinator], False)
Me.[For Mary].Visible = Nz(Me.CCPO_Supervisor, False)
Me.[Mary].Visible = Nz(Me.[CC_Program_Manager], False)
Me.[Non State].Visible = Nz(Me.[Non-State_Employee], False)
Me.[Claudia].Visible = Nz(Me.[Fairbanks_Supervisor], False)
Me.[Teresa].Visible = Nz(Me.[Juneau_Supervisor], False)
Me.[Marcey].Visible = Nz(Me.[Licensing_Specialist_II], False)

I am going to take out all the check boxes and have just have a drop down.
So for this one

Me.[Label167].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Label168].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Travel Description].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Facilities visiting].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[For LS].Visible = Nz(Me.[Licensing_Specialist], False)

Licensing_Specialist is no longer going to be a check box it will be a
combo. Hope I said that right.

I am not sure how to write the new code so it still does the same thing
If I choose Licensing Specialist I from the drop down then those things are
visable.
I think once I am shown the first one I can figure out the others.

Thanks
Cheyenne

 

[Pat Hartman]

you need to do it with an if.

If me.[Licensing_Specialist] = True Then
Me.[Label167].Visible = True
Else
Me.[Label167].Visible = False
End If

However, you have much bigger issues with this application. You seem to
have data fields named after individuals. What happens if someone changes
job responsibilities or leaves or some new person gets hired? You have
created a spreadsheet and called it a database and it is causing you
significantly more work than would be necessary if you had normalized the
data before creating the application.

 

[Nicholas Scarpinato]

I have to agree with Pat, you're breaking one of the cardinal rules of
database design: Never hard-code dynamic information into a database's
structure. That is to say, anything like user names or passwords or user
roles that can change at any time should never be hard-coded. That
information belongs in a table that can be read by the database to aquire
whatever information is needed.

Now if I understand your dilema correctly, your old code would display
specific controls on the form if you clicked the checkbox for whatever job
title you wanted. The way you do that with the drop-down is to use the
dropdown's value to determine what controls to make visible. Each field in
the dropdown list has a value, starting with 1 (it's zero when you have
nothing selected). You could use an If statement in the After Update event of
the dropdown:

If Me![ComboBox].Value = 1 Then Me.[LabelName1].Visible
ElseIf Me![ComboBox].Value = 2 Then Me.[LabelName2].Visible
ElseIf Me![ComboBox].Value = 3 Then Me.[LabelName3].Visible
....
End If

But I'm still curious as to why exactly you're wanting to go about this in
this manner. It seems like an awful lot of work that'll have to be redone
every time somebody leaves or somebody new comes in.

I guess the big question is, what exactly are you trying to accomplish?

I have this code
Me.[Label167].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Label168].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Travel Description].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Facilities visiting].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[For LS].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Mary].Visible = Nz(Me.[Licensing_Specialist_III], False)
Me.[Nita].Visible = Nz(Me.[Associate_Coordinator], False)
Me.[For Mary].Visible = Nz(Me.CCPO_Supervisor, False)
Me.[Mary].Visible = Nz(Me.[CC_Program_Manager], False)
Me.[Non State].Visible = Nz(Me.[Non-State_Employee], False)
Me.[Claudia].Visible = Nz(Me.[Fairbanks_Supervisor], False)
Me.[Teresa].Visible = Nz(Me.[Juneau_Supervisor], False)
Me.[Marcey].Visible = Nz(Me.[Licensing_Specialist_II], False)

I am going to take out all the check boxes and have just have a drop down.
So for this one

Me.[Label167].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Label168].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Travel Description].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Facilities visiting].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[For LS].Visible = Nz(Me.[Licensing_Specialist], False)

Licensing_Specialist is no longer going to be a check box it will be a
combo. Hope I said that right.

I am not sure how to write the new code so it still does the same thing
If I choose Licensing Specialist I from the drop down then those things are
visable.
I think once I am shown the first one I can figure out the others.

Thanks
Cheyenne

 

[Chey]

I am in the process of fixing this. That is why I need the help. I am still
learning.
I do not need to change this when a person comes or goes. I am not sure
what part you were talking about. In a table, I called Job_Title I have a
list of well Job Titles. In that table are the following
Licensing Specialist I
Licensing Specialist II
Licensing Specialist III
Associate Coordinator
Program Manager
Non-State Employee
EQCT I
there are probaly some more but I think you got the jist of it.

When a new employee come on I have a form (I hope I am saying this right
bere with me.) I fill out for them. It use to be a check box but now it is a
Combo box. I pick one from the list.

On other form I have (they are filliing out a travel request) Depending on
there "Job Title" a command button is visable. I named it the supervisors
names so I could visually remember who goes where. I can change these now.

So if Licensing Specialist I is booking travel 2 text boxes appear and the
combo box for them to click on. When they click on this command button it
sends an email to there supervisor for approval. LS are the only ones that
do this. The other employees send theres a email not but send me the
actually request. If there is a way to have 1 command button do multiple
tasks depending on there "Job Title" I would prefer that.

Would you be able to assit me in that?

I am good at following directions. If you can help me I can give you the
other codes that I have for each command button to mix them all into 1.


Thanks a bunch.

I have to agree with Pat, you're breaking one of the cardinal rules of
database design: Never hard-code dynamic information into a database's
structure. That is to say, anything like user names or passwords or user
roles that can change at any time should never be hard-coded. That
information belongs in a table that can be read by the database to aquire
whatever information is needed.

Now if I understand your dilema correctly, your old code would display
specific controls on the form if you clicked the checkbox for whatever job
title you wanted. The way you do that with the drop-down is to use the
dropdown's value to determine what controls to make visible. Each field in
the dropdown list has a value, starting with 1 (it's zero when you have
nothing selected). You could use an If statement in the After Update event of
the dropdown:

If Me![ComboBox].Value = 1 Then Me.[LabelName1].Visible
ElseIf Me![ComboBox].Value = 2 Then Me.[LabelName2].Visible
ElseIf Me![ComboBox].Value = 3 Then Me.[LabelName3].Visible
...
End If

But I'm still curious as to why exactly you're wanting to go about this in
this manner. It seems like an awful lot of work that'll have to be redone
every time somebody leaves or somebody new comes in.

I guess the big question is, what exactly are you trying to accomplish?

I have this code
Me.[Label167].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Label168].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Travel Description].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Facilities visiting].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[For LS].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Mary].Visible = Nz(Me.[Licensing_Specialist_III], False)
Me.[Nita].Visible = Nz(Me.[Associate_Coordinator], False)
Me.[For Mary].Visible = Nz(Me.CCPO_Supervisor, False)
Me.[Mary].Visible = Nz(Me.[CC_Program_Manager], False)
Me.[Non State].Visible = Nz(Me.[Non-State_Employee], False)
Me.[Claudia].Visible = Nz(Me.[Fairbanks_Supervisor], False)
Me.[Teresa].Visible = Nz(Me.[Juneau_Supervisor], False)
Me.[Marcey].Visible = Nz(Me.[Licensing_Specialist_II], False)

I am going to take out all the check boxes and have just have a drop down.
So for this one

Me.[Label167].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Label168].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Travel Description].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Facilities visiting].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[For LS].Visible = Nz(Me.[Licensing_Specialist], False)

Licensing_Specialist is no longer going to be a check box it will be a
combo. Hope I said that right.

I am not sure how to write the new code so it still does the same thing
If I choose Licensing Specialist I from the drop down then those things are
visable.
I think once I am shown the first one I can figure out the others.

Thanks
Cheyenne

 

[Nicholas Scarpinato]

So in your main form, are you adding new employees to the database?

I am in the process of fixing this. That is why I need the help. I am still
learning.
I do not need to change this when a person comes or goes. I am not sure
what part you were talking about. In a table, I called Job_Title I have a
list of well Job Titles. In that table are the following
Licensing Specialist I
Licensing Specialist II
Licensing Specialist III
Associate Coordinator
Program Manager
Non-State Employee
EQCT I
there are probaly some more but I think you got the jist of it.

When a new employee come on I have a form (I hope I am saying this right
bere with me.) I fill out for them. It use to be a check box but now it is a
Combo box. I pick one from the list.

On other form I have (they are filliing out a travel request) Depending on
there "Job Title" a command button is visable. I named it the supervisors
names so I could visually remember who goes where. I can change these now.

So if Licensing Specialist I is booking travel 2 text boxes appear and the
combo box for them to click on. When they click on this command button it
sends an email to there supervisor for approval. LS are the only ones that
do this. The other employees send theres a email not but send me the
actually request. If there is a way to have 1 command button do multiple
tasks depending on there "Job Title" I would prefer that.

Would you be able to assit me in that?

I am good at following directions. If you can help me I can give you the
other codes that I have for each command button to mix them all into 1.


Thanks a bunch.

I have to agree with Pat, you're breaking one of the cardinal rules of
database design: Never hard-code dynamic information into a database's
structure. That is to say, anything like user names or passwords or user
roles that can change at any time should never be hard-coded. That
information belongs in a table that can be read by the database to aquire
whatever information is needed.

Now if I understand your dilema correctly, your old code would display
specific controls on the form if you clicked the checkbox for whatever job
title you wanted. The way you do that with the drop-down is to use the
dropdown's value to determine what controls to make visible. Each field in
the dropdown list has a value, starting with 1 (it's zero when you have
nothing selected). You could use an If statement in the After Update event of
the dropdown:

If Me![ComboBox].Value = 1 Then Me.[LabelName1].Visible
ElseIf Me![ComboBox].Value = 2 Then Me.[LabelName2].Visible
ElseIf Me![ComboBox].Value = 3 Then Me.[LabelName3].Visible
...
End If

But I'm still curious as to why exactly you're wanting to go about this in
this manner. It seems like an awful lot of work that'll have to be redone
every time somebody leaves or somebody new comes in.

I guess the big question is, what exactly are you trying to accomplish?

I have this code
Me.[Label167].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Label168].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Travel Description].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Facilities visiting].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[For LS].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Mary].Visible = Nz(Me.[Licensing_Specialist_III], False)
Me.[Nita].Visible = Nz(Me.[Associate_Coordinator], False)
Me.[For Mary].Visible = Nz(Me.CCPO_Supervisor, False)
Me.[Mary].Visible = Nz(Me.[CC_Program_Manager], False)
Me.[Non State].Visible = Nz(Me.[Non-State_Employee], False)
Me.[Claudia].Visible = Nz(Me.[Fairbanks_Supervisor], False)
Me.[Teresa].Visible = Nz(Me.[Juneau_Supervisor], False)
Me.[Marcey].Visible = Nz(Me.[Licensing_Specialist_II], False)

I am going to take out all the check boxes and have just have a drop down.
So for this one

Me.[Label167].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Label168].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Travel Description].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Facilities visiting].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[For LS].Visible = Nz(Me.[Licensing_Specialist], False)

Licensing_Specialist is no longer going to be a check box it will be a
combo. Hope I said that right.

I am not sure how to write the new code so it still does the same thing
If I choose Licensing Specialist I from the drop down then those things are
visable.
I think once I am shown the first one I can figure out the others.

Thanks
Cheyenne

 

[Chey]

Yes when they first start working, but where the codes are, are in a
different form.

Thanks again

So in your main form, are you adding new employees to the database?

I am in the process of fixing this. That is why I need the help. I am still
learning.
I do not need to change this when a person comes or goes. I am not sure
what part you were talking about. In a table, I called Job_Title I have a
list of well Job Titles. In that table are the following
Licensing Specialist I
Licensing Specialist II
Licensing Specialist III
Associate Coordinator
Program Manager
Non-State Employee
EQCT I
there are probaly some more but I think you got the jist of it.

When a new employee come on I have a form (I hope I am saying this right
bere with me.) I fill out for them. It use to be a check box but now it is a
Combo box. I pick one from the list.

On other form I have (they are filliing out a travel request) Depending on
there "Job Title" a command button is visable. I named it the supervisors
names so I could visually remember who goes where. I can change these now.

So if Licensing Specialist I is booking travel 2 text boxes appear and the
combo box for them to click on. When they click on this command button it
sends an email to there supervisor for approval. LS are the only ones that
do this. The other employees send theres a email not but send me the
actually request. If there is a way to have 1 command button do multiple
tasks depending on there "Job Title" I would prefer that.

Would you be able to assit me in that?

I am good at following directions. If you can help me I can give you the
other codes that I have for each command button to mix them all into 1.


Thanks a bunch.

I have to agree with Pat, you're breaking one of the cardinal rules of
database design: Never hard-code dynamic information into a database's
structure. That is to say, anything like user names or passwords or user
roles that can change at any time should never be hard-coded. That
information belongs in a table that can be read by the database to aquire
whatever information is needed.

Now if I understand your dilema correctly, your old code would display
specific controls on the form if you clicked the checkbox for whatever job
title you wanted. The way you do that with the drop-down is to use the
dropdown's value to determine what controls to make visible. Each field in
the dropdown list has a value, starting with 1 (it's zero when you have
nothing selected). You could use an If statement in the After Update event of
the dropdown:

If Me![ComboBox].Value = 1 Then Me.[LabelName1].Visible
ElseIf Me![ComboBox].Value = 2 Then Me.[LabelName2].Visible
ElseIf Me![ComboBox].Value = 3 Then Me.[LabelName3].Visible
...
End If

But I'm still curious as to why exactly you're wanting to go about this in
this manner. It seems like an awful lot of work that'll have to be redone
every time somebody leaves or somebody new comes in.

I guess the big question is, what exactly are you trying to accomplish?

:

I have this code
Me.[Label167].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Label168].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Travel Description].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Facilities visiting].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[For LS].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Mary].Visible = Nz(Me.[Licensing_Specialist_III], False)
Me.[Nita].Visible = Nz(Me.[Associate_Coordinator], False)
Me.[For Mary].Visible = Nz(Me.CCPO_Supervisor, False)
Me.[Mary].Visible = Nz(Me.[CC_Program_Manager], False)
Me.[Non State].Visible = Nz(Me.[Non-State_Employee], False)
Me.[Claudia].Visible = Nz(Me.[Fairbanks_Supervisor], False)
Me.[Teresa].Visible = Nz(Me.[Juneau_Supervisor], False)
Me.[Marcey].Visible = Nz(Me.[Licensing_Specialist_II], False)

I am going to take out all the check boxes and have just have a drop down.
So for this one

Me.[Label167].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Label168].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Travel Description].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[Facilities visiting].Visible = Nz(Me.[Licensing_Specialist], False)
Me.[For LS].Visible = Nz(Me.[Licensing_Specialist], False)

Licensing_Specialist is no longer going to be a check box it will be a
combo. Hope I said that right.

I am not sure how to write the new code so it still does the same thing
If I choose Licensing Specialist I from the drop down then those things are
visable.
I think once I am shown the first one I can figure out the others.

Thanks
Cheyenne