If Date is Between 2 other dates disable forms

[burrina]

I have a main form named frm_Employees_on_Call_What_Order and on it I have 5 sub forms. Yes, I know, a lot!



I have 3 date forms that if the date is between the current On Call Period then I want to disable the other 3 forms.

Here is my feeble attempt at this; Also I refer to the fields, i.e. controls on the sub-form for my between dates criteria
Example if the current date is Date() and the On Call Period is between 10/18/12 and 10/24/12 then disable the other 3 forms. Not sure if I have to refer to the sub-form like Forms!frm_Employees_on_Call_What_Order![frm_Set_on_call_date].form or something like this ???
Main form is named frm_Employees_on_Call_What_Order


If Date() Between!Forms![frm_Set_on_call_date]![One] and Forms![frm_Set_on_call_date]![seven] Then ' refers to sub form frm_Set_on_call_date

Forms![frm_nextweek].Enabled = False ' sub form on main form frm_Employees_on_Call_What_Order
ElseIf
Forms![frm_weekafter].enable = False 'sub form on main form frm_Employees_on_Call_What_Order
ElseIf
Forms![frm_finalweek].Enabled = False 'sub form on main form frm_Employees_on_Call_What_Order
End If
__________________________________________________ __________________________________________________ ________________________________
Ok, I have finally figured out how to make at least one week's code work.Now though if I try to add the other weeks disable code, it disables all of the command buttons.

The code below disable all of the command buttons if I use it along with the code below it.

'If (Me.one.Value >= Date) Or (Me.two.Value <= Date) Then ' First Week Disable Command Buttons Code
'Me.SecondThursday.Enabled = False 'second week
'Me.SecondWednesday.Enabled = False 'second week
'Me.ThirdThursday.Enabled = False 'third week
'Me.ThirdWednesday.Enabled = False 'third week
'Me.FourthThursday.Enabled = False 'fourth week
'Me.FourthWednesday.Enabled = False 'fourth week
'End If

If (Me.three.Value >= Date) Or (Me.four.Value <= Date) Then ' Second Week Disable Command Buttons Code ' THIS WORKS
Me.FirstThursday.Enabled = False 'first week
Me.FirstWednesday.Enabled = False 'first week
Me.ThirdThursday.Enabled = False 'third week
Me.ThirdWednesday.Enabled = False 'third week
Me.FourthThursday.Enabled = False 'fourth week
Me.FourthWednesday.Enabled = False 'fourth week
End If

[John_G]

Hi -

First thing to note - subforms are not part of the forms! collection, so you cannot refer to them using that syntax. Instead, disable the control on the main form that contains the subform, e.g. : Forms!frm_Employees_on_Call_What_Order!subformcontrol.enabled = False Replace subformcontrol with the name of the control.

The syntax of this statement: If Date() Between!Forms![frm_Set_on_call_date]![One] and Forms![frm_Set_on_call_date]![seven] Then

depends on where that code is. If the code is in the subform frm_Set_on_call_date with the two fields [One] and [seven], the you can use Me!, and note the proper syntax (MS Access does not like 'between' - that is used in SQL)

If Date() >= Me![One] and date() <= me![seven] Then

Anywhere else and it's a bit more cumbersome:

With Forms!frm_Employees_on_Call_What_Order!subformcontrol.form
If Date() >= ![One] and date() <= ![seven] Then....
end with

Where subformcontrol is the name of the main form control which contains subform frm_Set_on_call_date.

Be careful not to try to disable the form the cursor is in (i.e. the one that has the focus) - that will cause an error.

HTH a bit - post back it it's too muddy!

John

[burrina]

You lost me. I have 2 fields to reference on the sub-form name one and seven and the date criteria has to be equal to or between these dates.
I have to run the code from the main form since it has the focus and not the sub-forms. I dont actually use them other than the date criteria.
Does this make sense?

[burrina]

Okay, I have tried many things since. I change/simplified the form. Here is my code and the error I am getting when I try to run it.
Name of Command Button textbox Code
FirstWeekThursday one =Format(CDate(Date())-(Weekday(CDate(Date())))-2,"m/d/yy")
FirstWeekWednesday two =Format(CDate(Date())-(Weekday(CDate(Date())))+4,"m/d/yy")
NextWeekThursday three =Format(CDate(Date())-(Weekday(CDate(Date())))+5,"m/d/yy")
NextWeekWednesday four =Format(CDate(Date())-(Weekday(CDate(Date())))+11,"m/d/yy")
ThirdWeekThursday five =Format(CDate(Date())-(Weekday(CDate(Date())))+12,"m/d/yy")
ThirdWeekWednesday six =Format(CDate(Date())-(Weekday(CDate(Date())))+18,"m/d/yy")
FinalWeekThursday seven =Format(CDate(Date())-(Weekday(CDate(Date())))+19,"m/d/yy")
FinalWeekWednesday eight =Format(CDate(Date())-(Weekday(CDate(Date())))+25,"m/d/yy")

Above is the command buttons with there names and what the textboxes below them are named as well and the code behind the command buttons.

Here is the code for the main form and it is run on the oncurrentevent.

With Me
.OnCallDte.Enabled = .OnCall
.CallNexttDte.Enabled = .OnCall
If .OnCall = True Then
If IsNull(.OnCallDte) Or IsNull(.CallNexttDte) Then
MsgBox "Both OnCall Start and End boxes must have a date"
End If
End If
If Date >= .FirstWeekThursday!one And Date <= .FirstWeekWednesday![two] Then
.NextWeekThursday.Enabled = False
.NextWeekWednesday.Enabled = False
.ThirdWeekThursday.Enabled = False
.ThirdWeekWednesday.Enabled = False
.FinalWeekThursday.Enabled = False
.FinalWeekWednesday.Enabled = False


End If
End With


End Sub

Explanation: I am trying to disable the other command buttons if they are not within the current weeks on call time period. 6 of them disabled, only 2 available at any time.
Here is a screenshot of the form.

[June7]

Duplicate thread https://www.accessforums.net/access/...not-29326.html

[burrina]

I am still having NO luck with my code. It ONLY disables the first,third and fourth weeks command buttons. Here is what I am trying; I even put more textboxes that have a date in them between the current weeks date criteria and of course they have different names, no luck.

If (Me.one.Value >= Text136) Or (Me.two.Value <= Date) Then ' First Week Disable Command Buttons Code
Me.SecondThursday.Enabled = False 'second week
Me.SecondWednesday.Enabled = False 'second week
Me.ThirdThursday.Enabled = False 'third week
Me.ThirdWednesday.Enabled = False 'third week
Me.FourthThursday.Enabled = False 'fourth week
Me.FourthWednesday.Enabled = False 'fourth week
End If

If (Me.three.Value >= Text138) Or (Me.four.Value <= Date) Then ' Second Week Disable Command Buttons Code
Me.FirstThursday.Enabled = False 'first week
Me.FirstWednesday.Enabled = False 'first week
Me.ThirdThursday.Enabled = False 'third week
Me.ThirdWednesday.Enabled = False 'third week
Me.FourthThursday.Enabled = False 'fourth week
Me.FourthWednesday.Enabled = False 'fourth week
End If

If (Me.five.Value >= Text140) Or (Me.six.Value <= Date) Then ' Third Week Disable Command Buttons Code
Me.FirstThursday.Enabled = False 'first week
Me.FirstWednesday.Enabled = False 'first week
Me.SecondThursday.Enabled = False 'second week
Me.SecondWednesday.Enabled = False 'second week
Me.FourthThursday.Enabled = False 'fourth week
Me.FourthWednesday.Enabled = False 'fourth week
End If

If (Me.seven.Value >= Text142) Or (Me.eight.Value <= Date) Then ' Fourth Week Disable Command Buttons Code
Me.FirstThursday.Enabled = False 'first week
Me.FirstWednesday.Enabled = False 'first week
Me.SecondThursday.Enabled = False 'second week
Me.SecondWednesday.Enabled = False 'second week
Me.ThirdThursday.Enabled = False 'third week
Me.ThirdWednesday.Enabled = False 'third week
End If

[burrina]

I also tried this code which is supposed to disable all command buttons except for the first week command buttons. The current date is not even in the date range but even so it disables ALL command buttons.

If (Me.one.Value >= Date) Or (Me.two.Value <= Date) Then ' First Week Disable Command Buttons Code
Me.SecondThursday.Enabled = False 'second week
Me.SecondWednesday.Enabled = False 'second week
Me.ThirdThursday.Enabled = False 'third week
Me.ThirdWednesday.Enabled = False 'third week
Me.FourthThursday.Enabled = False 'fourth week
Me.FourthWednesday.Enabled = False 'fourth week
End If

[June7]

Looks like a major redesign of the form. Have you step debugged? Follow the code as it executes, find where it deviates from expected behavior, fix, repeat. Review link at bottom of my post for guidance on debug techniques. If you need help beyond that, provide the revised db for analysis.

[burrina]

I get NO errors when the code runs.It just does NOT do what it is supposed to do. I have tried everything with mixed results.Either all of the command buttons are disable or not disabled.

[June7]

That reply is not responsive to my previous post.

Have you step debugged to determine WHY the code isn't behaving as you expect? This is what I would do if you provide the db.

[burrina]

I have tried everything I know how to do. It just does not work. Here is the attached db zipped because of size.

[June7]

The controls all have Enabled property set Yes. The code only sets this property to True, all the lines for False are commented out. Why isn't there an ELSE for the False setting?

Part of the issue is that Format function results in a string value so instead of a date from the textbox compared to Date() in code it is a string and date (apples and oranges). The textbox expressions don't need to be so complex, change to:

=Date()-Weekday(Date())-2

Then fix the code, like:
Me.FirstWednesday.Enabled = Me.one >= Date Or Me.two <= Date

Don't understand the date calculations. The dates under the buttons are 1 day off of the day of week in the button caption. Is this what you want?

[burrina]

Yes, the date calculations refer to the week of the actual payroll period which is Thursday thru Wednesday so that is why they are like that. Ok, Yes, I had just recently noticed that I also needed code to enable/disable the command buttons.All I knew was that the first line of code was wrong and did not work and I did not know how to fix it since in my mind it made perfect sense. I will try and get back with you, Oh and yes I commented out some code since I was trying to figure out what the problem was just using one week by itself to see if I could get it to work.


Thanks

[burrina]

Ok, I tried adjusting the code and maybe it is me but it still does not work. The time period is now in the second week and so the command buttons for that week should be enable and work but they are disabled with this code, why? The code for the first week has both command buttons disabled as well,, but that is what is supposed to happen.
Time period meaning that the second week of payroll is now in effect and the second week of on call time period is in effect as well. This module is, or will be folded into a very complex Invoice/Payroll database and hence the odd code behind the command buttons.

Me.FirstThursday.Enabled = Me.one >= Date Or Me.two <= Date 'First Week If date is within this week Enable button
Me.FirstWednesday.Enabled = Me.one >= Date Or Me.two <= Date 'Enables this button
If Me.one < Date Then ' If not Disable buttons
Me.FirstThursday.Enabled = False 'Disables this button
If Me.two > Date Then ' If not Disable buttons
Me.FirstWednesday.Enabled = False 'Disables this button
End If

Me.SecondThursday.Enabled = Me.three >= Date Or Me.four <= Date 'Second Week If date is within this week Enable button
Me.SecondWednesday.Enabled = Me.three >= Date Or Me.four <= Date 'Enables this button
If Me.three < Date Then ' If not Disable buttons
Me.SecondThursday.Enabled = False 'Disables this button
If Me.four > Date Then ' If not Disable buttons
Me.SecondWednesday.Enabled = False 'Disables this button
End If

End If
__________________________________________________ ____________ Also tried hard coding it, it did not work
Me.FirstThursday.Enabled = Me.one >= Date Or Me.two <= Date 'First Week If date is within this week Enable button
Me.FirstWednesday.Enabled = Me.one >= Date Or Me.two <= Date 'Enables this button
If Me.one = Format(CDate(Date) - (Weekday(CDate(Date))) - 3, "m/d/yy") Then ' If not within this week Disable button
Me.FirstThursday.Enabled = False 'Disables this button
If Me.two = Format(CDate(Date) - (Weekday(CDate(Date))) + 5, "m/d/yy") Then ' If not within this week Disable button
Me.FirstWednesday.Enabled = False 'Disables this button
End If

'Me.SecondThursday.Enabled = Me.three >= Date Or Me.four <= Date 'Second Week If date is within this week Enable button
'Me.SecondWednesday.Enabled = Me.three >= Date Or Me.four <= Date 'Enables this button
If Me.three = Format(CDate(Date) - (Weekday(CDate(Date))) + 4, "m/d/yy") Then ' If not within this week Disable button
Me.SecondThursday.Enabled = False 'Disables this button
If Me.four = Format(CDate(Date) - (Weekday(CDate(Date))) + 12, "m/d/yy") Then ' If not within this week Disable button
Me.SecondWednesday.Enabled = False 'Disables this button
End If

End If

[June7]

Whay do you show using Format and CDate functions? CDate is unnecessary and Format results in a string value.

If you want the buttons enabled only when the current date is between the two calculated dates, your operators are all wrong. This works for me:

Me.FirstThursday.Enabled = Me.one <= Date And Date <= Me.two
Me.FirstWednesday.Enabled = Me.one <= Date And Date <= Me.two
Me.SecondThursday.Enabled = Me.three <= Date And Date <= Me.four
Me.SecondWednesday.Enabled = Me.three <= Date And Date <= Me.four
Me.ThirdThursday.Enabled = Me.five <= Date And Date <= Me.six
Me.ThirdWednesday.Enabled = Me.five <= Date And Date <= Me.six
Me.FourthThursday.Enabled = Me.seven <= Date And Date <= Me.eight
Me.FourthWednesday.Enabled = Me.seven <= Date And Date <= Me.eight

Ooops, I was wrong about the dates not coinciding with the day of week on buttons - I was 1 day off.

BTW, if you use indentation in your code and use CODE tags in post, will be easier to read.