Code to avoid overlapping date entries in Products/Rates tables (1 Viewer)

[dillonhh]

Hello All,

So right to it...I have a Products table with a 1 to Many relationship with a ProductRates table.

PRODUCTS
ProductID
etc.

PRODUCTRATES
ID
ProductID
RateStartDate
RateEndDate
NetRate


The above are the fields that matter for each table. I am stuck on figuring out a way to write an even that will prevent a user from entering rates that have dates that overlap.

For example, Product# 1 has rates:

ID: 1
ProductID: 123
StartDate: 01/01/2008
EndDate: 01/04/2008 (April 1, 2008!)
Rate: 100.00

What I want to prevent is someone entering another entry in the table for say these dates:

ID: 2
ProductID: 123
StartDate: 01/04/2008 (April 1, 2008)
EndDate: 01/07/2008 (July 1, 2008)
Rate: 150.00

This is a problem because April 1, 2008 has now been entered for two different entries and thus has 2 different rates. I need an event statement that will make sure that any new rate entered is not between or equal to any dates already existing in the table, for that product. Therefore, i need something that will limit the check to rate-entries for just the product we are working with. If this helps, i have a form based on Products and a subform based on ProductRates with the ProductID obviously the field tying them together.

Thanks for the help, let me know if i am unclear, Dillon

 

[ted.martin]

One way would be to write a sub or module that essentially checks if ID2 StartDate is 'both' > ID1 StartDate & ID1 EndDate. Similarly also check if ID2 EndDate is > ID1 StartDate & > ID1 End Date. Only if both criteria are TRUE - will your dates not overlap. To do this you will need to get to the last record or better still any record using the FIND method for a recordset.

Quite a bit of coding to do but do-able.

 

[dillonhh]

Thanks Ted, good call, everything works well, but I am running into one issue:

Private Sub RateStartDate_BeforeUpdate(Cancel As Integer)

Set db = CurrentDb()
Set rstProductRates = db.OpenRecordset("ProductRates", dbOpenDynaset)

rstProductRates.MoveFirst

Do Until rstProductRates.EOF
     
    If rstProductRates!ProductID = Me.ProductID Then

        If Me.RateStartDate <= rstProductRates!RateEndDate And Me.RateStartDate >= rstProductRates!RateStartDate Then
        MsgBox "Date Already Exists"
        Cancel = True
        Exit Sub
        End If
    
    End If
    rstProductRates.MoveNext
   
Loop

End Sub

The Cancel = True statement is causing the error... "Property Not Found" and I cannot figure out why! Any ideas? Thanks a lot, Dillon

 

[dillonhh]

The error was # 3270. In the meantime I have just trapped the error as everything is working as it should be other than the annoying popup, but if anyone has any idea why I may be getting this error, i would love to know!

Thanks, Dillon