Duane,
Now the Immediate window is active. That is nice. Thank you for the tip. I was reading up on the things you can do in the Immediate window. It now shows my SQL in one long line. I have tried to follow the models I have found but nothing is working yet. I will continue my learning (efforts) later today.
Thank you for the help.
Bill
-----Original Message-----
From: Duane [mailto:duanehookom@hotmail.com]
Sent: Sunday, May 20, 2012 12:05 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Indexes
Bill,
The lightening seems to have let up on this side of town.
This is one of the main reasons while us veterans typically create a strSQL to store the SQL statement and then use DeBug.Print strSQL to find any syntax issues. We always do this when we get an error like this.
When you follow this advice, you will find you are missing at the end of each line:
Dim strSQL as String
'assuming hlpHlpID is numeric
strSQL = "INSERT INTO t_FacilitySchedule ( fscFacID, fscSpoID, fscSchDate, fscSchStartTime, fscEveID ) " & _
"SELECT hlpLocDay1, hlpSpoID, hlpDay1Date, hlpGame1Time, 1 " & _
"FROM t_ScheduleHelper " & _
"WHERE hlpHlpID=" & [Forms]![f_8TeamSeasonSchedule]![hlpHlpID]
debug.Print strSQL
db.Execute strSQL, dbFailOnError
Duane Hookom
MS Access MVP
--- In mailto:MS_Access_Professionals%40yahoogroups.com, "agent1of6" <Bill.Singer@...> wrote:
>
> 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 mailto:MS_Access_Professionals%40yahoogroups.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: mailto:MS_Access_Professionals%40yahoogroups.com [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of bill.singer@
> > Sent: Saturday, May 19, 2012 4:53 PM
> > To: mailto:MS_Access_Professionals%40yahoogroups.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: <mailto:MS_Access_Professionals%40yahoogroups.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: mailto:MS_Access_Professionals%40yahoogroups.com
> >
> > [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of agent1of6
> >
> > Sent: Friday, May 18, 2012 10:50 PM
> >
> > To: mailto:MS_Access_Professionals%40yahoogroups.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 mailto:MS_Access_Professionals%40yahoogroups.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: mailto:MS_Access_Professionals%40yahoogroups.com
> >
> > > [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Bill Singer
> >
> > > Sent: Thursday, May 17, 2012 11:34 PM
> >
> > > To: mailto:MS_Access_Professionals%40yahoogroups.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: mailto:MS_Access_Professionals%40yahoogroups.com
> >
> > > [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of John Viescas
> >
> > > Sent: Thursday, May 17, 2012 4:17 PM
> >
> > > To: mailto:MS_Access_Professionals%40yahoogroups.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: mailto:MS_Access_Professionals%40yahoogroups.com
> >
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> >
> > > [mailto:mailto:MS_Access_Professionals%40yahoogroups.com
> >
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill
> >
> > > Singer
> >
> > > Sent: Thursday, May 17, 2012 10:10 PM
> >
> > > To: mailto:MS_Access_Professionals%40yahoogroups.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: mailto:MS_Access_Professionals%40yahoogroups.com
> >
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> >
> > > [mailto:mailto:MS_Access_Professionals%40yahoogroups.com
> >
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
> >
> > > Viescas
> >
> > > Sent: Thursday, May 17, 2012 1:52 PM
> >
> > > To: mailto:MS_Access_Professionals%40yahoogroups.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: mailto:MS_Access_Professionals%40yahoogroups.com
> >
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> >
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> >
> > > [mailto:mailto:MS_Access_Professionals%40yahoogroups.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: mailto:MS_Access_Professionals%40yahoogroups.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]
> >
>
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar