Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / February 2006

Tip: Looking for answers? Try searching our database.

Add a record only if

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Tee See - 27 Feb 2006 22:12 GMT
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
KARL DEWEY - 27 Feb 2006 23:07 GMT
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.

> 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.
[quoted text clipped - 3 lines]
>
> Regards
Tee See - 27 Feb 2006 23:56 GMT
Thanks Karl, for your response. Is there a VBA way to do this without having
the subform?
> First you should have a one-to-many relation between the Customer table
> and
[quoted text clipped - 14 lines]
>>
>> Regards
Ken Sheridan - 27 Feb 2006 23:56 GMT
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

> 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.
[quoted text clipped - 3 lines]
>
> Regards
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.