How to use vba to create a query and execute the query based on what users typed in the form?

Question

I am trying to use vba where the users typed in something in a text box and the vba would execute the query with what the user typed in as a criteria. However, I keep getting error that says "Expected: end of statement"

   Private Sub btnSearch_Click()
On Error GoTo errorHandler
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qdf As QueryDef
    Dim sql As String

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Expiry")

    sqlString = "SELECT Format([Expiry_Date], ""mmmm"") AS [Month], Sum([Contracts].[Contract _Value (S$)]) AS [Contract Value], Count([Contracts].[Contract No]) AS [Number of Contract] FROM [Contracts] WHERE Year([Expiry_Date])= '" & Me.txtExpiryYear & "' GROUP BY Format([Expiry_Date],""mmmm"")"
    Debug.Print sql
    qdf.sql = sqlString


    If Nz(Me.txtExpiryYear, "") = "" Then
        MsgBox "Please enter the year"
        Resume Exit_Update
    End If

    Set rs = db.OpenRecordset(sqlString)

      qdf.Close
      CurrentDb.Close

Exit_Update:
Exit Sub

errorHandler:
If Err.Number = 3075 Then
    MsgBox Err.Description
    Resume Exit_Update
End If

End Sub

Possible duplicate of [How do I put double quotes in a string in vba?](https://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba) — BigBen, Jul 04 '19 at 02:51
I tried this, now my code gives me a different error. It says error 3075, syntax error (missing operator) in query expression Format([Expiry_Date],""mmmm"")' — acts17, Jul 04 '19 at 03:00
What is the purpose of the query? Running a select query with`.Execute`is rather useless (except you want to check for errors raised), as it doesn't return anything. Use`.Execute`with action queries (update, insert, etc), to select data create a recordset or for single values use a`DLookUp`. And don't close`CurrentDb`(useless), `Set db = Nothing`to destroy the reference for cleanup. — ComputerVersteher, Jul 04 '19 at 04:44
The purpose of the query is to allow users to search all the contracts that are expiring based on the year they typed in the form. The query would also sum up and display the value of the contracts in each month of that year. — acts17, Jul 04 '19 at 05:19
Now you have done too much on quotes! Add a `Debug.Print sql`before`qdf.sql`to have a look at the string. In example from BigBen, user has needed 2 double-quotes (as empty string value for argumnet). To archive that he has to use 4 dquotes as 2 of them are escaped to one. You want only one double-quotes , so just`Format(Date,""mmmm"")`. as this is confusing you can use a single quotes instead or use the ascii char for doubke-quote`Chr(34)`.With`qdf.Execute`nothing is displayed! Use`qdf.CreateRecordset`for a recordset with the data. — ComputerVersteher, Jul 04 '19 at 05:30
Hi, i have made changes to the quotes but now , although there is no error, i can't execute the query. When I click the button the query doesn't run. — acts17, Jul 04 '19 at 05:41
Like pulling teeth:( Can't execute code means error, then provide error msg. But I guess code executes but you still see nothing. Although you have a recordset now, you don't wotk with it. Question is what you expect. If form should display the data, assign`rs`to the forms recordset property`Set Me.Recordset = rs`, or loop through rs an put the data somewhere. — ComputerVersteher, Jul 04 '19 at 06:05
I still see nothing after assigning rs to the forms recordset property — acts17, Jul 04 '19 at 06:13
You could try setting RecordSource property to the SQL string. But why would you need to change form's dataset? Are controls bound to field names of this query? — June7, Jul 04 '19 at 07:06
I have already tried setting the RecordSource property to the SQL string. Still can't see anything. — acts17, Jul 04 '19 at 07:27
I just noticed a syntax error in the SQL. Year() returns a number value. Don't use apostrophe delimiters for its parameter. — June7, Jul 04 '19 at 07:31
See my answer code to loop rs. Any reason the query is created via vba instead being a saved query-def, that can be tested in query designer. — ComputerVersteher, Jul 04 '19 at 07:51

ComputerVersteher · Answer 1 · 2019-07-04T08:09:07.070

0

Debugging is the key to working code!

For simplicity start with displaying the values in immediate window.

If Nz(Me.txtExpiryYear, "") = "" Then 'start with check as if empty exit
    MsgBox "Please enter the year"
    Resume Exit_Update
End If
Dim sqlString As String

'sqlString = "SELECT Format([Expiry_Date], ""mmmm"") AS [Month], Sum([Contracts].[Contract _Value (S$)]) AS [Contract Value], Count([Contracts].[Contract No]) AS [Number of Contract] FROM [Contracts] WHERE Year([Expiry_Date])= '" & Me.txtExpiryYear & "' GROUP BY Format([Expiry_Date],""mmmm"")"
'Debug.Print sqlString
sqlString = "SELECT Format([Expiry_Date], ""mmmm"") AS [Month], Sum([Contracts].[Contract _Value (S$)]) AS [Contract Value], Count([Contracts].[Contract No]) AS [Number of Contract] FROM [Contracts] WHERE Year([Expiry_Date])= [paraYear] GROUP BY Format([Expiry_Date],""mmmm"")"
With CurrentDb.CreateQuerydef(vbNullString) 'temp querydef
    .sql = sqlString
    .Parameters("paraYear") = Me.txtExpiryYear ' using a paremter 
    with .OpenRecordset
        Do Until .Eof 'loop through rs
            Debug.Print .Fields("Month").Value & " " & .Fields("Contract Value").Value & " " & .Fields("Number of Contract").Value
            .MoveNext
        Loop
    End WIth
    Set me.Recordset = .OpenRecordset 'assign recordset to form: controls need to be bound to query fields.
End With

Exit_Update:
Exit Sub

errorHandler:
If Err.Number = 3075 Then
    MsgBox Err.Description
    Resume Exit_Update
End If

To display the recordset in the forms controls, assign it toMe.Recordsetand use the same field names as former recordset or you have to bind to a new field.

Me.MyTextBox.ControlSource = "Month"

What binds theMonthcolumn of the query toMyTextBox.

edited Jul 04 '19 at 08:09

answered Jul 04 '19 at 07:34

ComputerVersteher

2,638
1
10
20

Hi, when i click the button to execute the query I still cannot see anything. However, when click on the query itself, i was asked to include the a value in the paraYear – acts17 Jul 04 '19 at 08:47
And what happens when you set the parameter? Still no data -> try query without where condition. – ComputerVersteher Jul 04 '19 at 08:52
Hi, tks for your help. I found another way and managed to solve it, tks for your help tho! – acts17 Jul 04 '19 at 09:44
@acts17: please share your solution with the community! – ComputerVersteher Jul 04 '19 at 12:09
I simply put docmd.openquery after the qdf.sql and the rest same as the one i posted. – acts17 Jul 05 '19 at 01:08
OK, you don't know how to use a form with a populated recordset? You can use docmd.openquery debugging, but you shoudn't use it in production code as an opened query is missing forms events, which you should use to prevent users doing silly things (like select whole query -> delete). Always present data in forms or reports as creation can't be easier. Create an empty form/report, assign a query and then use "Add existing Fields" to drag and drop all wanted fields from the query to form, having them magically bound to matching controls, that displays the data and saves users changes to db. – ComputerVersteher Jul 05 '19 at 02:13

How to use vba to create a query and execute the query based on what users typed in the form?

1 Answers1