Execute SQL statement and display results on a form using VBA

[thebatfink]

Hi,

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 requirement is 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?

Thanks!

 

[pr2-eugin]

You can use an unbound Form and then have its RecordSource to the Query that you are executing in VBA.. something like..

mySQL = "SELECT * FROM RAWDATA;"
Forms!myDummyForm.Form.RecordSource = mySQL

But in my opinion, stored Query is much faster in computation. To your other question, you can auto export the result of the QUERY using the DoCmd.. This also will be easier with stored SQL Queries... (I Think !!)

 

[thebatfink]

Hi, thanks for the reply.

So I made the form, and tried your code and it does appear to add records to the form, but the form remains blank. So I have a couple of questions..

I was kind of hoping for the datasheet style table to appear on the form. I assume I have to use some control to display the records. What control could I use?

And then, this works great if the form is open, but how would I go about opening the form and passing the argument? Like with 'Docmd.OpenForm' or something?

Thanks!

 

[pr2-eugin]

Yes, normally you use TextBoxes or ComboBoxes, ListBoxes.. to display your details.. Select the ones that suit your needs.. If you are referring to open a Form based on a condition check out..
http://baldyweb.com/wherecondition.htm
Or did you mean something else and I misunderstood you on a entire level?