Linking one field in one table to another table (1 Viewer)

Starnheavn

Registered User.
Local time
Today, 03:00
Joined
Apr 30, 2012
Messages
31
I have created 3 tables. 1 with Customer Information, 1 with my RFQ Items (Qty, description, Price and notes), and 1 with RFQ info such as Quote #, Date Quoted , Date Received. I need to link the Quote # to the RFQ Items table. How do I do this?
 

Beetle

Duly Registered Boozer
Local time
Today, 02:00
Joined
Apr 30, 2011
Messages
1,808
The RFQ Items table should have a Foreign Key field that stores the value from the Primary Key field of the RFQ Info table. You would establish a relationship on these fields.
 

Starnheavn

Registered User.
Local time
Today, 03:00
Joined
Apr 30, 2012
Messages
31
Hi Beetle. I am lost. I have my tables set up One for RFQ Items and the other is just called RFQ. I have autonumbered the Quote Number Column in the RFQ Table and is primary. I have in RFQ Items the same Columnbut named Quote Number Link. I've created the relationship. Where I am lost at is after I created the relationship, what do I do next in order for the update to happen in RFQ Items once I add a record to RFQ? The Quote number should be in both tables. Please help. Thanks.
 

Beetle

Duly Registered Boozer
Local time
Today, 02:00
Joined
Apr 30, 2011
Messages
1,808
Adding a record to the "parent" table (in this case RFQ) does not, and should not, automatically create a record in the "child" table (in this case RFQ Items). Once you add a record (or records) to the child table, that is the point at which the Foreign Key value (in this case the quote number) gets inserted. However, this happens at the Form level, not the Table level. When you create a main form / subform, where the main form would be based on the RFQ table and the subform is based on the RFQ Items table, the Master/Child link property of the subform control handles the automatic insertion of the appropriate Foreign Key value. You should not be entering records directly into the table itself, except perhaps for testing purposes in the early stages of design. If this is what you are doing (testing) then you would have to manually insert the correct Foreign Key (quote number) in the RFQ Items table when you add record(s) to it.

On another note;

I have autonumbered the Quote Number Column in the RFQ Table

If you mean that this is the Autonumber data type, hopefully the "Quote Number" you are referring to here is only intended to be a unique identifier for each quote and not a number that is for "human consumption" (i.e. you are expecting it to be a sequentially incrementing number that users will see). The Autonumber data type is not guaranteed to be sequential, or even positive, only unique. You will end up with gaps in the sequence, or possibly even negative numbers. Just FYI.
 

Starnheavn

Registered User.
Local time
Today, 03:00
Joined
Apr 30, 2012
Messages
31
The Quote Number is autonumbered in order to give each entry it's own number (not to be duplicated) and is a unique identifier. My database is being set up in order to enter a quote for the customer with pricing information as well as product description. I have attached the DB here so you can see where I am going with this. I started it at 5066 as this is the number that we started at last year. And yes, I was entering one record for test purposes as you can see in the DB.
 

Attachments

  • TKing RFQ Database.zip
    42 KB · Views: 309

Beetle

Duly Registered Boozer
Local time
Today, 02:00
Joined
Apr 30, 2011
Messages
1,808
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).
 

Starnheavn

Registered User.
Local time
Today, 03:00
Joined
Apr 30, 2012
Messages
31
Thanks for the info. What common methods are there for generating my Quote Numbers?
 

Beetle

Duly Registered Boozer
Local time
Today, 02:00
Joined
Apr 30, 2011
Messages
1,808
This is typically done by using the DMax function to return the highest number currently in the table, then add 1 and save that value to the Quote Number field for the new record. Keep in mind this is done at the Form level, using a form's Before Update event (you can't do this at the table level). The code would look something like the following;

Code:
Private Sub Form_BeforeUpdate (Cancel As Integer)

If Me.NewRecord Then
    Me!QuoteNumber = DMax("[QuoteNumber]", "[RFQTable]") + 1
End If

End Sub

If you're going to display this quote number on the form then you should lock the text box so the users can see it, but not interact with it.

If you want the quotes to start at a certain number (5066 or whatever), the easiest way to do that is just to manually enter the first record using that number before you implement this code.
 

Users who are viewing this thread

Top Bottom