Solved

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

Posted on 2014-01-11
11
487 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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 …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now