Hi, I'm a Access/VBA newbie and hit a problem while doing a project.

I'm building a table right now for 'product types', and each type can, but not necessarily has to, have a reserved range of 'drawing numbers'. So basically 3 fields: type, starting number, end number.

My problem is when I want to enter a new product type via a form, I have to make sure that the range entered for the new record does not clash with any other record.

E.g. Record A reserved 10000<x<20000, Record B reserved 20000<x<30000, so a new record C cannot have either the starting or the end drawing number in any of these ranges.

The catch is sometimes there might be gaps in the reservation. So say A reserved 10000<x<20000, B reserved 40000<x<50000, C can reserve any range within 20000 - 40000

Anyone have any idea how to go about this? Any help is appreciated

Welcome to P2P! A couple of questions:

What about those EQUAL to 40000 or 20000? You have
only stated those LESS THAN.

Are those ranges preset? That is, can someone choose 10231 to 20231 or are they locked into groups of 10000 starting from 1 to 10000, 10001 to 20000, 20001 to 30000, etc.?