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]
Tidak ada komentar:
Posting Komentar