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.
I am able to list results of my query in a VBA drafted email, however I cannot put any criteria on it to limit the results. I have a form that is tied to my VBA code and when I click it, it will draft the email and list details on the form. The last item I need to list are items contained in a subform.
Currently I get results, however I get all results for all records and not the current record. Normally in the query I would input a simple line like: [Forms]![Supply Requests]![Request ID] to return the items only for the specific record. However when I do that on the query, the VBA code crashes...
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
The only piece I am having trouble with is linking the textbox to the query. I tried just setting the control source to the query, but I get a ?Name? error.
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?
This is what I want for display purposes. But I want to be able to create a tally of all the students that came. In the example attached, You can see that for the first record, there are actually 3 parent records displayed each with 80 kids. but truely there was 80 in total. not 240.
I was hoping to run some VB code to tally the results in my list box, but adding the values in the column wont give me the results I want. Any Ideas? should I use a hidden list box with ONLY the parent records
Im using Access 2003. It shows the DataView. How do I get the Results from the Query into the SubForm?
Please help, been stumped for days.
PS. I did try changing the (record source) To the Angle Query for the Form. But still it does not Display the Data in the form
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.