Further to what Duane said, when you leave the reference to the form embedded in
the SQL, THAT's the missing parameter. You have to subsitute the real value
before directly executing it. As for the method to supply the value when the
parameter is in the SQL, that involves creating a temp QueryDef object, then
setting the parameter via the Parameters collection before executing it. More
trouble than it's worth.
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/> http://www.viescas.com/
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane
Sent: Monday, May 21, 2012 4:45 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Indexes
If you check the immediate/debug window with both strSQL with the quotes in
different positions, you will see the one that works has a numeric value and the
one that doesn't has a reference to a control on a form.
There is a method of supplying parameter values to get the SQL with the embedded
control reference to work but I typically don't use it.
Duane Hookom
MS Access MVP
--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Singer"
<Bill.Singer@...> wrote:
>
> Duane,
>
> Thanks, My remaining hair follicles thank you. I woke up this morning at
> 4:30 thinking if it worked in a new query and not in my code it must be the
> formatting marks. I would have never found that on my own. But I did
> learn a lot. It is usually how I learn best. Jump in with both feet and
> then try to figure out how to swim.
>
>
>
> My last question and hopefully this thread will be closed. Why do I have to
> keep that reference to a form outside the quotations? What are the
> quotations doing? I was wondering why they are in this code and not in the
> SQL view of a query. Access must see things inside the quotes and outside
> the quotes differently.
>
>
>
> 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 Duane
> Sent: Sunday, May 20, 2012 11:31 PM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: [MS_AccessPros] Re: Indexes
>
>
>
>
>
> Bill,
> Here is your code:
> 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]"
>
> and here is my earlier suggestion:
> '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]
>
> Find the difference to resolve your parameter issue.
>
> Duane Hookom
> MS Access MVP
>
> --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> , "agent1of6"
> <Bill.Singer@> wrote:
> >
> > Duane/John
> >
> > An exercise in Frustration. I have learned some great tricks but it still
> is very frustrating.
> >
> > I believe I found the missing spaces. I followed the procedure that both
> of you suggested but the SQL view does not have quotes and spaces. The
> spaces fixed my error 3067, must contain one table.
> >
> > Once I got the spaces fixed and one more error fixed I am now back to the
> error 3061, too few parameters, Expected1. As a way to further trouble shoot
> as suggeste by an Allen Board I copied everything from the results in the
> immediate window and pasted it in the sql view of a new query. I selected
> datasheet view and it worked perfectly. I do not get that. The exact
> language in the VB window will not run. I would pull my hair but my
> follicles are leaving me too fast the way it is. I have tried a few things
> but that lead to other errors. I have checked the spelling and find no
> errors. I check my books but no help.
> >
> > Any idea what error 3061, too few parameters, Expected1 means?
> >
> > Here is the code.
> >
> > Private Sub Command155_Click()
> > Dim intTrans As Integer, db As DAO.Database
> > Dim strSQL As String
> >
> > On Error GoTo MyErrorTrap
> >
> > Set db = CurrentDb
> >
> > ' Tell error trap we're starting a transaction
> > intTrans = True
> > ' Start the transaction
> > BeginTrans
> >
> > ' Run the queries
> >
> > 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
> >
> > ' 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
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.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
> > > > >
> > > > >
> > >
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar