Implementing access form with dynamic recordset

Question

I have just implemented a messaging center in our contact management application (access). This includes text and email template management, and dynamic email/txt groups by sql queries maintain in a table. This all works great, and I can select a message template, and a SQLquery stored in a table and it sends either text or email messages to my contacts. PERFECT.

NOW, on the form where I manage my SQL queries to be selected from a combo box when sending the message... I've been asked to do one or both enhancements. One, after the sql statement is entered in the form... the ability to count the number of records that query will return. And two the ability to pull up a list of the customers in the query.

I believe I open a new form from a button and pass the ID of the record of the query to a new form. Do a simple dlookup and retrieve the sql statement from the table. Then I am assuming I can point my record set to the fieldvariable holding the value of the sql statment?

My confusion is I am unsure of the vba necessary to accomplish this, or which even this needs to be placed in. Further... will the fields defined in the sql query show up in "add existing fields" to a form? Or how does it know what fields I am able to place on my form?

Why are users selecting queries? Should be presented with a list of reports and/or select filter criteria on a 'search' form and VBA builds filter criteria. Review allenbrowne.com/ser-62.html — June7
No, I have a form where the admin (me) can build a sql statement in a long text field. LIke San Diego customers, I can write the sql on the fly for a specific list need... by customer... by sales volumne, but missed connection last visit to city, etc... I can save all my sql statements for mailing lists/text blasts/email blasts by each query -- which can change depending on needs for the mailing. Just easier to build them on a form than having to edit sql all the time in the back end. — Don Oldenburg

SunKnight0 SunKnight0 · Accepted Answer · 2018-08-08T19:26:30

All you need to count the number of results is:

SELECT COUNT(*) FROM ( [My Query SQL] )

The other part depends on how consistent your queries are. If they all have the same fields, like [CustomerName] use something like:

SELECT [CustomerName] FROM ( [My Query SQL] ) ORDER BY [CustomerName]

As for how to display the results it depends on the why you need them They can be added to simple MsgBox (you will need some VBA to convert the above query results to string) or to popup form or a sub-form.

To get the results into VBA:

How do I get the data from an SQL query in microsoft Access VBA?

Implementing access form with dynamic recordset

1 Answers