BuildCriteria errors out

G

G

I have a buildcriteria function on a search button
when I run it, it errors out on the first if-then statement. The error
message says I can't reference a property or method for a control unless the
control has the focus.
Do I have to .setfocus for each control? If so, it will go through all the
controls, causing bad flickering.

G


'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
'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 sql statement to the variable
Me.txtSql.SetFocus
Me.txtSql = sSql & sWhereClause
Me.cmdSearch.SetFocus
 
R

rkc

G said:
I have a buildcriteria function on a search button
when I run it, it errors out on the first if-then statement. The error
message says I can't reference a property or method for a control unless the
control has the focus.
Click to expand...

You can't reference the .Text property of a textbox unless
it has the focus. That's O.K though because what you really
want is the .Value property which can be referenced without
the focus being on the control.
 
G

G

Can you help again? I need to skip it if it is null. And I know that the
db_text does not allow for dates. I absolutely have to get this tonight.

Thanks,
G

Now I have:
'Loop through each control on the form to get its value
For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acTextBox, acComboBox
'This is the function that actually builds 'the clause.
If sWhereClause = " Where " Then
sWhereClause = sWhereClause & BuildCriteria(.Name,
DB_TEXT, .Value)
.SetFocus
Else: sWhereClause = sWhereClause & " and " &
BuildCriteria(.Name, .DB_TEXT, .Value)
End If
End Select
End With
Next ctl
 
R

rkc

G said:
Can you help again? I need to skip it if it is null. And I know that the
db_text does not allow for dates. I absolutely have to get this tonight.

Thanks,
Click to expand...

You had a dot before DB_TEXT in your second BUildCriteria clause that
was causing an error.

You can check for no value in a control that can have a value using
If Len(.value & vbNullString) > 0

So you end up with something like:

For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acTextBox, acComboBox
If Len(.value & vbNullString) > 0 Then
If sWhereClause = " Where " Then
sWhereClause = sWhereClause & BuildCriteria(.Name, _
DB_TEXT, .value)
Else
sWhereClause = sWhereClause & " and " & _
BuildCriteria(.Name, DB_TEXT, .value)
End If
End If
End Select
End With
Next ctl
 
G

G

Thank you! I was checking for null in the wrong spot, so I had taken it out.
I have struggled with this for quite awhile.

G
 
G

G

one more thing...
is it possible to use a "select case..." to only run this with those
controls that have a tab order of 1 to some number? Does that make sense?
The reason I'm asking is that I'm getting a type mismatch error, and I think
it's from where it is trying to use other controls, like the boxes, or labels.
G
 
G

G

I guess I still have something no quite right.
I go through and select one or more options from the text/combo boxes. First
time through, it works. Second time through, it get an error.
"You can use the Is operator only in an expression with Null or Not Null"
"The expression you entered contains invalid syntax, or you need to enclose
your text data in quotes"

And sometimes, just on the first run through, selecting only one item, I get
one of the messages.
G
 
R

rkc

G said:
I guess I still have something no quite right.
I go through and select one or more options from the text/combo boxes. First
time through, it works. Second time through, it get an error.
"You can use the Is operator only in an expression with Null or Not Null"
"The expression you entered contains invalid syntax, or you need to enclose
your text data in quotes"

And sometimes, just on the first run through, selecting only one item, I get
one of the messages.
Click to expand...

There's nothing in that little bit of a for loop that would cause
that error. How are you using the sWhereClause variable after it
is built? Have you tried printing sWhereClause to the immediate
window (debug.print)to see what it looks like?
 
R

rkc

G said:
one more thing...
is it possible to use a "select case..." to only run this with those
controls that have a tab order of 1 to some number? Does that make sense?
The reason I'm asking is that I'm getting a type mismatch error, and I think
it's from where it is trying to use other controls, like the boxes, or labels.
Click to expand...

It's possible to do just about anything, but that is not what is
causing your problem.

Cut and paste the code that is causing the error.
Don't re-type the code into a message.
Cut and paste the actual code you are trying to run.
 
G

G

ok, I added a watch and printed to the debug window.
I watched it grab all the correct values, but then when it gets to the end,
the watch shows
Change: : sWhereClause : <Out of context> : Empty :
Form_frmSearch.cmdSearch_Click

In the immediate window, it shows "Where cboProfileDt=#2/21/2005#"

I went out of the program and back in.
I selected 3 items.
When I selected one of the cbo text fields, I get the error: "You can use
the Is operator only in an expression with Null or Not Null."
Shouldn't it be grabbing all the text in the field? I'm asking because some
of the actual data shows "the customer is ....."
Is this a data issue? Or could the buildcriteria be reading it differently?
Or am I on the wrong track with this?

btw, I sure appreciate you helping me with this. I'm so frustrated right
now, I'd pay someone to fix the form, if it could be fixed by noon. But it's
too late to do that.

Any recommendations?

G

below is my code for the search button

Private Sub cmdSearch_Click()

On Error GoTo Err_cmdSearch_Click

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
Select Case .ControlType
Case acTextBox, acComboBox
Debug.Print sWhereClause
If Len(.Value & vbNullString) > 0 Then
If sWhereClause = " Where " Then
sWhereClause = sWhereClause & BuildCriteria(.Name,
..Tag, .Value)
Else
sWhereClause = sWhereClause & " and " &
BuildCriteria(.Name, .Tag, .Value)
End If
End If
End Select
End With
Next ctl


'set the sql statement to the variable
Me.txtSql.SetFocus
Me.txtSql = sSql & sWhereClause
'Me.cmdSearch.SetFocus

'With Me
'.subfrmDataFiltered.Form.SetFocus
'.subfrmDataFiltered.Form.RecordSource = Me.txtSql
'.subfrmDataFiltered.Requery
'DoCmd.Requery "subfrmDataFiltered"

'End With


Exit_Err_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Me.ActiveControl.SetFocus
Resume Exit_Err_cmdSearch_Click

End Sub
 
D

Dirk Goldgar

G said:
ok, I added a watch and printed to the debug window.
I watched it grab all the correct values, but then when it gets to
the end, the watch shows
Change: : sWhereClause : <Out of context> : Empty :
Form_frmSearch.cmdSearch_Click

In the immediate window, it shows "Where cboProfileDt=#2/21/2005#"

I went out of the program and back in.
I selected 3 items.
When I selected one of the cbo text fields, I get the error: "You can
use the Is operator only in an expression with Null or Not Null."
Shouldn't it be grabbing all the text in the field? I'm asking
because some of the actual data shows "the customer is ....."
Is this a data issue? Or could the buildcriteria be reading it
differently? Or am I on the wrong track with this?

btw, I sure appreciate you helping me with this. I'm so frustrated
right now, I'd pay someone to fix the form, if it could be fixed by
noon. But it's too late to do that.

Any recommendations?

G

below is my code for the search button

Private Sub cmdSearch_Click()

On Error GoTo Err_cmdSearch_Click

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
Select Case .ControlType
Case acTextBox, acComboBox
Debug.Print sWhereClause
If Len(.Value & vbNullString) > 0 Then
If sWhereClause = " Where " Then
sWhereClause = sWhereClause &
BuildCriteria(.Name, .Tag, .Value)
Else
sWhereClause = sWhereClause & " and " &
BuildCriteria(.Name, .Tag, .Value)
End If
End If
End Select
End With
Next ctl


'set the sql statement to the variable
Me.txtSql.SetFocus
Me.txtSql = sSql & sWhereClause
'Me.cmdSearch.SetFocus

'With Me
'.subfrmDataFiltered.Form.SetFocus
'.subfrmDataFiltered.Form.RecordSource = Me.txtSql
'.subfrmDataFiltered.Requery
'DoCmd.Requery "subfrmDataFiltered"

'End With


Exit_Err_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Me.ActiveControl.SetFocus
Resume Exit_Err_cmdSearch_Click

End Sub
Click to expand...

Please pardon me for jumping in. What are the names of (a) all the text
box and combo box controls that are involved here, and (b) all the
fields (in the table) that these control are bound to? Be aware that
the WHERE clause you are building must use the names of the *fields*, if
they are different from those of the controls. Do any of those names
have embedded spaces (as, for example "My Field Name")? That would
cause a problem, but it's one that can easily be solved.

You say you got an error "when I selected one of the cbo text fields".
On what line of code was that error raised? What was the value of
sWhereClause at the time?
 
R

rkc

G said:
btw, I sure appreciate you helping me with this. I'm so frustrated right
now, I'd pay someone to fix the form, if it could be fixed by noon. But it's
too late to do that.

Any recommendations?
Click to expand...

I'm not interested in being paid, but I'll take a look at your
form if you can email me enough of an .mdb to make it work.
Or post it somewhere that it can be downloaded.

Remove yabba.dabba.do and change bomb to com to email.
 
D

Dirk Goldgar

G said:
ok, I added a watch and printed to the debug window.
I watched it grab all the correct values, but then when it gets to
the end, the watch shows
Change: : sWhereClause : <Out of context> : Empty :
Form_frmSearch.cmdSearch_Click

In the immediate window, it shows "Where cboProfileDt=#2/21/2005#"

I went out of the program and back in.
I selected 3 items.
When I selected one of the cbo text fields, I get the error: "You can
use the Is operator only in an expression with Null or Not Null."
Shouldn't it be grabbing all the text in the field? I'm asking
because some of the actual data shows "the customer is ....."
Is this a data issue? Or could the buildcriteria be reading it
differently? Or am I on the wrong track with this?
Click to expand...

Aha! I just figured out what's going on. I've posted a reply to the
new thread you just started, "BuildCriteria Results --- To Marshall
Barton or ?", but to recap here:

You're right -- BuildCriteria is not interpreting your control's value
correctly. BuildCriteria has all kinds of logic in it to figure out
what the user means when she supplies a value to be used in a criterion
expression, and the presence of the keyword "Is" is significant in that
logic. Yes, it depends on what the meaning of "Is" is. <g>

If your criteria for text fields are going to contain the word "is" --
as a separate word, not in the middle of another word -- then I think
you're going to have to use logic like this if you want to use logic
like this:

' modified from the code posted in the other thread,
' where I see you are using the control's .Tag property
' to hold the field-type value:

sWhereClause = sWhereClause & " and " & _
BuildCriteria(.Name, .Tag, _
IIf(.Tag = dbText, _
"""" & Replace(.Value, Chr(34), """""") & """", _
.Value))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

BuildCriteria works in Northwind, but not in my db 8
VBA Code Help 6
Search that creates a dynamic query 1
Unable To Read Value in Masked Textbox 1
BuildCriteria in VBA 2
clearing the values of all the controls in a form 1
Form & subform Validation issue on Send Email button click 0
Run-time error '5' 4

Top