Main form/Subform problem

[arogers]

(Access 97) I have a main form "Customers"and a subform "Orders" linked via
CustomerID. I have the field name "AccountName" in both tables. I would like
to display the same data in each field without having to enter in manually in
the subform every time. I have tried adding the fields in the link child
field, which does display what I want, but it effects the OrdersID Autonumber
and no longer displays the unique order numbers for each customer as it did
before.Can you help?

Regards

Allan

 

[Guest]

Why (if you have the tables linked) would you want to have the same field in
both tables? This defeats the purpose of a relational database design.
Simply have the field in one table, and if you need it, join the tables with
a query and grab it from there.

Damian.

 

[arogers via AccessMonster.com]

The reason I wanted the AccountName displayed in the Orders Table is because
I have many employees linked to many different customers and orders. When I
run the orders table it displays the name of the employee the CustomerID the
OrderID but I want to see which account names go with which employees so I
can quickly filter the information to see which employees are associated with
particular customers and there orders. At the moment it just displays the
cutomersID Number. If this not possible and there is a query that will do
this for me, please let me know the detail.

Kind regards

Allan

Why (if you have the tables linked) would you want to have the same field in
both tables? This defeats the purpose of a relational database design.
Simply have the field in one table, and if you need it, join the tables with
a query and grab it from there.

Damian.

(Access 97) I have a main form "Customers"and a subform "Orders" linked via
CustomerID. I have the field name "AccountName" in both tables. I would like

[quoted text clipped - 7 lines]

Allan

 

[John Vinson]

The reason I wanted the AccountName displayed in the Orders Table is because
I have many employees linked to many different customers and orders.

Of course. That's how relational databases work.

When I
run the orders table it displays the name of the employee the CustomerID the
OrderID but I want to see which account names go with which employees so I
can quickly filter the information to see which employees are associated with
particular customers and there orders. At the moment it just displays the
cutomersID Number. If this not possible and there is a query that will do
this for me, please let me know the detail.

Tables should NEVER be used for routine interaction with data. Tables
are designed for data storage, *not* for data display or editing!

To interact with the table, use a Form - with a combo box storing the
CustomerID but displaying the customer name, another combo box storing
the EmployeeID but displaying the employee's name, etc.

You should only open the Table (and see the numeric ID's there) for
debugging purposes.

John W. Vinson[MVP]

 

[arogers via AccessMonster.com]

Sorry! I meant displayed in Forms not tables. I have a forms for both orders
and eployees but the dony display the account name.

Many thanks

Allan

The reason I wanted the AccountName displayed in the Orders Table is because
I have many employees linked to many different customers and orders.

Of course. That's how relational databases work.

When I
run the orders table it displays the name of the employee the CustomerID the

[quoted text clipped - 3 lines]

cutomersID Number. If this not possible and there is a query that will do
this for me, please let me know the detail.

Tables should NEVER be used for routine interaction with data. Tables
are designed for data storage, *not* for data display or editing!

To interact with the table, use a Form - with a combo box storing the
CustomerID but displaying the customer name, another combo box storing
the EmployeeID but displaying the employee's name, etc.

You should only open the Table (and see the numeric ID's there) for
debugging purposes.

John W. Vinson[MVP]

 

[John Vinson]

Sorry! I meant displayed in Forms not tables. I have a forms for both orders
and eployees but the dony display the account name.

There's a typo there: I cannot figure out what you mean by "the dony".

What type of Control are you using on the form for the account name?
If you're using a textbox bound to the AccountID, I'd suggest right
mouseclicking it and select the option to Change To a Combo Box. You
can use a Combo Box to select and store an AccountID from your
Accounts table, but display the account name; similarly for other
fields.

John W. Vinson[MVP]

 

[arogers via AccessMonster.com]

Sorry for the typo error. I have used your Combo Box suggestion.

Many thanks

Allan