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
Solved

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

Posted on 2014-01-11
11
506 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?
0
Comment
Question by:wlwebb
  • 5
  • 3
  • 3
11 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39773264
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

0
 

Author Comment

by:wlwebb
ID: 39773284
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39773332
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.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 29

Expert Comment

by:IrogSinta
ID: 39773676
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

0
 

Author Comment

by:wlwebb
ID: 39773880
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...
0
 

Author Comment

by:wlwebb
ID: 39773899
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
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
ID: 39773946
Corrected code:
Private Sub lstExistNameUpdate()
Dim lngSortSel As Long
Dim strLName As String
Dim strCtl As String
Dim strSql As String
Dim strWhere as String
Dim strOrder As String
Dim frm as Form

    Set frm = Parent.Sub2.Form
    StrSql = "SELECT RecPartyID, EntityNameTypeID, RecLName, RecFName, RecMName, RecSufName, RecEntityName, EntityNameType, PartyFullNameFML, PartyFullNameLFM, SortName, lstNameFilter FROM qrytbl_Parties"
                    
    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 If
        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

I'm not sure what you're talking about with txtFName. I don't see that in the code you posted. As for the other two, it's still the same as your code.
0
 

Author Comment

by:wlwebb
ID: 39773973
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.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39773987
I concur.  :-)

Ron
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39774284
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.
0
 

Author Closing Comment

by:wlwebb
ID: 39774341
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 ...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server views 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 Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

856 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