AutoLookup Query Select Problem

[jasonstewart]

I have two tables and I need to fill the [Customers].Name, Address1, Address2, City, State, Zip, CustID into the [Orders] table in the same named fields using a drop down in a form using either CustID, or Name dropdowns. I have a seperate query that runs to select the respective address1, address2,...for any certain customer name and that seems to work in the form but it doesn't insert the values into the orders table. I select a customer from the drop down and all the information is displayed on the screen but if I goto the table nothing has been saved but the CustID(keyed). Here is my SQL, any help would be appreciated.

UPDATE Orders INNER JOIN Customers ON [Customers].[CustID]=[Orders].[CustID] SET Customers.Name = [Orders].[Name], Customers.Address1 = [Orders].[Address1], Customers.Address2 = [Orders].[Address2], Customers.City = [Orders].[City], Customers.State = [Orders].[State], Customers.Zip = [Orders].[Zip];

 

[Quehay]

OK, here's the typical question: Why are you denormalizing the parent data in the child fields? (storing all the customer info in orders) You can always view this with a query joining on CUST_ID. You can materialize this view in Excel, etc. as needed.

 

[GingerR]

i agree with Quehay....one big thing about relational databases is that you enter data once...ie. address, name, etc...then use for example the custID to view that data later. what if your customer's address changes? must you then change it in every table you've entered it into? No!! Not if you have normalized data!! you edit the address one time, then in all queries, reports, etc the address is displayed correctly!!

 

[jasonstewart]

Thank you for the suggestion however, I am using it this way because it has to be in both places because the development software in the handheld, where this information is going and is coming from, doesn't have that capability of just running SQL commands and query's to get that information or I would have done it as you both suggested. So, having said that, the problem still remains, any other help or suggestions would be greatly appreciated.

 

[Quehay]

OK. The field will only store one value and the combo is providing customer ID. In the After_Update event of the combo:

INSERT into orders(custname, custadd1, etc.)

SELECT c.custname, c.custadd1, etc.
FROM customers c
WHERE c.custID = me.cboCustomer AND
orders.orderID = me.ORDERID

This should do it.

 

[jasonstewart]

thank you very much, i will try that today. i really appreciate the help very much.