How to Enable/Disable fields based on Date

[greeni91]

Hi There,

I am making a database that is for a Maintenance Schedule. I am having a problem when it comes to locking the fields I don't need to see at this time.

I have a main date at the top of my form (txtDate) that displays today's date using the function =Date()

Next I have a date for the next Maintenance scheduled on a machine (txtNextDate).

What I am trying to do is lock the fields I don't need when txtDate is greater than txtNextDate.

I have tried some code in various different formats but they don't seem to be making one bit of difference to my form...

Can anyone help me before I lose all my hair please!!!

/Sandy

[NeoPa]

You can run some code on the Open or Load events of the form. The control properties you may want to play with are :
.Visible
.Locked
.Enabled

Good luck Sandy :)

[munkee]

I think something simple like this would work in the form on open event

If (me.txtDate) > (me.txtNextDate) then
 
me.control1.visible = false
me.control2.visible = false
 
Else
 
me.control1.visible = true
me.control2.visible = true
 
End if

[NeoPa]

You might have some problems with that Chris.

I would expect (but only Sandy can say for sure) the property to use would be .Locked rather than .Visible. Two TextBoxes being compared would typically work on a string basis. This can give misleading results when working with dates (whose formats are unpredictable). Also, working with boolean values doesn't necessitate branching the code. If the variable is boolean and the calculation is boolean, then it makes most sense simply to assign the calculation to the variable.

Something like :

Me.Control1.Locked = (CDate(Me.txtNextDate) < Date())
Me.Control2.Locked = (CDate(Me.txtNextDate) < Date())

Of course, this is in it's simplest form. If there are many controls a For ... Each would often make sense. Otherwise assigning the calculation to a boolean variable assures the calculation is not run over and over too many times.

[greeni91]

Thanks for the help guys,

I have tried both methods but neither of them give me the results I am looking for. I have put the code in the OnLoad() property as there is one record per machine, so when that record loads it will lock all appropriate checks for me.

I have noticed in the past that you usually need the code in another property as well, say an AfterUpdate() property, I will play around with these a little bit to see if I get any results.

NeoPa is correct when he says there could be a lot of controls, approx. 5, but I have never used a For...Each statement before. If it could be done with case statements as well I might have a chance of being able to get the code sorted.

Thanks for the help,

/Sandy

[NeoPa]

Greeni91: I have tried both methods but neither of them give me the results I am looking for. I have put the code in the OnLoad() property as there is one record per machine, so when that record loads it will lock all appropriate checks for me.

Have you tried it in the OnOpen event procedure Sandy? Some data is not available to the code in the OnLoad one. This may be an example of that.

If you only have five controls to worry about, then listing them would make perfect sense. No need for a loop for just five items.

If you can let us know the names of the five and which property you would like to use (They all have different effects, but would all stop the controls' values from being changed), then we could show you how best to code it up.

[greeni91]

Sorry Guys,

I screwed up when putting in the OnLoad() event, when I typed out the private sub I wrote it as _OnLoad(). Now that the event has the right name the code works perfect... I am following it as NeoPa suggested in his last post.

Thanks a lot guys... I still have some hair left, haha

/Sandy

[NeoPa]

Is that all solved now then Sandy, or do you need any further help setting the .Locked properties for the five controls?

Actually, I would code it slightly differently for five of them :

Private Sub Form_Load()
    Dim blnLock As Boolean
 
    With Me
        blnLock = (CDate(.txtNextDate) < Date())
        .Control1.Locked = blnLock
        .Control2.Locked = blnLock
        .Control3.Locked = blnLock
        .Control4.Locked = blnLock
        .Control5.Locked = blnLock
    End With
End Sub