Problem with Search Button and SQL Statement

[Brenda]

I have five forms in my database. The Selection Table Form has two buttons
for the user to activate the other four forms. I am able to get the first
button to work, which allows the user open a specific form listed in a combo
box.

The other section contains a Search Button. The user has to select one of
the four forms listed in a combo box. Then they fill in one to three text
boxes and/or one combo box (company, website, city, state) with their search
criteria to bring up their findings. All of the forms have the same name for
these four items.

I am trying to get the findings to appear as a query. When I hit the
search button, I get the following error message: Run-time error '2342', A
RunSQL action requires an argument consisting of an SQL statement. I have
not been able to figure out why my variables values are not being but in my
SQL statement. Please help!!!

Below is my code:

Private Sub cmdSearch_Click()

Dim strCompN As String
Dim strWeb As String
Dim strCity As String
Dim strState As String
Dim strSQL As String
Dim strTable As String

If Me.Sourcetable.Value >= 1 Then

'Filtering each text box for values
If Not IsNull(Me.txtCompName) Then
strCompN = strCompN & "([Company] like ""*" & Me.txtCompName &
"*"")"
End If

If Not IsNull(Me.txtWeb) Then
strWeb = strWeb & "([Web] like ""*" & Me.txtWeb & "*"")"
End If

If Not IsNull(Me.txtCity) Then
strCity = strCity & "([City] like ""*" & Me.txtCity & "*"")"
End If

