SQL Code Problem 5

[billheath]

I wrote the following code:<br>
Function FindS(Area As String)<br>
<br>
Dim db As Database<br>
Dim rst As Recordset<br>
Dim strSQL As String<br>
Set db = CurrentDb<br>
strSQL = &quot;SELECT * FROM Table1 WHERE Type = &quot; & Area<br>
Set rst = db.OpenRecordset(strSQL, dbOpenTable)<br>
<br>
End Function<br>
<br>
When I try to run it (with Area = &quot;Screen&quot, I get the message: &quot;the MS Jet Database engine could not find the object 'Select * from table1 where type = Screen'. Make sure the object exists ...<br>
<br>
Thanks for your help!

 

[DougP]

Well What is &quot;Screen&quot;<br>
I guess you need a lesson 101 on SQL<br>
It wants a table. If you want to pass the recordset from the current form then have a look at RecordsetClone.<br>
<br>
This code will do that plus sync to a pecific record on that form.<br>
<br>
Dim FormName As String, SyncCriteria As String<br>
Dim f As Form, rs As Recordset<br>
<br>
' form name to be syncronized<br>
FormName = &quot;AutoCAD&quot;<br>
<br>
'Define the from object and recordset object for the AutoCAD form<br>
Set f = Forms(FormName)<br>
Set rs = f.RecordsetClone<br>
<br>
' define the criteria used for the sync<br>
SyncCriteria = &quot;[Part_num]=&quot; & Chr$(39) & Me![Part_num] & Chr$(39)<br>
<br>
' find the corresponding record in the Parts table<br>
rs.FindFirst SyncCriteria<br>
f.Bookmark = rs.Bookmark<br>
<br>
Change the &quot;Part_num&quot; to a field on your form.<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[billheath]

Dougp, Thanks for your response, but I don't understand. I'm not using a form. The SQL statement is asking for a set of records from table &quot;Table1&quot; and filtering it for all records where the field &quot;Type&quot; = the string, &quot;Screen&quot;

 

[DougP]

I think Access is getting confused becase you named your field &quot;Type&quot; which is a reserved command word used in VBA.<br>
Try changing it to &quot;MyType&quot; or &quot;Type1&quot; and make sure you change it both in your Table in design view and in your SQL statement.<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[JimConrad]

Try changing the following line...<br>
<br>
strSQL = &quot;SELECT * FROM Table1 WHERE Type = &quot; & Area<br>
<br>
to...<br>
<br>
strSQL = &quot;SELECT * FROM Table1 WHERE Type = <font color=red><b>'</b></font>&quot; & Area & &quot;<font color=red><b>'</b></font>&quot; <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>

 

[Brack]

The error is that the original SQL statement was improperly delimited. As Area is a string arg, the statement is correctly written thus:<br>
<br>
&quot;SELECT * FROM Table1 WHERE Type = '&quot; & Area & &quot;'&quot;<br>
<br>
...assuming Area doesn't/cannot contain an apostrophe.<br>
<br>
Now, whether Type creates a problem depends on who's processing the query (e.g. Jet or SQL Server). I might have to delimit it, too:<br>
<br>
&quot;SELECT * FROM Table1 WHERE [Type] = '&quot; & Area & &quot;'&quot;<br>
<br>
- CSV<br>
Author, related Access Help topics<br>
Free Downloads at <A HREF="

http://www.csvTools.com"

TARGET="_new">

http://www.csvTools.com</A>

 

[Amiel]

I have never encountered the error that you describe.<br><br>I have also never used the constant, <i>dbOpenTable</i>, with a SQL expression !<br>&nbsp;<br>Try leaving the second argument out altogether. Access will select the most appropriate type; unless there is some specific reason to specify a particular type of recordset to be returned.<br><br>Using <b><i> dbOpenTable </i> will always generate an error</b> when anything other than the name of a table is specified; such as the SQL expression that you provided.<br>&nbsp;<br><br><i>Set rst = db.OpenRecordset(strSQL)</i>, will work fine im sure ! <br><br><br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>Amiel

 

[JimConrad]

Nice catch, Amiel.&nbsp;&nbsp;The rest of us should have seen that.<br><br>You get my vote for TipMaster. <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>

 

[Daverg]

Actually Amiel... although you're right... the original omitted ONE very important item....&nbsp;&nbsp;it had to end as follows...<br>& area & &quot;;&quot;<br>A little side note...&nbsp;&nbsp;the first & MUST have a space before it and the auto format system won't put it in/\ ... <p>David Gray<br><a href=mailtoavidGray@CreativeOutdoor.com>DavidGray@CreativeOutdoor.com</a><br><a href=

http://www.creativeoutdoor.com>Creative

Outdoor Advertising</a><br>