I looked at your db and there are a few issues here, starting with your use of Autonumber. I think we already covered this in another thread of yours,
but I'm going to reiterate because this is important and you need to correct this before you get any further in the development of this application! In your last post you said;
I started it at 5066 as this is the number that we started at last year.
The mere fact that you wanted it to start at a particular number indicates that you
care about what the number is.
Autonumber (meaning the
Autonumber data type, which is what you are using) should
not be used for any number where you (meaning you, your users, or any other human on the planet) care about what the value of the first, next, last, or
any number in the sequence is. It is intended only to to generate unique numbers that the
database engine uses to identify records. If you are expecting the Quote Number field to have any sort of meaning to you or anyone else (as seems to be the case since you went to the trouble to try and control the starting number), then that field should
not be Autonumber (it should be Number or Text data type), should (arguably) not be the Primary Key, and you should use one of the commonly available code methods for generating the Quote Number.
Another problem here is that the Foreign Key field in your RFQ Items table is also an Autonumber data type. The RFQ Items table needs it's
own Primary Key field (which certainly could be Autonumber, as long as it's used appropriately) and then a
separate Foreign Key field which would need to be
Number data type. The Foreign Key field
cannot be
Autonumber.
A few other tips/suggestions;
- Don't use spaces in your object names (tables, fields, etc.). Instead of CUSTOMER NAME, use CustomerName. It's just as easy to read and saves you the hassle of having to account for the space later on in queries, code, etc.
- You have no relationship established between Customers and Quotes. Presumably each quote is for a given Customer (I would think) so you would need a Foreign Key field (CustomerID) in your RFQ table to determine which customer the quote is for.
- Your Vendors table has a field for the Rep. If a Vendor can have more than one Rep, then you should have a separate table for Reps.
- Your RFQ Items table has a text field where you enter the Item description. You may want to consider having a table for Items so users can just select the Item from a combo box on your data entry form (speeds up data entry and eliminates errors).