Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Nov 2011
    Posts
    103

    Unanswered: Pulling corresponding 2 forms

    Is there a way to pull 2 corresponding forms when search is done in one?

    For example, if I were to run a search for a record 2 of 10 in a form, I want the same 2 of 10 record also to be pulled in a different form that are linked by common denominator (parcel number, customer id, or whatever).

    Thanks.

  2. #2
    Join Date
    Nov 2011
    Posts
    103
    I tried to create a macro in which Where Condition = [Forms]![Lease]![PropertyID]=[Forms]![Rent and Option]![Combo204] but no dice. The form does open but not the right record.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use the Filter property of the first form and apply it to the second.
    Have a nice day!

  4. #4
    Join Date
    Nov 2011
    Posts
    103
    So would I use something like this on the first form

    Private Sub Combo204_Enter()
    Me.Filter = Me.Combo204
    End Sub

    Then use something like this in the second form?

    Private Sub Form_Open(Cancel As Integer)
    DoCmd.ApplyFilter , [Forms]![Rent_and_Options]![Combo204]
    End Sub

    I know this is wrong but I'm not sure how to use a filter.

    Thanks.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not exactly: Once the search is performed in the first form, this form is filtered (i.e. its Filter property is not empty any more). You can then set the value of the Filter property in the second form equal to the same property in the first form:
    Code:
    Forms("Form2").Filter = Forms("Form1").Filter
    Have a nice day!

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Cool

    Quote Originally Posted by tobabygu View Post
    So would I use something like this on the first form

    Private Sub Combo204_Enter()
    Me.Filter = Me.Combo204
    End Sub

    Then use something like this in the second form?

    Private Sub Form_Open(Cancel As Integer)
    DoCmd.ApplyFilter , [Forms]![Rent_and_Options]![Combo204]
    End Sub

    I know this is wrong but I'm not sure how to use a filter.

    Thanks.
    Hi,
    Just a quick note on labeling etiquette. For the sake of ease of readablilty and maintenance..ie someone else stepping into your code to know what is going on try this for your ComboBox. Give it an actual name. Something that tells what the ComboBox is, but in brief form. That will help later on down the road. Just a simple little tid bit of info. I learned from the guys on here long ago, and it really did help. Hope you find this useful.
    Happy Holidays to you and all.
    Have a nice one,
    Bud

  7. #7
    Join Date
    Nov 2011
    Posts
    103
    I can't seem to get this to work... Is the first part of my filter from the 16th correct? I'm getting either an error message where my filter is highlighted in yellow or nothing occurs.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This seems very unlikely to me:
    Code:
    Private Sub Combo204_Enter()
        Me.Filter = Me.Combo204
    End Sub
    1. I cannot imagine what the value of Combo204 can be but I guess it's a numeric, text or date value. A filter (for a form) is a string expression composed of at least three parts: "<ColumnName> <Operator> <Value>" e.g.
    Code:
    "CustomerName Like 'S*'"
    or:
    Code:
    "RowID = 12"
    Except if you store predefined filter expressions in the combo, you have to retrieve the value of that combo and assemble the string expression you want to use as a filter from it:
    Code:
    Me.Filter = "RowID = " & Me.Combo204.Value
    Me.FilterOn = True
    2. I dont understand why you use the "Enter" event of the combo. This event occurs before a control actually receives the focus from a control on the same form, so before the user has any possibility of typing or selecting anything.

    3. You should try to give meaningful names to the objects in your database: "Combo204" does not help to understand what's its purpose nor what it can contain.
    Have a nice day!

  9. #9
    Join Date
    Nov 2011
    Posts
    103
    Well it was a search combo box. Now even the search isn't working for some reason. It was working last week...

    Basically it is a combo box that has a list of all the PropetyId field data and whenever a PropertyID is chosen it would take you to that record. Of course, like I said, it's no longer working... And I'm not sure why.

    What I wanted to do was if I search in a form by a PropertyID using the search combo box then all other open form would automatcally also go to that record without having the user search in each form using its own combo search box. Does that makes sense?

    Thanks.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you define what a "search combo box" is?

    As far as I know, there are two possibilities to use a combo for searching records in a data set:
    1. You have some code in the 'AfterUpdate' event handler of the combo that assembles a criteria (a filter) from the value that was selected in the combo.
    2. A query that has a reference to the combo is run after the a value is selected. In this category I include a string used as the 'RecordSource' for a form or for a report, or the query (FilterName) or criteria (WhereCondition) parameter passed to this form/report when opening it using the 'DoCmd.OpenForm' method (fourth or fifth parameter).

    Was this combo created by a wizard? Can you chek what code (if any) exists in its 'AfterUpdate' event handler and post it?
    Have a nice day!

  11. #11
    Join Date
    Nov 2011
    Posts
    103
    Yeah no problem. Search Combo Box is the third possiblities that is available when you use the wizard to set your combo box. The third option is "Enable users to find a record by selecting a value from a list." Once it's chosen the steps are given to enable the search.

    Here is the code under "After Update." And sorry if I'm not posting the codes properly. I don't how everyone posts their codes in a special little box.

    Private Sub SearchID_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[PropertyID] = '" & Me![SearchID] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Now I understand. However, when you use this technique, you don't set any filter on the form, you simply change the current record.

    You can use a similar technique to synchronize the second form with the first one. Depending on whether the second form is already open or not, the solution would be slightly different. If the form is already open, you can use:

    1. in the class module of the second form ("Rent_and_Options"):
    Code:
    Public Sub Synchronize(ByVal SearchID As String)
    
        Dim rst as DAO.RecordSet
    
        Set rst = Me.RecordSetClone
        rst.FindFirst  "PropertyID = '" & SearchID & "'"
        If rst.NoMatch = False Then
            Me.BookMark = rst.BookMark
        End If
        Set rst = Nothing
    
    End Sub
    2. In the class module of the first form:
    Code:
    Private Sub SearchID_AfterUpdate()
    
        Dim rst As DAO.RecordSet
    
        Set rst = Me.RecordsetClone
        rst.FindFirst "PropertyID = '" & Me.SearchID.Value & "'"
        If Not rst.NoMatch Then 
            Me.Bookmark = rst.Bookmark
            Forms("Rent_and_Options").Synchronize Me.SearchID.Value
        End If
        Set rst = Nothing
    
    End Sub
    Your code is "broken" probably because you renamed the combo from "SearchID" to "Combo204"
    Have a nice day!

  13. #13
    Join Date
    Nov 2011
    Posts
    103
    So I should plug in your 1. Code into Rent_and_Options "Class Module" and plus the 2. Code into the initial form Lease "Class Module?"

    And what if I want this on more than one form? Easy modification or does it make it a whole another beast? Just curious incase the users asks me of this.

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The same technique is usable in any form, you just need to change the name of the form and possibly, the name and data type of the argument you pass to the "Synchronize" procedure.

    Inside this procedure ("Synchronize") you possibly have to adapt the line "rst.FindFirst..." according to the name of the column (field) in which the search is performed, as well as to the name and data type of the argument received.
    Have a nice day!

  15. #15
    Join Date
    Nov 2011
    Posts
    103
    Ok so I thought I understood this but maybe not since I can't get the forms to "Synchronize."

    The Combo Search Box is named SearchID in the Lease form and SearchPropID in Rent_and_Option form. I thought changed the codes to show the reflected info but nothing...

    I left 1.Code the same.
    2.Code was changed to

    Private Sub SearchID_AfterUpdate()

    Dim rst As DAO.Recordset

    Set rst = Me.RecordsetClone
    rst.FindFirst "PropertyID = '" & Me.SearchID.Value & "'"
    If Not rst.NoMatch Then
    Me.Bookmark = rst.Bookmark
    Forms("Rent_and_Options").Synchronize Me.SearchPropID.Value
    End If
    Set rst = Nothing

    End Sub

    Just a minor change. SearchID was changed to SearchPropID. Did I do something wrong? I'm putting them under Class Module which I know is a small seperate window in VBA explorer. Am I not using Class Module wrong? Is that why?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •