Any column limitation to a DAO.Recordset created with Me.RecordsetClone? (1 Viewer)

[mdlueck]

Is there any sort of limitation in DAO.Recordset objects that would prevent me from searching across multiple columns for a match with code along the lines of this code?

  Dim strFindInCol
  Dim strWhereClause
  Dim daoRS As DAO.Recordset

  'Find out which column is currently being sorted by
  If InStr(1, strCurrentSort, Chr(32), vbTextCompare) = 0 Then
    strFindInCol = strCurrentSort
  Else
    strFindInCol = Mid(strCurrentSort, 1, InStr(1, strCurrentSort, Chr(32), vbTextCompare) - 1)
  End If

  'Determine if the col is one we support string matches for
  'If not, then exit
  If Not (strFindInCol = "partnumber") And _
     Not (strFindInCol = "title") Then
     'Blank the search field
     Me.fldFind.Value = vbNullString
     'Debug.Print "Wrong Col: " & strFindInCol
     GoTo Exit_FindRecord
  End If

  'Atttach to the Form's record set
  Set daoRS = Me.RecordsetClone
  [COLOR=Blue][B]strWhereClause = "[" & strFindInCol & "] LIKE " & Chr(34) & "*" & strFindText & "*" & Chr(34)[/B][/COLOR]
  If flgFindNext = True Then
    daoRS.FindNext (strWhereClause)
  Else
    daoRS.FindFirst (strWhereClause)
  End If
  'If we could find no match
  If daoRS.NoMatch Then
    Me.fldFind.BackColor = vbRed
    'Debug.Print "NoMatch"
  'Else jump to it
  Else
    Me.Bookmark = daoRS.Bookmark
    With Me.fldFind
      .BackColor = vbWhite
      .SetFocus
      .SelStart = Len(strFindText)
    End With
    'Debug.Print "Match"
  End If

  'Clean up the connection to the database
  Set daoRS = Nothing

On SQL Server I have used this capability thusly:

-- This variable must be +2 to what ever the input arg length is
DECLARE @qrysearchstring varchar(52)
SET @qrysearchstring = '%' + @searchstring + '%' 

SELECT [aoes].[id],
       [aoes].[stationname],
       [aoes].[mnfm],
       [aoes].[loto]
FROM [dbo].[aoestation] AS [aoes]
[B][COLOR=Blue]WHERE [aoes].[stationname] LIKE @qrysearchstring
OR [aoes].[mnfm] LIKE @qrysearchstring
OR [aoes].[loto] LIKE @qrysearchstring;[/COLOR][/B]

Should I have success with the same type of SQL query WHERE clause using a DAO.Recordset created with Me.RecordsetClone?

Would the same UI be possible that it would jump to the next find hit, no matter which of the columns the hit was found in?

 

[Galaxiom]

Is this a theoretical question or are you actually having problems?

If the issue is not finding the expected record maybe it is something to do with the fact that a form's recordset is not fully loaded before it displays.

What happens if you do a MoveLast to make sure the recordset is fully loaded before trying the FindFirst?

 

[mdlueck]

I was more testing the waters of what I am thinking to do. I coded it up today. It appears to work the way I think it should.

The DAO.Recordsource is just an odd beast in that it accepts a SQL fragment, not the complete SQL. So I was wondering perhaps in the neutering process, MS disabled abilities to specify multiple different columns, or other such nonsense.

And another unique twist with this re-use of an existing form... the way the form is being used is via the traditional DoCmd syntax. This additional way I need to open the form is opening it via its class so I can have the WithEvents active! So when the form opens WithEvents I unhide the buttons coded in the WithEvents style code, and when the form opens via DoCmd then it takes the default logic path and the buttons call DoCmd style calls.

Initial testing looks promising... "The suspense is terrible... I hope it'll last."

 

[Galaxiom]

IThe DAO.Recordsource is just an odd beast in that it accepts a SQL fragment, not the complete SQL. So I was wondering perhaps in the neutering process, MS disabled abilities to specify multiple different columns, or other such nonsense.

Could you explain what you mean by this.

 

[mdlueck]

Could you explain what you mean by this.

rrrr????

The DAO.Recordsource is just an odd beast in that it accepts a SQL fragment, not the complete SQL.

This is not a complete SQL, it is only the WHERE clause of the SQL:

  'Atttach to the Form's record set
  Set daoRS = Me.RecordsetClone
  [B][COLOR=Blue]strWhereClause = "[" & strFindInCol & "] LIKE " & Chr(34) & "*" & strFindText & "*" & Chr(34)[/COLOR][/B]
  If flgFindNext = True Then
    daoRS.FindNext ([B][COLOR=Blue]strWhereClause[/COLOR][/B])
  Else
    daoRS.FindFirst ([COLOR=Blue][B]strWhereClause[/B][/COLOR])
  End If

So I was suspecting M$ could have placed other limitations on the capability, such as being limited to only specifying one column's criteria. That is all.

 

[Galaxiom]

It was your terminology Michael.

RecordSource is a property of the form. It certainly does accept a full SQL sentence. Try providing it a fragment and see how you go.

What you are applying is a filter. It accepts the same format as a Where clause in SQL. This is exactly as the Filter property is applied against the recordset of a form. Hardly surprising since it is a Filter not a RecordSource.

Same with a directly specified recordset. It has to be a whole clause and anything else is really just a filter.

The engine provides the recordset and DAO filters it. DAO doesn't have the ability to apply a full SQL sentence to a recordset. Nor does the engine for that matter.

I don't see anything odd at all.

 

[Galaxiom]

There is one odd limitation worth mentioning as we speak of this aspect of recordsets.


An ADO recordset filter or find cannot handle criteria with mixed comparisons. For example you cannot have an equals on one field and a greater than on another applied together.


That does seem an artificial limitation. I would be curious if anyone can say why it is this way.

 

[mdlueck]

99% of my use of SQL in Access is fully spelled out SQL statements.

This SQL Filter as you call it is in the 1% of my development work. Thus my lack of familiarity with the subtle ensconces. As you stated, there is one with ADO objects.