Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

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

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
wlwebb
Asked:
wlwebb
  • 3
  • 2
4 Solutions
 
Gustav BrockCIOCommented:
Use the Value property:

strLName = Me!txtLname.Value

or:

strLName = Nz(Me!txtLname.Value)

/gustav
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
wlwebbAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
wlwebbAuthor Commented:
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
 
Gustav BrockCIOCommented:
That look's OK.

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

    If Me.txtEntity.Text <> "" Then

/gustav
0
 
wlwebbAuthor Commented:
I'll split the points to all of you.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now