Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access - 2 Unbound Textboxes to filter a List based on which is being filled in

Posted on 2014-01-01
6
Medium Priority
?
403 Views
Last Modified: 2014-01-06
Good morning (or afternoon depending on where you are)

I have a Form where there are a series of unbound text boxes and an unbound Listbox.

I use 2 of the text boxes to Filter my Listbox.  Those 2 text boxes are "txtLName" and "txtEntity".  So if the user is inputting a person's name they fill in the textbox "txtLName" but if they are inputting an Entity name like XYZ Corp then they would fill in the textbox "txtEntity".

Now then, as they complete the textbox "txtLName" it works.  But, if I don't input anything into "txtLName" and just start inputting into the "txtEntity" I get an error (Run-time error '2185' You can't reference a property or method for a control unless the control has the focus).

I haven't even added code yet for the txtEntity textbox.
  My code is:
Private Sub txtLName_Change()
    Call lstExistNameUpdate
    
End Sub
Private Sub txtEntity_Change()
    Call lstExistNameUpdate

End Sub

Private Sub lstExistNameUpdate()
Dim strLName As String, lngLenLName As Long, lngSortSel As Long
lngSortSel = Me.frameExistingParties.Value

    If IsNull(Me.txtLname.Text) = False And Len(Me.txtLname.Text) > 0 Then
        strLName = Me.txtLname.Text
        lngLenLName = Len(Me.txtLname.Text)
        If lngSortSel = 1 Then
            Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties WHERE SortName LIKE '" & strLName & "*' ORDER BY SortName ASC"
        Else
            Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties WHERE SortName LIKE '" & strLName & "*' ORDER BY SortName DESC"
        End If
    Else
        If lngSortSel = 1 Then
            Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties ORDER BY SortName ASC"
        Else
            Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties ORDER BY SortName DESC"
            End If
    End If

End Sub

Open in new window

0
Comment
Question by:wlwebb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1332 total points
ID: 39749686
Use the Value property:

strLName = Me!txtLname.Value

or:

strLName = Nz(Me!txtLname.Value)

/gustav
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 668 total points
ID: 39749687
You can't refer to the .Text property of a control unless it has the focus. If you've moved off that control, however, you don't need to refer to the .Text property. Try this instead:


If Nz(Me.txtLname, "") <> "" Then 
        strLName = Me.txtLname
        lngLenLName = Len(strLName)
        If lngSortSel = 1 Then
            Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties WHERE SortName LIKE '" & strLName & "*' ORDER BY SortName ASC"
        Else
            Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties WHERE SortName LIKE '" & strLName & "*' ORDER BY SortName DESC"
        End If
    Else
        If lngSortSel = 1 Then
            Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties ORDER BY SortName ASC"
        Else
            Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties ORDER BY SortName DESC"
            End If
    End If

Open in new window

0
 

Author Comment

by:wlwebb
ID: 39749689
Neither suggestion worked.  I need to point out that I'm calling this from the x_Change event

I'm wanting to filter the list as they input characters in the field not after they've finished, if that makes a difference to the suggestions.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Accepted Solution

by:
wlwebb earned 0 total points
ID: 39749701
Here's how I came up with how to do it........

Any simpler way???

Private Sub txtEntity_Change()
    Call lstExistNameUpdate2

End Sub

Private Sub txtLName_AfterUpdate()
    Call lstExistNameUpdate2
    
End Sub

Private Sub lstExistNameUpdate2()
Dim strLName As String, lngLenLName As Long, lngSortSel As Long
Dim strCtl As String
lngSortSel = Me.frameExistingParties.Value

strCtl = Me.ActiveControl.Name

    If strCtl = "txtLName" Then
        If Nz(Me.txtLname.Text, "") <> "" Then
            strLName = Me.txtLname.Text
            lngLenLName = Len(strLName)
            If lngSortSel = 1 Then
                Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties WHERE SortName LIKE '" & strLName & "*' ORDER BY SortName ASC"
            Else
                Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties WHERE SortName LIKE '" & strLName & "*' ORDER BY SortName DESC"
            End If
        Else
            If lngSortSel = 1 Then
                Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties ORDER BY SortName ASC"
            Else
                Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties ORDER BY SortName DESC"
                End If
        End If
    Else
        If Nz(Me.txtEntity.Text, "") <> "" Then
            strLName = Me.txtEntity.Text
            lngLenLName = Len(strLName)
            If lngSortSel = 1 Then
                Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties WHERE SortName LIKE '" & strLName & "*' ORDER BY SortName ASC"
            Else
                Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties WHERE SortName LIKE '" & strLName & "*' ORDER BY SortName DESC"
            End If
        Else
            If lngSortSel = 1 Then
                Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties ORDER BY SortName ASC"
            Else
                Me.lstExistingParties.RowSource = "SELECT RecPartyID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName FROM qrytbl_Parties ORDER BY SortName DESC"
                End If
        End If
    End If
                                            
End Sub

Open in new window

0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1332 total points
ID: 39749708
That look's OK.

As .Text is a string, you can reduce to:

    If Me.txtEntity.Text <> "" Then

/gustav
0
 

Author Closing Comment

by:wlwebb
ID: 39758777
I'll split the points to all of you.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question