How to run query based on information from InputBox?

Question

On my form I have a button which the user can click. It will then prompt the user with 3 input boxes, where the user can enter in the information that he wants to run a query on. I want the query to run based on the values that he enters into the 3 inputboxes, but I cannot seem to figure this out. The query is based on another table in my database. Here is the code I've written. It won't compile because I have too many arguments. This is probably because I don't know how to pass variables with the DoCmd.OpenQuery command.

    Private Sub VariableQuery_Click()

    Dim strProdCode As String
    Dim strCampCode As String
    Dim strMailDate As String

    strProdCode = InputBox("Enter Product Code", "Product Code")
    strCampCode = InputBox("Enter Campaign Code", "Campaign Code")
    strMailDate = InputBox("Enter Mail Date", "Mail Date")

    DoCmd.OpenQuery "contribution", , , "[PRODUCT_CODE]=" & strProdCode & _
    "[CAMPAIGN_CODE]=" & strCampCode & "[MAIL_DATE]=" & strMailDate

    End Sub

Any help is appreciated. The name of the query I am trying to run is "contribution". PRODUCT_CODE, CAMPAIGN_CODE, and MAIL_DATE are the names of the fields in the database and PRODUCT_CODE and CAMPAIGN_CODE are both text fields, and MAIL_DATE is a Date/Time field.

Is the query a `SELECT` query or some kind of action query? – ashareef Jun 24 '13 at 14:05 — ashareef, Jun 24 '13 at 14:05
It's a make table query. – nedstark179 Jun 24 '13 at 14:15 — nedstark179, Jun 24 '13 at 14:15

score 0 · Accepted Answer · edited May 23 '17 at 11:56

0

I don't know if there is a different way to do this but how I would approach the problem is by creating the SQL string

Dim strSQL As String
strSQL = "SELECT ... INTO ... " _
       & "FROM ... " _
       & "WHERE [PRODUCT_CODE]='" & strProdCode &"' AND [CAMPAIGN_CODE]='" & strCampCode & "' AND [MAIL_DATE]=#" & strMailDate & "#"
DoCmd.RunSQL strSQL

Things to note

When you have WHERE kind of clauses you have to qualify the values of the fields appropriately e.g. '' around strings, ## around dates and nothing around numbers
DoCmd.RunSQL I believe only runs actions queries e.g. UPDATE, DELETE etc. Plain SELECT are not action queries.
If you want to hide the warnings that popup there are two ways to do it, one is to use CurrentDB.Execute(strSQL) instead or to use DoCmd.SetWarnings(False) and DoCmd.SetWarnings(True)

Edit (Possible helpful links)

http://allenbrowne.com/tips.html
Building SQL strings in Access/VBA
You can Google "building sql strings in ms access" or some variation of that for more sources

edited May 23 '17 at 11:56

Community

1
1

answered Jun 24 '13 at 13:56

ashareef

1,846
13
19

I'm not familiar with SQL. What do these two lines mean? And where do I specify which query I want to run. s= "SELECT ... INTO ... " _ & "FROM ... " _ – nedstark179 Jun 24 '13 at 14:58
I made a typo, `s = ` should be `strSQL = `. Now what this looks like is something like `SELECT MyFieldsName INTO MyTableName ...`. If you're completely unfamiliar with SQL I'm not sure if I have a non-SQL method to do what you want to do. I've edited some links that might be a good starting point to learn some of the basics if you want to learn how to use SQL. – ashareef Jun 24 '13 at 16:45
OK thank you very much. I'm going to spend this afternoon learning SQL and then use your code and hopefully I'll be able to figure it out. Thanks again! – nedstark179 Jun 24 '13 at 16:58

How to run query based on information from InputBox?

1 Answers1