John,
Well I have moved from one error to another. I think that is progress. I am now getting Error 3067, Query input must contain at least one Table. I have researched it on the web but had not found any clear solutions and now I have a lightening storm coming so I am shutting down for a while. Maybe I am missing some [] or a Dim.
I am attempting to get one of the three append queries to work in the code before I put all 3 in.
Here is the code, you will recogonize most of it.
Private Sub Command155_Click()
Dim intTrans As Integer, db As DAO.Database
On Error GoTo MyErrorTrap
Set db = CurrentDb
' Tell error trap we're starting a transaction
intTrans = True
' Start the transaction
BeginTrans
' Run the queries
db.Execute "INSERT INTO t_FacilitySchedule ( fscFacID, fscSpoID, fscSchDate, fscSchStartTime, fscEveID )" & _
"SELECT t_ScheduleHelper.hlpLocDay1, t_ScheduleHelper.hlpSpoID, t_ScheduleHelper.hlpDay1Date, t_ScheduleHelper.hlpGame1Time, 1 AS Event" & _
"FROM t_ScheduleHelper" & _
"WHERE (((t_ScheduleHelper.hlpHlpID)=[Forms]![f_8TeamSeasonSchedule]![hlpHlpID]));", dbFailOnError
' db.Execute "aq_EventPosting2Visitor", dbFailOnError
' Commit the transaction
CommitTrans
' Turn off the flag
intTrans = False
MyExit:
Exit Sub
MyErrorTrap:
' Display the error (You can also test the error # and display a custom msg)
MsgBox "Unexpected error: " & Err & ", " & Error
' If transaction in progress, roll it back
If intTrans Then Rollback
' Turn off the flag so we don't try to rollback again
intTrans = False
' Bail
Resume MyExit
End Sub
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Bill-
>
> Yes, you would use your error trap to test the Err value for duplicate value and handle accordingly.
>
> 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@yahoogroups.com] On Behalf Of bill.singer@...
> Sent: Saturday, May 19, 2012 4:53 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Re: Indexes
>
>
> John, upon further research I believe the answer is yes. I will adjust the code and give it a try. Is this the place I should also put the code to prevent the duplicates? I am at my daughter's graduation today so I will not get to this until tonight.
>
> Thank you for your help.
> Bill
>
> Sent from my Verizon 4G smartphone
>
> ----- Reply message -----
> From: "John Viescas" <JohnV@...>
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Re: Indexes
> Date: Sat, May 19, 2012 12:41 am
> Bill-
>
> What's the SQL of the three queries? Do they have parameters in them? If they
>
> do, you can't use Execute. You must set up the SQL in code with the parameters
>
> resoved, then execute that.
>
> 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@yahoogroups.com] On Behalf Of agent1of6
>
> Sent: Friday, May 18, 2012 10:50 PM
>
> To: MS_Access_Professionals@yahoogroups.com
>
> Subject: [MS_AccessPros] Re: Indexes
>
>
>
> John,
>
> I do not quite understand all of this so not I am in the learning mode again. I
>
> am glad the colors change when this is inserted into Access. I put the correct
>
> query names in there.
>
> When I hit the button on the form this is the message I get.
>
> Unexpected Error: 3061, Too few parameters. Expected 1
>
> Below is the exact code copied to this email.
>
> I am not sure what the error code means but I am going to keep working on it but
>
> I thought I would send it as well. I did not find much good informaion on the
>
> Execute Method. Is there some in your book?
>
> Thanks for all you work.
>
> Bill
>
> Private Sub postG1D1_Click()
>
> Dim intTrans As Integer, db As DAO.Database
>
> On Error GoTo MyErrorTrap
>
> Set db = CurrentDb
>
> ' Tell error trap we're starting a transaction
>
> intTrans = True
>
> ' Start the transaction
>
> BeginTrans
>
> ' Run the queries
>
> db.Execute "aq_Day1Game1", dbFailOnError
>
> db.Execute "aq_EventPosting2Home", dbFailOnError
>
> db.Execute "aq_EventPosting2Visitor", dbFailOnError
>
> ' Commit the transaction
>
> CommitTrans
>
> ' Turn off the flag
>
> intTrans = False
>
> MyExit:
>
> Exit Sub
>
> MyErrorTrap:
>
> ' Display the error (You can also test the error # and display a custom msg)
>
> MsgBox "Unexpected error: " & Err & ", " & Error
>
> ' If transaction in progress, roll it back
>
> If intTrans Then Rollback
>
> ' Turn off the flag so we don't try to rollback again
>
> intTrans = False
>
> ' Bail
>
> Resume MyExit
>
> End Sub
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
>
> >
>
> > Bill-
>
> >
>
> > If you're running three queries in succession, not only should you be using
>
> > Execute, but you should also be using a Transaction around the three queries
>
> so
>
> > that if any one of them fails, you can roll back everything. The basic code
>
> is:
>
> >
>
> > Dim intTrans As Integer, db As DAO.Database
>
> >
>
> > On Error GoTo MyErrorTrap
>
> > ….
>
> >
>
> > Set db = CurrentDb
>
> >
>
> > ….
>
> >
>
> > ' Tell error trap we're starting a transaction
>
> > intTrans = True
>
> > ' Start the transaction
>
> > BeginTrans
>
> >
>
> > ' Run the queries
>
> > db.Execute "Query1", dbFailOnError
>
> > db.Execute "Query2", dbFailOnError
>
> > db.Execute "Query3", dbFailOnError
>
> >
>
> > ' Commit the transaction
>
> > CommitTrans
>
> > ' Turn off the flag
>
> > intTrans = False
>
> >
>
> > ….
>
> > MyExit:
>
> > Exit Sub
>
> >
>
> > MyErrorTrap:
>
> > ' Display the error (You can also test the error # and display a custom msg)
>
> > MsgBox "Unexpected error: " & Err & ", " & Error
>
> > ' If transaction in progress, roll it back
>
> > If intTrans Then Rollback
>
> > ' Turn off the flag so we don't try to rollback again
>
> > intTrans = False
>
> > ' Bail
>
> > Resume MyExit
>
> >
>
> > End Sub
>
> >
>
> > 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@yahoogroups.com] On Behalf Of Bill Singer
>
> > Sent: Thursday, May 17, 2012 11:34 PM
>
> > To: MS_Access_Professionals@yahoogroups.com
>
> > Subject: RE: [MS_AccessPros] Indexes
>
> >
>
> >
>
> > John,
>
> >
>
> > Actually I will have to run at least three append queries for each game. I
>
> > have two tables in a one to many relationship. One to schedule the
>
> > FACILITY, DATE, TIME, LOCATION and the other two to add the EVENT
>
> > PARTICIPANTS (the many side) The other two queries (one for each team) for
>
> > the many side table grabs the PK from the first table and adds it to the
>
> > record in the Many table. All of that works and the relationship is
>
> > determined correctly. I just need to prevent duplicates.
>
> >
>
> > I do not know what the execute method is so I guess I will be reading up on
>
> > that tonight.
>
> >
>
> > Bill Singer
>
> >
>
> > From: MS_Access_Professionals@yahoogroups.com
>
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
>
> > Sent: Thursday, May 17, 2012 4:17 PM
>
> > To: MS_Access_Professionals@yahoogroups.com
>
> > Subject: RE: [MS_AccessPros] Indexes
>
> >
>
> > Bill-
>
> >
>
> > In each case, are you inserting one or multiple rows? Actually, if you use
>
> > the
>
> > Execute method of a Database object, you can trap the error in code by
>
> > including
>
> > the dbFailOnError parameter. Your On Error routine can examine the error
>
> > code
>
> > and display an appropriate message.
>
> >
>
> > 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 10:10 PM
>
> > To: MS_Access_Professionals@yahoogroups.com
>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
>
> > Subject: RE: [MS_AccessPros] Indexes
>
> >
>
> > 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%40yahoogroups.com>
>
> > [mailto:MS_Access_Professionals@yahoogroups.com
>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
>
> > Viescas
>
> > Sent: Thursday, May 17, 2012 1:52 PM
>
> > To: MS_Access_Professionals@yahoogroups.com
>
> > <mailto:MS_Access_Professionals%40yahoogroups.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%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: Thursday, May 17, 2012 8:19 PM
>
> > To: MS_Access_Professionals@yahoogroups.com
>
> > <mailto:MS_Access_Professionals%40yahoogroups.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]
>
> >
>
> > [Non-text portions of this message have been removed]
>
> >
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar