Dlookup Value from Calendar

Giganews Newsgroups
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.

Replies