HELP with ERROR Message

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I produced the following and am getting an ERROR message stating - Syntax
error in FROM clause. Any help appreciated.

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
Dim varItem As Variant
Dim strWhere As String
Dim strWhere1 As String
Dim strWhere2 As String
Dim lngLen As Long
Dim strDelim As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

With Me.lstGroup
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & .ItemData(varItem) & strDelim & ","
End If
Next varItem
End With

lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[GroupID] IN (" & Left$(strWhere1, lngLen) & ") "
End If

With Me.lstClass
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & "'" & strDelim & .ItemData(varItem) &
strDelim & "',"
End If
Next varItem
End With

lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[CO] IN (" & Left$(strWhere2, lngLen) & ") "
End If

strWhere = strWhere1

If Len(strWhere) > 0 And Len(strWhere2) > 0 Then
strWhere = strWhere & " AND " & strWhere2
Else
strWhere = strWhere & strWhere2
End If

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT tblProduct.* FROM tblProduct "
strSQL = strSQL & strWhere
MsgBox strSQL
'*** delete the previous query
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

'*** open the query
DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdOK_Click
End If

End Sub
 
Hi -

Can I suggest you use debug.print to print the offending SQL and then post it
here? It's much easier if we can see it.

John


New said:
I produced the following and am getting an ERROR message stating - Syntax
error in FROM clause. Any help appreciated.

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
Dim varItem As Variant
Dim strWhere As String
Dim strWhere1 As String
Dim strWhere2 As String
Dim lngLen As Long
Dim strDelim As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

With Me.lstGroup
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & .ItemData(varItem) & strDelim & ","
End If
Next varItem
End With

lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[GroupID] IN (" & Left$(strWhere1, lngLen) & ") "
End If

With Me.lstClass
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & "'" & strDelim & .ItemData(varItem) &
strDelim & "',"
End If
Next varItem
End With

lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[CO] IN (" & Left$(strWhere2, lngLen) & ") "
End If

strWhere = strWhere1

If Len(strWhere) > 0 And Len(strWhere2) > 0 Then
strWhere = strWhere & " AND " & strWhere2
Else
strWhere = strWhere & strWhere2
End If

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT tblProduct.* FROM tblProduct "
strSQL = strSQL & strWhere
MsgBox strSQL
'*** delete the previous query
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

'*** open the query
DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdOK_Click
End If

End Sub
Click to expand...
 
Not sure HOW to go about debug.print. NEW GUY on this end. Some guidance
appreciated.

J_Goddard via AccessMonster.com said:
Hi -

Can I suggest you use debug.print to print the offending SQL and then post it
here? It's much easier if we can see it.

John


New said:
I produced the following and am getting an ERROR message stating - Syntax
error in FROM clause. Any help appreciated.

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
Dim varItem As Variant
Dim strWhere As String
Dim strWhere1 As String
Dim strWhere2 As String
Dim lngLen As Long
Dim strDelim As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

With Me.lstGroup
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & .ItemData(varItem) & strDelim & ","
End If
Next varItem
End With

lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[GroupID] IN (" & Left$(strWhere1, lngLen) & ") "
End If

With Me.lstClass
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & "'" & strDelim & .ItemData(varItem) &
strDelim & "',"
End If
Next varItem
End With

lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[CO] IN (" & Left$(strWhere2, lngLen) & ") "
End If

strWhere = strWhere1

If Len(strWhere) > 0 And Len(strWhere2) > 0 Then
strWhere = strWhere & " AND " & strWhere2
Else
strWhere = strWhere & strWhere2
End If

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT tblProduct.* FROM tblProduct "
strSQL = strSQL & strWhere
MsgBox strSQL
'*** delete the previous query
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

'*** open the query
DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdOK_Click
End If

End Sub
Click to expand...
Click to expand...
 
debug.print "prints" to the VB immediate pane, which you can open with Ctrl-G.
So, if in your code you put debug.print strSQL , the text is "written" to
the immediate pane. After you close the form that has the error, open the
immediate pane with Ctrl-G, and you will see the SQL string. From there, you
can cut and paste to this NG, but you might be able to see the syntax error
yourself.

