ACCESS FEED: RE: [MS_AccessPros] Re: Playing with subforms again

Walter-

When you want to define a multiple field PKey in Access, open the table in
Design view, select the two fields you want as the PKey, then click the Key icon
to set it.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of nkpberk
Sent: Wednesday, June 29, 2011 10:12 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Playing with subforms again

John;
I guess I don't understand the 2 key PKey.
an example for dummies please.
Walter

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Walter-
>
> If there are other tables related to your "primary" ones, you'll have to
adjust
> the foreign keys in those to match the new two-key PKey.
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of nkpberk
> Sent: Wednesday, June 29, 2011 8:51 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Playing with subforms again
>
> John;
> I got it to work (with one form anyway)! I'll go break another form and see if
I
> can make it work with 2 :-)
> Walter
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "nkpberk" <wgriffin48@>
> wrote:
> >
> > John; "Tabletype" is a new field containing "Book"? Yes?
> > also I have another subform on a couple of parent forms listing
> article/subjects/page# etc as necc. How bad is this gonna fubar the other
> dependencies? (that work)
> > Walter
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
wrote:
> > >
> > > Walter-
> > >
> > > Close, but no cigar. In the Book table, the key would be:
> > >
> > > TableType: "Book"
> > > ID: AutoNumber
> > >
> > > In the child table, the keys would be:
> > >
> > > TableType: String
> > > ID: Long Integer
> > >
> > > In the Book table, make the Default value of TableType "Book". Include
that
> > > field in the recordset of the form, but you don't have to show it on the
> outer
> > > form. Set the Link Child / Link Master properties: TableType; ID
> > >
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of nkpberk
> > > Sent: Wednesday, June 29, 2011 7:00 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: Re: [MS_AccessPros] Playing with subforms again
> > >
> > > John;
> > > are you saying to use a calculated field of tabletype "Book" +
> str(autonumber)
> > > to create a PKey in the parent form?
> > > and do the same for the subform "CollInfo" + str(autonumber)?
> > > An example would help a lot.
> > > Walter
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> wrote:
> > > >
> > > > Walter-
> > > >
> > > > That's not a 1-1 relationship. For starters, IDCollectionsInfo cannot
be
> > > > AutoNumber in the subform table. If all the Parent tables have an
> AutoNumber
> > > > PKey, you cannot build the relationship. You would need a PKey in each
> > > "parent"
> > > > table that is perhaps a combination of table type and a unique number -
> that
> > > > unique number could be AutoNumber. The PKey in the child table would
have
> to
> > > be
> > > > TableType and a Long Integer field to match the AutoNumber. TableType
> could
> > > be
> > > > a field not displayed with a different default value in each Parent
table.
> > > You
> > > > could then set up the subform to have Link Master / Link Child settings
> using
> > > > both the TableType and Number fields in both tables.
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2010 Inside Out
> > > > Microsoft Office Access 2007 Inside Out
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas.com/
> > > > (Paris, France)
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: MS_Access_Professionals@yahoogroups.com
> > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of nkpberk
> > > > Sent: Wednesday, June 29, 2011 6:28 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: Re: [MS_AccessPros] Playing with subforms again
> > > >
> > > >
> > > >
> > > >
> > > > John;
> > > > The key fields for the "Parent" tables are an auto number, however I
have
> a
> > > > number field in each "parent" forms table called "idCollectionInfo" This
> needs
> > > > the value of the subforms table Key field "IDCollectionsInfo" also an
auto
> > > > number field to link the 2 records together.
> > > >
> > > > (sorry I use the same names I just capitalize "ID" when it is a key
field)
> > > >
> > > > Apparently I need some sort of query inserting that subform key field
> value
> > > > "IDCollectionInfo" into the parent forms table receiving field
> > > > "idCollectionInfo"
> > > > I don't know just how to format the query or what event on the parent
form
> to
> > > > place it into.
> > > > Walter
> > > >
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> wrote:
> > > > >
> > > > > Walter-
> > > > >
> > > > > I need to see the structure of the two tables - at least the relevant
> key
> > > > > fields. What is the Primary Key of one of the "Parent" tables, and
what
> is
> > > > the
> > > > > Primary Key of the "Child" table?
> > > > >
> > > > > John Viescas, author
> > > > > Microsoft Office Access 2010 Inside Out
> > > > > Microsoft Office Access 2007 Inside Out
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside Out
> > > > > SQL Queries for Mere Mortals
> > > > > http://www.viescas.com/
> > > > > (Paris, France)
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of nkpberk
> > > > > Sent: Wednesday, June 29, 2011 4:54 PM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: Re: [MS_AccessPros] Playing with subforms again
> > > > >
> > > > > John;
> > > > > I should probably note that the data entry on the subform works BUT I
> cannot
> > > > > figure out how to get the subforms record ID to append to the parent
> form.
> > > The
> > > > > data is there and if I "plug in" the record ID manually into the
parent
> > > forms
> > > > > table (In spreadsheet mode) when I review the form view it works (but
> that's
> > > > > seriously impractical ;-)
> > > > > Walter
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, "nkpberk"
<wgriffin48@>
> > > > > wrote:
> > > > > >
> > > > > >
> > > > > >
> > > > > > John:
> > > > > > The "parent" form contains information about the Item being
> inventoried.
> > > > > Currently there are 3 "parent" forms, Book, media and periodical
> containing
> > > > data
> > > > > like title, author, subjects etc.. Each "parent" form has a different
> table
> > > > > because the inventories have different table/field structures. I am
> trying
> > > to
> > > > > use a subform (the same subform) on all three "parent" forms
containing
> data
> > > > > such as cost ,condition, location/disposition etc.. as this data is
the
> same
> > > > > across all items in the library. So as to not duplicate all those data
> > > fields
> > > > in
> > > > > each table I thought I could link a "collectionInfo" table to each
> record in
> > > > the
> > > > > "Inventory" tables and thereby append only an "IDCollectionInfo" field
> to
> > > the
> > > > > tables populating the "parent" forms. The relationship is 1 to 1 as
the
> > > > > collectionInfo records are specific to each inventory item.
> > > > > > I hope that makes sense. The plan seems valid to me.
> > > > > > Walter Griffin
> > > > > >
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas"
<john@>
> > > > wrote:
> > > > > > >
> > > > > > > Walter-
> > > > > > >
> > > > > > > I'm having a hard time picturing this. What is the structure of
the
> two
> > > > > tables?
> > > > > > > Which is the "parent" table in the 1-1 relationship?
> > > > > > >
> > > > > > >
> > > > > > > John Viescas, author
> > > > > > > Microsoft Office Access 2010 Inside Out
> > > > > > > Microsoft Office Access 2007 Inside Out
> > > > > > > Building Microsoft Access Applications
> > > > > > > Microsoft Office Access 2003 Inside Out
> > > > > > > SQL Queries for Mere Mortals
> > > > > > > http://www.viescas.com/
> > > > > > > (Paris, France)
> > > > > > >
> > > > > > >
> > > > > > > -----Original Message-----
> > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> nkpberk
> > > > > > > Sent: Wednesday, June 29, 2011 2:16 AM
> > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > > Subject: [MS_AccessPros] Playing with subforms again
> > > > > > >
> > > > > > > Hello Group;
> > > > > > > trying something with a subform that's a bit different.
> > > > > > > I have a some forms which all share the some of the same data.
> > > > > > > this is a library/collections app, where I am segregating the
common
> > > data
> > > > to
> > > > > a
> > > > > > > subform I can use in each mainform. This is a 1 to 1 relationship.
I
> am
> > > > > doing
> > > > > > > this so I don't have to reinvent the wheel on every data entry
form
> and
> > > > > > > duplicate the data.
> > > > > > > I have made a "CollectionInfo" field in the table for the main
form
> and
> > > it
> > > > > > > should contain the value of IDCollectionInfo" from the table in
the
> > > > subform.
> > > > > > > The subform wiz want to link these backwards (subform field to
equal
> > > > > mainform
> > > > > > > field) the mainform field is nada! How do I make these play nice?
> > > > > > > Walter Griffin
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > ------------------------------------
> > > > > > >
> > > > > > > Yahoo! Groups Links
> > > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ------------------------------------
> > > > >
> > > > > Yahoo! Groups Links
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

------------------------------------

Yahoo! Groups Links