We help IT Professionals succeed at work.

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

wlwebb
wlwebb asked
on
555 Views
Last Modified: 2014-01-11
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?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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.
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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...

Author

Commented:
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
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
CERTIFIED EXPERT

Commented:
I concur.  :-)

Ron
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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.

Author

Commented:
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 ...
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.