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

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
>