If Not IsNull(Me.cboState) Then
strState = strState & "([State]= """ & Me.cboState & """) and"
End If

strTable = Me.Sourcetable.Value

strSQL = "SELECT strTable.[Company], strTable.[Web], strTable.[City],
strTable.[State] FROM strTable WHERE strCompN & strWeb & strCity & strState"

DoCmd.RunSQL strSQL

Else
'Check to see if choice is selected
IsNull (Me.Sourcetable) Or Me.Sourcetable = ""
MsgBox "Please select a source form", vbOKOnly, "Required Data"
Me.Sourcetable.SetFocus
End If

End Sub

 

[Ken]

I couldn't quite wrap my brain around your database structure, but I see some
problems with your code. I've done what your doing a couple times and it
took a while to hammer the bugs out.

I don't understand why you are adding your variables back to themselves:
strWeb = strWeb & "([Web] like ""*" & Me.txtWeb & "*"")"
When I've done this I've defined a "condition" variable and a counter
variable so you check to see if a criteria on a form is left blank. Then
the code is structured as follows:

IF Form!form_name!field1 <>""THEN
Condition = condition + "((table.field)='Value')"
counter = counter + 1
END IF
IF Form!form_name!field2 <>"" THEN
IF counter > 0 THEN
conditon = condition + "AND"
End If
condition = condition + ""((table.field)='Value')"
counter = counter +1
END IF
IF Form!form_name!field3 <>"" THEN
IF counter > 0 THEN
conditon = condition + "AND"
End If
condition = condition + ""((table.field)='Value')"
counter = counter +1
END IF

repeat the IFs as needed then when building your sql statement like
SQLStmt = "SELECT table.field, FROM Table Where" & condition & ";"
Then I set a hidden unbound field in my form to the SQLStmt and use that as
the recordsource (called on open) to a report or how ever I want to show the
results.

Note the single quotes in the ""((table.field)='value')"
also very handy is using the Debug.print SQLstmt in the code to check your
statement for errors (cntrl-G in the VBA window)

Kind of Genaric, but hope it helps.

 

[Brenda]

I got some of the code from Allen Browne Search Example, and I am using the
like statements in case the user does not know the entire name or looking for
all occurrences containing a part of a word. What is not working is the
strSQL statement.

The strTable and other string variables are not showing the value from the
search. For example, I chose JobSource as the strTable and Career as the
Company name (strCompN). These values are shown in the debugger for the
values. However, the strSQL statement does not put in the values for
strTable or strCompN. Does anyone know how to correct this problem?

 

[Ken]

if you Debug the entire sql Statment and Post it, it might be easier to see
theproblem.

I got some of the code from Allen Browne Search Example, and I am using the
like statements in case the user does not know the entire name or looking for
all occurrences containing a part of a word. What is not working is the
strSQL statement.

The strTable and other string variables are not showing the value from the
search. For example, I chose JobSource as the strTable and Career as the
Company name (strCompN). These values are shown in the debugger for the
values. However, the strSQL statement does not put in the values for
strTable or strCompN. Does anyone know how to correct this problem?

I couldn't quite wrap my brain around your database structure, but I see some
problems with your code. I've done what your doing a couple times and it
took a while to hammer the bugs out.

I don't understand why you are adding your variables back to themselves:
strWeb = strWeb & "([Web] like ""*" & Me.txtWeb & "*"")"
When I've done this I've defined a "condition" variable and a counter
variable so you check to see if a criteria on a form is left blank. Then
the code is structured as follows:

IF Form!form_name!field1 <>""THEN
Condition = condition + "((table.field)='Value')"
counter = counter + 1
END IF
IF Form!form_name!field2 <>"" THEN
IF counter > 0 THEN
conditon = condition + "AND"
End If
condition = condition + ""((table.field)='Value')"
counter = counter +1
END IF
IF Form!form_name!field3 <>"" THEN
IF counter > 0 THEN
conditon = condition + "AND"
End If
condition = condition + ""((table.field)='Value')"
counter = counter +1
END IF

repeat the IFs as needed then when building your sql statement like
SQLStmt = "SELECT table.field, FROM Table Where" & condition & ";"
Then I set a hidden unbound field in my form to the SQLStmt and use that as
the recordsource (called on open) to a report or how ever I want to show the
results.

Note the single quotes in the ""((table.field)='value')"
also very handy is using the Debug.print SQLstmt in the code to check your
statement for errors (cntrl-G in the VBA window)

Kind of Genaric, but hope it helps.

 

[Brenda]

I know how to set up the watch which shows me the values for my str
variables. The watch the variables corrent for everthing, but the strSQL
statment which is not putting in the value of the variables. For example,
strCompN = Career. strSQL shows strCompN instead of Career. Is there some
symbol I am missing to get the str value to show instead of the name of the
variable?

if you Debug the entire sql Statment and Post it, it might be easier to see
theproblem.

I got some of the code from Allen Browne Search Example, and I am using the
like statements in case the user does not know the entire name or looking for
all occurrences containing a part of a word. What is not working is the
strSQL statement.

The strTable and other string variables are not showing the value from the
search. For example, I chose JobSource as the strTable and Career as the
Company name (strCompN). These values are shown in the debugger for the
values. However, the strSQL statement does not put in the values for
strTable or strCompN. Does anyone know how to correct this problem?

I couldn't quite wrap my brain around your database structure, but I see some
problems with your code. I've done what your doing a couple times and it
took a while to hammer the bugs out.

I don't understand why you are adding your variables back to themselves:
strWeb = strWeb & "([Web] like ""*" & Me.txtWeb & "*"")"
When I've done this I've defined a "condition" variable and a counter
variable so you check to see if a criteria on a form is left blank. Then
the code is structured as follows:

IF Form!form_name!field1 <>""THEN
Condition = condition + "((table.field)='Value')"
counter = counter + 1
END IF
IF Form!form_name!field2 <>"" THEN
IF counter > 0 THEN
conditon = condition + "AND"
End If
condition = condition + ""((table.field)='Value')"
counter = counter +1
END IF
IF Form!form_name!field3 <>"" THEN
IF counter > 0 THEN
conditon = condition + "AND"
End If
condition = condition + ""((table.field)='Value')"
counter = counter +1
END IF

repeat the IFs as needed then when building your sql statement like
SQLStmt = "SELECT table.field, FROM Table Where" & condition & ";"
Then I set a hidden unbound field in my form to the SQLStmt and use that as
the recordsource (called on open) to a report or how ever I want to show the
results.

Note the single quotes in the ""((table.field)='value')"
also very handy is using the Debug.print SQLstmt in the code to check your
statement for errors (cntrl-G in the VBA window)

Kind of Genaric, but hope it helps.