Search Form Using Three Criteria

  • Thread starter Thread starter Londa Sue
  • Start date Start date
L

Londa Sue

Hello,

I'd like to develop a search form that will allow users to search using
three criteria. The criteria are via combo boxes:

CSISystem
DocumentType
Status

I have a command button named "Retrieve Records" which should find all
records in tbl_record or qyr_record that match the selected criteria. These
values will populate a list box named "SrchList" so that users can
double-click on the desired record. What I've tried has not worked, so any
help is appreciated.

Thank you,
 
Ken,

You're marvelous. I'm almost there.

The application has a search form (frm_search) that is built on a secondary
query of qry_record2. On this page in the header, there are three combo
boxes from which users will be able to select for searching. They are (1)
CSISystem; (2) DocumentType, and (3) Status. The values that will be
filtered are in a list box (SrchList). Everything is working except the
filter on the list box, and I'm sure that's because I'm not a programmer so
all the language isn't clear to me right off. In any case, I'm having some
trouble with the following. What am I supposed to put in this section?

Private Sub cmdSearch_Click()
Dim strSQL As String

On Error Resume Next

DoCmd.Hourglass True

' move focus to clear button
Me.cmdClear.SetFocus
' build sql string for form's RecordSource
strSQL = BuildWhereString
strSQL = "SELECT * FROM " & strMainformRecordSource & _
IIf(strSQL = "", "", " WHERE ") & strSQL & ";"

Me.RecordSource = ""
Me.RecordSource = strSQL

Call SetVisibility(True)

DoCmd.Hourglass False


End Sub

-------
Here's what I put in this section that is not working:

Private Sub cmd_search_Click()
Dim strSQL As String

On Error Resume Next

DoCmd.Hourglass True

' move focus to clear button
Me.cmd_clear.SetFocus
' build sql string for form's RecordSource
strSQL = BuildWhereString
strSQL = "SELECT * FROM " & SrchList & _
IIf(strSQL = "", "", " WHERE ") & strSQL & ";"

Me.SrchList = ""
Me.SrchList = strSQL

Call SetVisibility(True)

DoCmd.Hourglass False


End Sub
 
Okay. That does the job. This help on this site is a wonder.

I've another issue now. When other data related to record is added (such as
"Author"), that data is showing up in the list box, although the list box is
based on qry_record2 which is only ID, DocumentID, DocumentTitle,
DocumentType, ReleaseDate, Revision, System, and Status. Should I do a
SELECT FROM to keep this list to just those I want? I've never had this
happen with list boxes. I'll check the rest of the code and make sure I
haven't mixed my queries. (I think for efficiency I should have put the
search features on the main form but that has filter buttons of another sort
and there are plenty of those.)

Thank you so very much,

Londa Sue
 
It'll be easier for us to troubleshoot if you can show actual example of
what you mean by "showing up". Also, not knowing the SQL statement of
qry_record2, and not knowing the other properties of the listbox, it's hard
to give any suggestions yet.
 
