Duane,
Even though I am still currently struggling with the first SQL statement I have another question for down the road.
My plan is to eventually run three append queries in the code.
For the first query you suggested the
Dim strSQL as String
strSQL = MyCode
db.Execute strSQL
When I do the next bit of code, which will be very similar to the first should I do...
Dim strSQL2 as String
strSQL2 = MyCode
db.Execute strSQL2
and keep using that pattern for the number of queries I want to run in the code?
Thanks for your help, I really wish I could find a class on this stuff.
Bill
I hope you and your computer survived the lightening storm. I am find but it sure seemed close.
--- In MS_Access_Professionals@yahoogroups.com, "Duane" <duanehookom@...> wrote:
>
> Viewing the SQL statement in the debug/immediate window might be enough to determine the issue with your syntax. If not, copy the statement to your clipboard, open a new query design, view its SQL, and paste the SQL statement. This will likely provide a better view of your issue.
>
> Duane Hookom
> MS Access MVP
>
> --- In MS_Access_Professionals@yahoogroups.com, Bill.Singer@ wrote:
> >
> > 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@]
> > 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
> > >
> > >
>
Tidak ada komentar:
Posting Komentar