L24Conflict for Invoice Only - How To Build a Reservation System and Copy Orders With Details Using Recordsets in Microsoft Access


	Back to Access Developer 24 Lessons Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

L24Conflict for Invoice Only

Lesli Cochran

4 years ago

I'm working on conflict resolution. There are estimates and invoices in the Order table. I need to only check for reservation conflicts when the Order is an Invoice. The IsQuotation is Boolean and would be False for an Invoice. How can I fix this? Also, the date field are Date and Time.

This is my current code:

' start date cannot fall inside another reservation
    S = "EventStartDate<=#" & SD & "# AND #" & SD & "#=<EventEndDate"
    ID = Nz(DLookup("OrderID", "OrderT", _
        "OrderID<>" & OrderID & " AND " & S & " AND " & IsQuotation = False), 0)
    If ID <> 0 Then
        MsgBox "Event Start Date conflicts with booked event.", vbCritical
        Cancel = True
        Exit Sub
    End If

    ' end date cannot fall inside another reservation
    S = "EventStartDate<#" & ED & "# AND #" & ED & "#<=EventEndDate"
    ID = Nz(DLookup("OrderID", "OrderT", _
        "OrderID<>" & OrderID & " AND " & S & " AND " & IsQuotation = False), 0)
    If ID <> 0 Then
        MsgBox "Event End Date conflicts with booked event.", vbCritical
        Cancel = True
        Exit Sub
    End If

    ' this reservation cannot overlap another one
    S = "#" & SD & "#<EventStartDate AND EventEndDate<=#" & ED & "#"
    ID = Nz(DLookup("OrderID", "OrderT", _
        "OrderID<>" & OrderID & " AND " & S & " AND " & IsQuotation = False), 0)
    If ID <> 0 Then
        MsgBox "This booking overlaps an existing one. Please change the date and/or time.", vbCritical
        Cancel = True
        Exit Sub

Thank you

	Kevin Robertson @Reply 4 years ago
Before the code put: `If IsQuotaion Then Exit Sub` This will exit out and the rest of the code will only run if it is an invoice.

	Lesli Cochran OP @Reply 4 years ago
Thank you Kevin, I want to run the code for estimates and check the order table for Invoices with conflicting dates. Adding this line of code will allow the rest of the code to run & compare the dates on my estimate to only Invoice dates on the order table?

Lesli Cochran OP @Reply

4 years ago

I want the code to run regardless if the form is an Estimate or Invoice. The code should check the order table to an Invoice with a conflicting date. This (part of the whole) code is calling what I want but I'm getting a syntax error (3075):

S = "EventStartDate<=#" & SD & "# AND #" & SD & "#=<EventEndDate"
    Q = "IsQuotation = False"
    ID = Nz(DLookup("OrderID", "OrderT", _
        "OrderID<>" & OrderID & " AND " & S & " AND " & Q), 0)
    If ID <> 0 Then
        MsgBox "Event Start Date conflicts with booked event.", vbCritical
        Cancel = True
        Exit Sub
    End If

	Kevin Robertson @Reply 4 years ago
In the first line you have the second inequality wrong. You have put =<. It should be <=.

	Lesli Cochran OP @Reply 4 years ago
Thank you Kevin!

	Kevin Robertson @Reply 4 years ago
You're welcome.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer 24 Lessons.

Next Unseen

New Feature: Comment Live View