BuildCriteria works in Northwind, but not in my db

[Guest]

I have a form with 75 fields for the user to input/select search criteria.
I went into the help and pulled up a sample from the Northwind database, did
the sample and it worked great.
When I put it into my form, it does not construct the entire statement. It
only gives me "select * from data Where ". Any help on this would be greatly
appreciated.

Below is my code. I am only putting data in one text box, then clicking
"search button". I also need to know if I have the correct syntax for a
combobox and checkbox.

Private Sub cmdSearch_Click()

On Error Resume Next

Dim ctl As Control
Dim sSQL As String
Dim sWhereClause As String

'Initialize the Where Clause variable.
sWhereClause = " Where "

'Start the first part of the select statement.
sSQL = "select * from data "

'Loop through each control on the form to get its value.
For Each ctl In Me.Controls
With ctl
'The only Control you are using is the text box.
'However, you can add as many types of controls as you want.
Select Case .ControlType

Case acTextBox
.SetFocus
'This is the function that actually builds 'the clause.
If sWhereClause = " Where " Then
sWhereClause = sWhereClause & BuildCriteria(.Name, dbTEXT,
..Text)
Else: sWhereClause = sWhereClause & " and " &
BuildCriteria(.Name, dbTEXT, .Text)
End If

'Case acComboBox
'.SetFocus
'This is the function that actually builds 'the clause.
'If sWhereClause = " Where " Then
'sWhereClause = sWhereClause & BuildCriteria(.Name, dbTEXT,
..Text)
'Else: sWhereClause = sWhereClause & " and " &
BuildCriteria(.Lname, dbTEXT, .Value)
'End If

'Case acCheckBox
'.SetFocus
'This is the function that actually builds 'the clause.
'If sWhereClause = " Where " Then
'sWhereClause = sWhereClause & BuildCriteria(.Name, dbTEXT,
..Text)
'Else: sWhereClause = sWhereClause & " and " &
BuildCriteria(.Name, dbTEXT, .Text)
'End If


End Select

End With
Next ctl

'Set the forms recordsource equal to the new 'select statement.
Me.txtSql = sSQL & sWhereClause
Me.RecordSource = sSQL & sWhereClause
Me.Requery

End Sub

 

[Marshall Barton]

I have a form with 75 fields for the user to input/select search criteria.
I went into the help and pulled up a sample from the Northwind database, did
the sample and it worked great.
When I put it into my form, it does not construct the entire statement. It
only gives me "select * from data Where ". Any help on this would be greatly
appreciated.

Below is my code. I am only putting data in one text box, then clicking
"search button". I also need to know if I have the correct syntax for a
combobox and checkbox.

Private Sub cmdSearch_Click()

On Error Resume Next

Dim ctl As Control
Dim sSQL As String
Dim sWhereClause As String

'Initialize the Where Clause variable.
sWhereClause = " Where "

'Start the first part of the select statement.
sSQL = "select * from data "

'Loop through each control on the form to get its value.
For Each ctl In Me.Controls
With ctl
'The only Control you are using is the text box.
'However, you can add as many types of controls as you want.
Select Case .ControlType

Case acTextBox
.SetFocus
'This is the function that actually builds 'the clause.
If sWhereClause = " Where " Then
sWhereClause = sWhereClause & BuildCriteria(.Name, dbTEXT,
.Text)
Else: sWhereClause = sWhereClause & " and " &
BuildCriteria(.Name, dbTEXT, .Text)
End If

'Case acComboBox
'.SetFocus
'This is the function that actually builds 'the clause.
'If sWhereClause = " Where " Then
'sWhereClause = sWhereClause & BuildCriteria(.Name, dbTEXT,
.Text)
'Else: sWhereClause = sWhereClause & " and " &
BuildCriteria(.Lname, dbTEXT, .Value)
'End If

'Case acCheckBox
'.SetFocus
'This is the function that actually builds 'the clause.
'If sWhereClause = " Where " Then
'sWhereClause = sWhereClause & BuildCriteria(.Name, dbTEXT,
.Text)
'Else: sWhereClause = sWhereClause & " and " &
BuildCriteria(.Name, dbTEXT, .Text)
'End If


End Select

End With
Next ctl

'Set the forms recordsource equal to the new 'select statement.
Me.txtSql = sSQL & sWhereClause
Me.RecordSource = sSQL & sWhereClause
Me.Requery

End Sub

I can't tell from the code, but the first thing you need to
do is replace the On Erro Resume Next with some real error
handling so you can find out if there are any errors.

