If you have the date and time in two different fields, they can be combined
later in your code if needed.
For the situation you mention, I assume that the screen you want to appear
(a form?) will be closed when the database is opened and that it will only
be opened between the times specified on the day of the week specified. So,
when the database is opened, you would need a form with a timer that will
periodically check to see if you are within the specified time period. There
are then four conditions you need to handle. If not within the time frame
and the form is currently closed, do nothing. If not within the time frame
and the form is currently open, close it. If within the time frame and the
form is open, do nothing. If within the time frame and the form is closed,
open it.
By your description, I take it that the user would set the day of the week
and time of day during that day that the form should open. They would also
set the day of the week and time of day during that day that the form should
be closed. Anytime in between these two times the form should be open. So,
if they chose to open the form Friday at 6pm and close it on Sunday at 2am,
the form would be open for that 32 hour period.
For this example, use an Option Group, Combo Box, or Single Select List Box
to pick the day of week. The value returned should be 1-7, for Sunday -
Saturday. This will not work if you want it open for more than one week.
Since you're not using an actual date, you'll have to take over some of the
work of finding out where you are reference the dates yourself.
Example (untested):
intOpenDay = Me.optStartDay
'Adjust intCloseDay to be the number of days after intOpenDay
If Me.optEndDay > intOpenDay Then
intCloseDay = Me.optEndDay - intOpenDay
ElseIf Me.optEndDay < intOpenDay Then
intCloseDay = Me.optEndDay + 8 - intOpenDay
Else
intCloseDay = 1
End If
dteOpenTime = "#" & Me.txtOpenTime & "#"
dteCloseTime = "#" & Me.txtCloseTime & "#"
If dteCloseTime = dteOpenTime Then
'Don't let the close time = the open time
'since we're setting a one minute difference here
'the timer running this code must run it more frequently
'than once per minute. Adjust as needed.
dteCloseTime = DateAdd("n", -1, dteOpenTime)
End If
dteTime = Time
'Handle if same day
If intOpenDay = intCloseDay Then
Select Case dteTime
Case dteOpenTime To dteCloseTime
If IsOpen("frmMyForm") Then
Exit Sub
Else
DoCmd.OpenForm "frmMyForm"
End If
Case Is < dteCloseTime
If IsOpen("frmMyForm") Then
Exit Sub
Else
DoCmd.OpenForm "frmMyForm"
End If
Case dteCloseTime To dteOpenTime
If IsOpen("frmMyForm") Then
DoCmd.Close acForm, "frmMyForm", acSaveNo
End If
End Select
Exit Sub
End If
Select Case Weekday(Date, intOpenDate)
Case 1
'If Open Day, check the time and if the form
'isn't open and it's past Open time, open the form
If dteTime >= dteOpenTime Then
If IsOpen("frmMyForm") Then
Exit Sub
Else
DoCmd.OpenForm "frmMyForm"
End If
End If
Case intCloseDay
'If close day, check the time then
'close the form if it is open and past close time
If dteTime >= dteCloseTime Then
If IsOpen("frmMyForm") Then
DoCmd.Close acForm, "frmMyForm", acSaveNo
Else
Exit Sub
End If
End If
Case 1 to intCloseDay
'If between the open and close day, the form should be open
If IsOpen("frmMyForm") Then
Exit Sub
Else
DoCmd.OpenForm "frmMyForm"
End If
Case Else
'If not on or between open and close days, the form
'should be closed.
If IsOpen("frmMyForm") Then
DoCmd.Close acForm, "frmMyForm", acSaveNo
End If
End Select
Place the following in a standard module:
Public Function IsOpen(strFormName As String) As Boolean
IsOpen = Application.CurrentProject.AllForms(strFormName).IsLoaded
End Function
or you could use this more versatile one:
Public Function IsOpen(strName As String, Optional varObjectType As Variant)
'Returns True if strName is open, False otherwise.
'Assume the caller wants to know about a form.
If IsMissing(varObjectType) Then varObjectType = acForm
IsOpen = (SysCmd(acSysCmdGetObjectState, varObjectType, strName) <> 0)
End Function
--
Wayne Morgan
MS Access MVP
DS said:
Thanks Wayne a lot of good information....so hees my dilema. I have
screens that are to appear up automaticaly as such.
I want a screen to appear on Friday at 10:00 AM and disappear on Friday at
9:00 PM. In order to do this it would work off of the computers time. So
I set up these fields [StartDay],[StartTime],[EndDay] and
[EndTime].....I'm not sure if this is right after reading your answer.
Should it just be [Start] and [End]? But how would the customer choose
the day and time then? Of course then there is the situation of Saturday
into Sunday and the past Midnite situation to deal with. Mind you the
customer would not be dealing with dates but rather days of the week only.
Sun, Mon, Tue, etc. Once again Thank you, Wayne.
DS