debug.print is a great debugging tool; you can use multiple debug.print
statements to track down exactly where where code is failing and why.

John


New said:
Not sure HOW to go about debug.print. NEW GUY on this end. Some guidance
appreciated.
Hi -
Click to expand...
[quoted text clipped - 79 lines]
Click to expand...
 
I placed the debug.print strSQL statement near the top of my code. I ran the
code from the command button on the form and got the message - Syntax error
in FROM Clause; I closed the error box and hit the Ctrl+G to lite the
Immediate window in Visual Basic - BLANK i removed the two last lines of
code and NO more messages, but still stumped as to WHY?

Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

'*** open the query
DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

J_Goddard via AccessMonster.com said:
debug.print "prints" to the VB immediate pane, which you can open with Ctrl-G.
So, if in your code you put debug.print strSQL , the text is "written" to
the immediate pane. After you close the form that has the error, open the
immediate pane with Ctrl-G, and you will see the SQL string. From there, you
can cut and paste to this NG, but you might be able to see the syntax error
yourself.

debug.print is a great debugging tool; you can use multiple debug.print
statements to track down exactly where where code is failing and why.

John


New said:
Not sure HOW to go about debug.print. NEW GUY on this end. Some guidance
appreciated.
Hi -
Click to expand...
[quoted text clipped - 79 lines]
End Sub
Click to expand...
Click to expand...
Click to expand...
 
The Debug.Print statement needs to go after the variable has been assigned
its value. Put it where you had MsgBox strSQL in your original post.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


New Guy said:
I placed the debug.print strSQL statement near the top of my code. I ran
the
code from the command button on the form and got the message - Syntax
error
in FROM Clause; I closed the error box and hit the Ctrl+G to lite the
Immediate window in Visual Basic - BLANK i removed the two last lines
of
code and NO more messages, but still stumped as to WHY?

Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

'*** open the query
DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

J_Goddard via AccessMonster.com said:
debug.print "prints" to the VB immediate pane, which you can open with
Ctrl-G.
So, if in your code you put debug.print strSQL , the text is
"written" to
the immediate pane. After you close the form that has the error, open
the
immediate pane with Ctrl-G, and you will see the SQL string. From there,
you
can cut and paste to this NG, but you might be able to see the syntax
error
yourself.

debug.print is a great debugging tool; you can use multiple debug.print
statements to track down exactly where where code is failing and why.

John


New said:
Not sure HOW to go about debug.print. NEW GUY on this end. Some
guidance
appreciated.

Hi -

[quoted text clipped - 79 lines]

End Sub
Click to expand...
Click to expand...
Click to expand...
 
I got it to work after your input.
SELECT tblProduct.* FROM tblProduct [GroupID] IN (3) AND [CO] IN ('B')

Yet, I don't see the error; any ideas

Douglas J. Steele said:
The Debug.Print statement needs to go after the variable has been assigned
its value. Put it where you had MsgBox strSQL in your original post.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


New Guy said:
I placed the debug.print strSQL statement near the top of my code. I ran
the
code from the command button on the form and got the message - Syntax
error
in FROM Clause; I closed the error box and hit the Ctrl+G to lite the
Immediate window in Visual Basic - BLANK i removed the two last lines
of
code and NO more messages, but still stumped as to WHY?

Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

'*** open the query
DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

J_Goddard via AccessMonster.com said:
debug.print "prints" to the VB immediate pane, which you can open with
Ctrl-G.
So, if in your code you put debug.print strSQL , the text is
"written" to
the immediate pane. After you close the form that has the error, open
the
immediate pane with Ctrl-G, and you will see the SQL string. From there,
you
can cut and paste to this NG, but you might be able to see the syntax
error
yourself.

debug.print is a great debugging tool; you can use multiple debug.print
statements to track down exactly where where code is failing and why.

John


New Guy wrote:
Not sure HOW to go about debug.print. NEW GUY on this end. Some
guidance
appreciated.

Hi -

[quoted text clipped - 79 lines]

End Sub
Click to expand...
Click to expand...
Click to expand...
 
I don't see the word WHERE anywhere in that... that is the error for sure.

You don't see the error message because you are trapping it with On Error
GoTo..., and you have (I think) taken the MsgBox statement out.

John



New said:
I got it to work after your input.
SELECT tblProduct.* FROM tblProduct [GroupID] IN (3) AND [CO] IN ('B')

Yet, I don't see the error; any ideas
The Debug.Print statement needs to go after the variable has been assigned
its value. Put it where you had MsgBox strSQL in your original post.
Click to expand...
[quoted text clipped - 39 lines]
Click to expand...
 
That "FIVE" letter word made all the difference. Thanks for being patient
with the New Guy. Have a GREAT HOLIDAY!

J_Goddard via AccessMonster.com said:
I don't see the word WHERE anywhere in that... that is the error for sure.

You don't see the error message because you are trapping it with On Error
GoTo..., and you have (I think) taken the MsgBox statement out.

John



New said:
I got it to work after your input.
SELECT tblProduct.* FROM tblProduct [GroupID] IN (3) AND [CO] IN ('B')

Yet, I don't see the error; any ideas
The Debug.Print statement needs to go after the variable has been assigned
its value. Put it where you had MsgBox strSQL in your original post.
Click to expand...
[quoted text clipped - 39 lines]
End Sub
Click to expand...
Click to expand...
Click to expand...
 
New Guy,

1. My first thought is that although you dimension strDelim, you never
actually give it a value. That may be intentional, since you are using a
numeric ID field, or it may have been an oversight. Now that I look a little
further, I see in strWhere2 you are actually delimiting with an apostrophe,
but not using strDelim. I would add lines of code before each list loop that
sets strDelim for that loop. For your first listbox, set strDelim = "" For
the second, set it = "'". In the 2nd list loop, use:

strWhere2 = strWhere2 & strDelim & .ItemData(varItem) & strDelim & ","

2. Replace: strSQL = strSQL & strWhere
With : strSQL = strSQL & IIF(LEN(strWhere) > 0, " WHERE " &
strWhere, "")

3. I'm not sure why you are deleting the previous query, why not just reset
its SQL property. Replace those two lines with:

currentdb.querydefs("qryMyQuery").SQL = strSQL

Personally, I like to use variants when I am doing this. It allows me to
avoid some of the tests.

Dim varItem as Variant
Dim varWhere as Variant, varWhere1 as Variant, varWhere2 as Variant
Dim strDelim as string

strDelim = ""
With me.lstGroup
For each varItem in .ItemsSelected
varWhere1 = (varWhere1 + ", ") & (strDelim & .ItemData(varItem) &
strDelim)
Next varItem
End With
varWhere1 = "[GroupID] IN (" + varWhere1 + ")"

strDelim = "'"
With me.lstClass
For each varItem in .ItemsSelected
varWhere2 = (varWhere2 + ", ") & (strDelim & .ItemData(varItem) &
strDelim)
next varItem
end with
varWhere2 = "[CO] IN (" + varWhere2 + ")"

varWHERE = varWhere1 _
& IIF(ISNULL(varWhere1) OR ISNULL(varWhere2), NULL, " AND ") _
& varWhere2


HTH
Dale


--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


New Guy said:
I produced the following and am getting an ERROR message stating - Syntax
error in FROM clause. Any help appreciated.

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
Dim varItem As Variant
Dim strWhere As String
Dim strWhere1 As String
Dim strWhere2 As String
Dim lngLen As Long
Dim strDelim As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

With Me.lstGroup
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & .ItemData(varItem) & strDelim & ","
End If
Next varItem
End With

lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[GroupID] IN (" & Left$(strWhere1, lngLen) & ") "
End If

With Me.lstClass
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & "'" & strDelim & .ItemData(varItem) &
strDelim & "',"
End If
Next varItem
End With

lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[CO] IN (" & Left$(strWhere2, lngLen) & ") "
End If

strWhere = strWhere1

If Len(strWhere) > 0 And Len(strWhere2) > 0 Then
strWhere = strWhere & " AND " & strWhere2
Else
strWhere = strWhere & strWhere2
End If

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT tblProduct.* FROM tblProduct "
strSQL = strSQL & strWhere
MsgBox strSQL
'*** delete the previous query
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

'*** open the query
DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdOK_Click
End If

End Sub
Click to expand...
 
Back
Top