Errorcode 3065

[kmrowlan]

Need some help! I'm getting this error:

Run-time error '3065': Cannot execute a select query.

Here is the code:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("cmTable", dbOpenDynaset)

Dim strSQL As String
Dim strQuoteID As Long
strQuoteID = Text2
If Nz(Rep.Column(0), 0) = 0 Then
MsgBox "You must Select A Rep"
Exit Sub
Else
End If

strSQL = "SELECT * From cmTable " & _
"WHERE (((cmTable.QuoteID) = 724));"

CurrentDb.Execute strSQL, dbFailOnError
DoCmd.Close acForm, "CopyQuote_Header", acSaveYes

I know it is an issue of data but not sure how to solve it.
Any help would be greatly appreciated! Thanks

 

[boblarson]

I know it is an issue of data but not sure how to solve it.
Any help would be greatly appreciated! Thanks

No, it is an issue of using the wrong code to try to run a select query. You can't use CurrentDb.Execute for anything but an Append, Delete, or Update query.

So, what is it you are trying to accomplish?

 

[kmrowlan]

I am trying to capture a record and append it to the same table using a sequential autonumber.

 

[boblarson]

I am trying to capture a record and append it to the same table using a sequential autonumber.

Sorry but why? What woujld be the reason for having the same record except for the autonumber?

And the reason why it isn't going to work with what you have now is that you can't use the * for selecting all of the fields as well. You would have to name them each separately and which field they go in AND it be an append query, not a select.

 

[kmrowlan]

I have two tables: one has project information and one has items. I link them with a field called QuoteID. There can be multiple projects with the same items on it. I want to give the user the ability to create a new project with a reference to a copy of existing item information (which the user may wish to edit)

I am able to do this by running an access query, but cannot get it to work in code. I now get a syntax error that says missing operator. The code I am using is as follow:

strSQL = "INSERT INTO cmTable ( PanelType, Thick, Sqft, Line, Location, Price, TotalCost, LPrice, NPrice, QuoteID, Mult, Description, TimeFrame)" & _
'"SELECT cmTable.PanelType, cmTable.Thick, cmTable.Sqft, cmTable.Line, cmTable.Location, cmTable.Price, cmTable.TotalCost, cmTable.LPrice, cmTable.NPrice, '" & strNewQuoteID & "', cmTable.Mult, cmTable.Description, cmTable.TimeFrame" & _
'"FROM cmTable" & _
'"WHERE (((cmTable.QuoteID)Like '*" & strQuoteID & "*'));"

Is there a work around the syntax?
Thanks for your help.

 

[CharlesChuckieCharles]

Is quote ID a string and is it SQL server or access?

 

[CharlesChuckieCharles]

and IS quotid an autonumber if so why are you trying to specify it?

 

[kmrowlan]

QuoteID is a string and it is Access that I am using.

I have two tables, on contains the project information and QuoteID is an AutoNumber. cmTable is where the items reside that I would like to duplicate. Both tables are linked with QuoteID.

 

[boblarson]

One thing I see that might be necessary is that you have a field named LINE. Line is an Access Reserved Word, so it needs to be in square brackets.

 

[kmrowlan]

Thank you.
Put it in brackets. I still get the same error message but, at least that is ruled out.

Here is the error message:

<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600">
Run-time error ‘3075’
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Syntax error(missing operator) in query expression
‘cmTable.TimeFrameFrom cmTableWhere(((cmTable.QuoteID)Like
‘*722*))’.
<v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>