Best way to display VBA query results?

What is the best way to display the results from a VBA query in Access (97)?

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)?

Sponsored Links:

Related Topics

VBA Code to show query results
I have another issue. I'm still working on my code for over 2 weeks now and I've made great strides, but I have one final hurdle.

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...


Execute SQL statement and display results on a form using VBA
I have a form which builds and executes SQL statements based on the users inputs on some checkbox and textbox controls on a form.

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?


VBA Show query in a form/subform
I am have great difficulty with getting a subform to show data.

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.

E.g

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


Display Query Results on a Form
I have a query that finds birthdays in the current or upcoming month. I would just like to display the results of the query on a form, I was thinking in a textbox, and have the textbox refresh the query everytime the form is opened.

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.


Pivot/Array results
I need to display my results from a query of multiple records into 1 record. I can't figure out the query that will give me the results I need below,

See example below:


display query results in a form
This is probably a simple questions, but I have a query that I am hoping to use to either open a form to the first record of the query, or display the results of a query on a form (or report) and allow the user to click on a result and open another from. Make sense?


Tally records in VBA
currently I have a listbox which displays some results using an SQL query in the row source. In the list box I display records from a parent and child table. Therefor I'm getting duplicate information from the parent. (picture)

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


Query To Form
I have a query that runs fine. It pops up and gives me the results I want, however I want those results to be displayed on a SubForm.

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


How to Build and export a crosstab query in VBA?
What I'm trying to do is create a crosstab query (based on a temp table) in VBA, then export it to Excel. The temp table is built off a form where the user enters a parameter(s) in a textbox(es).

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?


Exporting the results of a query to Excel
I have created a query in Access 2007 which pulls data from the table for a certain time period. The query contains lookups so the results of the query display table values rather than ID's.

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.


Displaying Query Results in a Form List Box
I have a form that is linked to a query (and vice versa).

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.


Formating a query
I have some query results that are coming back as negative integers despite formatting them as #.00. Anyone know how I can get the results to display in decimal form instead?


assembling a list of parameter count query results
The basic idea here is to run a single parameter COUNT query a bunch of times, looping through a table for the parameters. Each time, the query returns two values: the name of the parameter, and the count.

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:

MREXCEL 240
OZGRID 120
UBERACCESS 38
etc.


top 10 results in query
The following query returns about 6000 results on my db:

SELECT Calls.[SKU], Count(Calls.CallID) AS CountOfCallID FROM Calls GROUP BY Calls.[SKU];

The data it displays is correct, however, I am only interested in seeing the top 10, that is, the 10 SKUs that have the most calls, not all the results.

How can I limit the results? and then.How do I display them on a form with 10 unbound textboxes


Display message if filter returns no results
When users apply a filter on the form, if there are no results, the form appears "blank". I'd like to pop up a message that tells the users that no results were returned. Is there any VBA to account for a simple filter not returning results?


Display message if filter returns no results
I have a question regarding filtering on an access form. When users apply a filter on the form, if there are no results, the form appears "blank". I'd like to pop up a message that tells the users that no results were returned. Is there any VBA to account for a simple filter not returning results


Form input to query/report
I have a "Multiple Items Form" called frmSearch where the user puts in criteria and the form displays the results. This part works correctly.

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.


Filting query results within VBA code module
I use Access 2010 but haven’t written much VBA code and could use some help. I have a table that provides the name of a query in a text field. The field name is “Query”.

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:


Close Form once Another Opens
I am trying to build multi field search into an Access 2007 database I have with 2 forms & 1 query. I have a form for entering the details & a query, with criteria such as 'Like "*" & [Forms]![frmSearch]![txtFirstName] & "*"' and 'Like "*" & [Forms]![frmSearch]![txtLastName] & "*"'.

Rather than running the query from the form, & getting a datasheet display of results, I have made a 2nd form which will display the query results. The Search form has a button to open this Results form &all works well.

What I would like is to be able to close the Search form when I click the button to open the Results form. In the Search form, if I put DoCmd.Close before DoCmd.OpenForm in the code for the button, it closes my Search form which is feeding the query for the Results form before the results are displayed.


Displaying multiple records on one form
I am designing a restaurant kitchen display screen. I have a query structured from two tables, one with the order numbers and the other with the items in each order.

I would like the results of the query displayed in several list boxes (aiming for at least 12 to show current orders) on one single form to display all the current orders on a screen.

I would need to refresh/requery the form to update the query results as new orders are taken.