Add a record only if

T

Tee See

I know this has been asked many times before and I know I have the answer in
a book but cannot seem to think of what the lookup would be.
Want to add a record to a the "orders" table only if the customernumber is a
valid entry in the "Customers" table. A brief example or compuer link would
be appreciated.

Regards
 
K

KARL DEWEY

First you should have a one-to-many relation between the Customer table and
the Orders table.
Then use a main form (Customers) with subform (Orders) for your data entry -
both using the same query that has both tables joined on CustomersID field.
 
K

Ken Sheridan

The sample Northwind database which comes with Access illustrates this.
Firstly you'll see that in the relationships window the relationship between
Customers and Orders has referential integrity enforced. It also has a
referential cascade update operation enforced, though as the CustomerID
column in Customers is an autonumber and can’t be changed, that is not
actually necessary.

As regards data entry the usual approach, and that adopted in Northwind, is
to have an Orders form which in the case of the Northwind example is based on
a query which joins the Orders and customers table. The latter table is in
the query to supply not only the customer name, but also the customer's
address data which is used to insert values into the various shipping
controls in the form. In a less complex form you would probably not include
the Customers table in the underlying query, just a combo box bound to the
foreign key customernumber column. Values from other columns in Customers
for that customer can be shown in unbound controls on the form.

You'll see that the Orders form in the Northwind database includes an order
details subform which shows the individual items for each order. This
subform is based on the Order Details table which models the many-to-many
relationship type between Orders and Products. The subform is linked to the
parent form on the OrderID columns in the tables.

Ken Sheridan
Stafford, England
 
T

Tee See

Thanks Karl, for your response. Is there a VBA way to do this without having
the subform?
 
Top