AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of wlwebb
wlwebb
Flag for United States of America asked on

Tab Control Subforms - Updating Listbox on one form while inputting info on another Subform

Hello All!

I have a Tabbed Form.  On one of the Control Tabs I have 3 Sub Forms (Not subs of each other just of the Tab Control page)

On Sub1 I have an unbound Text box where the user inputs RecLName and RecFName etc...

On Sub2 I have a Listbox that shows all Names that have already been input into a table of names.

Originally, I had that listbox on the same form as the unbound textbox.  However, I decided to split it into its own Subform.

On that original form while the user input the last name and the first name it filtered the listbox.  However, as you guys know, when I moved it off to its own subform that doesn't work anymore.

This code was being executed from
Private Sub txtLName_Change()
    Call lstExistNameUpdate
   
End Sub

This is the original Code  I used:
Private Sub lstExistNameUpdate()
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 IsNull(Me.txtEntity) = True Then
            If Me.txtLname.Text <> "" Then
                strLName = Me.txtLname.Text
                lngLenLName = Len(strLName)
                If lngSortSel = 1 Then
                    Me.lstExistingParties.RowSource = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties WHERE lstNameFilter LIKE '" & strLName & "*' ORDER BY SortName ASC"
                Else
                    Me.lstExistingParties.RowSource = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties WHERE lstNameFilter LIKE '" & strLName & "*' ORDER BY SortName DESC"
                End If
            Else
                If lngSortSel = 1 Then
                    Me.lstExistingParties.RowSource = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties ORDER BY SortName ASC"
                Else
                    Me.lstExistingParties.RowSource = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties ORDER BY SortName DESC"
                    End If
            End If
        Else
            If Me.txtLname.Text <> "" Then
                If Me.cboSelectEntityType = 1 Then
                    strLName = Me.txtEntity & Me.txtLname.Text
                Else
                    strLName = Me.txtLname.Text
                End If
                lngLenLName = Len(strLName)
                If lngSortSel = 1 Then
                    Me.lstExistingParties.RowSource = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties WHERE lstNameFilter LIKE '" & strLName & "*' ORDER BY SortName ASC"
                Else
                    Me.lstExistingParties.RowSource = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties WHERE lstNameFilter LIKE '" & strLName & "*' ORDER BY SortName DESC"
                End If
            Else
                If lngSortSel = 1 Then
                    Me.lstExistingParties.RowSource = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties ORDER BY SortName ASC"
                Else
                    Me.lstExistingParties.RowSource = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties ORDER BY SortName DESC"
                    End If
            End If
        End If
    Else
        If Me.txtEntity.Text <> "" Then
            strLName = Me.txtEntity.Text
            lngLenLName = Len(strLName)
            If lngSortSel = 1 Then
                Me.lstExistingParties.RowSource = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties WHERE lstNameFilter LIKE '" & strLName & "*' ORDER BY SortName ASC"
            Else
                Me.lstExistingParties.RowSource = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties WHERE lstNameFilter LIKE '" & strLName & "*' ORDER BY SortName DESC"
            End If
        Else
            If lngSortSel = 1 Then
                Me.lstExistingParties.RowSource = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties ORDER BY SortName ASC"
            Else
                Me.lstExistingParties.RowSource = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties ORDER BY SortName DESC"
                End If
        End If
    End If
                                            
End Sub

Open in new window


If I move that code to Sub2 form it errors out.  So, is there a way to Call the Sub2's Private Sub from Sub1?  Or do I have to move it all to a module?
Microsoft Access
Avatar of undefined
Last Comment
wlwebb
8/22/2022 - Mon
mbizup
You CAN do this by changing the Private Sub to Public Sub and using a form reference:

Call Forms!YourFormName,YourPublicSubName

However, I personally would move it to a separate module and pass the form name:

Sub YourSubName(strFrmName as string)

Call it like this:

YourSubName  "YourFormName"

Open in new window


And change any references including 'Me' such as  Me.lstExistingParties.RowSource to

Forms(strFormName).lstExistingParties

Open in new window

wlwebb
ASKER
Mbiz

Well I attempted to understand that.......  

in the Change event for my unbound textbox of subform1, I changed it to :
Private Sub txtLName_Change()

    NewlstExistNameUpdate "sfrmSys_ExistingPartiesSelect"

End Sub

Open in new window


And in the Module I put:
Private Sub NewlstExistNameUpdate(strFrmName As String)
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 IsNull(Me.txtEntity) = True Then
            If Me.txtLname.Text <> "" Then
                strLName = Me.txtLname.Text
                lngLenLName = Len(strLName)
                If lngSortSel = 1 Then
                    Forms(strFormName).lstExistingParties = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties WHERE lstNameFilter LIKE '" & strLName & "*' ORDER BY SortName ASC"
                Else
                    Forms(strFormName).lstExistingParties = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties WHERE lstNameFilter LIKE '" & strLName & "*' ORDER BY SortName DESC"
                End If
            Else
                If lngSortSel = 1 Then
                    Forms(strFormName).lstExistingParties = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties ORDER BY SortName ASC"
                Else
                    Forms(strFormName).lstExistingParties = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties ORDER BY SortName DESC"
                    End If
            End If
        Else
            If Me.txtLname.Text <> "" Then
                If Me.cboSelectEntityType = 1 Then
                    strLName = Me.txtEntity & Me.txtLname.Text
                Else
                    strLName = Me.txtLname.Text
                End If
                lngLenLName = Len(strLName)
                If lngSortSel = 1 Then
                    Forms(strFormName).lstExistingParties = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties WHERE lstNameFilter LIKE '" & strLName & "*' ORDER BY SortName ASC"
                Else
                    Forms(strFormName).lstExistingParties = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties WHERE lstNameFilter LIKE '" & strLName & "*' ORDER BY SortName DESC"
                End If
            Else
                If lngSortSel = 1 Then
                    Forms(strFormName).lstExistingParties = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties ORDER BY SortName ASC"
                Else
                    Forms(strFormName).lstExistingParties = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties ORDER BY SortName DESC"
                    End If
            End If
        End If
    Else
        If Me.txtEntity.Text <> "" Then
            strLName = Me.txtEntity.Text
            lngLenLName = Len(strLName)
            If lngSortSel = 1 Then
                Forms(strFormName).lstExistingParties = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties WHERE lstNameFilter LIKE '" & strLName & "*' ORDER BY SortName ASC"
            Else
                Forms(strFormName).lstExistingParties = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties WHERE lstNameFilter LIKE '" & strLName & "*' ORDER BY SortName DESC"
            End If
        Else
            If lngSortSel = 1 Then
                Forms(strFormName).lstExistingParties = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties ORDER BY SortName ASC"
            Else
                Forms(strFormName).lstExistingParties = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties ORDER BY SortName DESC"
                End If
        End If
    End If

End Sub

Open in new window


It's erroring out on the Change event telling me Compile Error Sub or Function not defined
mbizup
In your module, you'll have to remove the "Private", or change Private to Public.

Otherwise the sub will only be visible in the context of that module - nowhere else in the database.

You will also have to change all of the "Me" references to Forms(strFormName).  Looks like you got most of them, but there are still a few left.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
IrogSinta
You could also try putting your code on the first subform and just refer to the controls in the second subform. Here's your code which I modified a bit. Any control that's on the 2nd subform should be prefixed with frm instead of Me. I also removed references to lngLenLName since it looks like it's not being used.

Private Sub lstExistNameUpdate()
Dim lngSortSel As Long
Dim strLName As String
Dim strCtl As String
Dim strWhere as String
Dim strOrder As String
Dim frm as Form

    Set frm = Parent.Sub2.Form
    lngSortSel = Me.frameExistingParties.Value
    strCtl = Me.ActiveControl.Name

    If strCtl = "txtLName" Then
        strLName = Me.txtLname.Text
        If IsNull(Me.txtEntity) = True Then
            If Me.txtLname.Text <> "" Then
                strWhere = "Y"
            End IfstrWhere
        Else
            If Me.txtLname.Text <> "" Then
                If Me.cboSelectEntityType = 1 Then
                    strLName = Me.txtEntity & Me.txtLname.Text
                End If
                strWhere = "Y"
            End If
        End If
    Else
        If Me.txtEntity.Text <> "" Then
            strLName = Me.txtEntity.Text
            strWhere = "Y"
        End If
    End If

    If strWhere = "Y" Then strWhere = "WHERE lstNameFilter LIKE '" & strLName & "*'"
    strOrder = "ORDERS BY SortName " & IIf(lngSortSel = 1, "","DESC")
    frm.lstExistingParties.RowSource = strSQL & strWhere &  strOrder
                                       
End Sub

Open in new window

wlwebb
ASKER
mbiz....
I changed the suggested "me." throughout the Module to "Forms(strFormName)."

However it does not like my variable

strCtl = Forms(strFormName).ActiveControl.Name
which was the second line after the Dims ........

I supposed it's because of the .ActiveControl but I don't know..... Not versed enough in modules to understand if that would work within a module or if I have to pass that ActiveControl info from the form to the module somehow...
wlwebb
ASKER
Irog......
Thanks for chiming in too!

There were two errors that I get..
Code Line 18
            End IfstrWhere

I assumed it should be End if 'strWhere


And on Line 36 the =strSQL

strSQL isn't defined.....


I tried all of the strXXXX in place and my 2nd subform on that same TabControl page just blanks out...... no result....


Also a question... even if I get that working... I notice you reference strLName and are just getting that for the field txtLName....

My original code I used this updating of that listbox whenever the were entering info for the fields:
txtLName
txtFName
txtEntity
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
IrogSinta
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
wlwebb
ASKER
Irog.... That worked!!  with two corrections....

Line 11 needed a space at the end of the Select stmt
StrSql = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties "

and Line 38 ORDERS BY should be ORDER BY

Do you concur?

I'm also leaving open for a while to see if Mbiz will respond re: the Module issue as well so I can try to understand that too.

THANK you for your help as always.!!!!


PS.... YOU WERE ALSO CORRECT RE: THE txtFName issue.... my fault.
IrogSinta
I concur.  :-)

Ron
mbizup
Wlwebb,  sorry for the delay here.

it looks like Ron has you covered, so go ahead and accept his answer.

There are a variety of reasons why the module version isn't working but I'm not going to be able to get back to this before tomorrow night.  I'll follow up, but don't hold up closing the question on my account.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
wlwebb
ASKER
Irog..

Thanks so much! Appreciate the help.  Your code is so much more efficient than mine.

[for my future refer - see the post after accepted solution for two typo corrections].

MBiz  I thank you too for the input..... hope you have the time to follow up on module ...
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent