Updating two tables using 1 form

[scottcraft]

I am using two tables in a consolidated form. I would like to be able when I update the form that both tables be updated. Meaning that the new primary key information placed in the 1st table be automatically inserted to the 2nd table. The items in both tables are CUSTOMER and PARTNUMBER. Please treat me like a kid and spell out where exactly I have to go and what I must do to create such a link. thanks in advance.

Army Scott

 

[HitechUser]

Army,

Your question is a little vague. Are you just wanting the keys to be updated? Then how about this.

http://msdn.microsoft.com/library/d.../off2000/html/acconWhatIsCascadingUpdateS.asp

I hope this helps..

Good Luck...

 

[GingerR]

what is the table structure of all the tables involved? i.e.

Table Name:
Field:
Field:

How are the tables related?
What is the recordsource of the form?
When you say you are "using two tables in a consolidated form" what do you mean? you have two Subforms?
What data in the form are you updating, and what data in both tables do you then want to be updated?

 

[scottcraft]

Wow guys lots of Q's, thanks

Ok, there are 2 tables in question

table 1 named STEP 1
customer (primary Key)
address
city
state

table 2 named STEP 2
customer
buyer (primary key)
buyerName
buyerPhone
buyerFax

table 3 named STEP 3

Customer
PartNumber (each customer has their own part numbers)(primary key)

table 4 named DATABASE
Transaction (AUTONUMBER)(Primary Key)
Customer
Buyer
PartNumber
PricePer
other various fields.

desire to add new customer, new buyer, new partnumber and new transaction on one form.

 

[GingerR]

First some structural questions:

0) Is each Buyer really only related to one Customer? Or can one Buyer buy for more than one customer? What's a Buyer? Do they work for the customer or for you?

A) I would have a CustomerID, not use "Customer" as the Primary Key. Customer names can change. Then have CustomerID as foreign keys in your other tables.

B) Can a transaction only have one Part on it?
C) Can a transaction only have one Buyer on it?
D) Just making sure: a PartNumber can only be bought by a single customer? I mean that two different customers can not buy the same part?

Suggestions:

1) I would name the tables something a little more descriptive, like "Customers","Buyers","CustomerParts" and "Transactions".

2) Have you checked out the Northwind.mdb sample database that comes with MS Access? It could help you out a lot.

So let us know the answers to those questions and we'll proceed!

 

[scottcraft]

thanks again GingerR

0) Yes each Buyer is related to only one customer. However, there can be numerous buyers for the customer. Buyer is the name of the individual representing the customer.

A) You are correct, there was a company who changed names, (side notte: I build this in Excel last year and it worked fine, just trying to give them some bells and whistles) and half the buyer information was for one customer and then switched. How can I best use CustomerID?

B) Yes, a transaction can be one part, or many parts
C) Yes, transaction can only have one buyer
D) A PartNumber is assigned to each part by the CUSTOMER, not the maker. Stating that, various buyers from within the CUSTOMER can order the parts. The buyer is the POC at the company.

I thought of the simpler names also. That's why I picked Step 1, Step 2, Step 3 and Database. But your suggestion is probably better. I'm open to any suggestions. I really want to learn this stuff.

thanks

Army Scott

 

[GingerR]

Not sure what you mean by your question at the end of A).

I think things are set up pretty ok.

Usually folks have the database open to a Switchboard sort of form. I don't use the Access 'switchboard' feature. I make my own form. On it you'd have a button like:

Add Customers
Add Buyers
Add Parts
Transactions

D) Add Customers: would open a form based on the table Customers where you fill in unique info like Address, etc.

E) Add Buyers: would open a form based on table Buyers so you can enter info there.

F) Add Parts: ditto

G) Transactions: bringing it all together--you'd have a form based on the table Transactions. There'd be a combo box where you choose the customer. There'd be another combo box where you'd choose the buyer. Then a subform where you'd choose Parts (a subform cause you can choose many parts).

Try starting there and let's see how far you can get.

g