If Date is Between 2 other dates disable forms

[burrina]

I tried your new code, thanks. No luck, it shows 2 weeks available, i.e. 4 command buttons, not just 2. Here is a pic of form with code being run.



This is an add on module for a database I wrote that it is very date/time sensitive so therefore the odd date criteria. It is necessary thought for it to function.
I have just about lost my religion over this and so when this happens, I generally scrap it all and try something else.
I do appreciate all of the help I have got from this forum a lot! For now at least I have revised the form and will use a simple calendar to set dates.

Downside of this is that the user can screw up, but oh well, can't have cake and eat it too all the time. Here is a pic of new form.

I removed all dead code and here is the complete procedure as is right now.

Private Sub Form_Current()

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


If DCount("EmployeeID", "Employees", "eligible=True") = 4 Then 'only show 4 records
Me.OnCall.Enabled = False
End If

If OnCall = True Then ' Is On Call
ExpName_Label.Visible = True ' shows label which says Is On Call
Else
ExpName_Label.Visible = False
End If

If OnCall = False Then 'shows label Is NOT On Call
Label117.Visible = True
Else
Label117.Visible = False
End If


If IsNull(Text109) Then
OnCall.Enabled = False
End If


End Sub

[June7]

Did you fix the calcs in the textboxes (post #12)?

I did all the edits I describe and everything works. Only week2 buttons are available.

[burrina]

Not sure if we are on the same page or not? Did you see my last post with all of the dead code removed? The code for the textboxes I suppose could be edited for some other code but don't know how this would give the same result. The Payroll for the database behind this module uses Thursday thru Wednesday as it's default start and end payroll week. Anyway, with the last code, ALL of the command buttons were disabled.
Where exactly are you suggesting I put that code? In the oncurrent event or one of my textboxes? I am not using the 2 test textboxes on the form, they were just for testing purposes.


Okay, I adjusted all of the textboxes with your new code which does the same thing but maybe eliminates some problems.Hope so! Right now it appears to be working. I will continue to test and see what happens.

Thanks so much, I will let you know when the next pay period starts if it works or now.

[June7]

Yes, I saw your code in post 16.

In post 12 I described changing the expressions that calculate dates in the textboxes.

Then I provided the revised code for the form OnCurrent event to set the 8 buttons Enabled property.

The result is that only week2 buttons are enabled because those dates are the only ones that meet the criteria of current date (today) falling between the two calculated dates.

Edits really not that involved. Don't know how I can more clearly present.

[burrina]

No, you did just fine. It is working right now, so many thanks. I will continue to test as the next week comes up. Thanks so much for the simplified date code. I was my own worst enemy in this case with old code I had wrote from over 12 yrs ago.I am now thinking that because of updating from access 97 to 2002 that it is causing some code problems such as the one I posted about the module.

Thanks Again.

Just one thing, or maybe two, any suggestions for this module as you look at it, add on's etc... ? Or other adjustments you would make? Also I am wanting to upgrade Access and so wonder what version I should upgrade to!

[June7]

No thoughts on the form design because just don't know enough about your business process.

As for upgrade, I never worked with 97 or 2002 but except 2003 will be quite similar in structure. However, 2007 is a significant change in the Access framework. I like Access 2010. Access 2013 drops a feature I need so won't be moving up anytime soon.

[burrina]

I am now trying to see the person who is on call right now on my form.


How can I make a textbox on the form show me who is on call now?

ExpName is name of employee and I need to see him for this week

[June7]

Try:
=DLookUp("ExpName","qry_On_Call","Format([OnCallDte],'wwyyyy')=Format(Date(),'wwyyyy')")

[burrina]

Well the next OnCall time period changed there were a couple of problems. One is that it changes the last 2 employees OnCall dates to Week 2 and 3 and keeps of course there OnCall status set to yes. It is now showing the wrong employee on call also.

I need a way to take the employee off oncall status when there oncall week has passed. Also revise code to see who is on call now.

[June7]

I don't understand. What event changes the employees OnCall dates to Week 2 and 3?

You want employee to automatically be taken off oncall? What event should trigger this code? Form Open?

I forgot, your week doesn't start on Sunday. I guess that would be an issue for calculating the week number.

Instead, calculate the date of the Thursday preceeding the current date (if it is not Thursday) then use that as criteria for the person currently on call. Consider:

=DLookUp("ExpName","qry_On_Call","[OnCallDte]=DateAdd('d', WeekDay(Date())-4, Date())")

[burrina]

Yes, they should be taken off and using the on open event. I dont know what changes them to be on call for the next week.Still trying to wrap my head around that one. It freaked me out when I saw that. There is nothing saying that they are off call so I guess the dates just went ahead progressively. I was too tired to, think last night and just got up so I will ponder this.

I looked at the query qry_On_Call and the employees stay as eligible unless taken off and so the dates automatically progress if you look at the query and so it dictates that if they stay marked as being on call they advance in date order as well which is NOT good.

So basically what I am saying is if CallNexttDte is greater than CallNexttDte +1 then set OnCall status to False. I THINK this will work, not sure!

NO, on second thought the code would have to fire on that week ending in the NexttDte date if I understand my problem correctly.

So I changed Order on Employees table to date criteria and thinking maybe use this as current week criteria, something like DatePart("ww",Date()) And CallNexttDte date is last day of week, being a Wednesday, then the next day set OnCall status to False.

[June7]

I still don't understand. Seems to me I am seeing the same four names and the same dates in qry_On_Call. Describe exactly what changed for you.

CallNexttDte would never be greater than CallNexttDte+1. What did you mean to say? Could be: Anyone who's CallNexttDte is less than the FirstThursday need not be on call any longer?

You have so many 'on call' queries I am not sure which one to use for what. I used qry_On_Call in my suggestions but now see it is filtered on the Eligible field instead of OnCall. The On Call subform has qry_on_call_list as RecordSource so maybe use that query instead. Here is something for the form Open event that will automatically modify the OnCall field but it seems to mess up the Who Is On Call subform. Maybe you can figure that out.

CurrentDb.Execute "UPDATE Employees SET [On Call]=No WHERE CallNexttDte < #" & Date - Weekday(Date) - 2 & "#;"

Why do you use alias OnCall? name for Call field? These alias names in tables drive me nuts. I never do that and I never set Lookups in tables. Also field Expr1015 is in EmployeesInfo query because Order is in the query twice.

Did you try the DLookup for current on call? Here is even simpler expression: =DLookUp("ExpName","qry_On_Call","[OnCallDte]=Date()+WeekDay(Date())-4")

[burrina]

I am confused too. Let's say you take Mark and Buck off call and do it by un-checking their on call status. This should be automated somehow.

Then how would I then add 2 other people to the on call list? Sure, In can set their status to on call by checking the checkbox but then I am presented with how do I set their dates since all other command buttons are disabled? See my dilemna ?

If I take Mark and Buck off the on call list, their on call dates are still there. So now I also need a Null value (OnCallDte and CallNexttDte) as well as a UN-check of there on call status when their on all week ends.

[June7]

The question is Why are you disabling command buttons anyway?

I have no idea WHY you want to do what you want to do. You wanted code to disable buttons under certain conditions, I offered help on that request. Now you have a condition where you want all the buttons enabled? Or you want code to decide which buttons should be available when you check an employee as 'on call' or you just want the code to set the dates?

I don't understand your dilemma because I don't know your business processes.

[burrina]

I am seeing that it just cant be done disabling the command buttons. It is getting too complex as well. So the rule becomes simplify. I think it best to just maybe highlight the command buttons for that week or something then let happen what may.

I thank you for all your help. I did not mean for you to write the db for me. I will just enable all buttons for now and think on this for a while.

P.S. The original idea was to keep the user from using the wrong dates, i.e. the next set of dates in succession.