Need help with a validation rule

[HLiu]

[FONT=Verdana, Arial, Helvetica]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
[/FONT]

 

[gemma-the-husky]

instead of storing the whole range of drawings numbers, store a base drawing number and a suffix/variation. To get this properly relational, you need to have one table with the project data, and ANOTHER table with the drawings information.

that way, you have project number 1234 (say), and store general information about this project in that record - then you link to this details of the associated drawings, each of which could include info such as date created, prepared by, printed by etc etc
1234-056 - even a link to the file location of the actual drawing - but you DONT need to store any of the general stuff held in the master record

Then all you have to do is make sure the new drawing number is unique, and the suffix is unique for that drawing.

You can easily use a query to eg say, find the highest suffix where drawing = 1234, to find the next suffix to use. You can also easily design a query that says - show me all the drawings for base 1234 - This is a lot easier than trying to slice a single number which represents a base drawing AND a suffix. A lot of this comes with experience. What you have to avoid is people who don't realy understand it, advising you to use an inappropriate mechanism.

Hope this helps

 

[HLiu]

Hmm, I'm not quite with you. By suffix/variation do you mean like say the base drawing number is 10000 (they are all 5 digit), the variation could be say 5000 to indicate every drawing number from 10000 - 15000 is reserved?

To give a little more info on my database: The table I'm stuck on is 'Product_type', this is already a separate, mini table from the master 'Main_Projects'. Product_type is the source for a combo box field in Main_Projects. When a user adds a new record in the master table, they would only choose what product type it is, and enter a drawing number, the possible drawing numbers are limited by the product type.

So when a user chooses say product type 'WCVF' when adding a new record, and 'WCVF' is listed as having reserved 10000 - 20000 in the product_type table, it would only allow the user to enter any number within that range as the drawing number.

Now if the user wishes to add a new product type to the list, and reserve some drawing numbers for it, there has to be some sort of code to prevent the reservations from clashing. Which means not only the starting/end numbers have to be unique for each product type(which I have done by making the two fields indexed and no duplicate), but they can't be within any of the currently reserved RANGES either.

So currently my table is basically allowing product type A to reserve 10000 - 20000, and product type B to reserve 10001 -20001, which is absurd.