From Newsgroup: comp.databases.ms-sqlserv

Hi Team

I was hoping someone can assist with the following please:

I need the correct structure so that when an assessor is entering assessment time, it looks at the calendar to see if the time he/she has chosen is not already booked.

So I have the following forms:

frmAppoints: ( Single Form )
--anfAppointID
--dtfDate
--lnfAssessTypeID
--lnfAssessorID
--txfSiteID
--txfLicNo
--txf0500, txf0600, txf0700, txf0800, txf0900, txf1000, txf1100, txf1200 --txf1300, txf1400, txf1500, txf1600, txf1700, txf1800, txf1900, txf2000

frmCalendar:

Identical Fields to frmAppoint, except it is a ( Snapshot, Continuous ) Form.

So, before the records time is updated, the code need to Dlookup(Match) 2 initial criteria:
1. Match the Date
2. Match the Assessor ID
then
3. See if the matching Time IsNull = True or False.

If "IsNull = True" then pass the txfLicNo to the applicable Time in the below example.
If "IsNull = False" then myMess fires.

The following syntax is wrong, but I am using it the best way I know as a means to explain it.

Private Sub txf0500_BeforeUpdate(Cancel As Integer)

Dim myMess as Integer

With txf0500
If Forms!frmCalendar!dtfDate = Me.dtfDate Then
If Forms!frmCalendar!lnfAssessorID = Me.lnfAssessorID Then
IF IsNull(Forms!frmCalendar!txt0500) = False Then
myMess = MsgBox("This time is already booked, please choose a different time", vbOKOnly)
Cancel = True
Else
If Forms!frmCalendar!dtfDate = Me.dtfDate Then
If Forms!frmCalendar!lnfAssessorID = Me.lnfAssessorID then
If IsNull(Forms!frmCalendar!txt0500) = True Then
With Me.txf0500
If Me.lnfAssessType = 1 Then
Me.txf0500.Value = Me.txfLicNo.Value
Me.txf0600.Value = Me.txfLicNo.Value
Me.txf0700.Value = Me.txfLicNo.Value
End If
If Me.lnfAssessType = 2 Then
Me.txf0500.Value = Me.txfLicNo.Value
Me.txf0600.Value = Me.txfLicNo.Value
End If
If Me.lnfAssessType = 3 Then
Me.txf0500.Value = Me.txfLicNo.Value
End If
End With
End If
End If
End If
End If
End If
End If
End With

End Sub

As always

Many thanks in advance for any assistance.
Regards
Mark.
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

On Thursday, June 18, 2020 at 4:45:46 PM UTC-5, noodnutt wrote:

Hi Team

I was hoping someone can assist with the following please:

I need the correct structure so that when an assessor is entering assessment time, it looks at the calendar to see if the time he/she has chosen is not already booked.

So I have the following forms:

frmAppoints: ( Single Form )
--anfAppointID
--dtfDate
--lnfAssessTypeID
--lnfAssessorID
--txfSiteID
--txfLicNo
--txf0500, txf0600, txf0700, txf0800, txf0900, txf1000, txf1100, txf1200 --txf1300, txf1400, txf1500, txf1600, txf1700, txf1800, txf1900, txf2000

frmCalendar:

Identical Fields to frmAppoint, except it is a ( Snapshot, Continuous ) Form.

So, before the records time is updated, the code need to Dlookup(Match) 2 initial criteria:
1. Match the Date
2. Match the Assessor ID
then
3. See if the matching Time IsNull = True or False.

If "IsNull = True" then pass the txfLicNo to the applicable Time in the below example.
If "IsNull = False" then myMess fires.

The following syntax is wrong, but I am using it the best way I know as a means to explain it.

Private Sub txf0500_BeforeUpdate(Cancel As Integer)

Dim myMess as Integer

With txf0500
If Forms!frmCalendar!dtfDate = Me.dtfDate Then
If Forms!frmCalendar!lnfAssessorID = Me.lnfAssessorID Then
IF IsNull(Forms!frmCalendar!txt0500) = False Then
myMess = MsgBox("This time is already booked, please choose a different time", vbOKOnly)
Cancel = True
Else
If Forms!frmCalendar!dtfDate = Me.dtfDate Then
If Forms!frmCalendar!lnfAssessorID = Me.lnfAssessorID then
If IsNull(Forms!frmCalendar!txt0500) = True Then
With Me.txf0500
If Me.lnfAssessType = 1 Then
Me.txf0500.Value = Me.txfLicNo.Value
Me.txf0600.Value = Me.txfLicNo.Value
Me.txf0700.Value = Me.txfLicNo.Value
End If
If Me.lnfAssessType = 2 Then
Me.txf0500.Value = Me.txfLicNo.Value
Me.txf0600.Value = Me.txfLicNo.Value
End If
If Me.lnfAssessType = 3 Then
Me.txf0500.Value = Me.txfLicNo.Value
End If
End With
End If
End If
End If
End If
End If
End If
End With

End Sub

As always

Many thanks in advance for any assistance.
Regards
Mark.

This is the problem of un-normalized tables. Add an 2nd tables for time slots then you can use a combo box control using a query showing only open slots with no need for code.

tblAppointentTimeSlot
ID
anfAppointID
TimeSlot

Make anfAppointID + TimeSlot an unique key to eliminate any chance of a duplicate booking.

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-sqlserv

Hi Ron

Apologies for the very late response. I have been super-busy on other tasks.

Many thanks for the advise. Turns out with all the Covid-19 issues, this has been shelved until way down the track when conditions improve.

Cheers
Mark.
--- Synchronet 3.21d-Linux NewsLink 1.2