Results 1 to 8 of 8
  1. #1
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150

    You can't reference a property or method for control unless the control has the focus-Dynamic Search

    Error Message: You can't reference a property or method for control unless the control has the focus...



    Situation: I have a form that has a search textbox in the form header section that allows the users to dynamically search for items in a listbox. The code is in the OnChange event, this as the user types a letter, the listbox reduces in size as it finds matches. The user can then double click an item on the listbox and it will add a record to the form. The search function works perfectly fine as long as there's a record already in the form. However, when the form is open and there's no record, not talking about the listbox, but the detail section of the form. This may be related, but not certain. In the on open event, I have set that textbox set to receive focus. That textbox has focus upon opening the form when there's minimum one record, however, when there's no record, there nothing has focus that I can tell.

    I have used two methods below. Both work fine as long as the form has a record in it.

    1) This method, the listbox has a criteria (Like "*" & [forms]![myForm].[txtItemSearch] & "*") that looks at this textbox as it's been typed.

    Code:
        Dim vSearchString As String    Dim bytCharacters As Byte
    
    
        Me.lstBudget.Requery
        Me.lstBudget = Me.lstBudget.ItemData(0)
        Me.lstBudget.SetFocus
        Me.txtItemSearch.SetFocus
        bytCharacters = Len(Nz(Me.txtItemSearch))
        If bytCharacters > 0 Then
           Me.txtItemSearch.SelStart = bytCharacters
        End If
    2) This method, I change the rowsource and use a hidden textbox called txtSrchItemText

    Code:
        Dim vSearchItemString As String
        Me.txtItemSearch.SetFocus
        vSearchItemString = Nz(txtItemSearch.Text)
    
    
        Dim strClientSQL As String
        txtSrchItemText.Value = vSearchItemString
    
    
        strClientSQL = "SELECT BudgetCode, BudgetItem FROM tblListBudgetCode WHERE BudgetItem= " & "'" & txtSrchItemText & "'"
        Debug.Print strClientSQL
        Me.lstBudget.RowSource = strClientSQL
        Me.lstBudget = Me.lstBudget.ItemData(0)
        Me.lstBudget.Requery

  2. #2
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    I have searched and searched, and I came across one explanation. If you let the form to allow additions, it seems to work fine. I had it set to No, as I wanted the users to insert a new data from double clicking the listbox versus typing it in. I don't understand why allowing addition is set to No and when there's a record, the codes above work perfectly fine.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Exactly which line triggers the error?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by June7 View Post
    Exactly which line triggers the error?
    Me.txtItemSearch.SelStart = bytCharacters

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Try setting the nz section of txtitemfcus as "". Currently it's missing

    Also there is no point setting focus to lstBudget when you immediately afterwards set focus to txtitemfocus
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If not specified Nz() should default to empty string. Tested in VBA Immediate Window:

    ?IsNull(Null)
    True

    ?IsNull(Nz(Null))
    False

    ?Nz(Null) = ""
    True

    ?Nz(Null) = "x"
    False

    ?IsEmpty(Nz(Null))
    True

    I have never used Byte datatype but that should not be an issue for this code.

    The posted code is attempted in textbox OnChange event?

    Analyzing issue with first method setting focus.
    Form is set AllowEditions Yes, form filtered so no records returned - No error.
    Form is set AllowEditions Yes, not filtered - No error.
    Form is set AllowEditions No, not filtered - No error.

    Cannot replicate. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I've read where Nz returns either 0 or an empty string, which I always thought to be kinda vague. Seems confusing to me because
    ?nz(null) = 0
    True
    ?nz(null) = ""
    True
    Also
    Private Sub WhatsANzType()
    Dim myType As Variant
    myType = Null
    MsgBox VarType(myType) 'returns 1 which is Null
    MsgBox VarType(Nz(myType)) 'returns 0, which is Empty
    End Sub
    I continue to live with it...

    Anyway, is this control bound? txtItemSearch.
    Probably not, but if yes and the form is set to not allow additions (thus you can't enter records) then it's selLength cannot be set. I'd say the message would be somewhat misleading in that case.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Oh crud, the one Nz() expression I didn't test!!!

    I was testing the SelStart code with an UNBOUND textbox and form set to not allow additions and no error. Just discovered doesn't matter how many characters I type into the textbox, it is Null and bytCharacters is set to 0. Need to use Text property.

    bytCharacters = Len(Me.txtItemSearch.Text)

    Now it is actually running the SelStart code and still no error.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 05-12-2016, 02:27 PM
  2. Replies: 3
    Last Post: 01-05-2016, 11:49 PM
  3. Replies: 1
    Last Post: 10-06-2015, 06:50 AM
  4. Set the control in focus
    By ScottXe in forum Forms
    Replies: 3
    Last Post: 04-06-2015, 11:31 AM
  5. Tab Control Out of Focus - HELP!
    By derek7467 in forum Forms
    Replies: 2
    Last Post: 02-13-2014, 01:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums