Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-01-11
11
Medium Priority
?
517 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
[X]
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
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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 2000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

610 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