What code appends the Orders and OrderDetails tables in Northwind?

[SIman]

Hi all,
Another daft question from a newbie
I have been studying the Northwind 2 database and trying to get my head around how it works. I thought I was getting somewhere but have realised that I can't see what updates the Orders and OrderDetails tables (or the Companies table for that matter).
When you use the frmOrderDetails form and create a new order the new order pops up in the Orders table and the details pop up in the Order Details table. So, I thought that in the code I was going to find some 'SQL append' stuff going on that updates the tables... but I can't find any, nor what on earth is modifying those tables.
Somebody please put me out of my misery before I give up !!!
Sara x

 

[plog]

The form.

When you use a bound form, Access does all that behind the scenes and saves you from a ton of work. You won't find the SQL anywhere, it's just inherent in what a bound form does.

 

[SIman]

Ahhhh!! I see. Just had another look and I see it's bound to a query called qryOrder. So does that mean that changes made in the form can append data to a table just by the table being referenced in a query? Thanks for you help, I'll keep trying to work it out!
Sara x

 

[Albert D. Kallal]

Ahhhh!! I see. Just had another look and I see it's bound to a query called qryOrder. So does that mean that changes made in the form can append data to a table just by the table being referenced in a query? Thanks for you help, I'll keep trying to work it out!
Sara x

Sure, but any old form in Access as "general" rule is able to add new records. I Guess we could call this a append, but we tend to think of this that the form is on a new record, user edits the data, and when you leave that new record is automatic saved/updated to the table.

So, even the most simple bound form can add new records, and if you hit the record navigation button ">*", you are now editing a new record that will be saved.

So, this button:

So, navigation area has next, previous record, first record, last record, and new blank record.
So, this is built into access, and in general no code in a form is required to edit existing records, or jumping to a new record of which then you edit, and it will be saved for you. So, editing of records, and that of creating a new record does not require code in Access.

The given form in question can be based on a query, or directly to the table - but the results are 100% the same (in near all cases).
Hence, often there is little or no reason to base a form on a query as opposed to basing the form directly on the table.

And of course in some cases, you might want/have/desire/think good idea/save user some trouble to have say some button called "add new customer", and with your code (docmd.OpenForm), you can launch a form in add mode, and this just saves the user having to hit/navigate to a new blank record to edit......

 

[tvanstiphout]

> I have been studying the Northwind 2 database and trying to get my head around how it works.
Good for you. That's exactly why we made them: to discover what Access can do, and learn a few new things in the process.
Use the yellow information buttons in the upper right corner of most forms. Also check out the "Northwind Features" form, and the Learn page.
Come back here for any questions. We love to talk about NW2.

 

[Ken Sheridan]

So does that mean that changes made in the form can append data to a table just by the table being referenced in a query?

Provided the query is updatable. A simple query based on a single table, where no values are aggregated in the query, will be updatable. If the query aggregates values e.g. returns the sum of the costs of all items ordered, grouped by customer, then that query won't be updatable.

You'll sometimes see it said that a form for entering and/or editing data should not be based on two or more joined tables. That's an oversimplification, however. A form joining a referenced table to a referencing table, e.g a Customers table to an Orders table, should not be used for entering or editing data in both tables. The correct way to do that would be to have a form based on Customers, and within it a subform based on Orders, linking the subform to the parent form on CustomerID.

On the other hand, it is perfectly valid to base a form on a table which, in the form's query, is joined to one or more referenced tables, e.g a form might be used to edit rows from an Orders table where the form is based on a query which joins the Orders table to Customers, Cities, and Regions tables:

SELECT
    Orders.OrderNumber,
    Orders.OrderDate,
    Orders.Invoiced,
    Orders.CustomerID,
    Customers.Address,
    Cities.City,
    Regions.Region
FROM
    Regions
    INNER JOIN (
        Cities
        INNER JOIN (
            Customers
            INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
        ) ON Cities.CityID = Customers.CityID
    ) ON Regions.RegionID = Cities.RegionID;

However, the form should only be used to enter or edit data in the controls bound to columns from the Orders table. The controls bound to columns from the other tables should have their Locked property set to True (Yes) to prevent the user attempting to edit them. Note that the query does not include the customer's names. This is because, in the form, the control bound to the CustomerID column in the Orders table would be a combo box, set up so that the first column (CustomerID) returned by its RowSource query would be hidden, so that the user sees the customer's name in the visible second column, which, in the query would concatenate the FirstName and LastName columns as follows:

Customers.FirstName & " " & Customers.LastName AS Customer

There are other ways of showing data from referenced tables in a form. For instance, the customer address, city and region names could be returned in additional columns in the CustomerID combo box's RowSource query, and those columns referenced in unbound controls in the form, in which case it would not be necessary to include the Customers, Cities, and Regions tables in the form's query.