ACCESS FEED: Re: [MS_AccessPros] duplicate records

John, It took me a longer to get to it than I thought but after I fixed the name on one of my combo boxes this worked.

The user will not get a message if they try to put one player on two seperate teams.

Awsome! Thanks you very much for your help.

Thanks
Bill

--- In MS_Access_Professionals@yahoogroups.com, "Bill Singer" <Bill.Singer@...> wrote:
>
> Thanks John,
>
> I will work on this tonight and let you know how it goes.
>
>
>
> Bill Singer
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> Sent: Thursday, March 15, 2012 3:21 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] duplicate records
>
>
>
>
>
> Bill-
>
> There must be values in cboPlayer on the current form,
> [Forms]![f_TeamCfgChooser]![cboTeamName], and
> [Forms]![f_TeamCfgChooser]![cboSeason]. If not, the DLookup will fail. It's
> basically doing:
>
> SELECT tfgTfgID FROM t_TeamConfiguration
> WHERE rfgPlaID = [this player]
> AND tfgTnaID <> [this team]
> AND tfgTnaID IN
> (SELECT tnaTnaID FROM t_TeamName
> WHERE tnaSeaID = [this season])
>
> "If you can find a team configuration record with the selected player but
> another team, and that team is in this same season, then ..."
>
> I would guess from the syntax error that there's nothing in cboPlayer. This
> code should be running in the Before_Update event of the cboPlayer control
> on
> the current form.
>
> 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)
>
> ---------------------------------------
> 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 agent1of6
> Sent: Thursday, March 15, 2012 3:01 AM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: Re: [MS_AccessPros] duplicate records
>
>
> John,
> Thanks for the info. I have been researching DLookup.
>
> I took what you started with and changed a few form names.
>
> Here is what I have.
>
> If Not IsNull(DLookup("tfgTfgID", "t_TeamConfiguration", "tfgPlaID = " & _
> Me.cboPlayer & " AND tfgTnaID <> " &
> [Forms]![f_TeamCfgChooser]![cboTeamName] &
> _
> " AND tfgTnaID IN (SELECT tnaTnaID FROM t_TeamName " & _
> "WHERE tnaSeaID = " & [Forms]![f_TeamCfgChooser]![cboSeason] & ")")) Then
> MsgBox "You have already placed this player on another team " & _
> "for the current season."
> Cancel = True
> End If
>
> The combo boxes that control this form are on another form called
> f_TeamCfgChooser.
>
> It looks like this statement is saying.. find a tfgTfgID (PK) in the
> t_TeamConfiguration (table), where the tfgPlaID (playerID) in the PlayerID
> combo
> box on this form and the tnaTnaID (Team Name ID) is not equal to the
> tnaTnaID
> (Team Name ID) on the chooser form combo box and the seaSeaID (Season ID) is
> the
> same.... send a message if another tfgTfgID is found.
>
> I am a little fuzzy on parts of it, such as the IN and the AND.
>
> I tried to run it and here is the message I received.
>
> Run-time error '3075'
> Syntax error (missing operator) in query expression 'tfgPlaID = AND
> t_TeamName
> WHERE tnaSeaID = 1)'.
>
> The season I used was 2012 which has a ID value of 1.
>
> Any ideas?
>
> Thanks,
> Bill
> MN
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas
> <JohnV@> wrote:
> >
> > Bill-
> >
> > The Before_Update event procedure of your combo box could look something
> like:
> >
> > If Not IsNull(DLookup("tfgTfgID", "t_TeamConfiguration", "tfgPlaID = " & _
> > Me.cboPlayer & " AND tfgTnaID <> " & Me.Parent!cboTeam & _
> > " AND tfgTnaID IN (SELECT tnaTnaID FROM t_TeamNames " & _
> > "WHERE tnaSeaID = " & Me.Parent!cboSeason & ")")) Then
> > MsgBox "You have already placed this player on another team " & _
> > "for the current season."
> > Cancel = True
> > End If
> >
> >
> > Note that I'm assuming the control containing the Team ID on the outer
> form is
> > called cboTeam.
> >
> > Basically, I'm looking for another record for the same player and a
> different
> > team where that team is one of the ones listed for the current season. If
> I
> > find that, you're entering a duplicate.
> >
> > 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)
> >
> >
> >
> >
> >
> >
> > 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 Bill
> Singer
> > Sent: Wednesday, March 14, 2012 5:22 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: RE: [MS_AccessPros] duplicate records
> >
> >
> > John
> >
> > The structure is as follows for Table that holds the team names.
> >
> > t_TeamNames
> >
> > tnaTnaID PK
> >
> > tnaSpoID FK to t_Sport - to designate the sport the team
> > name is associated with
> >
> > tnaSeaID FK to t_Season - to designate the Season the
> > team name is associated with
> >
> > tnaName Text, which is the team Name , such as
> > "Raptors"
> >
> > tnaGraID FK to t_Grade - to designate the grade the team
> > is associated with
> >
> > thanks for your help.
> >
> > Bill Singer
> >
> > 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 John
> Viescas
> > Sent: Wednesday, March 14, 2012 11:05 AM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: RE: [MS_AccessPros] duplicate records
> >
> > Bill-
> >
> > What's the structure of t_teams? Basically, you need to do a DLookup to
> > ensure
> > that the assigned player ID doesn't show up in any other record in t_Team
> > Configuration for another team in the same season. If you give me the
> > structure
> > of t_teams, I can take a stab at some code.
> >
> > 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)
> >
> > -----------------------------
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
> > Singer
> > Sent: Wednesday, March 14, 2012 4:58 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: RE: [MS_AccessPros] duplicate records
> >
> > John,
> >
> > I ruined it last night so I had to recreate it.
> >
> > Here is the SQL of the Combo Box, which pulls information from a Query.
> >
> > SELECT q_PlayerSeason.plaPlaID, q_PlayerSeason.FullName
> >
> > FROM q_PlayerSeason
> >
> > ORDER BY q_PlayerSeason.[FullName];
> >
> > Here is the Query that it pulls from, which sorts players by Season, Sport
> > and Grade to make sure that only 9th grade basketball players get assigned
> > to a 9th grade basketball team.
> >
> > SELECT t_Players.plaPlaID, t_Players.plaFN, t_Players.plaMI,
> > t_Players.plaLN, [plalN] & " , " & [plaFN] & " , " & [plaMI] AS FullName,
> > t_FeePayments.fpaSeaID, t_FeePayments.fpaSpoID, t_FeePayments.fpaGraID
> >
> > FROM t_Players INNER JOIN t_FeePayments ON
> > t_Players.plaPlaID=t_FeePayments.fpaPlaID
> >
> > WHERE (((t_FeePayments.fpaSeaID)=Forms!f_TeamCfgChooser!cboSeason) And
> > ((t_FeePayments.fpaSpoID)=Forms!f_TeamCfgChooser!cboSport) And
> > ((t_FeePayments.fpaGraID)=Forms!f_TeamCfgChooser!cboGrade));
> >
> > Thanks,
> >
> > Bill Singer
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
> > Viescas
> > Sent: Wednesday, March 14, 2012 9:50 AM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: RE: [MS_AccessPros] duplicate records
> >
> > Bill-
> >
> > What's the Row Source (the SQL, please) of the Player combo box on the
> > subform?
> >
> > 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)
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
> > Singer
> > Sent: Wednesday, March 14, 2012 3:28 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: RE: [MS_AccessPros] duplicate records
> >
> > I thought I had this problem licked last night, but at the end of the
> night,
> > failure.
> >
> > I have a very small table that associates players with teams. Teams are
> > associated with seasons (2012 or 2013)
> >
> > Here is the table.
> >
> > t_TeamConfiguration
> >
> > tfgTfgID PK
> >
> > tfgTnaID FK to t_TeamName
> >
> > tfgPlaID FK to t_Player
> >
> > I am trying to figure out how to make sure I do not assign one kid to two
> > different teams during the same season. So I cannot ever have a duplicate
> > combination of the PlayerID and the SeasonID, which is on the t_TeamName.
> >
> > The team setup is done on a form with a subform. The Main form is tied to
> > the TeamName, the subform is a continuous form with a combo box that
> selects
> > kids name. I sorted the records my combo box sees by players that have a
> > Null team assignment but that affected all my records on my continuous
> > subform and not just the new record.
> >
> > Can you point me I the right direction. I do better with Macros than Code.
> >
> > Thanks for your guidance.
> >
> > Bill Singer
> >
> > MN
> >
> > Access 2010
> >
> > [Non-text portions of this message have been removed]
> >
> > _____
> >
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 2012.0.1913 / Virus Database: 2114/4870 - Release Date: 03/14/12
> >
> > [Non-text portions of this message have been removed]
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
> _____
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.1913 / Virus Database: 2114/4871 - Release Date: 03/14/12
>
>
>
> [Non-text portions of this message have been removed]
>