Bill_To,Ship_To

[bbrazeau]

I have a table "Orders" with 2 "lookup" fields "Bill_To" and "Ship_To" that get their values via lookups from a table "Customers". I have another table "Contacts" that has a "lookup" field "Customer_ID" and fields describing the rest of a street address. The idea is to be able to enter seperate bill to and ship to addresses on an orders form. I have the Orders form with "Bill_To" and "Ship_To" combobox controls and combobox controls for picking "Contacts" for each, but... How can I filter the "Contacts" comboboxes to show only "Bill_To" and "Ship_To"'s related contacts and how do I bind them to the correct parents("Bill_To" or "Ship_To")? P.S. When I look at the relationships window there are 2 Customer tables Customers (with a link to Contacts) and Customers1 with no link to Contacts.

[June7]

Is this concerning your HT1 project posted in other thread?

[bbrazeau]

hi June7, Yes, it is.

[bbrazeau]

I tied a bunch of different things to try and resolve this. Heres what I got to work. I added 2 more fields to the Orders table Bill_To_Contact and Ship_To_Contact. On the Sales_Orders form I made 2 new comboboxes to lookup these 2 fields. On the form OnCurrent event I requery the comboboxes for the Contacts and also on the Bill_To and Ship_To comboboxes OnChange event. On the Report I added2 sets of text boxes for Address,City,State, and Zipcode. I then used the Dlookup function in the expression builder to get the proper values for the fields. These Dlookup expressions were tuff for me, but I got em. Here is one of them.

=DLookUp("[CustomerContacts]![Contact_Street_Address]","CustomerContacts","[CustomerContacts]![Customer_Contact_ID]=" & [Forms]![Sales_Orders]![ShipToContactName])

I now have an extra customer_Contacts table in my relationships window. Does anyone have any comments or warnings about this?! It seems wrong,but I can't figure another way to do it.

[Stingaway]

Typically what I would do in this scenario is break out the address table and add an "address type" field - which allows you to have multiple addresses per customer - example types: Bill-To, Ship-To, Home, Work, other, etc...
You could then use queries (instead of dlookup) to populate the text fields based on filters that looked for the specific type.

qry_Ship_To_Addresses would be one query that filters on both the customer id and the address type

qry_Bill_To_Addresses would be another

I won't go as far to say there is anything "wrong" with your approach, just different. If it works, then it works - and that's usually good enough for me.. ;-)

[June7]

No, the duplication of table in relationships due to multiple joins is expected. I have not had chance to take another look at your project yet. Like Stingaway, I might also recommend an Addresses table.

[bbrazeau]

Hi June7 and Stingaway, I think the way you describe would work for me as 99% of the time both Ship_To and Bill_To would be the same Customer just different locations: say "Headquarters" and "Distribution center". However sometimes, In my case, The Customer, and addresses would both be the same just different project managers. Once in awhile, if a customer allows us to sell to someone other than them, both the Customer and the contact will have different addresses. My DB currently is an Orders/Inventory reduction DB that is really pretty generic. That is, it would work for a lot of different businesses. As I progress, it will become more specific to my needs (Plastic injection molding company). I have attached my latest version here. My next step is to add Re-stock to Inventory. Re-stock will have to handle Ordering from outside vendors, for externally supplied parts and production runs for internally supplied parts.

[Stingaway]

Forgot to mention that with the address table I also have a Y/N field for "Primary" address - which defaults it in cases where there are 30 addresses but the bill to and the ship to are the same...