DoCmd.RunSQL not working 4

[cs211024]

Here is my code:
*************
Private Sub VerifyAddress_Click()

Dim strSQL As String

If Forms!MAIN!Country = "USA" Then
strSQL = "SELECT ZIPCODEWORLDBASIC.state FROM ZIPCODEWORLDBASIC;"
ElseIf Forms!MAIN!Country = "CANADA" Then
strSQL = "SELECT * FROM IP;"
Else
MsgBox "Please Input the Country first."
Exit Sub
End If

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

End Sub
*************

The error message is "A RunSQL action requires an argument consisting of an SQL statement."

I have no clue about since everything seems fine. Thanks for your help.

 

[PHV]

The RunSQL method works for action queries like UPDATE, INSERT, DELETE, ALTER, ... not for SELECT.
What was your intent with such SQL ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cs211024]

I want to open an query. Maybe I should try "execute" command. But I do remember that DoCmd.RunSQL can take an select query as its argument.

 

[123FakeSt]

Wouldn't you use ...
DoCmd.OpenQuery "QueryName"
???

If i need to view a query but don't want to create the object i use (especially if i want to show it to the user)...

DoCmd.RunSQL "Select * into TempTable from [OrginTable] Where [conditions];"
DoCmd.OpenTable "TempTable"
DoCmd.RunSQL "Drop TempTable"

Hope this helps...
Rusty


The early bird gets the worm, but the second mouse gets the cheese.

 

[willir]

cs211024

What is the final objective for running strSQL ?

If you are trying to retrieve a record set for a form, try using "Me.RecordSoruce = " + "Me.Requery" or "Me.Filter = " + "Me.FilterOn = True" commands

If you are trying to verify zip code or postal code, try using DLookUp.

Use MS Help for more info on these commands.

Richard

 

[hwkranger]

Have you considered a recordset? You can use SQL with a recordset.

Are you using 97 or a higher version?



Randall Vollen
National City Bank Corp.

 

[SiJP]

My preference for creating queries on the fly is:

Private Function fCreateQueries(strQueryToRun As String)

    Dim strSQL As String
    Dim strDateFormat As String
    Dim qdf As QueryDef
    strDateFormat = "ddd, dd mmm yyyy"
    
    'Build SQL statement
     strSQL = "SELECT * FROM tblTableName"


    'delete old query
    For Each qdf In CurrentDb.QueryDefs
        If qdf.Name = strQueryToRun Then
         CurrentDb.QueryDefs.Delete qdf.Name
        Else
        End If
    Next qdf
    
    
    'run a new query
    With CurrentDb
        Set qdf = .CreateQueryDef(strQueryToRun, strSQL)
        DoCmd.OpenQuery qdf.Name
        .QueryDefs.Delete qdf.Name
    End With

    Set qdf = Nothing

End Function

Then all you need to do is make a call to the function with the name you want to call the query.. e.g.

Call fCreateQueries("qryMyNewQuery")

HTH's....

------------------------
Hit any User to continue

 

[rb9999]

I am trying to do something similar to this too. I want to create a query on the fly and then run it. It is a crosstab query.

I understand what you are doing above, but what if the query doesn't already exist? I want to create the query based on what is selected from a listbox. The item selected in the list box will be the field that I query (not the criteria).

Can a create a query on the fly by using a variable consisting of SQL code?

 

[PHV]

rb9999, just read the previous post !

 

[rb9999]

I saw the post from 123FakeStreet, but the query I am running is a crosstab and it needs to be viewed as a crosstab datasheet. So it isn't practical to dump it to a table first as he does in his example. He appears to dump it to a table then open the table. That won't work in my case.

 

[PHV]

Read the SiJp one :
Set qdf = .CreateQueryDef(strQueryToRun, [highlight]strSQL[/highlight])

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rb9999]

Holy mackeral it worked!!!!

I have been trying to do this for over a week. I have spent a lot of time researching this!

Thanks a bunch!!!