The proper way to grab data from a relationship table

[Drak]

So for so many questions, this is my last post. (I hope ).

I have a customer table, which holds there information, then I have an orders table. The orders table has a "foreign" key "CustomerID" which is a one-to-many relationship to the primary key in the customer table. Now on my "order" form, the record source being the orders table. I have a combo box filled with the company names. When I select a company, the number (value) the combobox is, is the primary key for the customer table. So I store this found ID in the foreign key in the orders table.



What exactly is the point of the relationship even existing? I currently use "DLookup()" to pull information from the customer table to display on the order form. (Since the record source is not the costumer table)

 Result = DLookup("[Company]","tblCostumer","[ID]=" & Me.CustomerID)

I feel that I might be doing this wrong, since even if I didn't have that relationship setup, I can still do what I'm doing.

Sorry for the long explanation.. I have a orders table, which has a relationship to a specific customer. When I'm entering a "new order" how to I "properly" pull information from the costumer table, that the primary id equals the CustomerID. I figured this is where relationships plays a role.

[June7]

The primary/foreign key relationship is so that you don't need fields in the Orders table for all the customers info and repeatedly enter this data on each order record. A basic principle of relational database is 'don't repeat data'. So by only saving a key value, all of the customer's related info can be retrieved for display in a form (or a report) by joining the tables in a query and making that query the form/report data source. Domain aggregate functions (DLookup, etc) in queries or in textboxes can make processes slow.

You could use a form/subform arrangement for the data entry of customer and order, or to find existing customer and add order.

What you have is a solo form just for order entry with a combobox from which a customer ID is selected. This combobox could be multi-column with the ID field hidden and shows only customer name. If you want to display related customer info (address, etc), the RecordSource for this form can be a query that joins the two tables, join type 'show all order records ...'. Do not allow edit of customer info on this form, set the controls displaying customer info as Locked Yes, TabStop No. If users need to see the customer ID to make sure they have the correct Joe Smith, then that field could also be displayed in a locked textbox.

In summary, the most efficient method to retrieve related data is by query joins.

[Drak]

Thanks so much. You're the man!