I'm working on a DB in MS 2002. Below is the code. Basically the
Docmd.RunSQL is not working. I can paste the SQL string into a query and it
works just fine. Has anyone had this problem? Please let me know if you see
anything odd. Thanks.

CODE:

Dim strQry As String
Dim strFLT As String
Dim strORD As String


strQry = "SELECT [DNIL_DNIL_MAIN].[CSCI] & [DNIL_DNIL_MAIN].[DNIL_TYPE]
& [DNIL_DNIL_MAIN].[DNIL_NUMBER] AS ID, DNIL_DNIL_MAIN.STATUS,
DNIL_DNIL_MAIN.SUBSYSTEM, DNIL_DNIL_MAIN.PRIORITY,
DNIL_DNIL_MAIN.DESCRIPTION, DNIL_DNIL_OP_IMPACT.OP_IMPACT,
DNIL_DNIL_OP_IMPACT.HOW_TO_TEST, DNIL_DNIL_MAIN.PROGRAM,
DNIL_DNIL_MAIN.PROJECT_NAME FROM DNIL_DNIL_MAIN LEFT JOIN DNIL_DNIL_OP_IMPACT
ON DNIL_DNIL_MAIN.DNIL_NUMBER = DNIL_DNIL_OP_IMPACT.DNIL_NUMBER"

strORD = " ORDER BY [DNIL_DNIL_MAIN].[CSCI] &
[DNIL_DNIL_MAIN].[DNIL_TYPE] & [DNIL_DNIL_MAIN].[DNIL_NUMBER]"

If Me!cboProgram <> "" Then
strFLT = " WHERE [DNIL_DNIL_MAIN].[PROGRAM] = '" & Me!cboProgram &
"' "
ElseIf Me!cboProject <> "" Then
strFLT = " WHERE [DNIL_DNIL_MAIN].[PROJECT_NAME] = '" &
Me!cboProject & "' "
Else
'give them all programs and projects
strFLT = ""
End If

'MsgBox (strFLT)

strQry = strQry & strFLT & strORD & ";"
'MsgBox (strQry)
DoCmd.RunSQL strQry

DoCmd.RunSQL is used to run an action query (update, delete, maketable) or a
data-definition query. It will not run a select query, which is what you're
trying to do here.

What are you wanting to do with this query when you "run" it in code?

--

Ken Snell
<MS ACCESS MVP>


"BS Galactica" <(E-Mail Removed)> wrote in message
news:BA2AF5B1-CB6B-4614-95C3-(E-Mail Removed)...
> I'm working on a DB in MS 2002. Below is the code. Basically the
> Docmd.RunSQL is not working. I can paste the SQL string into a query and
> it
> works just fine. Has anyone had this problem? Please let me know if you
> see
> anything odd. Thanks.
>
> CODE:
>
> Dim strQry As String
> Dim strFLT As String
> Dim strORD As String
>
>
> strQry = "SELECT [DNIL_DNIL_MAIN].[CSCI] & [DNIL_DNIL_MAIN].[DNIL_TYPE]
> & [DNIL_DNIL_MAIN].[DNIL_NUMBER] AS ID, DNIL_DNIL_MAIN.STATUS,
> DNIL_DNIL_MAIN.SUBSYSTEM, DNIL_DNIL_MAIN.PRIORITY,
> DNIL_DNIL_MAIN.DESCRIPTION, DNIL_DNIL_OP_IMPACT.OP_IMPACT,
> DNIL_DNIL_OP_IMPACT.HOW_TO_TEST, DNIL_DNIL_MAIN.PROGRAM,
> DNIL_DNIL_MAIN.PROJECT_NAME FROM DNIL_DNIL_MAIN LEFT JOIN
> DNIL_DNIL_OP_IMPACT
> ON DNIL_DNIL_MAIN.DNIL_NUMBER = DNIL_DNIL_OP_IMPACT.DNIL_NUMBER"
>
> strORD = " ORDER BY [DNIL_DNIL_MAIN].[CSCI] &
> [DNIL_DNIL_MAIN].[DNIL_TYPE] & [DNIL_DNIL_MAIN].[DNIL_NUMBER]"
>
> If Me!cboProgram <> "" Then
> strFLT = " WHERE [DNIL_DNIL_MAIN].[PROGRAM] = '" & Me!cboProgram &
> "' "
> ElseIf Me!cboProject <> "" Then
> strFLT = " WHERE [DNIL_DNIL_MAIN].[PROJECT_NAME] = '" &
> Me!cboProject & "' "
> Else
> 'give them all programs and projects
> strFLT = ""
> End If
>
> 'MsgBox (strFLT)
>
> strQry = strQry & strFLT & strORD & ";"
> 'MsgBox (strQry)
> DoCmd.RunSQL strQry

Hi.

The DoCmd.RunSQL method only works with action queries. A SELECT statement
isn't an action query. That's why it works with the regular query, but not
in VBA code.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts, so that all
may benefit by filtering on "Answered questions" and quickly finding the
right answers to similar questions. Remember that the best answers are often
given to those who have a history of rewarding the contributors who have
taken the time to answer questions correctly.


"BS Galactica" wrote:

> I'm working on a DB in MS 2002. Below is the code. Basically the
> Docmd.RunSQL is not working. I can paste the SQL string into a query and it
> works just fine. Has anyone had this problem? Please let me know if you see
> anything odd. Thanks.
>
> CODE:
>
> Dim strQry As String
> Dim strFLT As String
> Dim strORD As String
>
>
> strQry = "SELECT [DNIL_DNIL_MAIN].[CSCI] & [DNIL_DNIL_MAIN].[DNIL_TYPE]
> & [DNIL_DNIL_MAIN].[DNIL_NUMBER] AS ID, DNIL_DNIL_MAIN.STATUS,
> DNIL_DNIL_MAIN.SUBSYSTEM, DNIL_DNIL_MAIN.PRIORITY,
> DNIL_DNIL_MAIN.DESCRIPTION, DNIL_DNIL_OP_IMPACT.OP_IMPACT,
> DNIL_DNIL_OP_IMPACT.HOW_TO_TEST, DNIL_DNIL_MAIN.PROGRAM,
> DNIL_DNIL_MAIN.PROJECT_NAME FROM DNIL_DNIL_MAIN LEFT JOIN DNIL_DNIL_OP_IMPACT
> ON DNIL_DNIL_MAIN.DNIL_NUMBER = DNIL_DNIL_OP_IMPACT.DNIL_NUMBER"
>
> strORD = " ORDER BY [DNIL_DNIL_MAIN].[CSCI] &
> [DNIL_DNIL_MAIN].[DNIL_TYPE] & [DNIL_DNIL_MAIN].[DNIL_NUMBER]"
>
> If Me!cboProgram <> "" Then
> strFLT = " WHERE [DNIL_DNIL_MAIN].[PROGRAM] = '" & Me!cboProgram &
> "' "
> ElseIf Me!cboProject <> "" Then
> strFLT = " WHERE [DNIL_DNIL_MAIN].[PROJECT_NAME] = '" &
> Me!cboProject & "' "
> Else
> 'give them all programs and projects
> strFLT = ""
> End If
>
> 'MsgBox (strFLT)
>
> strQry = strQry & strFLT & strORD & ";"
> 'MsgBox (strQry)
> DoCmd.RunSQL strQry