Onclick event for button, build a query string and run it

[emanon]

I have a MS Access form where it will ask users to select or type in words that will be used in the where clause for the query. When the button is clicked, it will build the string and use that to run the query. This will be a read only and to view the subset results from a link table in SQL Server. Nothing more. I tried it with DoCmd.RunSQL but that is only for action type like update, delete, etc.


On click button event, it will have similar query. strSQL = "Select top 10 * Where ID = ''" + textbox1.value + "'' from linked_Table_Name;"
This did not work. DoCmd.RunSQL strSQL

[ranman256]

you dont need to build an SQL, just run the query using the same sql:
query Q1:

Select top 10 * Where ID =forms!fMyForm!txtBox1 from linked_Table_Name

your code:
docmd.openquery "Q1"

[emanon]

This is in the click event. I get Microsoft Access can't find object 'Select top 10 * from RPT_Compliance_Dashboard_Details;' I am not sure what I am doing wrong. Once I get the basic query to work, then I can add the where clause and do that part.

Dim strSQL As String
strSQL = "Select top 10 * from RPT_Compliance_Dashboard_Details;"

DoCmd.OpenQuery strSQL

[emanon]

I got it to work. I created an empty saved query then did the below stuff in on click event.

Dim strSQL As String

strSQL = "Select top 10 * from RPT_Compliance_Dashboard_Details;"


Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryResult")
qdf.SQL = strSQL
DoCmd.OpenQuery ("qryResult")
Set qdf = Nothing

[ranman256]

or just docmd.openquery