wlwebb
asked on
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:
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'll split the points to all of you.
ASKER
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.