Restrict daily scheduled to below 10 (1 Viewer)

Georgenetwork

Registered User.
Local time
Today, 00:21
Joined
Nov 1, 2019
Messages
21
I am providing free psychosocial counseling to young African girls. The staff
Who handles the scheduling over-scheduled participants is which is a problem.

Is there any way to limit daily scheduled to 10 or below daily?
 

jdraw

Super Moderator
Staff member
Local time
Today, 03:21
Joined
Jan 23, 2006
Messages
15,364
Details please.
Staff, schedules, participants, over-scheduling, counseling sessions????
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:21
Joined
Oct 29, 2018
Messages
21,358
Hi George. Welcome to AWF! Are you using an Access database for your scheduling?
 

Georgenetwork

Registered User.
Local time
Today, 00:21
Joined
Nov 1, 2019
Messages
21
I want Microsoft Access to prevent over 10 person from being scheduled on any given date. I really need this
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:21
Joined
Sep 21, 2011
Messages
14,050
Use a Dcount for the date and check to see if limit has been reached?
 

Georgenetwork

Registered User.
Local time
Today, 00:21
Joined
Nov 1, 2019
Messages
21
Yes I am using Microsoft Access
I am not understanding you. Here are my form fields:
PatientID
FirstName
LastName
Date of Birthday
FirstScheduledDate
SecondScheduledDate
Comment
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:21
Joined
Sep 21, 2011
Messages
14,050
Last edited:

Georgenetwork

Registered User.
Local time
Today, 00:21
Joined
Nov 1, 2019
Messages
21
I am finding it a little hard to write the code
Can you help? Please
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:21
Joined
Sep 21, 2011
Messages
14,050
Something along these lines?
Your form says no more than 5 per date?, but what is wrong with 7 at date1 and 3 at date2, so you still have no more than 10 during a day.?

You also need to check that the same person is not in twice for the same day.

Might need some tweaking, but start with that.

HTH
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iDate1Count As Integer, iDate2Count As Integer, iDateTotal As Integer

If IsDate(Me.CounselingDate1) Then
    iDate1Count = DCount("*", "tblscheduling", "CounselingDate1 = #" & Format(Me.CounselingDate1, "mm/dd/yyyy") & "#")
    iDate1Count = iDate1Count + 1
    If iDate1Count > 10 Then
        strMsg = "Counseling Date 1 has reached maximum for date " & Me.CounselingDate1 & vbCrLf
    End If
End If
If IsDate(Me.CounselingDate2) Then
    iDate2Count = DCount("*", "tblscheduling", "CounselingDate2 = #" & Format(Me.CounselingDate2, "mm/dd/yyyy") & "#")
    iDate2Count = iDate2Count + 1
    If iDate2Count > 10 Then
        strMsg = strMsg & "Counseling Date 2 has reached maximum for that date " & Me.CounselingDate1 & vbCrLf
    End If
End If
iDateTotal = iDate1Count + iDate2Count
If iDateTotal > 10 Then
    strMsg = strMsg & "Combination of Counseling Date 1 and Date 2 exceeds maximum for that date"
End If

If strMsg <> "" Then
    MsgBox strMsg
    Cancel = True
End If

End Sub
 

Georgenetwork

Registered User.
Local time
Today, 00:21
Joined
Nov 1, 2019
Messages
21
My form say not more than 5 persons can be scheduled on any day.

Example:
Jane September 1, 2019
Sarah September 1, 2019
Eliza September 1, 2019
Elizabeth September 1, 2019
Cecelia September 1, 2019

If you want to enter another record for another person,
Access should prompt you that 5 persons are already scheduled on September 1, 2019
 

Georgenetwork

Registered User.
Local time
Today, 00:21
Joined
Nov 1, 2019
Messages
21
Thank you, it's working for only CounselingDate1. I was the count or limit apply to both counselingDate1 and CounselingDate2. Let it treat it as the same count.
Someone can be scheduled for the June 1, 2019 in CounselingDate1 while another person can be scheduled for CounselingDate2 on the same June 1, 2019

Participant are scheduled twice for counseling and enroll date varies. The status shows if participant had completed their enrollment for each visit date. Note: since date of enrollment varies, participant Elizabeth can be scheduled for Date1 June 1, 2019 while participant Susan can be scheduled for the same date but visit 2.
So the idea is only 5 person can be scheduled on any given date. So, when we are scheduling someone for Date1 on June 1, 2019, MS Access should check in Date1 and Date2 to make sure that not more than 5 persons are already scheduled




Something along these lines?
Your form says no more than 5 per date?, but what is wrong with 7 at date1 and 3 at date2, so you still have no more than 10 during a day.?

You also need to check that the same person is not in twice for the same day.

Might need some tweaking, but start with that.

HTH
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iDate1Count As Integer, iDate2Count As Integer, iDateTotal As Integer

If IsDate(Me.CounselingDate1) Then
    iDate1Count = DCount("*", "tblscheduling", "CounselingDate1 = #" & Format(Me.CounselingDate1, "mm/dd/yyyy") & "#")
    iDate1Count = iDate1Count + 1
    If iDate1Count > 10 Then
        strMsg = "Counseling Date 1 has reached maximum for date " & Me.CounselingDate1 & vbCrLf
    End If
End If
If IsDate(Me.CounselingDate2) Then
    iDate2Count = DCount("*", "tblscheduling", "CounselingDate2 = #" & Format(Me.CounselingDate2, "mm/dd/yyyy") & "#")
    iDate2Count = iDate2Count + 1
    If iDate2Count > 10 Then
        strMsg = strMsg & "Counseling Date 2 has reached maximum for that date " & Me.CounselingDate1 & vbCrLf
    End If
End If
iDateTotal = iDate1Count + iDate2Count
If iDateTotal > 10 Then
    strMsg = strMsg & "Combination of Counseling Date 1 and Date 2 exceeds maximum for that date"
End If

If strMsg <> "" Then
    MsgBox strMsg
    Cancel = True
End If

End Sub
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 07:21
Joined
Sep 21, 2011
Messages
14,050
Yes, that is why I get the count of each and add them together, as if you can only schedule 10 or whatever in a day, that should apply whether date1 or date 2.

You now have a basis to work from at least.
You will need to tweak it as you need.
If you need to allow 5 slots for date 2, then you would limit Date1 to 5 as well?
You need perhaps to make sure that date1 and date2 are not the same for the same client, so there is a lot of scope for improvement.

All I was doing was showing you one way of determining how many of each you have.
 

Users who are viewing this thread

Top Bottom