Best way to display VBA query results?
User fires of query with a button click. I just want to open up a table view of the results but since I am new to Access dev I am not sure how to do this.
Dim productName As String
Dim strSql As String
Dim dbs As Database
Set dbs = CurrentDb()
Dim rs As Recordset
productName = "ADSL"
strSql = "SELECT [Information].* From [Information] WHERE [Information].Product = " & Chr(34) & productName & Chr(34) & ";"
Set rs = dbs.OpenRecordset(strSql, dbOpenSnapshot)
How do I open the RecordSet up in a table (view)?
Thank you for taking the time to report an issue.
What's wrong... Please write below.
Currently I then take the formed SQL, create a temporary query and then open the newly created query read only. The result is the query output in the normal Access datasheet style view.
What I would like to do is form my SQL as I am currently doing, but rather than create and open a query - execute the SQL and display the results into / onto a form. One other requirementis I need the user to be able to copy the entire results to clipboard so the data can be pasted into Excel or Word for example.
Is this possible or have I got to stick with the query datasheet view?
How I Ideally want this to work Is that I have one form/subform that I can then pass the results for query’s to via VBA every time buttons on some of my other forms are pressed.
Form 1 button click
opens the form/subform wIth the query
Select * from tbltest
Form 2 button click
opens the same form/subform but has the results from
Select * from tblretest
Is there a way to do thIs so that I can have one form/subform and just pass query results for It to display.
I really don’t want to have to make a form for each Individual query
See example below:
I created the crosstab query using the wizard and copied the code into my VBA (button click), but it's not an action query, so DoCmd.RunSQL won't work.
Then I tried:
1) Dim rst as Recordset
2) Set rst = CurrentDb.OpenRecordset(strSQL)
But where do I go from there?
I guess, really, I don't need to put it into a table if I'm just going to export it to Excel anyway, and it need not be a crosstab query: How do I create a SQL SELECT statement in VBA and display the results?
However, when I export the results of the query to Excel, the ID's not the values are displayed. How can I get access to export the exact results of the query.
I want to display the results of the query in the form.
I have lists boxes on the form, but when I use a button to launch the query, it does exactly what I don't want . launches the query.
I just want the query results to populate the List Boxes that I have linked to the fields in the query.
There are about 20 values, so there will be about 20 results. Then I want to display the results for the user in such a way that they can all be seen at once.
The parameters are names of websites, so the final results would look sort of like this:
I have a field in this same table that shows a department value for each record. That fieldname is “Dept”.
Within a huge VBA function module I’m writing, I have a string variable assigned to the Query field called strQuery. I also have string variable assigned to the Dept field called strDept.
The query that is assigned to the variable strQuery is designed to return ALL results for ALL departments. However, I want the VBA to run the query and return results for only the department that are assigned to the variable strDept.
If I leave out the department limitation, it works fine with the following code:
Either query will not display specific parameter rather display every contents of table or either it won't display anything.
On this form I have a command button, called cmdCreateReport which generates a report, called Search Report. This report references a query, called qrySearchReport and in the criteriasection of the query I have the following code (which is adjusted depending on the field title).
Like [Forms]![frmSearch]![txtReportID] & "*"
What I have been told this should do is ignore the text box on the form in the query if the box is blank. However, when I generate the report or run the query, it does not display any results. It should display the same results shown on frmSearch.