Is DLookup the Best Approach for This?

[Tim777]

Here is what I am trying to achieve. This should be very simple. My apologies, I am learning and horrible with syntax.

I have a DB used to schedule daily repairs. I have a repair entry form with a combo box with a list of SlotIDs. Each SlotID is related to a timeslot. The user selects the SlotID and the timeslot is auto populated.

SlotID Timeslot
O1 10:00 AM - 11:30 AM
O2 11:00 AM - 1:00 PM
O3
O4
OA All Day

The form also has a date. If an OA - All day event is scheduled then the user should be prevented from scheduling any other repairs.
So, when the user attempts to enter a SlotID for a particular date, I want to check and see if there is an entry in the table for "OA" (All day) on that date. If OA is an entry for that day, then I want a message box to inform them and prevent them from entering any additional appointments on that day.

This is what I have but it doesn't work..

Private Sub cmbSlotID_BeforeUpdate(Cancel As Integer)
Dim IDValue As String
IDValue = DLookup("[SlotID]", "Data", "[SlotID] = 'OA'")
If IDValue = "'OA'" And "[Start Date] = #" & Me.txtStart_Date & "#" Then
    MsgBox "In House Repairs are scheduled for all day. No time slots are available."
End If
End Sub

Am I even close?

Thanks!!!

[June7]

Why doesn't it work - error message, wrong results, nothing happens?

IDValue is declared as a string but if the DLookup doesn't find a matching value, the result is null and code should error because a string variable cannot be null. Also, the DLookup will return SlotID for any date that has OA code so also need criteria for the date.

Try:

If Not IsNull(DLookup("[SlotID]", "Data", "[SlotID] = 'OA' AND [Start Date]=#" & Me.txtStart_Date & "#")) Then
MsgBox "In House Repairs are scheduled for all day. No time slots are available."
End If

OR

If Nz(DLookup("[SlotID]", "Data", "[SlotID] = 'OA' AND [Start Date]=#" & Me.txtStart_Date & "#"),"") <> "" Then
MsgBox "In House Repairs are scheduled for all day. No time slots are available."
End If

[ssanfu]

If an OA - All day event is scheduled then the user should be prevented from scheduling any other repairs.

So what happens if there are slots scheduled:

SlotID  Timeslot
O1      10:00 AM - 11:30 AM
O2      11:00 AM - 1:00 PM

then someone tries to schedule:

OA  All Day

???
Is that possible??

[Tim777]

Why doesn't it work - error message, wrong results, nothing happens?

IDValue is declared as a string but if the DLookup doesn't find a matching value, the result is null and code should error because a string variable cannot be null. Also, the DLookup will return SlotID for any date that has OA code so also need criteria for the date.

Try:

If Not IsNull(DLookup("[SlotID]", "Data", "[SlotID] = 'OA' AND [Start Date]=#" & Me.txtStart_Date & "#")) Then
MsgBox "In House Repairs are scheduled for all day. No time slots are available."
End If

OR

If Nz(DLookup("[SlotID]", "Data", "[SlotID] = 'OA' AND [Start Date]=#" & Me.txtStart_Date & "#"),"") <> "" Then
MsgBox "In House Repairs are scheduled for all day. No time slots are available."
End If

June7,

Thank you very much! Very clear explanation. I tried your first suggestion and it works great. Before it did nothing. No errors, just didn't work.
I think I understand why now.

Steve,

Yes, thank you, good eye. That is a possibility and I need to address that situation as well.

I think (hope) I can modify the code June7 supplied to work in that situation. Otherwise, I might need help.

Thanks so much! You guys are great!!!

[Tim777]

Ok, this works but I'm positive this is not the best way to code it. What would be a good way to clean this up?

Private Sub cmbSlotID_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[SlotID]", "Data", "[SlotID] = 'OA' AND [Start Date]=#" & Me.txtStart_Date & "#")) Then
MsgBox "In House Repairs are scheduled for all day. No time slots are available."
Cancel = True
End If

If Me.cmbSlotID.Value = "OA" And Not IsNull(DLookup("[SlotID]", "Data", "[SlotID] = 'O1' AND [Start Date]=#" & Me.txtStart_Date & "#")) Then
    MsgBox "Customer Repairs are already scheduled for today. An All Day event cannot be scheduled. "
Cancel = True
End If
If Me.cmbSlotID.Value = "OA" And Not IsNull(DLookup("[SlotID]", "Data", "[SlotID] = 'O2' AND [Start Date]=#" & Me.txtStart_Date & "#")) Then
    MsgBox "Customer Repairs are already scheduled for today. An All Day event cannot be scheduled. "
Cancel = True
End If
If Me.cmbSlotID.Value = "OA" And Not IsNull(DLookup("[SlotID]", "Data", "[SlotID] = 'O3' AND [Start Date]=#" & Me.txtStart_Date & "#")) Then
    MsgBox "Customer Repairs are already scheduled for today. An All Day event cannot be scheduled. "
Cancel = True
End If
If Me.cmbSlotID.Value = "OA" And Not IsNull(DLookup("[SlotID]", "Data", "[SlotID] = 'O4' AND [Start Date]=#" & Me.txtStart_Date & "#")) Then
    MsgBox "Customer Repairs are already scheduled for today. An All Day event cannot be scheduled. "
Cancel = True
End If
If Me.cmbSlotID.Value = "OA" And Not IsNull(DLookup("[SlotID]", "Data", "[SlotID] = 'O5' AND [Start Date]=#" & Me.txtStart_Date & "#")) Then
    MsgBox "Customer Repairs are already scheduled for today. An All Day event cannot be scheduled. "
Cancel = True
End If

End Sub

[June7]

So if any 'O' code is present, don't allow record? Use LIKE operator:

If Me.cmbSlotID.Value = "OA" And Not IsNull(DLookup("[SlotID]", "Data", "[SlotID] LIKE 'O*' AND [Start Date]=#" & Me.txtStart_Date & "#")) Then
MsgBox "Customer Repairs are already scheduled for today. An All Day event cannot be scheduled. "
Cancel = True
End If

[Tim777]

June7, thanks a ton!!! Much better!