| Subject: | Dlookup Value from Calendar |
| Posted by: | noodnutt (noodnu…@gmail.com) |
| Date: | Thu, 18 Jun 2020 |
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.