Syntax Error Runtime 3075

  • Thread starter Thread starter S Jackson
  • Start date Start date
S

S Jackson

I have such trouble with this! Everytime! Ack! I am trying to open another
form from a list box on the Double Click event. Here is part of the code:

strSQL = "SELECT * " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.SurveyorID = " & Me.lbxEmployees.Value

I keep getting a syntax error. I put in the Msgbox strSQL and the statement
reads like this:

SELECT * FROM tblSurveyors WHERE tblSurveyors.SurveyorID = 1

Looks fine to me. But Access doesn't seem to like it. What am I doing
wrong here?
TIA
 
Yep.
Here is what I put in the new query:

SELECT * FROM tblSurveyors WHERE tblSurveyors.SurveyorID = 1

Works fine. I don't get it. Sigh . . .
 
I am doing some trouble-shooting. I shortened the query to:
strSQL = "SELECT * " _
& "FROM tblSurveyors;"

I still get the Runtime 3075 syntax error. Am I missing something obvioius?
 
Figured it out. The problem was not in the SQL language, but in my Docmd
statement. I put the strSQL in the Where Condition spot instead of in the
Filter spot.

What is the difference between these two?

The original statement looked like this:
DoCmd.OpenForm strDocName, acNormal, , strSQL

The working statement now reads:
DoCmd.OpenForm strDocName, acNormal, strSQL

TIA
 
The difference is that the third argument, where you put your SQL string, is
a "filter" argument, where you specify a query to serve as the filter for
the form. Quite honestly, during a recent survey we MVPs did among
ourselves, none of us use this manner of filtering a form or report.

The fourth argument is where you put a WHERE clause (without the WHERE word)
in order to filter the form based on the form's "built-in" record source;
thus, for example, if you want to see just one record of the form's
recordset, you can put a filtering criterion statement in the fourth
argument to limit the form to just that record:
"ID=3"
This is the much more commonly used way to filter a form or report.
 
On Tue, 14 Dec 2004 10:49:45 -0600, "S Jackson"

Press F1
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
How do you use the fourth argument when opening up a 2nd form based on
criteria from the first form?
 
Something like this:

DoCmd.OpenForm "FormName", acNormal, , "[IDField]=" & Me.ControlName.Value
 
How cool is that? I've learned something new! Thanks so much. All I have
is this newsgroup, VBA help, the web and some books to learn this stuff.
Mostly, I like to learn by "doing" and applying what I've read to my
circumstance rather than just reading. Much easier for me.
Thanks again.
S. Jackson

Ken Snell said:
Something like this:

DoCmd.OpenForm "FormName", acNormal, , "[IDField]=" & Me.ControlName.Value


--

Ken Snell
<MS ACCESS MVP>

S Jackson said:
How do you use the fourth argument when opening up a 2nd form based on
criteria from the first form?

string, in
the
Click to expand...
part
of and
the am
Click to expand...
Click to expand...
 
You're welcome.

--

Ken Snell
<MS ACCESS MVP>

S Jackson said:
How cool is that? I've learned something new! Thanks so much. All I have
is this newsgroup, VBA help, the web and some books to learn this stuff.
Mostly, I like to learn by "doing" and applying what I've read to my
circumstance rather than just reading. Much easier for me.
Thanks again.
S. Jackson

Ken Snell said:
Something like this:

DoCmd.OpenForm "FormName", acNormal, , "[IDField]=" & Me.ControlName.Value


--

Ken Snell
<MS ACCESS MVP>

S Jackson said:
How do you use the fourth argument when opening up a 2nd form based on
criteria from the first form?

The difference is that the third argument, where you put your SQL string,
is
a "filter" argument, where you specify a query to serve as the
Click to expand...
filter
for
the form. Quite honestly, during a recent survey we MVPs did among
ourselves, none of us use this manner of filtering a form or report.

The fourth argument is where you put a WHERE clause (without the WHERE
word)
in order to filter the form based on the form's "built-in" record source;
thus, for example, if you want to see just one record of the form's
recordset, you can put a filtering criterion statement in the fourth
argument to limit the form to just that record:
"ID=3"
This is the much more commonly used way to filter a form or report.
--

Ken Snell
<MS ACCESS MVP>



Figured it out. The problem was not in the SQL language, but in my
Docmd
statement. I put the strSQL in the Where Condition spot instead
Click to expand...
Click to expand...
of
in trying
to part What
am
Click to expand...
Click to expand...
 
Back
Top