Use Form to generate Query for a Report

[Ben]

Hello,

I'm fairly new to Access, so maybe there's a better way to do what I'm
trying to do, but here it is...

I'm an engineer and I'm making a database to store results and setup
info for tests peformed.

1 function of the db is to be able to search for test results. I want
users of the db to be able enter as many or as few criteria, selected
from combo boxes and entered in text boxes on a form, as they choose
(about 6 form fields total, including TestNumber, TestDate,
PartNumber, TestDescription, etc). The search form would have 2 main
areas: 1st they would select the criteria that must be matched in the
tables of test results, 2nd they would choose which specific fields
will be displayed on the report (which is intended to be printed on
8.5x11)

The end result I'd like is a report listing the test results. I'm
trying to avoid hard coding all the different queries. I want an sql
statement to be generated as the form is filled out. Once a "Search"
button is hit the SQL statement would do what it does to query the
results from basically 2 tables, including only the fields selected by
the user. Then a report would automatically open, reading from the
query that was made.

I've tried using "DoCmd.RunSQL" in the OnClick code of the "search"
button with very little luck. Here's an example of me trying to select
all the tested seats of either program A or B:
Dim SQL_Text As String
SQL_Text = "SELECT tblSeats.*, tblSeats.ProgramID " & _
"FROM [tblSeats] " & _
"WHERE (((tblSeats.ProgramID)='A' Or (tblSeats.ProgramID)='B'));"
DoCmd.RunSQL SQL_Text, False

I then get an error like:
"A RunSQL action requires an argument consisting of an SQL statement"

One problem is I don't really understand what RunSQL is doing... is it
creating a new querty object? If not, can the results even by used for
a report?

I've also tried linking the search form to a table with a field for
each field in the form. Each time a new search was entered, the form
deleted the table's data, and entered the new criteria...the table
only kept the 1 record. This record was then used for the WHERE clause
in my report query. This allowed for no flexibility because all of the
search criteria had to be selected, allowing only for very limited and
narrow search results.

Any help, whether specific examples or general db practices, is
appreciated.
Thanks.

 

[Douglas J. Steele]

RunSQL can only be used with Action queries (INSERT INTO, UPDATE, DELETE)

What you can do is change the SQL of an existing query. Something like:

Dim qdfExisting As DAO.QueryDef
Dim SQL_Text As String

SQL_Text = "SELECT tblSeats.*, tblSeats.ProgramID " & _
"FROM [tblSeats] " & _
"WHERE (((tblSeats.ProgramID)='A' Or (tblSeats.ProgramID)='B'));"

Set qdfExisting = CurrentDb().QueryDefs("NameOfQuery")
qdfExisting.SQL = SQL_Text

You can then use that query in your report (or you can simply open the query
using the OpenQuery method)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

I'm fairly new to Access, so maybe there's a better way to do what I'm
trying to do, but here it is...

I'm an engineer and I'm making a database to store results and setup
info for tests peformed.

1 function of the db is to be able to search for test results. I want
users of the db to be able enter as many or as few criteria, selected
from combo boxes and entered in text boxes on a form, as they choose
(about 6 form fields total, including TestNumber, TestDate,
PartNumber, TestDescription, etc). The search form would have 2 main
areas: 1st they would select the criteria that must be matched in the
tables of test results, 2nd they would choose which specific fields
will be displayed on the report (which is intended to be printed on
8.5x11)

The end result I'd like is a report listing the test results. I'm
trying to avoid hard coding all the different queries. I want an sql
statement to be generated as the form is filled out. Once a "Search"
button is hit the SQL statement would do what it does to query the
results from basically 2 tables, including only the fields selected by
the user. Then a report would automatically open, reading from the
query that was made.

I've tried using "DoCmd.RunSQL" in the OnClick code of the "search"
button with very little luck. Here's an example of me trying to select
all the tested seats of either program A or B:
Dim SQL_Text As String
SQL_Text = "SELECT tblSeats.*, tblSeats.ProgramID " & _
"FROM [tblSeats] " & _
"WHERE (((tblSeats.ProgramID)='A' Or (tblSeats.ProgramID)='B'));"
DoCmd.RunSQL SQL_Text, False

I then get an error like:
"A RunSQL action requires an argument consisting of an SQL statement"

One problem is I don't really understand what RunSQL is doing... is it
creating a new querty object? If not, can the results even by used for
a report?

I've also tried linking the search form to a table with a field for
each field in the form. Each time a new search was entered, the form
deleted the table's data, and entered the new criteria...the table
only kept the 1 record. This record was then used for the WHERE clause
in my report query. This allowed for no flexibility because all of the
search criteria had to be selected, allowing only for very limited and
narrow search results.

Any help, whether specific examples or general db practices, is
appreciated.
Thanks.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), are several small Access
database samples: "CreateQueries2.mdb", "CreateQueries4.mdb",
"CreateQueries5.mdb" which might give you some ideas for simple query/report
generators.

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

Hello,

I'm fairly new to Access, so maybe there's a better way to do what I'm
trying to do, but here it is...

I'm an engineer and I'm making a database to store results and setup
info for tests peformed.

1 function of the db is to be able to search for test results. I want
users of the db to be able enter as many or as few criteria, selected
from combo boxes and entered in text boxes on a form, as they choose
(about 6 form fields total, including TestNumber, TestDate,
PartNumber, TestDescription, etc). The search form would have 2 main
areas: 1st they would select the criteria that must be matched in the
tables of test results, 2nd they would choose which specific fields
will be displayed on the report (which is intended to be printed on
8.5x11)

The end result I'd like is a report listing the test results. I'm
trying to avoid hard coding all the different queries. I want an sql
statement to be generated as the form is filled out. Once a "Search"
button is hit the SQL statement would do what it does to query the
results from basically 2 tables, including only the fields selected by
the user. Then a report would automatically open, reading from the
query that was made.

I've tried using "DoCmd.RunSQL" in the OnClick code of the "search"
button with very little luck. Here's an example of me trying to select
all the tested seats of either program A or B:
Dim SQL_Text As String
SQL_Text = "SELECT tblSeats.*, tblSeats.ProgramID " & _
"FROM [tblSeats] " & _
"WHERE (((tblSeats.ProgramID)='A' Or (tblSeats.ProgramID)='B'));"
DoCmd.RunSQL SQL_Text, False

I then get an error like:
"A RunSQL action requires an argument consisting of an SQL statement"

One problem is I don't really understand what RunSQL is doing... is it
creating a new querty object? If not, can the results even by used for
a report?

I've also tried linking the search form to a table with a field for
each field in the form. Each time a new search was entered, the form
deleted the table's data, and entered the new criteria...the table
only kept the 1 record. This record was then used for the WHERE clause
in my report query. This allowed for no flexibility because all of the
search criteria had to be selected, allowing only for very limited and
narrow search results.

Any help, whether specific examples or general db practices, is
appreciated.
Thanks.

 

[Tom Wickerath]

Hi Ben,

Access MVP Armen Stein has a ready-made sample that you can download. See
the sample called "Report Selection Techniques":

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

I have a tutorial for doing much the same, but for an unbound form with the
results displayed in a subform. This is the February, 2008 download available
here:

http://www.seattleaccess.org/downloads.htm

There is a related download for January, 2008 that I recommend that you
become familiar with first, before diving into the February download. In case
you are new to VBA coding, take a look first at the download for Jan/Feb
2007. Also, grab a copy of Access MVP Crystal's tutorials, here:

http://www.accessmvp.com/Strive4Peace/Index.htm

Good luck, and happy coding!


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hello,

I'm fairly new to Access, so maybe there's a better way to do what I'm
trying to do, but here it is...

I'm an engineer and I'm making a database to store results and setup
info for tests peformed.

1 function of the db is to be able to search for test results. I want
users of the db to be able enter as many or as few criteria, selected
from combo boxes and entered in text boxes on a form, as they choose
(about 6 form fields total, including TestNumber, TestDate,
PartNumber, TestDescription, etc). The search form would have 2 main
areas: 1st they would select the criteria that must be matched in the
tables of test results, 2nd they would choose which specific fields
will be displayed on the report (which is intended to be printed on
8.5x11)

The end result I'd like is a report listing the test results. I'm
trying to avoid hard coding all the different queries. I want an sql
statement to be generated as the form is filled out. Once a "Search"
button is hit the SQL statement would do what it does to query the
results from basically 2 tables, including only the fields selected by
the user. Then a report would automatically open, reading from the
query that was made.

I've tried using "DoCmd.RunSQL" in the OnClick code of the "search"
button with very little luck. Here's an example of me trying to select
all the tested seats of either program A or B:
Dim SQL_Text As String
SQL_Text = "SELECT tblSeats.*, tblSeats.ProgramID " & _
"FROM [tblSeats] " & _
"WHERE (((tblSeats.ProgramID)='A' Or (tblSeats.ProgramID)='B'));"
DoCmd.RunSQL SQL_Text, False

I then get an error like:
"A RunSQL action requires an argument consisting of an SQL statement"

One problem is I don't really understand what RunSQL is doing... is it
creating a new querty object? If not, can the results even by used for
a report?

I've also tried linking the search form to a table with a field for
each field in the form. Each time a new search was entered, the form
deleted the table's data, and entered the new criteria...the table
only kept the 1 record. This record was then used for the WHERE clause
in my report query. This allowed for no flexibility because all of the
search criteria had to be selected, allowing only for very limited and
narrow search results.

Any help, whether specific examples or general db practices, is
appreciated.
Thanks.