Multiple subforms -- same table

[Guest]

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?

 

[Guest]

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

 

[Guest]

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.....

 

[Guest]

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

 

[Guest]

I am going to assume a little.

When you say that the subforms would not update i am going to assume that
you open the form and then change the WBS and nothing happens. To allow the
subforms to update I would write very simple VBA code in the AfterUpdate
field.

Private WBS_AfterUpdate()
me.subform1.requery
me.subform2.requery
me.subform3.requery
End Sub

Hope this helps.