Search form problem

[carriey]

Nikos made a few suggestions in a previous post (Search Form with list box
doesn't search) and suggested I post back if they didn't work. I have a
form/subform that I need the user to click on Find and have a Search Form
open up. Copied the code from a free download as this is way over my head
but am having trouble with it. Here is the code:

Option Compare Database
Private Sub List_Results_DblClick(Cancel As Integer)
DoCmd.OpenForm "Search_Form", , , "[ID] = & Me.List_Results, , acDialog"
End Sub

Private Sub Search_Records_Click()
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strSQL = "SELECT Compl_MAIN_Table.Compliance_ID, Compl_MAIN_Table.Status,
Compl_DETAIL_Table.District_Location,
Compl_DETAIL_Table.Subdistrict_Location, Compl_DETAIL_Table.Area_Location,
Compl_DETAIL_Table.Field_Location, Compl_DETAIL_Table.DLS_LSD,
Compl_DETAIL_Table.DLS_SEC, Compl_DETAIL_Table.DLS_TWP,
Compl_DETAIL_Table.DLS_RGE, Compl_DETAIL_Table.DLS_MER,
Compl_DETAIL_Table.NTS_QUnit, Compl_DETAIL_Table.NTS_Except,
Compl_DETAIL_Table.NTS_Unit, Compl_DETAIL_Table.NTS_4Block,
Compl_DETAIL_Table.NTS_Map, Compl_DETAIL_Table.NTS_6Block,
Compl_DETAIL_Table.NTS_7Block" & _
"FROM Compl_MAIN_Table INNER JOIN Compl_DETAIL_Table ON
Compl_MAIN_Table.Compliance_ID = Compl_DETAIL_Table.Compliance_ID"

strWhere = "WHERE"

strOrder = "ORDER BY Compl_DETAIL_Table.District_Location;"

If Not IsNull(Me.Compl_ID) Then
strWhere = strWhere & " (Compl_MAIN_Table.Compliance_ID) Like '*" &
Me.Compl_ID & "*' AND"
End If

If Not IsNull(Me.txtStatus) Then
strWhere = strWhere & " (Compl_MAIN_Table.Status) Like '*" & Me.txtStatus
& "*' AND"
End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 18)
Debug.Print strSQL
Debug.Print strWhere
Debug.Print strOrder
Me.List_Results.RowSource = strSQL & " " & strWhere & " " & strOrder
End Sub

There are 18 If Not IsNull Statements which is why I put the -18 on the
strWhere. This seems to be one of the lines I'm having trouble with. Nikos
explained that this is the number of characters to cut off. Wouldn't it be
18?

Added in the Debug.Print lines but am not sure where I am supposed to see
the values assigned to them - I don't think I'm seeing anything. When I type
values in any of the search fields on the form, nothing happens at all. (Not
sure if it matters but some of the fields are combo boxes on the form where
the data is entered but I left them as text fields on the Search form because
I couldn't figure out how to get the dropdown lists to show up).

Any advice is appreciated - thanks in advance.

 

[SteveS]

Nikos made a few suggestions in a previous post (Search Form with list box
doesn't search) and suggested I post back if they didn't work. I have a
form/subform that I need the user to click on Find and have a Search Form
open up. Copied the code from a free download as this is way over my head
but am having trouble with it. Here is the code:

Option Compare Database
Private Sub List_Results_DblClick(Cancel As Integer)
DoCmd.OpenForm "Search_Form", , , "[ID] = & Me.List_Results, , acDialog"
End Sub

Private Sub Search_Records_Click()
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

*****SNIP ********
*****SNIP ********

If Not IsNull(Me.txtStatus) Then
strWhere = strWhere & " (Compl_MAIN_Table.Status) Like '*" & Me.txtStatus
& "*' AND"
End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 18)
Debug.Print strSQL
Debug.Print strWhere
Debug.Print strOrder
Me.List_Results.RowSource = strSQL & " " & strWhere & " " & strOrder
End Sub

There are 18 If Not IsNull Statements which is why I put the -18 on the
strWhere. This seems to be one of the lines I'm having trouble with. Nikos
explained that this is the number of characters to cut off. Wouldn't it be
18?

Added in the Debug.Print lines but am not sure where I am supposed to see
the values assigned to them - I don't think I'm seeing anything. When I type
values in any of the search fields on the form, nothing happens at all. (Not
sure if it matters but some of the fields are combo boxes on the form where
the data is entered but I left them as text fields on the Search form because
I couldn't figure out how to get the dropdown lists to show up).

Any advice is appreciated - thanks in advance.

Each of the 18 "If Not IsNull Statements " end with "(space)AND". By the
time you get thru all of the IF()'s, there will always be an "AND" that needs
to be removed - 4 characters, not 18.

Instead ov the Mid() function, I use Left():

strWhere = Left(strWhere, Len(strWhere) - 4)


To see the results of the Debug.Print statements, press 'Ctrl+G' (control
G). This opens the IDE and the immediate window. To delete everything in the
Immediate Window, click on a line in the immediate window, then press
'Ctrl+A', then the Delete button.

When you run (or single step thru) the code, you will be able to see the
values of the variables in the immediate window, in this case the values from
strSQL, strWhere and strOrder.

HTH