Access Append Query

I have a table to register organization names (Org) and a related table
(Area) to store the area's where these organizations operate. When a new
Organization is registered, the area(s) they work in are set.
My form collects the field "Area". On exit from this field on the form I
would like to use the "Area" in the criteria field of my APpend Query. Is
this possible?

Why use an append query? A form / subform design will do that for you
automatically. The main form would be bound ot the Org table, and the
subform would be bound to the Area table. As the user enters data in either
form or subform, the corresponding table is automatically updated.

If for some reason you don't want to use form / subform, tell us how your
form is structured so that it allows you to enter new Org and the Area info.

--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/


"Kary" wrote in message
...
I have a table to register organization names (Org) and a related table
(Area) to store the area's where these organizations operate. When a new
Organization is registered, the area(s) they work in are set.
My form collects the field "Area". On exit from this field on the form I
would like to use the "Area" in the criteria field of my APpend Query. Is
this possible?

As Ken says, a subform is a far easier solution, though its not a difficult
task to execute an append query in code for each area to which an
organization is assigned. However, unless each area has only one
organization operating in it at any one time, you'll need a third table to
model the many-to-many relationship between Orgnizations and Areas. The Area
table would have only one row per area and the new table, AreaOrganizations
say, would have one row per organization/area combination. It would do this
by having two foreign key columns (fields) Organization and Area each
referencing the primary keys of the other two tables. The primary key of
this table would be a composite one made up of the Organization and Area
columns. It might well also have other columns representing other attributes
of each organization's involvement with an area, e.g. the date when they
become involved.

Again a subform would be the easiest solution for data entry, but would now
be based on the AreaOrganizations table and be linked to the parent form on
the Organization columns. The subform would have a combo box bound to the
Area column, with its RowSource listing the areas from the Area table, so
that a user can simply pick an area from a list, along with other controls
bound to any other columns. It would be unnecessary to include a column
bound to the Organization column in the subform, however, as the value is
entered automatically into this column behind the scenes by means of the
linking mechanism.

Ken Sheridan
Stafford, England

Kary wrote:
I have a table to register organization names (Org) and a related table
(Area) to store the area's where these organizations operate. When a new
Organization is registered, the area(s) they work in are set.
My form collects the field "Area". On exit from this field on the form I
would like to use the "Area" in the criteria field of my APpend Query. Is
this possible?

--
Message posted via http://www.accessmonster.com