Updating Forms Recordset (1 Viewer)

jeran042

Registered User.
Local time
Today, 04:10
Joined
Jun 26, 2017
Messages
127
I have a continuous form that is a search form. A user can input any criteria (text before this question) and the record set of the form would be updated with anything LIKE what was typed into the search box. This all works fine.

What I am now trying to accomplish is to add Integers to the search. For example, if the user types "15.46" into the search box, I want to look through the record set for that exact string.

Here is what I currently have for code:

Code:
Private Sub cmdSEARCH_Click()

Dim strsearch As String
Dim strText As String

'Error handling
    On Error GoTo Error_Handler

'Pull value from text box
strText = Trim(Me.txtSearch.value)

'SQL Statement
strsearch = "Select * from qryLedger_Detail_SEARCH WHERE DESCRIPTION like ""*" _
            & strText & "*"" or VOUCHER like ""*" _
            & strText & "*"" or POLICY like ""*" _
            & strText & "*"" or ACCOUNT_NUMBER like ""*" _
            & strText & "*"" or NOTES like ""*" _
            & strText & "*"" or DEBIT = " _ 'newly added
            & strText * " OR CREDIT =" _ 'newly added
            & strText * " ORDER BY [MO_DAY] DESC" 

'Update the recordsource with SQL statement
    Me.RecordSource = strsearch

'Reload form with blank textbox
    Me.txtSearch = Null

'Set focus of textbox after search
    txtSearch.SetFocus


'Error handling
Error_Handler_Exit:
    Exit Sub
    
Error_Handler:
    Select Case Err.NUMBER
        Case 94
            Err.Clear
            Resume Error_Handler_Exit
        Case Else
            MsgBox "Error No. " & Err.NUMBER & vbCrLf & "Description: " & Err.Description, vbExclamation, "Database Error"
            Err.Clear
            Resume Error_Handler_Exit
    End Select


End Sub

I currently get a Runtime error 13, which I would expect, as this was originally only meant for text, any help updating the "strsearch" would be appreciated,
 

MarkK

bit cruncher
Local time
Today, 04:10
Joined
Mar 17, 2004
Messages
8,178
What line causes the error?
 

jeran042

Registered User.
Local time
Today, 04:10
Joined
Jun 26, 2017
Messages
127
Code:
strsearch = "Select * from qryLedger_Detail_SEARCH WHERE DESCRIPTION like ""*" _
            & strText & "*"" or VOUCHER like ""*" _
            & strText & "*"" or POLICY like ""*" _
            & strText & "*"" or ACCOUNT_NUMBER like ""*" _
            & strText & "*"" or NOTES like ""*" _
            & strText & "*"" or DEBIT = " _
            & strText * " OR CREDIT =" _
            & strText * " ORDER BY [MO_DAY] DESC"
 

Cronk

Registered User.
Local time
Today, 22:10
Joined
Jul 4, 2013
Messages
2,770
Code:
strsearch = "Select * from qryLedger_Detail_SEARCH WHERE DESCRIPTION like ""*" _ 

          & strText & "*"" or VOUCHER like ""*" _
            & strText & "*"" or POLICY like ""*" _    

           & strText & "*"" or ACCOUNT_NUMBER like ""*" _
             & strText & "*"" or NOTES like ""*" _
            & strText & "*"" or DEBIT = " _ 

            & strText [COLOR=Red]*[/COLOR] " OR CREDIT =" _ 

            & strText [B][COLOR=red]*[/COLOR][/B] " ORDER BY [MO_DAY] DESC
Notice the discrepancy on the last 2 lines with what comes after strText?
 

Mark_

Longboard on the internet
Local time
Today, 04:10
Joined
Sep 12, 2017
Messages
2,111
Before anything else, I would use either a message box or debug.print on your search string to show EXACTLY what your SQL is. I think you are not sending it what you THINK you are sending it...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:10
Joined
Feb 28, 2001
Messages
26,996
The type mismatch is caused by the asterisks noted by Cronk, because there is no possible operation of multiplying strings. So Access / SQL gripes about doing math on text strings. That's the mixed mode.

I know this one intimately because "&" and "*" are next to each other on the keyboard and thus it is one of my FAVORITE typos! ;)
 

MarkK

bit cruncher
Local time
Today, 04:10
Joined
Mar 17, 2004
Messages
8,178
I would approach this problem using a parameterized querydef. That way the query text is constant, and the query is only ever parsed and compiled once, which is considerably faster.
Code:
Private Const SQL As String = _
    "SELECT t.* " & _
    "FROM tTestData As t " & _
    "WHERE t.Name Like [COLOR="darkred"]p0[/COLOR] " & _
        "OR t.Value Like [COLOR="darkred"]p0[/COLOR] " & _
        "OR t.Sort = [COLOR="Blue"]p1[/COLOR] " & _
        "OR t.Dollars = [COLOR="Blue"]p1[/COLOR];"

Private m_qdf As DAO.QueryDef

Property Get SearchQuery() As DAO.QueryDef
    If m_qdf Is Nothing Then Set m_qdf = CurrentDb.CreateQueryDef("", SQL)
    Set SearchQuery = m_qdf
End Property

Private Sub tbSearch_Change()
    With Me.SearchQuery
        If IsNumeric(Me.tbSearch.Text) Then
[COLOR="Green"]            ' here we search for numeric values[/COLOR]
            .Parameters("[COLOR="DarkRed"]p0[/COLOR]") = Null
            .Parameters("[COLOR="Blue"]p1[/COLOR]") = Me.tbSearch.Text
        Else
[COLOR="green"]            ' here we search for text, noting the addition of wildcards...[/COLOR]
            .Parameters("[COLOR="darkred"]p0[/COLOR]") = "*" & Me.tbSearch.Text & "*"
            .Parameters("[COLOR="Blue"]p1[/COLOR]") = Null
        End If
        
        Set [COLOR="Blue"]Me.lstFound.Recordset[/COLOR] = .OpenRecordset
    End With
End Sub
Also, this example sets a ListBox's Recordset property directly, but you could also set the Recordset of a form. Note also in this code we handle every keystroke, and open a new recordset for every character that changes in the tbSearch textbox, so you don't have to enter text and then hit a button.

Hope this gives some ideas about additional ways to solve this problem, and solve it robustly,
Mark
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:10
Joined
May 7, 2009
Messages
19,169
there goes again, the all in one solution.
 

MarkK

bit cruncher
Local time
Today, 04:10
Joined
Mar 17, 2004
Messages
8,178
I encourage you to point out my mistakes arnel. I am eager to learn, just like you. Have a nice day,
Mark
 

jeran042

Registered User.
Local time
Today, 04:10
Joined
Jun 26, 2017
Messages
127
I would approach this problem using a parameterized querydef. That way the query text is constant, and the query is only ever parsed and compiled once, which is considerably faster.
Code:
Private Const SQL As String = _
    "SELECT t.* " & _
    "FROM tTestData As t " & _
    "WHERE t.Name Like [COLOR="darkred"]p0[/COLOR] " & _
        "OR t.Value Like [COLOR="darkred"]p0[/COLOR] " & _
        "OR t.Sort = [COLOR="Blue"]p1[/COLOR] " & _
        "OR t.Dollars = [COLOR="Blue"]p1[/COLOR];"

Private m_qdf As DAO.QueryDef

Property Get SearchQuery() As DAO.QueryDef
    If m_qdf Is Nothing Then Set m_qdf = CurrentDb.CreateQueryDef("", SQL)
    Set SearchQuery = m_qdf
End Property

Private Sub tbSearch_Change()
    With Me.SearchQuery
        If IsNumeric(Me.tbSearch.Text) Then
[COLOR="Green"]            ' here we search for numeric values[/COLOR]
            .Parameters("[COLOR="DarkRed"]p0[/COLOR]") = Null
            .Parameters("[COLOR="Blue"]p1[/COLOR]") = Me.tbSearch.Text
        Else
[COLOR="green"]            ' here we search for text, noting the addition of wildcards...[/COLOR]
            .Parameters("[COLOR="darkred"]p0[/COLOR]") = "*" & Me.tbSearch.Text & "*"
            .Parameters("[COLOR="Blue"]p1[/COLOR]") = Null
        End If
        
        Set [COLOR="Blue"]Me.lstFound.Recordset[/COLOR] = .OpenRecordset
    End With
End Sub
Also, this example sets a ListBox's Recordset property directly, but you could also set the Recordset of a form. Note also in this code we handle every keystroke, and open a new recordset for every character that changes in the tbSearch textbox, so you don't have to enter text and then hit a button.

Hope this gives some ideas about additional ways to solve this problem, and solve it robustly,
Mark

Mark this sounds great!! Just a little beyond my skill set to adapt to my project. I do get the concept, but to adapt it, I'm not sure I would be able to.

So how would I go about implementing this?

I know I would change "tTestData " to my query name (tqryLedger_Detail_SEARCH), and change your "Me.lstFound.Recordset" to my original "Me.RecordSource", but would I change my "strsearch" to your "tbSearch"? and do I swap your "p0" or "p1" for my actual field names?

Thanks for your help,
 

MarkK

