Subforms and Search Function

Jae8201 · December 11th, 2005, 06:41 AM

Hi,

There are two sections to the form that I am creating. In the top section, I have the fields that I want the users to be able to update. In the bottom section (subform), I have all the records listed in the datasheet view. These records are locked so that users can't manipulate them at all, only view them.

1.) Since the record source in both the main form and subform are the same, I know I can link them using any of the fields. If a user accesses one of the records in the main form, is there a way to show that record and all the records before and after in the subform Datasheet view in the bottom half of my form?

2.) I want to create a search function for my users because there will be approximately 40,000 records in this database. I have figured out how to create a query that takes input from the user and produces the results. How can I take that query and apply it to the record source for my form so that when I search for *bank*, my form will populate with all records that contain the word bank in them?

If anyone has insight on these two problems, I would greatly appreciate it. Please let me know if anything is unclear and I can clarify. Thanks for all the help.

Bob Bedell · December 15th, 2005, 01:16 AM

I'll have a wack at part two. Use DAO to delete the form's current query recordsource, write some SQL in code incorporating your new search parameters, use the SQL to create a new query def, assign the new query def as your form's recordsource:

Code:

Private Sub cmdSearch_Click()
    Dim dbs As Database, qdf As QueryDef
    Dim strSQL As String
    Dim strSearchCriteria As String

    strSearchCriteria = Me.txtSearchCriteria

    If Not IsNull(strSearchCriteria) Then

        Set dbs = CurrentDb

        dbs.QueryDefs.Refresh

        ' If qryData (the form's recordsource) exists, delete it.
        For Each qdf In dbs.QueryDefs
            If qdf.Name = "qryData" Then
                dbs.QueryDefs.Delete qdf.Name
            End If
        Next qdf

        ' Create SQL string based on new search parameters.
        strSQL = "SELECT * FROM tblData WHERE [Field1] LIKE '*" & strSearchCriteria & "*'"

        ' Create new QueryDef object.
        Set qdf = dbs.CreateQueryDef("qryData", strSQL)

        ' Assign new query def object to form's recordsource property
        Me.RecordSource = "qryData"

        Set dbs = Nothing

    End If

End Sub

I always do this sort of thing with list box controls, though, populated from arrays of searched and sorted ADO recordset data. The following, while only marginally functional, gives an idea of what I'm talking about.

Code:

Private Sub btnSearch_Click()

    Dim strCriteria As String
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim flds As ADODB.Fields
    Dim strSQL As String
    Dim avarFields() As Variant
    Dim intIndex As Integer
    Dim intPosition As Integer

    strCriteria = Me.txtQuery

    If Not IsNull(strCriteria) Then 'Search criteria entered in textbox

        ' Open connection
        Set cnn = CurrentProject.Connection

        ' Open strings table
        Set rst = New ADODB.Recordset
        strSQL = "tblStrings"
        rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly, adCmdTable

        intIndex = 0

        Do Until rst.EOF

            ' Enumerate fields collection of each record
            For Each fld In rst.Fields

                ' See if search string is present in field
                position = InStr(1, fld.Value, strCriteria)
                If position <> 0 Then

                    ' If search string is present in field, store in array
                    ReDim Preserve avarFields(intIndex)
                    avarFields(intIndex) = fld.Value
                    intIndex = intIndex + 1
                End If
            Next fld
            rst.MoveNext
        Loop
    End If


    'Sort the qualifying fields values in the array
    Call QuickSort(avarFields, LBound(avarFields), UBound(avarFields))


    'Populate a list box in sorted order
    'Set list box row source type to "Value List"
    Me.lstResults.RowSource = ""

    For intFirstCount = 0 To UBound(avarFields)
        Me.lstResults.AddItem avarFields(intFirstCount)
    Next

End Sub

Public Sub QuickSort(ByRef varArray As Variant, intBottom As Integer, intTop As Integer)

  Dim strPivot As String, strTemp As String
  Dim intBottomTemp As Integer, intTopTemp As Integer

  intBottomTemp = intBottom
  intTopTemp = intTop

  strPivot = varArray((intBottom + intTop) \ 2)

  While (intBottomTemp <= intTopTemp)

    While (varArray(intBottomTemp) < strPivot And intBottomTemp < intTop)
      intBottomTemp = intBottomTemp + 1
    Wend

    While (strPivot < varArray(intTopTemp) And intTopTemp > intBottom)
      intTopTemp = intTopTemp - 1
    Wend

    If intBottomTemp < intTopTemp Then
      strTemp = varArray(intBottomTemp)
      varArray(intBottomTemp) = varArray(intTopTemp)
      varArray(intTopTemp) = strTemp
    End If

    If intBottomTemp <= intTopTemp Then
      intBottomTemp = intBottomTemp + 1
      intTopTemp = intTopTemp - 1
    End If

  Wend

  If (intBottom < intTopTemp) Then QuickSort varArray, intBottom, intTopTemp
  If (intBottomTemp < intTop) Then QuickSort varArray, intBottomTemp, intTop

End Sub

HTH,

Bob

ahying · June 9th, 2006, 03:05 AM

Dear Bob,

I had copied your ADO code above, it shows an unexpected error on lstResults.
Can you please verify it and post a reply.
Thank you

ahying

wscheiman · June 22nd, 2006, 10:40 AM

How 'bout if I try a response for part 2 as well? :D

Looks like you basically need to filter the data on the form
based on a value specified by the end user. Obviously, in order
to show the selected records, I'd use a combo box control, maybe
in the header section of the form. My example shows the selection
for a single field/column search. There's actually a way to allow
a user to pick their own column name and then search on it, too.

But, for now, here's what I would do:

#1 Create an unbound combo box. For this example, I'll call it cboSearch.

#2 Set the rowsource for the combo box. Something like: SELECT
   column_name FROM table_name ORDER BY column_name.

#3 In the cboSearch_AfterUpdate(), you can include the following code:

Private Sub cboSearch_AfterUpdate()

   Dim strSQL As String

   ' If the user somehow picked a blank/null value, exit the process
   If IsNull(Me.cboSearch) Then
      Exit Sub
   End If

   ' Build a SQL string using the selected value
   strSQL = "SELECT * FROM table_name WHERE column_name = '" & Me.cboSearch & "'"

   ' Reset the recordsource for the form
   Me.RecordSource = strSQL
   Me.Requery

End Sub

If you want to have a button to reset the filter, here's the
code:

Private Sub cmdFilterReset_Click()

   Dim strSQL As String

   strSQL = "SELECT * FROM table_name"

   Me.RecordSource = strSQL
   Me.Requery

End Sub

Of course, I kind of used "table_name" and "column_name"
because I don't know the actual names of either.

I've used this in TONS of applications without a problem.

Hope that helps!

Warren

wscheiman · June 22nd, 2006, 10:46 AM

Forgot something - sorry.

In order to keep from getting duplicate values in your
combo box (which I notated in step #2), use the DISTINCT
clause as part of the SELECT statement.

Thanks.