RE: Multiple subforms -- same table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Jim,

As I mentioned, I'm not really sure why your query was non-updateable--I was
just suggesting a common reason. I've seen a lot of users include the
primary key from the one side, instead of the corresponding foreign key of
the many side in their queries. So, SQL similar to the following should be
an updateable recordset:

SELECT MyManySide.*
WHERE MyManySide.TransactionType = x (where x is the particular value for
the particular subquery)

Hope that helps.
Sprinks

"JimS" wrote:

> So, are you saying that the reason the query became non-updateable was
> because of referential integrity...meaning I included only the "many" table
> in the query, and not the "one" table, thus making referential integrity an
> issue?
>
> My query does not use the one-side at all (since it's only for reference).
> Should I just include it and try it? I'll do that.....
>
>
> --
> Jim
>
>
> "Sprinks" wrote:
>
> > Jim,
> >
> > Your initial approach, to use queries as the subforms' RecordSource, is the
> > appropriate one. Although there are many reasons queries can be updateable,
> > the most common is including the primary key from the one side of a
> > one-to-many relationship.
> >
> > If this doesn't help, post the SQL version of your query, and I'll see if I
> > can help.
> >
> > Sprinks
> >
> >
> > "JimS" wrote:
> >
> > > I have a table with three different transaction types. It's Primary key is
> > > "WBS". The source table for main form has the same primary key and is set as
> > > a one-to-many relationship with the table having different transaction
> > > types...
> > >
> > > I want to develop a form where the user selects the WBS, and ther system
> > > displays three separate subforms, each based on the same table with each
> > > selecting all records of a given transaction type. I'd prefer they be
> > > updateable.
> > >
> > > The form/subform relationship seems to work fine. When I select WBS "XYZ",
> > > the subforms all jump to transactions related to "XYZ" just fine. Sadly, they
> > > don't filter for transaction type (no matter what I do to their control
> > > source or filter properties...), so all three subforms are essentially
> > > identical.
> > >
> > > I was able to make it work by using queries as the source for the subforms,
> > > but they become non-updateable.
> > >
> > > Any Ideas?
> > > --
> > > Jim
.

Relevant Pages

  • Re: sqlite empty inserts
    ... PRIMARY KEY, query text) ... Now for some reason, the first insert works, but thereafter all ... inserts result in empty rows ... query_table_name text PRIMARY KEY NOT NULL ... ...
    (comp.lang.python)
  • Re: "call to undefined function" mysql_error when adding new rows to table
    ... ie, for some reason, the INSERT query is trying to enter the new row ... with the same primary key as an already existing row, ... stupid question...) ...
    (alt.php)
  • Re: Problem with Access concatenate query
    ... records in the final query. ... You probably need to INNER JOIN the tables, although on what column, I am not sure. ... PriceID -- Primary Key ... ItemID --- Foreign Key ...
    (microsoft.public.access.queries)
  • RE: Processing thousands of records
    ... Jerry Whittle, Microsoft Access MVP ... Access automatically creates an index for primary key fields. ... that the query is working faster, you don't need the 1stVisit02 query. ... where do I read about fundamental indexing and normalization? ...
    (microsoft.public.access.queries)
  • RE: Query Problem, Please Help!!
    ... > this table to be laid out the way it is. ... The reason that experts recommend using a query as the Record Source for a ... The query ... will allow the designer to display the data in just about any way needed. ...
    (microsoft.public.access.queries)