bit cruncher
Local time
Today, 04:10
Joined
Mar 17, 2004
Messages
8,178
No, the p0 and p1 are parameter names. Rather than push your search text into the query as a string constructed on the fly, we pre-write the query SQL as a constant. Then we hand our search text over to the QueryDef, which it substitutes in using our named parameters, so the query does not have to be re-parsed and re-compiled.
Here's how your SQL would look converted to this approach...
Code:
Const SQL As String = _
    "SELECT * " & _
    "FROM qryLedger_Detail_SEARCH " & _
    "WHERE DESCRIPTION like p0 " & _
        "OR VOUCHER like p0 " & _
        "OR POLICY like p0 " & _
        "OR ACCOUNT_NUMBER like p0 " & _
        "OR NOTES like p0 " & _
        "OR DEBIT = p1 " & _
        "OR CREDIT = p1 " & _
    "ORDER BY [MO_DAY] DESC"
The rest shouldn't be too tough. One thing I can't get working though is to set the .Recordset to the form's recordset. So this line...
Code:
        Set Me.lstFound.Recordset = .OpenRecordset
If I change it to ...
Code:
        Set Me.Recordset = .OpenRecordset
... the tbSearch loses focus, so you might have to use a button click if you want to change out the current form's recordset, like...
Code:
Private Sub cmdSearch_Click()
    With Me.SearchQuery
        If IsNumeric(Me.tbSearch) Then
            ' here we search for numeric values
            .Parameters("p0") = Null
            .Parameters("p1") = Me.tbSearch.Text
        Else
            ' here we search for text, noting the addition of wildcards...
            .Parameters("p0") = "*" & Me.tbSearch.Text & "*"
            .Parameters("p1") = Null
        End If
        
        Set Me.Recordset = .OpenRecordset
    End With
End Sub
hth
Mark
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 07:10
Joined
Oct 17, 2012
Messages
3,276
I encourage you to point out my mistakes arnel. I am eager to learn, just like you. Have a nice day,
Mark

Maybe he's griping about passing NULL to parameters, since you can't compare to NULL using the = operator and instead have to do the whole 'Or Like [ParameterName] Is Null' thing. (At least, it's always failed when I've accidentally tried it.)

That said, I ALWAYS try to go with a one-size-fits-all solution if there's one available. Beats the hell out of saving 8 different querydefs or procedurally creating the query.
 

MarkK

bit cruncher
Local time
Today, 04:10
Joined
Mar 17, 2004
Messages
8,178
No, he's grumpy about this thread, where in post #9 I disagree with his post #6.
 

Mark_

Longboard on the internet
Local time
Today, 04:10
Joined
Sep 12, 2017
Messages
2,111
Hey, no fair! That was some quality silk bondage rope he was providing! And if the OP over there was really into some kinky stuff (not sure, they didn't reply) it could be invaluable to them!
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 07:10
Joined
Oct 17, 2012
Messages
3,276
Oy, vey. I remember looking at that thread when it was only up to post 5 and saying to myself that I totally didn't have the time get involved on that one.

Looks like I made the right call.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:10
Joined
Jul 9, 2003
Messages
16,244

jeran042

Registered User.
Local time
Today, 04:10
Joined
Jun 26, 2017
Messages
127
No, the p0 and p1 are parameter names. Rather than push your search text into the query as a string constructed on the fly, we pre-write the query SQL as a constant. Then we hand our search text over to the QueryDef, which it substitutes in using our named parameters, so the query does not have to be re-parsed and re-compiled.
Here's how your SQL would look converted to this approach...
Code:
Const SQL As String = _
    "SELECT * " & _
    "FROM qryLedger_Detail_SEARCH " & _
    "WHERE DESCRIPTION like p0 " & _
        "OR VOUCHER like p0 " & _
        "OR POLICY like p0 " & _
        "OR ACCOUNT_NUMBER like p0 " & _
        "OR NOTES like p0 " & _
        "OR DEBIT = p1 " & _
        "OR CREDIT = p1 " & _
    "ORDER BY [MO_DAY] DESC"
The rest shouldn't be too tough. One thing I can't get working though is to set the .Recordset to the form's recordset. So this line...
Code:
        Set Me.lstFound.Recordset = .OpenRecordset
If I change it to ...
Code:
        Set Me.Recordset = .OpenRecordset
... the tbSearch loses focus, so you might have to use a button click if you want to change out the current form's recordset, like...
Code:
Private Sub cmdSearch_Click()
    With Me.SearchQuery
        If IsNumeric(Me.tbSearch) Then
            ' here we search for numeric values
            .Parameters("p0") = Null
            .Parameters("p1") = Me.tbSearch.Text
        Else
            ' here we search for text, noting the addition of wildcards...
            .Parameters("p0") = "*" & Me.tbSearch.Text & "*"
            .Parameters("p1") = Null
        End If
        
        Set Me.Recordset = .OpenRecordset
    End With
End Sub
hth
Mark


Mark,

Thank you so much for your time on this
Very educational, and helpful!!
 

MarkK

bit cruncher
Local time
Today, 04:10
Joined
Mar 17, 2004
Messages
8,178
You bet jeran, all the best with your project.
Mark
 

Users who are viewing this thread

Top Bottom