John,
Below is my first shot at a preventing this multiple posting of game times. However, here is the funny thing, if I run the append query off of a button on a form I do not think there is any place to put the code. A button is not like a combo box. Kind of funny.
If Not IsNull(DLookup("fscFscID","t_FacilitySchedule",&_
"fscFacID"=[forms]![f_8TeamSeasonSchedule]![cboLocDay1]&_
"fscSchDate"=[forms]![f_8TeamSeasonSchedule]![hlpDay1Date]&_
"fscSchStartTime"=[forms]![f_8TeamSeasonSchedule]![hlpGame1Time]))&_
Then MsgBox "You have already schedule this game or another activity at this time"
Cancel=True
End If
Here is the table I am appending to:
Table Name: t_FacilitySchedule
t_fscFacID
fscFscID PK
fscFacID FK to t_Facility
fscSpoID FK to t_Sport
fscSchDate Date of Activity
fscSchStartTime Start Time of Activity
fscSchStopTime
fscEveID FK to t_Event
--- In MS_Access_Professionals@yahoogroups.com, "Bill Singer" <Bill.Singer@...> wrote:
>
> John,
>
> John, Hopefully this will answer your first question.
>
>
>
> This is going to be tricky to explain. I wish I could send an attachment.
> I am working on this sports database. As an helpful game scheduling tool to
> the administrator of the league, I have created a form that will show a 10
> game schedule for a league with 8 teams. (very common where I am) It is a
> standard game schedule for an 8 team league. (Team1 vs. Team2, Team3 vs.
> Team4, etc)
>
> Each day has 4 games with two teams per game. On that form you can also
> enter dates across the top and game times on the left side. It looks
> similar to an excel spreadsheet. However, the form is just a visual
> representation of how a schedule could look and it is only achieved by me
> rearranging the text boxes for each day. There are only 8 different text
> boxes, I just make copies and re-arrange them for each game date to visually
> show how the team match ups will look. The underlying table only had 8 TEAM
> fields and not 80, which is how many show on the form.
>
>
>
> However it would be great if the administrator could schedule the games from
> that form. For example, if the administrator liked how the schedule looked
> for GameDay1 he could click the button on the form and it would run an
> append query that would grab the DATE, TIME, LOCATION, & TEAMS for the
> GameDay1 schedule and post them to the SCHEDULING table. I have the append
> queries built (and they work) and I know how to do buttons. The issue I
> want to avoid is a person pushing the button multiple times and scheduling
> the same game multiple times. If they do push the button multiple times I
> want a nice message to show up.
>
>
>
> Hopefully that makes sense.
>
>
>
> I will get back with the other info when I get home.
>
>
>
> Thanks,
>
> Bill Singer
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> Sent: Thursday, May 17, 2012 1:52 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Indexes
>
>
>
>
>
> Bill-
>
> The only way to trap the error is to test for a duplicate in Before Update,
> issue your own error message, and cancel the save. Why are you running an
> Append query? If the user is entering the new data in a form bound to the
> table, that shouldn't be necessary. I would need to know the structure of
> your
> table (at least the key fields) to help you with the Before Update 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@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
> Singer
> Sent: Thursday, May 17, 2012 8:19 PM
> To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: RE: [MS_AccessPros] Indexes
>
>
> I have created a unique index on a table to prevent duplicate records from
> being added,(you can't play two events at the same facility at the same
> time) however the error message that comes up is not very user friendly. I
> am wondering if there is a way to change that message?
>
> The other option is to put a test and message box in the before update
> property of the button that runs that append query that updates the table.
> However this programming is a bit more difficult for me.
>
> Thanks for steering me in the right direction.
>
> Bill Singer
>
> MN
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar