By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 670 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

How to do Query by Form?

P: n/a
I'm using A2K.

I would like to create a form that would allow the user to run a
particular query by entering the parameters into one or more text
boxes.

I am currently using a parameterized query similar to this:

SELECT tblListData.*
FROM tblListData
WHERE (((tblListData.ExamNum)=[Enter Exam Number]) AND
((tblListData.Score)>=[Enter Low Score]))
ORDER BY tblListData.Score DESC;

But I don't want the parameter dialogs. It would prefer to enter the
criteria into a form, click a button and get the results.

I would also like to be able include / exclude data from selected
catagories by selecting checkboxes on the form.
For example, I would like a query to return payroll information of
various units in the company, e.g. ExecUnit, AdminUnit, HRUnit, etc.
in any combination selected by the checkboxes.

I have no experience using forms for queries, so I don't even know
where to start.

Any help is appreciated.
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use form referencing. E.g.:

PARAMETERS Forms!form_name!txtExamNumber Long,
Forms!form_name!txtLowScore Integer;
SELECT tblListData.*
FROM tblListData
WHERE (((tblListData.ExamNum)=Forms!form_name!txtExamNum ber) AND
((tblListData.Score)>=Forms!form_name!txtLowScore) )
ORDER BY tblListData.Score DESC;

For a dynamic query (changes in columns, tables, criteria, etc.) one
usually uses VBA to create and run the query. E.g.:

Private Sub cmdListData_Click()

dim strSQL as string
dim strWhere as string

strSQL = "SELECT * FROM table_name "

if me!chkExecUnit = True Then
strWhere = "ExecUnit = True "
end if

if me!chkAdminUnit = True Then
if len(strWhere)>0 then strWhere = strWhere & " AND "
strWhere = "AdminUnit = True "
end if

if len(strWhere)>0 then strWhere = " WHERE " & strWhere

strSQL = strSQL & strWhere

' run the query - other things can be done w/ SQL string
dim db as dao.database
dim rs as dao.recordset
set db = currentdb
set rs = db.openrecordset(strSQL)

' ... etc. ... use data as you wish...

End Sub

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRz7x4echKqOuFEgEQL13QCeIj7WzluJQvj2yRQlSEtBtw VwntQAoIux
dogKmDp6aYKznRR0fUCdwlai
=UdHP
-----END PGP SIGNATURE-----
RBohannon wrote:
I'm using A2K.

I would like to create a form that would allow the user to run a
particular query by entering the parameters into one or more text
boxes.

I am currently using a parameterized query similar to this:

SELECT tblListData.*
FROM tblListData
WHERE (((tblListData.ExamNum)=[Enter Exam Number]) AND
((tblListData.Score)>=[Enter Low Score]))
ORDER BY tblListData.Score DESC;

But I don't want the parameter dialogs. It would prefer to enter the
criteria into a form, click a button and get the results.

I would also like to be able include / exclude data from selected
catagories by selecting checkboxes on the form.
For example, I would like a query to return payroll information of
various units in the company, e.g. ExecUnit, AdminUnit, HRUnit, etc.
in any combination selected by the checkboxes.

I have no experience using forms for queries, so I don't even know
where to start.


Nov 13 '05 #2

P: n/a
MGFoster <me@privacy.com> wrote in message news:<fZ******************@newsread1.news.pas.eart hlink.net>...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use form referencing. E.g.:

PARAMETERS Forms!form_name!txtExamNumber Long,
Forms!form_name!txtLowScore Integer;
SELECT tblListData.*
FROM tblListData
WHERE (((tblListData.ExamNum)=Forms!form_name!txtExamNum ber) AND
((tblListData.Score)>=Forms!form_name!txtLowScore) )
ORDER BY tblListData.Score DESC;

For a dynamic query (changes in columns, tables, criteria, etc.) one
usually uses VBA to create and run the query. E.g.:

Private Sub cmdListData_Click()

dim strSQL as string
dim strWhere as string

strSQL = "SELECT * FROM table_name "

if me!chkExecUnit = True Then
strWhere = "ExecUnit = True "
end if

if me!chkAdminUnit = True Then
if len(strWhere)>0 then strWhere = strWhere & " AND "
strWhere = "AdminUnit = True "
end if

if len(strWhere)>0 then strWhere = " WHERE " & strWhere

strSQL = strSQL & strWhere

' run the query - other things can be done w/ SQL string
dim db as dao.database
dim rs as dao.recordset
set db = currentdb
set rs = db.openrecordset(strSQL)

' ... etc. ... use data as you wish...

End Sub


Nicely done.

James A. Fortune
Nov 13 '05 #3

P: n/a
Thank you for your help. I won't have a chance to try this until
Monday, 8/23, but this looks like exactly what I need.

Randy

MGFoster <me@privacy.com> wrote in message news:<fZ******************@newsread1.news.pas.eart hlink.net>...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use form referencing. E.g.:

PARAMETERS Forms!form_name!txtExamNumber Long,
Forms!form_name!txtLowScore Integer;
SELECT tblListData.*
FROM tblListData
WHERE (((tblListData.ExamNum)=Forms!form_name!txtExamNum ber) AND
((tblListData.Score)>=Forms!form_name!txtLowScore) )
ORDER BY tblListData.Score DESC;

For a dynamic query (changes in columns, tables, criteria, etc.) one
usually uses VBA to create and run the query. E.g.:

Private Sub cmdListData_Click()

dim strSQL as string
dim strWhere as string

strSQL = "SELECT * FROM table_name "

if me!chkExecUnit = True Then
strWhere = "ExecUnit = True "
end if

if me!chkAdminUnit = True Then
if len(strWhere)>0 then strWhere = strWhere & " AND "
strWhere = "AdminUnit = True "
end if

if len(strWhere)>0 then strWhere = " WHERE " & strWhere

strSQL = strSQL & strWhere

' run the query - other things can be done w/ SQL string
dim db as dao.database
dim rs as dao.recordset
set db = currentdb
set rs = db.openrecordset(strSQL)

' ... etc. ... use data as you wish...

End Sub

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRz7x4echKqOuFEgEQL13QCeIj7WzluJQvj2yRQlSEtBtw VwntQAoIux
dogKmDp6aYKznRR0fUCdwlai
=UdHP
-----END PGP SIGNATURE-----
RBohannon wrote:
I'm using A2K.

I would like to create a form that would allow the user to run a
particular query by entering the parameters into one or more text
boxes.

I am currently using a parameterized query similar to this:

SELECT tblListData.*
FROM tblListData
WHERE (((tblListData.ExamNum)=[Enter Exam Number]) AND
((tblListData.Score)>=[Enter Low Score]))
ORDER BY tblListData.Score DESC;

But I don't want the parameter dialogs. It would prefer to enter the
criteria into a form, click a button and get the results.

I would also like to be able include / exclude data from selected
catagories by selecting checkboxes on the form.
For example, I would like a query to return payroll information of
various units in the company, e.g. ExecUnit, AdminUnit, HRUnit, etc.
in any combination selected by the checkboxes.

I have no experience using forms for queries, so I don't even know
where to start.

Nov 13 '05 #4

P: n/a
Thanks. I tweaked the code, and it does exactly what I need. Now, a
follow up question: how can I populate a report with the results of
this query? I tried to save the results with DoCmd.Save, but I can't
get it to work.
MGFoster <me@privacy.com> wrote in message news:<fZ******************@newsread1.news.pas.eart hlink.net>...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
For a dynamic query (changes in columns, tables, criteria, etc.) one
usually uses VBA to create and run the query. E.g.:

Private Sub cmdListData_Click()

dim strSQL as string
dim strWhere as string

strSQL = "SELECT * FROM table_name "

if me!chkExecUnit = True Then
strWhere = "ExecUnit = True "
end if

if me!chkAdminUnit = True Then
if len(strWhere)>0 then strWhere = strWhere & " AND "
strWhere = "AdminUnit = True "
end if

if len(strWhere)>0 then strWhere = " WHERE " & strWhere

strSQL = strSQL & strWhere

' run the query - other things can be done w/ SQL string
dim db as dao.database
dim rs as dao.recordset
set db = currentdb
set rs = db.openrecordset(strSQL)

' ... etc. ... use data as you wish...

End Sub

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRz7x4echKqOuFEgEQL13QCeIj7WzluJQvj2yRQlSEtBtw VwntQAoIux
dogKmDp6aYKznRR0fUCdwlai
=UdHP
-----END PGP SIGNATURE-----

Nov 13 '05 #5

P: n/a
For anyone else this may help, I saved the query by doing this:
db.QueryDefs("qryMyQuery").SQL = strSQL

Randy

ra*****@hotmail.com (RBohannon) wrote in message news:<ad**************************@posting.google. com>...
Thanks. I tweaked the code, and it does exactly what I need. Now, a
follow up question: how can I populate a report with the results of
this query? I tried to save the results with DoCmd.Save, but I can't
get it to work.
MGFoster <me@privacy.com> wrote in message news:<fZ******************@newsread1.news.pas.eart hlink.net>...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
For a dynamic query (changes in columns, tables, criteria, etc.) one
usually uses VBA to create and run the query. E.g.:

Private Sub cmdListData_Click()

dim strSQL as string
dim strWhere as string

strSQL = "SELECT * FROM table_name "

if me!chkExecUnit = True Then
strWhere = "ExecUnit = True "
end if

if me!chkAdminUnit = True Then
if len(strWhere)>0 then strWhere = strWhere & " AND "
strWhere = "AdminUnit = True "
end if

if len(strWhere)>0 then strWhere = " WHERE " & strWhere

strSQL = strSQL & strWhere

' run the query - other things can be done w/ SQL string
dim db as dao.database
dim rs as dao.recordset
set db = currentdb
set rs = db.openrecordset(strSQL)

' ... etc. ... use data as you wish...

End Sub

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRz7x4echKqOuFEgEQL13QCeIj7WzluJQvj2yRQlSEtBtw VwntQAoIux
dogKmDp6aYKznRR0fUCdwlai
=UdHP
-----END PGP SIGNATURE-----

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.