Results 1 to 6 of 6
  1. #1
    Join Date
    May 2007
    Posts
    74

    Unanswered: Hopefully, a simple SQL question

    I am trying to do a global find in a table for any field in the table to match my search text.

    So, if I have a table named "tblStuff", and I have search text = "yadaYadaYada", I would like to get a recordset that contains all the records in tblStuff that have any field that contains "yadaYadaYada".

    I am having a bit of a problem coming up with the SQL statement that would accomplish this. I think that I'll need a "SELECT * FROM tblStuff WHERE ...", but I'm not sure what the WHERE clause would be when the fields for tblStuff are not known.

    I'm not even sure if this is possible. (The way my day has been going, probably not. )

    NOTE: I am trying to replicate the functionality of the "Find First" button from the standard "Find" dialog that Microsoft so wisely removed for Access 2003. I may not need to do the recordset thing, but it seemed to make the most sense. Any other ideas on this would certainly be appreciated!
    AzJazz

    "I'm afraid the holodeck will be society's last invention." - Scott Adams

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "... when the fields for tblStuff are not known."

    there's your problem right there

    how come you don't know them?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89

    Thumbs up

    I presume you're trying to recreate the find dialogue box and navigate the form calling the dialogue box form. Here's one way you might do it:

    1. Create a form, frmFind, with all the controls on the find dialogue box you want to replicate.
    2. Use Docmd.OpenForm "frmFind ", , , , , , Me.Name in the calling form to open frmFind.
    3. Within frmFind code, you can get a RowSource (field list) in the for the "Look In" ComboBox, cmbLookIn by including this in the Form_Open or Form_Load event:

    Dim rstSearch As DAO.RecordSet
    Dim fldSearch As DAO.Field
    Dim strRowSource As String

    Set rstSearch = Forms(Me.OpenArgs).RecordsetClone
    For Each fldSearch In rstSearch.Fields
    strRowSource = strRowSource & fldSearch.Name & ";" & fldSearch.Properties("Caption") & ";"
    Next
    cmbLookIn.RowSource ="-1;All", strRowSource

    You could also loop thru' all of the Forms(Me.OpenArgs) controls, picking the ones that are bound to a field (and get the caption of that field as above) if you've got any fields you don't want to include in the search.

    Dim rstSearch As DAO.RecordSet
    Dim fldSearch As DAO.Field
    Dim frmTarget As Form
    Dim ctlTarget As Form
    Dim strRowSource As String

    Set frmTarget = Forms(Me.OpenArgs)
    Set rstSearch = frmSearch.RecordsetClone
    For Each ctlTarget In frmTarget.Controls
    If ctlTarget.ControlType=acTextBox Or ctlTarget.ControlType=acComboBox Or ctlTarget.ControlType=acCheckBox Then
    If Not ctlTarget.ControlSource="" Then
    Set fldSearch = rstSearch.Fields(ctlTarget.ControlSource)
    strRowSource = strRowSource & fldSearch.Name & ";" & fldSearch.Properties("Caption") & ";"
    End If
    End If
    Next
    If Not strRowSource="" Then
    cmbLookIn.RowSource ="-1;All", strRowSource
    End If

    Make sure that cmbLookIn.ColumnCount=2, cmbLookIn.BoundColumn=1, and cmbLookIn.ColumnWidths=0

    2. In the "Find Next" command button, cmdFindNext, click event you'll need something like this:

    Dim rstSearch As DAO.RecordSet
    Dim fldSearch As DAO.Field
    Dim frmTarget As Form
    Dim strCriteria As String
    Dim lngRow As Long

    Set rstSearch = Forms(Me.OpenArgs).RecordsetClone
    If cmbLookIn.RowSource ="-1" Then
    'Loop though all building criteria for multiple fields
    For lngRow=1 to cmbLookIn.ListCount - 1
    'Use cmbLookIn(0,lngRow) to get field name
    If Not strCriteria = "" Then
    strCriteria = strCriteria & " Or "
    End If
    strCriteria = cmbLookIn(0,lngRow)='" & txtFindWhat.Value "'"
    Next
    Else
    'Build criteria for single field. Use cmbLookIn.Value to build
    strCriteria = cmbLookIn.Value & "='" & txtFindWhat.Value & "'"
    End If
    'Sync recordset cursor with form.
    rstSearch.Bookmark= frmTarget.Bookmark
    rstSearch.FindNext strCriteria
    If rstSearch.NoMatch Then
    MsgBox "Have finished searching the records. Item was not found" Else
    'Sync form with recordset cursor.
    frmTarget.Bookmark = rstSearch.Bookmark
    End

    This example would only work for text matches in one direction. So you'll have to do some more work on creating criteria for different field data types and using different Find arguments if you want to do anything other than set search direction to Down. But it's a start...!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by azjazz
    ...
    So, if I have a table named "tblStuff", and I have search text = "yadaYadaYada", I would like to get a recordset that contains all the records in tblStuff that have any field that contains "yadaYadaYada".

    I am having a bit of a problem coming up with the SQL statement that would accomplish this. I think that I'll need a "SELECT * FROM tblStuff WHERE ...", but I'm not sure what the WHERE clause would be when the fields for tblStuff are not known....
    im not surprised you are having a problem
    if the search text can appear in anyone of a number of fields then it seesm to me that you have a suspect design.

    Generally speaking I find if you are struggling with such concepts the physical design of the db is flawed.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You might try exporting the table to Excel, and doing the search in Excel. You can, perhaps, delete any records in Excel that don't contain the search argument, and re-import the remaining records into Access as a separate table.

    Sam

  6. #6
    Join Date
    May 2007
    Posts
    74
    Sorry I haven't responded back on this thread to your questions, but I've been on vacation for a week ...

    Quote Originally Posted by r937
    "... when the fields for tblStuff are not known."

    there's your problem right there

    how come you don't know them?
    r937:

    This is because I am trying to come up with a generic "Find" dialog that will be usable for searching through any of multiple tables and forms in the database. I won't know which table or form to search until the user presses the "Find" button.

    The real pain here is that Microsoft seems to have permanently removed this basic generic "Find First" functionality from Access, and I have customers that want it back, so I'm trying to implement what Microsoft removed.

    Quote Originally Posted by healdem
    im not surprised you are having a problem
    if the search text can appear in anyone of a number of fields then it seesm to me that you have a suspect design.

    Generally speaking I find if you are struggling with such concepts the physical design of the db is flawed.
    healdem:

    The problem is that I don't know what field or which form they will be searching on. This is replicating the older "Find" dialog that Microsoft had in earlier versions of Access, in which the user would just type text, and Access would find the first/next occurrence of that text, regardless of the field. The user never had to enter anything about the fields in the database when using the standard "Find/Replace" dialog.

    MyNewFlavour:

    What you are proposing looks very interesting! It may lead to the solution I am looking for. I'll experiment.
    AzJazz

    "I'm afraid the holodeck will be society's last invention." - Scott Adams

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •