Disabling controls on form by date

[vix666]

Private Sub Form_Current()
On Error Resume Next
Dim ctls As Controls
Dim ctlCur As Control
Dim fEnabled As Boolean
Dim i As Integer

If Me![Date] >= Date - 10 Then

fEnabled = Not (Me![Verify Release] Or Me![Op Verify Release] Or Me![RS Verify Release])

Set ctls = Me.Controls

For i = 1 To ctls.Count
Set ctlCur = ctls(i)

ctlCur.Enabled = fEnabled
'ctlCur.Locked = Not fEnabled
Next i

Command55.Enabled = True
Command56.Enabled = True
Command57.Enabled = True
End If

End Sub

I have inherited a database and basically I am having some trouble with getting the controls to disable by date.

The form is to verify a release and when it needs releasing they currently have to tick a tickbox and it will disable all of the controls on the form so no-one can tamper with it after it has been verified.
But they now want this to be automatically done for one department by date as they forget to verify their releases. So i need all the release record controls disabled for each record which is 10 days old.

I have tried adding in a date part and removing parts of the code but i cant get it to work.Im struggling with this as im new to vba, so any help would be greatly appreciated

Thanks in advance

Vicky

 

[FancyPrairie]

I believe your problem is the fact that you named a contol on your form using a reserved word (i.e. Date). Change the control name from Date to something like txtDate. Then change your code to reflect the new name. For example,

If Me![txtDate] >= Date - 10 Then

 

[LonnieJohnson]

Have you tried using the DateAdd function?

DateAdd ("d", - 10,Date())

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[vix666]

Thanks for the help but that just disables the controls on all records, any other ideas?

thanks

vicky

 

[LonnieJohnson]

Also what are these controls...


fEnabled = Not (Me![Verify Release] Or Me![Op Verify Release] Or Me![RS Verify Release])


From the way this is written, fEnabled will only be true if all three controls are false.

Just a thought...

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[vix666]

Ive tried the dateadd function and it doesnt work either.

The fEnabled = Not (Me![Verify Release] Or Me![Op Verify Release] Or Me![RS Verify Release]) was done by the previous guy whose database ive inherited.

These controls are tick boxes which when ticked cause the controls to be disabled. But i have now removed this line as they want to do it by date and my code is as below

Thanks for the help, any other suggestions?


Private Sub Form_Current()
On Error Resume Next
Dim ctls As Controls
Dim ctlCur As Control
Dim fEnabled As Boolean
Dim i As Integer

If Me![txtDate] >= DateAdd("d", -10, Date) Then

Set ctls = Me.Controls

For i = 1 To ctls.Count
Set ctlCur = ctls(i)

ctlCur.Locked
'= Not fEnabled
ctlCur.BackColor = "12632256"
ctlCur.ForeColor = "8421504"

Next i

Command55.Enabled = True
Command56.Enabled = True
Command57.Enabled = True
End If

End Sub

 

[Rolliee]

Vicky,

Look at my FAQ on How to find the years, months and days between two dates. Then use that principle to fiind your days. Do not think like a math major. Think like a computer which can say was it yesterday and if not was it the day before and if not was it the .... It can quickly go back without fancy code.

If this does not help, you could send me a zipped small sample of your database and I will see if I can do what you wish on it.

rollie@bwsys.net

 

[FancyPrairie]

Don't have time to look into it, but this line
For i = 1 To ctls.Count
should read
For i = 0 To ctls.Count-1

However, use debug to step thru your code. You should be able to see what your problem is. If you have not used debug before, it's real easy. Simply place the word Stop above the line of code you want to examine, or place the cursor on the line and press F9 (which toggles the breakpoint on/off). Now execute your code. Your code will pause execution when it encounters the Stop statement or a breakpoint. Place the cursor over the variable you want to examine. A tooltip will display the value of the variable. Or, via the menu bar, select View|Immediate Window, then in the Immediate Window, type:
?YourVariableName and hit enter. Debug will display the value of YourVariableName. Press F8 to step thru your code 1 line at a time. Press F5 to continue execution of your code until the next stop statement, breakpoint or the end of your code.

 

[vix666]

Ive had a look in the code by using debug and theres a problem with the date part of the code on the date bit after -10, its saying that it cant find the date expression

If Me![txtDate] >= DateAdd("d", -10, Date) Then

Rolliee thanks for the faq, but i got a bit confused. I would send you my db, but company policy means i cant

any other ideas?

Thanks for all the help tho

 

[LonnieJohnson]

Sounds like you may be missing some reference libraries on your machine.

Try this to see if it works...

Me![txtDate] >= DateAdd("d", -10, Now)

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[Rolliee]

Send a sample that does the same thing. I am not interested in looking at propretiry info anyway.


Rollie E


Look at using FORMAT to put the date in the form you want too.

 

[vix666]

Sorry for the late reply, that work was put on hold as i had an urgent job.

The code using NOW worked So thanks very much for all your help, it is much appreciated

Vicky