[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, "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, "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
> > > >
> > > >
> >
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (21)

Recent Activity:

• New Members 2
• New Files 3

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___