Check box to control editablility of text box and return all recor

  • Thread starter Thread starter Huskybydezign
  • Start date Start date
H

Huskybydezign

Right now I have a form that is used to search a query, it has two text boxes
(JobStart) and (JobEnd), and three combo boxes (ClientName),
(JurisdictionName) and (TypeName). Right now the two text boxes are used to
enter a range of numbers and the combo boxes are used to select a value from
a list. I would like the user to be able to leave any of the combo boxes
null (or blank) and have records returned based on the other combo box
selections. Additionally I would like there to be a check box that controls
the two text boxes, if the check box is selected than all values are returned
and the text boxes become invisible or uneditable, i.e. turn gray in
appearance. If the check box is not checked than the user has to input a
range (# in both boxes) for the query to use as search criteria. Thanks in
advance.

Here is current SQL:

SELECT DISTINCT [Project information].[Project Number], [Project
information].[Project Name], [Project information].Client, [Project
information].Jurisdiction, [Project information].[Street Address], [Project
information].City, [Project information].State, [Project information].Zip,
[Project information].Units, [Project information].Flats, [Project
information].TH, [Project information].Type, [Project information].[Area
(acres)], [Project information].[Units/Acre], *
FROM [Project information]
WHERE ((([Project information].[Project Number]) Between [Forms]![QBF
Form]![JobStart] And [Forms]![QBF Form]![JobEnd]) AND (([Project
information].Client) Like IIf([forms]![QBF Form]![ClientName] Is
Null,"*",[forms]![QBF Form]![ClientName])) AND (([Project
information].Jurisdiction) Like IIf([forms]![QBF Form]![JurisdictionName] Is
Null,"*",[forms]![QBF Form]![JurisdictionName])) AND (([Project
information].Type) Like IIf([forms]![QBF Form]![TypeName] Is
Null,"*",[forms]![QBF Form]![TypeName])));
 
It is possible to craft the WHERE clause of the query so that it returns a
TRUE expression if the controls are null, e.g.:
WHERE (([Forms].[Form1].[Combo1] Is Null)
OR ([SomeField] = [Forms].[Form1].[Combo1]))
AND ...

However, you may find that it is better to omit the WHERE clause from the
query, and create a filter string in code. Here's an example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Benefits:
a) Much more flexible
b) Easier to maintain
c) More efficient to execute
d) Query can be reused (since it's not reading from one form.)
 
Back
Top