Build SQL From Form

[Marvin]

I’m looking for some code to help me get started on part of a project.

I would like to provide the functionality on a form that allows a user to
select from five or more multi-select listboxes, checkboxes, and maybe
comboboxes for creating the Recordsource or filter for a report or other form.

For example: The user makes some selections from the controls on the form,
then clicks a button to open a report that uses their selections to set the
recordsource or filter for the report they want to view.

I would make this work by using something like this pseudo code to build
part of the SQL string:
For each ctl in Me.Controls
If ctl.tag like “Field filter†then
sqlString = sqlString & ctl.Value “ In “ & GetfieldName(ctl.name) …
End If
Next ctl
~ assemble the rest of the string and pass it as a parameter to the report ~
Etc.

If someone knows of some code that does this in a generic fashion or in a
fairly simple example, it could save me a lot of coding time.

Any help is greatly appreciated.

~ Marvin

 

[FatMan]

Marvin:
Not sure if this will help as it might be too simple for your example.

I have created many forms where the users can select various values from
combo boxes, check boxes and text fields. I have then create queries to read
the values from the forms by using the "build" wizard in the query by example
grid of access. To access this wizard all you have to do is right click in
the "criteria" section for a fuild and click on build. From there you can
access your forms and create you criteria selection. It has worked for me
many times. Bleow is the SQL statement generated by access to extract some
sales data from a table based on the dates ([txtStartDateCY]) and
([txtEndDateCY]) provide by the user on the form ([frmMainMenu].

SELECT tblSales.ProductClass, Sum(tblSales.InvoiceValue) AS
SumOfInvoiceValue, Sum(tblSales.CostValue) AS SumOfCostValue
FROM tblSales
WHERE (((tblSales.Branch)="07" Or (tblSales.Branch)="08") AND
((tblSales.TrnDate) Between [Forms]![frmMainMenu]![txtStartDateCY] And
[Forms]![frmMainMenu]![txtEndDateCY]))
GROUP BY tblSales.ProductClass;

I hope this helps,
FatMan

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), are a number of small Access
database samples that might help. All of them start with "CreateQueries".
CreateQueries3.mdb is probably closest to what you want, but the others may
give you ideas as well.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Marvin]

Thank you FatMan. I appreciate your response. It's not quite what I'm looking
for, but I will keep this in mind.

Marvin:
Not sure if this will help as it might be too simple for your example.

I have created many forms where the users can select various values from
combo boxes, check boxes and text fields. I have then create queries to read
the values from the forms by using the "build" wizard in the query by example
grid of access. To access this wizard all you have to do is right click in
the "criteria" section for a fuild and click on build. From there you can
access your forms and create you criteria selection. It has worked for me
many times. Bleow is the SQL statement generated by access to extract some
sales data from a table based on the dates ([txtStartDateCY]) and
([txtEndDateCY]) provide by the user on the form ([frmMainMenu].

SELECT tblSales.ProductClass, Sum(tblSales.InvoiceValue) AS
SumOfInvoiceValue, Sum(tblSales.CostValue) AS SumOfCostValue
FROM tblSales
WHERE (((tblSales.Branch)="07" Or (tblSales.Branch)="08") AND
((tblSales.TrnDate) Between [Forms]![frmMainMenu]![txtStartDateCY] And
[Forms]![frmMainMenu]![txtEndDateCY]))
GROUP BY tblSales.ProductClass;

I hope this helps,
FatMan

I’m looking for some code to help me get started on part of a project.

I would like to provide the functionality on a form that allows a user to
select from five or more multi-select listboxes, checkboxes, and maybe
comboboxes for creating the Recordsource or filter for a report or other form.

For example: The user makes some selections from the controls on the form,
then clicks a button to open a report that uses their selections to set the
recordsource or filter for the report they want to view.

I would make this work by using something like this pseudo code to build
part of the SQL string:
For each ctl in Me.Controls
If ctl.tag like “Field filter†then
sqlString = sqlString & ctl.Value “ In “ & GetfieldName(ctl.name) …
End If
Next ctl
~ assemble the rest of the string and pass it as a parameter to the report ~
Etc.

If someone knows of some code that does this in a generic fashion or in a
fairly simple example, it could save me a lot of coding time.

Any help is greatly appreciated.

~ Marvin

 

[Sergiy Korzh]

Hello,

Did you see our EasyQuery library: http://devtools.korzh.com/eq/

Not sure if it works in Access (we did not test) but it seems ActiveX edition
of this library (EasyQuery.AX) should work well there.

 

[Marvin]

Thanks for the reply. CreateMultiSelectQuery.mdb and CreateQueries3.mdb
incorporate the basic elements I'm looking for.

I’m still interested in any code anyone might have that would let me kind of
plug-in information on a form or in a table to build the SQL for a report
record source.