Solved

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

Posted on 2014-01-01
6
339 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
  • 3
  • 2
6 Comments
 
LVL 49

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 49

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now