Solved Help with dynamic search on form with read only recordset (1 Viewer)

[slharman1]

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

 

[pbaldy]

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.

 

[slharman1]

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

 

[pbaldy]

I can't think of why being editable or not would affect search code. Seeing the code may help.

 

[Cronk]

Don't bind the search box to any field in the form.

 

[slharman1]

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.

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

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

 

[slharman1]

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

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

I see now I was wrong, I am usng .text I will change that and give it a try.

 

[pbaldy]

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.

 

[slharman1]

Ok, Tried this and now I get invalid use of null,
still won't work:

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

 

[slharman1]

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.

I don't have to filter after every key stroke but it sure helps on searching model numbers

 

[pbaldy]

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

 

[pbaldy]

Try temporarily commenting out the "On Error..." line and when the error occurs hit Debug and see what line is throwing the error.

 

[slharman1]

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.

I had to go back to .text because with .value it won't let me type more than one character.

 

[pbaldy]

I had to go back to .text because with .value it won't let me type more than one character.

Yes, see post 11 for why.

 

[pbaldy]

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.

 

[pbaldy]

Can you post the SQL of the query? It's hard to read in the picture.

 

[slharman1]

Try temporarily commenting out the "On Error..." line and when the error occurs hit Debug and see what line is throwing the error.

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

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

 

[slharman1]

Can you post the SQL of the query? It's hard to read in the picture.

Here it is:

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]

ok here's the line causing the error
.SelStart = Len(sText)

Shot in the dark, but does change the previous like to:

.Text = sText

do anything?

 

[slharman1]

Shot in the dark, but does change the previous like to:

.Text = sText

do anything?

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.