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?
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
In the beforeupdate event of the form use Dcount to count the number of records for the date entered on the form.
Presumably do it for the SecondScheduledDate as well.
If the returned value is > 9 then do not allow the record to be saved?
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
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
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.