Topics Search

Best way to display VBA query results?

Best way to display VBA query results?
Views: 91
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:

Similar posts...

 

Execute SQL statement and display results on a form using VBA

Execute SQL statement and display results on a form using VBA Icon
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

VBA Show query in a form/subform Icon
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
 

Pivot/Array results

Pivot/Array results Icon
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:
 

How to Build and export a crosstab query in VBA?

How to Build and export a crosstab query in VBA? Icon
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

Exporting the results of a query to Excel Icon
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

Displaying Query Results in a Form List Box Icon
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.
 

assembling a list of parameter count query results

assembling a list of parameter count query results Icon
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.
 

Filting query results within VBA code module

Filting query results within VBA code module Icon
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:
 

Query will not display specific parameter rather display every contents of table

Query will not display specific parameter rather display every contents of table Icon
I have created a query based on the table which gets updated every time I enter new data in forms. I am having difficulty when I create specific parameter and try to run the query I don't see the anticipated results.

Either query will not display specific parameter rather display every contents of table or either it won't display anything.
 

Form input to query/report

Form input to query/report Icon
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.