I changed the source to qry_record since it was the same data. I currently
only want to display seven fields (the primary key is hidden--set at width
0"). When the Search button is clicked, the records are filtered
appropriately, but I get some extra fields that I do not want to display
(such as author, owner). The other fields are shifted two to the right.
Here is the code for the form:

Option Compare Database
Option Explicit


Private Const strContainWildCard As String = "*'"
Private Const strEqual_1 As String = "= '"
Private Const strEqual_2 As String = "'"
Private Const strMainformRecordSource As String = "qry_record"
Private Const strNoStartWildCard As String = "Like '"
Private Const strStartWildCard As String = "Like '*"

Private Function BuildWhereString() As String
Dim strWhere As String
Dim varItemSel As Variant

On Error Resume Next

strWhere = ""

' ... build "CSI System" criterion expression
If Len(Me.CSISystem.Value & "") > 0 Then _
strWhere = strWhere & "CSISystem='" & Me.CSISystem.Value & "' And "

' ... build "Document Type" criterion expression
If Len(Me.DocumentType.Value & "") > 0 Then _
strWhere = strWhere & "DocumentType='" & Me.DocumentType.Value & "' And "

' ... build "Status" criterion expression
If Len(Me.Status.Value & "") > 0 Then _
strWhere = strWhere & "Status='" & Me.Status.Value & "' And "

' Strip off the trailing " And " text string
If Len(strWhere) > 0 Then strWhere = Left(strWhere, Len(strWhere) - _
Len(" And "))


BuildWhereString = strWhere
Exit Function
End Function

Private Sub SrchList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![SrchList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Private Sub cmd_clear_Click()
Dim lngNumber As Long

On Error Resume Next

DoCmd.Hourglass True
Application.Echo False

Me.SrchList.SetFocus

Call SetVisibility(False)
Call SetDefaultFilters
Me.SrchList = ""

Application.Echo True
DoCmd.Hourglass False

End Sub

Private Sub cmd_close_Click()
On Error GoTo Err_cmd_close_Click


DoCmd.Close

Exit_cmd_close_Click:
Exit Sub

Err_cmd_close_Click:
MsgBox Err.Description
Resume Exit_cmd_close_Click

End Sub
Private Sub cmd_search_Click()
Dim strSQL As String

On Error Resume Next

DoCmd.Hourglass True

' move focus to clear button
Me.cmd_clear.SetFocus
' build sql string for form's RecordSource
strSQL = BuildWhereString
strSQL = "SELECT * FROM " & strMainformRecordSource & _
IIf(strSQL = "", "", " WHERE ") & strSQL & ";"

Me.SrchList.RowSource = ""
Me.SrchList.RowSource = strSQL

Call SetVisibility(True)

DoCmd.Hourglass False


End Sub

Private Sub SetVisibility(ByVal blnMakeVisible As Boolean)
Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Tag = "A" Then ctl.Visible = blnMakeVisible
Next ctl

End Sub
Private Sub SetDefaultFilters()
Dim varSel As Variant

On Error Resume Next

Me.CSISystem.Value = Null
Me.DocumentType.Value = Null
Me.Status.Value = Null

Exit Sub
End Sub
Private Function HowManySelectedInListBox(SrchList As ListBox) As Long
' *** THIS FUNCTION RETURNS THE NUMBER OF ITEMS SELECTED IN A LISTBOX.

Dim xlngSelected As Long
Dim xvarSelected As Variant

On Error Resume Next

xlngSelected = 0

For Each xvarSelected In SrchList.ItemsSelected
xlngSelected = xlngSelected + 1
Next xvarSelected

HowManySelectedInListBox = xlngSelected
Err.Clear
End Function

Private Sub SrchList_DblClick(Cancel As Integer)
DoCmd.OpenForm "frm_record", , , "[ID] = " & Me.SrchList
End Sub
 
I apologize that I didn't reply sooner.. somehow, I missed your reply in the
thread.

Are you saying that you see extra fields in the form's data set? Or in a
listbox's data set?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Londa Sue said:
I changed the source to qry_record since it was the same data. I currently
only want to display seven fields (the primary key is hidden--set at width
0"). When the Search button is clicked, the records are filtered
appropriately, but I get some extra fields that I do not want to display
(such as author, owner). The other fields are shifted two to the right.
Here is the code for the form:

Option Compare Database
Option Explicit


Private Const strContainWildCard As String = "*'"
Private Const strEqual_1 As String = "= '"
Private Const strEqual_2 As String = "'"
Private Const strMainformRecordSource As String = "qry_record"
Private Const strNoStartWildCard As String = "Like '"
Private Const strStartWildCard As String = "Like '*"

Private Function BuildWhereString() As String
Dim strWhere As String
Dim varItemSel As Variant

On Error Resume Next

strWhere = ""

' ... build "CSI System" criterion expression
If Len(Me.CSISystem.Value & "") > 0 Then _
strWhere = strWhere & "CSISystem='" & Me.CSISystem.Value & "' And "

' ... build "Document Type" criterion expression
If Len(Me.DocumentType.Value & "") > 0 Then _
strWhere = strWhere & "DocumentType='" & Me.DocumentType.Value & "' And
"

' ... build "Status" criterion expression
If Len(Me.Status.Value & "") > 0 Then _
strWhere = strWhere & "Status='" & Me.Status.Value & "' And "

' Strip off the trailing " And " text string
If Len(strWhere) > 0 Then strWhere = Left(strWhere, Len(strWhere) - _
Len(" And "))


BuildWhereString = strWhere
Exit Function
End Function

Private Sub SrchList_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![SrchList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Private Sub cmd_clear_Click()
Dim lngNumber As Long

On Error Resume Next

DoCmd.Hourglass True
Application.Echo False

Me.SrchList.SetFocus

Call SetVisibility(False)
Call SetDefaultFilters
Me.SrchList = ""

Application.Echo True
DoCmd.Hourglass False

End Sub

Private Sub cmd_close_Click()
On Error GoTo Err_cmd_close_Click


DoCmd.Close

Exit_cmd_close_Click:
Exit Sub

Err_cmd_close_Click:
MsgBox Err.Description
Resume Exit_cmd_close_Click

End Sub
Private Sub cmd_search_Click()
Dim strSQL As String

On Error Resume Next

DoCmd.Hourglass True

' move focus to clear button
Me.cmd_clear.SetFocus
' build sql string for form's RecordSource
strSQL = BuildWhereString
strSQL = "SELECT * FROM " & strMainformRecordSource & _
IIf(strSQL = "", "", " WHERE ") & strSQL & ";"

Me.SrchList.RowSource = ""
Me.SrchList.RowSource = strSQL

Call SetVisibility(True)

DoCmd.Hourglass False


End Sub

Private Sub SetVisibility(ByVal blnMakeVisible As Boolean)
Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Tag = "A" Then ctl.Visible = blnMakeVisible
Next ctl

End Sub
Private Sub SetDefaultFilters()
Dim varSel As Variant

On Error Resume Next

Me.CSISystem.Value = Null
Me.DocumentType.Value = Null
Me.Status.Value = Null

Exit Sub
End Sub
Private Function HowManySelectedInListBox(SrchList As ListBox) As Long
' *** THIS FUNCTION RETURNS THE NUMBER OF ITEMS SELECTED IN A LISTBOX.

Dim xlngSelected As Long
Dim xvarSelected As Variant

On Error Resume Next

xlngSelected = 0

For Each xvarSelected In SrchList.ItemsSelected
xlngSelected = xlngSelected + 1
Next xvarSelected

HowManySelectedInListBox = xlngSelected
Err.Clear
End Function

Private Sub SrchList_DblClick(Cancel As Integer)
DoCmd.OpenForm "frm_record", , , "[ID] = " & Me.SrchList
End Sub


Ken Snell MVP said:
It'll be easier for us to troubleshoot if you can show actual example of
what you mean by "showing up". Also, not knowing the SQL statement of
qry_record2, and not knowing the other properties of the listbox, it's
hard
to give any suggestions yet.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Click to expand...
Click to expand...
 
Back
Top