Solved Help with dynamic search on form with read only recordset

S

slharman1

Member
Local time
Today, 11:09
Joined
Mar 8, 2021
Messages
483
Does anyone know how to write code for a dynamic search box for data in a form who’s recordset is read only? The one I’ve been using works great when the query is editable but when I add a calculated field I get the dreaded “can’t reference a property unless the control has the focus”
Thanks
 
What is your code? That error usually results from using the .Text property of a control, which typically isn't needed and does require focus.
 
I will post the code when I get neck to my workstation but I am not using .text, I am using value. It works when the underlying query is editable, but not when it is read only by adding a sub query to the query with calculated fields even though it is not the field I am searching on.
thanks
 
I can't think of why being editable or not would affect search code. Seeing the code may help.
 
Don't bind the search box to any field in the form.
 
pbaldy said:
What is your code? That error usually results from using the .Text property of a control, which typically isn't needed and does require focus.
Click to expand...
Here is my code and a snaphot of my query, I am starting to think it's the redundancy in the query but I am not sure.
qryEstimatesListActive is the query the form uses as a record source (pic 1), qryEstimateTotalPrice, is a subquery added to the main query(pic 2)
Thanks

Code: 
Private Sub txtModelFilter_Change()

    Dim sText As String
    Dim strFilter As String
    On Error GoTo ErrHandler
    sText = Me!txtModelFilter.Text
    If sText <> "" Then
        strFilter = "[ModelNumber] Like '*" & sText & "*'"
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    With Me.txtModelFilter
        .SetFocus
        .Value = sText
        .SelStart = Len(sText)
        .SelLength = 0
    End With
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation

End Sub
 

Attachments

  • pic 1.jpg
    pic 1.jpg
    32.4 KB · Views: 321
  • pic 2.jpg
    pic 2.jpg
    34.6 KB · Views: 332
slharman1 said:
Here is my code and a snaphot of my query, I am starting to think it's the redundancy in the query but I am not sure.
qryEstimatesListActive is the query the form uses as a record source (pic 1), qryEstimateTotalPrice, is a subquery added to the main query(pic 2)
Thanks

Code: 
Private Sub txtModelFilter_Change()

    Dim sText As String
    Dim strFilter As String
    On Error GoTo ErrHandler
    sText = Me!txtModelFilter.Text
    If sText <> "" Then
        strFilter = "[ModelNumber] Like '*" & sText & "*'"
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    With Me.txtModelFilter
        .SetFocus
        .Value = sText
        .SelStart = Len(sText)
        .SelLength = 0
    End With
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation

End Sub
Click to expand...
I see now I was wrong, I am usng .text I will change that and give it a try.
 
You're using it but within the change event it should be okay. Do you really want to filter with every keystroke? I typically use the after update event, so nothing happens until the user hits tab or enter.
 
Ok, Tried this and now I get invalid use of null,
still won't work:
Code: 
Private Sub txtModelFilter_Change()

    Dim sText As String
    Dim strFilter As String
    On Error GoTo ErrHandler
    sText = Me!txtModelFilter.Value   'changed to .Value from .Text
    If sText <> "" Then
        strFilter = "[ModelNumber] Like '*" & sText & "*'"
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    With Me.txtModelFilter
        .SetFocus
        .Value = sText
        .SelStart = Len(sText)
        .SelLength = 0
    End With
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation

End Sub
 
pbaldy said:
You're using it but within the change event it should be okay. Do you really want to filter with every keystroke? I typically use the after update event, so nothing happens until the user hits tab or enter.
Click to expand...
I don't have to filter after every key stroke but it sure helps on searching model numbers
 
Actually the value property isn't updated until the user completes entry via tab or enter, so can't be used in the change event:

Value vs Text

www.baldyweb.com
 
Try temporarily commenting out the "On Error..." line and when the error occurs hit Debug and see what line is throwing the error.
 
pbaldy said:
You're using it but within the change event it should be okay. Do you really want to filter with every keystroke? I typically use the after update event, so nothing happens until the user hits tab or enter.
Click to expand...
I had to go back to .text because with .value it won't let me type more than one character.
 
More accurately, the error occurs because you're trying to put a null value into a variable declared as a String, which can't accept a null.
 
Can you post the SQL of the query? It's hard to read in the picture.
 
pbaldy said:
Try temporarily commenting out the "On Error..." line and when the error occurs hit Debug and see what line is throwing the error.
Click to expand...

ok here's the line causing the error
.SelStart = Len(sText)
my code so there's no confusion as to which version am using:
Code: 
Private Sub txtModelFilter_Change()

    Dim sText As String
    Dim strFilter As String
    'On Error GoTo ErrHandler
    sText = Me!txtModelFilter.Text
    If sText <> "" Then
        strFilter = "[ModelNumber] Like '*" & sText & "*'"
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    With Me.txtModelFilter
        .SetFocus
        .Value = sText
        .SelStart = Len(sText)
        .SelLength = 0
    End With
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation

End Sub
 
pbaldy said:
Can you post the SQL of the query? It's hard to read in the picture.
Click to expand...
Here it is:
Code: 
SELECT tblEstimates.EstimateID, tblEstimates.ModelNumber, tblEstimates.Description, tblEstimates.UofM, tblEstimates.Active, tblEstimates.DateModified, tblProductType.ProductType, tblOwner.Owner, tblEstimates.Stock, qryEstimateTotalPrice.Price
FROM tblProductType RIGHT JOIN ((tblEstimates LEFT JOIN tblOwner ON tblEstimates.OwnerID = tblOwner.OwnerID) INNER JOIN qryEstimateTotalPrice ON tblEstimates.EstimateID = qryEstimateTotalPrice.EstimateID) ON tblProductType.ProductTypeID = tblEstimates.ProductType
WHERE (((tblEstimates.Active)=True))
ORDER BY tblEstimates.ModelNumber;
 
pbaldy said:
Shot in the dark, but does change the previous like to:

.Text = sText

do anything?
Click to expand...
Endless loop then restart.
Maybe some error handling for if it is not in the records but I am not sure how to do that.
 
You must log in or register to reply here.

Similar threads

H
Problem with Search Boxes
2
Replies
24
Views
935
Pat Hartman
P
I
Dynamic/reusable forms
4 5 6
Replies
111
Views
3,800
Imb
I
H
Solved Search across multiple fields
Replies
8
Views
584
Harry Paraskeva
H
K
Solved linking text box with after_update procedure using VBA.
Replies
17
Views
710
Kingz
K
J
Read Only Message
Replies
6
Views
236
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom