Solved

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

Posted on 2014-01-01
6
388 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 50

Assisted Solution

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

strLName = Me!txtLname.Value

or:

strLName = Nz(Me!txtLname.Value)

/gustav
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 333 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Runtime Error '3070' 5 52
Menus 6 58
GA Ribbon creator 9 65
Access Query To Find The Average Date Between Two Dates 3 25
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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