Updating Forms Recordset

jeran042 · Aug 23, 2018

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 · Aug 23, 2018

What line causes the error?

jeran042 · Aug 23, 2018

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 · Aug 23, 2018

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_ · Aug 23, 2018

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 · Aug 23, 2018

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 · Aug 24, 2018

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 · Aug 24, 2018

there goes again, the all in one solution.

MarkK · Aug 24, 2018

I encourage you to point out my mistakes arnel. I am eager to learn, just like you. Have a nice day,
Mark

jeran042 · Aug 24, 2018

MarkK said:
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 · Aug 24, 2018

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 · Aug 24, 2018

MarkK said:
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 · Aug 24, 2018

No, he's grumpy about this thread, where in post #9 I disagree with his post #6.

Mark_ · Aug 24, 2018

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 · Aug 24, 2018

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 · Aug 25, 2018

The_Doc_Man said:
because there is no possible operation of multiplying strings.

It could well be a typo but I have also noticed that MS Access sometimes formats your strings for you, causing you enormous headaches! See video 12 from my set of videos "VBA beginner" And also directly on YouTube at this time index:-

https://www.youtube.com/watch?v=RoC5EJLe7l4&t=10s

VBA Beginner - VBA Queries - P2 - Nifty Access

jeran042 · Aug 27, 2018

MarkK said:
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 · Aug 28, 2018

You bet jeran, all the best with your project.
Mark

Updating Forms Recordset

jeran042

Registered User.

MarkK

bit cruncher

jeran042

Registered User.

Cronk

Registered User.

Mark_

Longboard on the internet

The_Doc_Man

Immoderate Moderator

MarkK

bit cruncher

arnelgp

..forever waiting... waiting for jellybean!

MarkK

bit cruncher

jeran042

Registered User.

MarkK

bit cruncher

Frothingslosh

Premier Pale Stale Ale

MarkK

bit cruncher

Mark_

Longboard on the internet

Frothingslosh

Premier Pale Stale Ale

Uncle Gizmo

Nifty Access Guy

jeran042

Registered User.

MarkK

bit cruncher

Similar threads

Users who are viewing this thread