Another thing is to get rid of all those SetFocus lines and
use the Value property instead of the Text property (a
CheckBox doesn't even have a Text property).

You code assumes that the name of the field i the
table/query is the same as the name of the control. I'm not
sure, but this might cause some name conflicts. You should
at least double check that each control has the correct
name.

I suspect that you might be using BuildCriteria's type
argument improperly. It's supposed to specify the type of
the field in the table/query and you'll get some funny
results for numeric or date type fields.

The code for combo and text boxes looks the same, so you
could consolidate that into one Case acTextBox, acComboBox

Note that setting the RecordSource automatically initiates a
requery, Your Me.Requery at the end is redundant and just
wastes time.

 

[Guest]

Marshall,

Thank you. I'm not sure I understand all your comments. Even if I remark out
the parts regarding the combo boxes and checkboxes, leaving only text boxes,
it does not work in my database, but it does work in the Northwind database.
I took the coding directly from the MSDN.

The error handling I can change.
Why would I take out the set focus, when it actually needs to get the name,
type of data and value of each control. If it does not set the focus, can it
still get this info for each control?

All my text boxes are named "txt...." & the field name with no spaces. All
my combo boxes are named "cbo..." Is this what you meant by checking that
each control has the correct name?

How can I handle the numeric or date type fields?

The MSDN example only showed the "Case acTextBox" code (which worked fine in
the northwind db). And it said "The only Control you are using is the text
box. However, you can add as many types of controls as you want." You mention
consolidating all my cases into one. If I do that, and use the "value"
instead of "text", will that take care of the different data types?

The BuildCritera method looks like the best way to do this, but do you know
of a better way?

Thank you!

 

[Marshall Barton]

Comments inline below:
--
Marsh
MVP [MS Access]

Thank you. I'm not sure I understand all your comments. Even if I remark out
the parts regarding the combo boxes and checkboxes, leaving only text boxes,
it does not work in my database, but it does work in the Northwind database.
I took the coding directly from the MSDN.

A key thing to remember here is that Controls and Fields are
very different things. A bound Control can be used to
display/edit the value of a Field in the record source
table/query.

In your case of a search form, the controls are all unbound
so there should be no confusion about this. The fields that
BuildCriteria is working with are the fields in the table
that you want to search.

The error handling I can change.

Why would I take out the set focus, when it actually needs to get the name,
type of data and value of each control. If it does not set the focus, can it
still get this info for each control?

The SetFocus is only needed to retrieve special properties
of a control. These properties, such as Text, only have
meaning while the value of the control is in the act of
being edited, which you do not care about here. The values
you want are the controls' Value property.

All my text boxes are named "txt...." & the field name with no spaces. All
my combo boxes are named "cbo..." Is this what you meant by checking that
each control has the correct name?

That's fine, but you are required to supply the Name of the
FIELD in BuildCriteria's first argument. You are using the
Control's name. These two names could be the same, but,
IMO, that's not a good idea.

For a search from, I prefer to name the important controls
with a fixed length prefix such are "srch" followed by the
name of the Field that you want the criteria to be applied
to:
BuildCriteria(Mid(.Name, 5), ...

How can I handle the numeric or date type fields?

Another critical aspect of using BuildCriteria is to specify
the FIELD's data type in the second argument. I usually use
the Tag property for this. Then I can call BuildCriteria
this way:
BuildCriteria(Mid(.Name, 5), .Tag, .Value)

Check Object Browser (VBA window - View menu), DAO Library,
to get a list of each data type and its code number. If any
of your fields are a numeric type, you must specify the
exact type of number (Long, Double, Currency, etc).
BuildCriteria will pretty much take care of everything,
including Date fields, as long as you tell it the proper
type of the field.

The MSDN example only showed the "Case acTextBox" code (which worked fine in
the northwind db). And it said "The only Control you are using is the text
box. However, you can add as many types of controls as you want." You mention
consolidating all my cases into one. If I do that, and use the "value"
instead of "text", will that take care of the different data types?

All that about the NorthWind example is true, but it is an
extremely limited example that does a very poor job of
demonstrating how to use BuildCriteria.

I tried to explain the data types issue above. The field
data type issue isn't really tied to the use of the Select
Case code structure. The Select Case (or a simple If
statement) is primarily used to distinguish the Controls
that you allow users to specify search criteria from other
controls (e.g. Labels) that are used for other purposes.
This is why I use a specific prefix on the search control's
name:
Select Case Left(.Name, 4)
Case "srch"
. . . BuildCriteria . . .
End Select
or just:
If Left(.Name, 4) = "srch" Then
. . . BuildCriteria . . .
End If

The BuildCritera method looks like the best way to do this, but do you know
of a better way?

BuildCriteria is an extremely powerful function. It's
actually the same function that Access uses to construct
valid SQL Where clauses from what you might enter in the
criteria row of the query design grid.

I don't think there is anything better, but I'm not familiar
with every third party product that might be available
somewhere. If anything, you may find that BuildCriteria is
too powerful in that it allows users to specify some very
tricky search criteria, possibly something they didn't even
intend ;-)

 

[Guest]

Thank you Marsh!
All makes sense now.
And thank you for your patience.

G

 

[Guest]

Thank you!

G

 

[Guest]

Quick question
Marshall,
you said to use the .tag to get the field's data type. I'm using ACCESS 2003
and all that shows is "smart tags". If I use that, it errors out and says is
not supported.
I think I could still use a little more assistance with this
G

 

[Marshall Barton]

Quick question
Marshall,
you said to use the .tag to get the field's data type. I'm using ACCESS 2003
and all that shows is "smart tags". If I use that, it errors out and says is
not supported.

Each FIELD (in the table) that you want to apply criteria to
will have an associated CONTROL on the form. Every control
on a form has a Tag property (Other tab on the property
sheet). Look up the number of the FIELD's type (using the
Object Browser) and type that number into the Control's Tag
property.

After you have placed the correct type number into the Tag
property, your code can retrieve it for use in the
BuildCriteria function as I demonstrated earlier.

 

[Guest]

Hi.
I posted a new question for you, since sometimes this thread is available
and sometimes not.
Could you look at that post and assist me again?
Thank you.
G