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

Coach Bill-

Is 8 the ID of the child or the parent? Both David and Spencer should have the
SAME parent ID as a foreign key. Each parent can have many children.

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@yahoogroups.com] On Behalf Of Bill Singer
Sent: Tuesday, April 24, 2012 9:56 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Many-to-Many input form

Yes, but if a parent has two kids in the league then the parent will be
listed two times.

My son David has a parent, Bill (me) with and ID of 8.
My son Spencer has a parent, Bill (me) with an ID of 27.

Parents are the coaches of the teams and if I want to track the coaching
history the system does not know that coach Bill, ID 8 is the same as coach
Bill, ID 27. Especially if I coach two separate teams there is no way to
see the merged history of my coaching.

Maybe there is another way to handle it that I am not aware of.

Coach Bill
MN

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of argeedblu
Sent: Tuesday, April 24, 2012 2:30 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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]
>
>
>

[Non-text portions of this message have been removed]

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

Yahoo! Groups Links

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