[Subforms] Anyway to Reference one table but save to another.

[Help Wanted]

With a subform is it possible to reference one table but save the data to a new table?

Say i created a table (Address Table) that has my cutomers name and address in. It has 7 fields as such "customer ID, Name, Address1, Address2, Town, County, Postcode"

Now i want to create an order form. It it possible on that order form to add this subform so when i type in the customer ID on the order form it brings up the address, well it is because i did it.

My problem is whats on the form saves to a new table (Order Table) but the information the subform brings up doesnt save to the new table.

Is there a way to save the data the subform brings up to the order table with the rest of the order or is there an even easier i can reference the address data and save to the order table without using a subform?

 

[macca the hacke]

Why do you want the address details saved to the order table? It is better to keep as 2 separate tables.

 

[Help Wanted]

Because eventually i want to be able to produce a report that has the customers address on it and what they ordered. Now if they have more than one order in a day under a different order number i would like to get it so the address shows, all items are added together and then the order numbers on the right.

So if one order is for 2 bottle of coke and a bottle of orange and then another order is for 3 bottles of coke i can do a report that says.

Customer Address, 5 bottles of coke, 1 bottle of orange, order 1, order 2.

I'm very new to access and just thought saving all the orders with address in one table would be best. If it isnt please tell me.

 

[macca the hacke]

You really don;t want to be saving address with orders. You need 2 separate tables - 1 that has order info (including a customer reference no), and 1 that has address info (including a customer reference no). The tables can then be linked to give you info aboout order and address.

 

[CEH]

Sounds like you really have what you need.... You just may not know it
You have one table, "Customers".... Second Table "Orders". If you have a form with a subform this should have been built on a query. A query of the "Customer" and "Order" tables. These tables should be joined... Looks like you are using CustomerID.. This field alone should be in both tables.
Your report can then be done off of the same query used to build the form and it will return the info you want. If you insert one CustomerID number in the query then run it... you will see it only returns records for that customer.

 

[Help Wanted]

Thanks for your help guys.

Turns out i did have exactly what i wanted. Having a customer reference for the address in both tables linked up in the relationships bit. All i had to do was put in the query and adapt it to show the results i wanted and then produce the report.

Great stuff.