ACCESS FEED: RE: [MS_AccessPros] Many-to-Many input form

Bill,

The relationship between parents and children is one to many. What you may
be missing is a many to many relationship between children and sports --
one child can play many sports/each sport has many players. That doesn't
change the relationship between children and parents.

Glenn

Sent from my ASUS eee Transformer Prime Tablet
On Apr 24, 2012 3:21 PM, "Bill Singer" <Bill.Singer@at-group.net> wrote:

> **
>
>
> In designing my sports team database I thought a one-to-many between player
> and parents would be fine. It made it very easy to input the information on
> a form and a sub form. Now I have two boys in basketball and they both have
> a dad named Bill Singer (me) and now I have double records. This becomes a
> problem when I try to track some history information with for "Bill
> Singer".
> I know how to make an association table to make the relationship a
> many-to-many but how do I make a nice form that will allow me to input the
> player and the multiple parents on the same form. The one-to-many
> relationship made that easy, the many-to-many has my brain locked up
> because
> I need to populate the t_parents and then the new association table..and I
> would like to do it on the same form.
>
> Can you point me in the right direction.
>
> Bill Singer
>
> MN
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> Sent: Tuesday, April 24, 2012 2:05 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Re: Forms Population Question
>
> Terry-
>
> Then I don't understand what you're trying to do!
>
> 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/
> (Villefranche-sur-mer, France)
>
> ------------------------------
>
> From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Terry
> Olsen
> Sent: Tuesday, April 24, 2012 7:20 PM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: [MS_AccessPros] Re: Forms Population Question
>
> During initial data entry, the Badge is not in tblCurrentPersonnel, but
> will
> be
> once entered.
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas
> <JohnV@...> wrote:
> >
> > Terry-
> >
> > If Badge is in tblCurrentPersonnel, you can use the AfterUpdate event of
> the
> > Badge control to open a recordset on tblCurrentPersonnel for records with
> the
> > same Badge number. Grab the first record (if any), and populate the
> fields
> you
> > want. You can leave the text boxes unlocked so the user can change stuff
> after
> > your code loads the "default" values.
> >
> > 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/
> > (Villefranche-sur-mer, France)
> >
> > ------------------------------------------------
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Terry
> Olsen
> > Sent: Monday, April 23, 2012 3:09 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: [MS_AccessPros] Re: Forms Population Question
> >
> >
> > The record source of the form is tblCurrentPersonnel. All controls are
> bound
> > controls. Badge, ID, PIN, Name are text boxes. Currently, I enter the
> same
> data
> > into each of them, varying only Name to a slight degree.
> > What I'd like to have the form do is:
> > 1) I enter a value into badge
> > 2) I move to another control on the form and
> > 3) PIN, ID, and Name values are populated from Badge
> > 4) I'd also need to have Name be editable as I still need to add the
> > concatenation.
> > Thanks
> > Terryomsn
> >
> > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> wrote:
> > >
> > > Terry-
> > >
> > > OK, what's the Record Source of the form, and please explain how you're
> using
> > > Badge, ID, Name, and Pin and how you expect some values to
> autopopulate.
> > >
> > > 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/
> > > (Villefranche-sur-mer, France)
> > >
> > > ------------------------------------
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Terry
> Olsen
> > > Sent: Sunday, April 22, 2012 7:21 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > Subject: [MS_AccessPros] Re: Forms Population Question
> > >
> > >
> > > Thanks, Duane. I am not storing duplicate values in the table.
> > > Terryomsn
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , "Duane" <duanehookom@
> >
> wrote:
> > > >
> > > > Terryomsn,
> > > >
> > > > I think John was asking for more information regarding your tables.
> Are
> you
> > > really storing multiple duplicate values in the same table? Is the form
> bound
> > to
> > > some type of a transaction table? Is there a separate employee table?
> > > >
> > > > How about provide more context to what you are trying to do so
> someone
> can
> > > give the best help?
> > > >
> > > > Duane Hookom
> > > > MS Access MVP
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , "Terry Olsen"
> <terryomsn@>
> > > wrote:
> > > > >
> > > > > Thanks for your response.
> > > > > I currently have a bound control for each of four employee fields
> Badge,
> > ID,
> > > Name, and Pin. While using the form, I've found that Badge needs to
> entered,
> > and
> > > 2 of the 3 other values are always the same and the third (Name) varies
> only
> > by
> > > the concatenation of a 2 character code. That works fine if I continue
> to
> > enter
> > > the same value 3 times.
> > > > > While trying alternatives, I added 3 text boxes intending to
> display
> the
> > > value entered in Badge in ID, Name, and PIN after the Badge control had
> been
> > > populated and the user moved to another control on the form.
> > > > >
> > > > > Do I need to have these 3 additional text boxes or is it possible
> to
> > > populate the original bound controls in the same manner?
> > > > >
> > > > > I've been studying the Access 2010 Inside Out book. While I have
> found
> > > information on what the control and form events are, I haven't found a
> > > discussion yet that pertains to populating forms in this manner. I am
> also
> > > looking for how and when, or if I can, reference the values in the
> control
> and
> > > record buffers.
> > > > > Thanks
> > > > > Terryomsn
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> > wrote:
> > > > > >
> > > > > > Terry-
> > > > > >
> > > > > > How is the table containing those three fields related to the
> table
> > you're
> > > > > > editing? Access might fetch that info for you automatically if
> you
> > include
> > > that
> > > > > > table in your form's Record Source, and it's linked on Badge.
> > > > > >
> > > > > > 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/
> > > > > > (Villefranche-sur-mer, France)
> > > > > >
> > > > > > -----------------------------------
> > > > > >
> > > > > > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Terry
> > Olsen
> > > > > > Sent: Saturday, April 21, 2012 6:45 AM
> > > > > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > Subject: [MS_AccessPros] Forms Population Question
> > > > > >
> > > > > >
> > > > > > For a new entry, I have a data entry form that I want to reduce
> > keystrokes
> > > on.
> > > > > > In one bound control, badge, after initial data entry, I want to
> > populate
> > > three
> > > > > > other text boxes, txtEmployeeId, txtEmployeePin, and
> txtEmployeeID.
> > After
> > > all
> > > > > > entries on the form are complete, I want to populate fields in
> the
> DB
> > > table
> > > > > > columns for each of them.
> > > > > > Which event do I use?
> > > > > > What persists the data to the relevant DB record?
> > > > > > Thanks
> > > > > > Terryomsn
> > > > > >
> > > > >
> > > >
> > >
> >
>
> _____
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.1913 / Virus Database: 2411/4956 - Release Date: 04/24/12
>
> [Non-text portions of this message have been removed]
>
>
>