Help! I'm trying to create a code that will display a field depending on the value of a calculation. Basically, if the years of service is greater than 5, I want the field to show. Otherwise, I do not want it to show. Here's what I have and I cannot get it to work. Can anyone help?
Private Sub Vested_Click()
If (Now() - StartDate)/365 > 5 Then
Vested.Visible = True
Else Vested.Visible = False
End If
End Sub
You are asking a control to be made invisible in response to clicking on it. Seems an unlikely strategy. I also doubt that a control can be made invisible while it has focus.
What I'm looking for is to have a box be visible only if the years of service is more than 5. When I say it's not working, I mean the box stays visible no matter what. Do I need to change the Click to OnCurrent?
No luck. The box stays visible no matter what. I also have the same problem with another visible code. The field stays visible no matter what. I want it to be invisible if the field is empty. See below:
Private Sub Salary_OnCurrent()
If Salary = 0 Then
Salary.Visible = False
Salary_Label.Visible = False
Else: Salary.Visible = True
Salary_Label.Visible = True
End If
And, just a note, every other code I've worked on so far has worked. Just the visible one is the problem. Not sure what I'm doing different with that one.
Going back to the code in post 1, add a line setting the focus to another control
Change the name to a control on your own form
Code:
Private Sub Vested_Click()
If (Now() - StartDate)/365 > 5 Then
Vested.Visible = True
Else
[B][COLOR="Red"]Me.AnotherControl.SetFocus[/COLOR][/B]
Vested.Visible = False
End If
End Sub
Private Sub Salary_OnCurrent() ' <=== #1
If Salary = 0 Then
Salary.Visible = False ' <=== #2
Salary_Label.Visible = False
Else
Salary.Visible = True
Salary_Label.Visible = True
End If
End Sub
Your problem starts with what appears to be something you attempted to do by hand, bypassing the form's wizards. Granted, the wizards are individually dumber than a box of rocks, but they can build GREAT starting points for subsequent customization.
At point #1, you refer to something that ONLY applies to a Form called Salary, because in the context of your stated problem, only a FORM has a current event. (OnCurrent is not Control event.) But normally, a form's OnCurrent entry-point name wouldn't be as you wrote it. It would just be Form_Current. Which is why I surmised that you built that by hand and avoided the wizards. They wouldn't do what you showed us.
At point #2, you use Salary as a control, not as a form name. Here, it might actually make sense, because the the label created automatically by typical form wizards or control wizards might bear the name of the underlying field and a label as you describe. The syntax doesn't look wrong.
Given that I see no problem with syntax, I'm going to guess that the reason your code isn't working is because it never gets called. The formula for the IF isn't what you were discussing in other parts of the thread, but once you get this to actually DO something, perhaps you can go back and work on the math. So let's get the logic up and running.
Here's what you do...
1. In Form Design mode on that form, with the properties sheet showing, select the Event properties and find the OnCurrent property. I'm guessing that it will be blank.
2. Double-click inside the box for OnCurrent; it will open up a code window into the VBA code for the form. You are now inside a window where you can see ALL of the code associated with your form's class module, not just the OnCurrent event.
3. Take the body of your Salary_OnCurrent (but not the entry point line and not the End Sub line because the form wizard just created a valid entry/exit pair for you) and do a Cut/Paste operation.
4. Delete the now-empty entry point and end point for your original Salary_OnCurrent because that is now an empty subroutine.
Doc is partly correct here.
Its not JUST a focus problem
What Michelle is trying to do is achievable but ... not easily reversible.
If that isn't an issue then fine ....but to me it makes little sense
I've knocked together a simple database using the same fields as in Michelle's post.
The screengrab below may help explain what Doc was saying
Database attached in case anyone wants to play with it.
Doc : I seem to remember you don't download any databases from the forum
Unless you want to make an exception in this case, you'll just have to trust me ...I'm a doctor!
Colin, I'm a doctor too - but not of medicine. My screen name came about when one of my co-workers realized I had a Ph.D. (earned) degree so he said, "It's The Doc Man" - and I liked the sound of it enough to use it as a screen name.
You are correct. I so very rarely download anything that could have active code in it that you might just as well say "never" - but that is a side-effect of having been a systems admin on systems with "Sensitive But Unclassified" rating or higher. I've seen too many systems compromised by something innocent that just adjusts a minor setting and, bang-zoom, it's system rebuild time.
