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

    Dynamic Combo Box Based on a Search Box


    Hi,

    I'm trying to build a combo box that would limit its list based on a search box. The below code worked before on another application I built, but it seems to give me problems now. The search box is txtCustomerSearch, the combo box is cboCustomer. When I get to
    Code:
    vSearchString = Me.txtCustomerSearch.Text
    , I get an error message "You can't reference a property or method for a control unless the control has the focus". Note that the line before focuses on the text box. I tried to change it to Me.txtCustomerSearch.Value but vSearchString returns blank.
    Code:
      
    Private Sub txtCustomerSearch_Change()  
        Dim vSearchString As String    Dim strSql As String
        
        
        Me.txtCustomerSearch.SetFocus
        vSearchString = Me.txtCustomerSearch.Text
        
        txtCustomerSearchValue = vSearchString
        strSql = "SELECT * FROM qryBudgetCustomerListDropdown"
        Me.cboCustomer.RowSource = strSql
        Me.cboCustomer = Me.cboCustomer.ItemData(0)
        Me.cboCustomer.Requery
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I would use textbox AfterUpdate event instead of Change.

    Then don't use Text property, use Value, and since Value is default for data controls don't even have to reference.
    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.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If trying to limit a combo list based on some value in another control, that will never work regardless of what event you use. Here's one of several ways this could be handled in the search criteria field After Update event:

    Code:
    Dim strCustomer As String, strSql As String
    
    strCustomer = Me.txtCustomerSearch
    strSql = "SELECT * FROM qryBudgetCustomerListDropdown "
    
    If Not Nz(strCustomer,"") = "" Then
      Me.cboCustomer.RowSource =  strSql & "WHERE Customer = '" & strCustomer & "'" 
    Else
      Me.cboCustomer.RowSource = strSql
    End If
    
      Me.cboCustomer.Requery
    NOTE - it is assumed Customer is the name of the field you want to use in the query criteria AND that it is a text field and not a number. If number, the single quotes have to go.
    Last edited by Micron; 06-26-2019 at 06:45 PM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by Micron View Post
    If trying to limit a combo list based on some value in another control, that will never work regardless of what event you use. Here's one of several ways this could be handled in the search criteria field After Update event:

    Code:
    Dim strCustomer As String, strSql As String
    
    strCustomer = Me.txtCustomerSearch
    strSql = "SELECT * FROM qryBudgetCustomerListDropdown "
    
    If Not Nz(strCustomer,"") = "" Then
      Me.cboCustomer.RowSource =  strSql & "WHERE Customer = '" & strCustomer & "'" 
    Else
      Me.cboCustomer.RowSource = strSql
    End If
    
      Me.cboCustomer.Requery
    NOTE - it is assumed Customer is the name of the field you want to use in the query criteria AND that it is a text field and not a number. If number, the single quotes have to go.
    I'm not sure how the code I used above worked on a different app. I guess why it shouldn't work is due to the fact that as you're typing texts in a text box, the string being typed cannot be stored until it loses focus? If I can extract whatever is being typed and store that in a variable, then this quest should be simple enough.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    What you describe is a cascading combobox. This is topic of many threads. Conventional approach is code in form Current event, first control AfterUpdate, combobox GotFocus, not Change event. No example uses Text property.

    A 'find as you type' functionality is complicated, review http://allenbrowne.com/AppFindAsUType.html
    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: 10
    Last Post: 01-06-2019, 01:51 PM
  2. Dynamic Search in Form
    By LonghronJ in forum Modules
    Replies: 3
    Last Post: 02-28-2018, 01:09 PM
  3. Search Query Based on Multiple Combo / Text Boxes
    By ItsJustRey in forum Queries
    Replies: 5
    Last Post: 07-20-2016, 02:04 PM
  4. Simple Dynamic Client Search
    By Lorne in forum Forms
    Replies: 4
    Last Post: 12-21-2011, 04:58 PM
  5. Replies: 1
    Last Post: 05-25-2011, 08:37 AM

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