Solved

Apply filter from one subform to another subform

Posted on 2009-05-04
9
547 Views
Last Modified: 2013-11-29
This seems so simple and it's killing me ...

I have a form that has two subfoms on it. Subform1 is a continuous form that looks like a datasheet view. Subform2 is a standard view form that shows details about the record that was selected on Subform1. All I want to do is when you click on a record on Subform1, I want it to show the details in SubForm2 that are joined to that record.

The best example that I can think of is a MASTER account number, LOAN numbers, and LoanDetails. The Main form is the MASTER, Subform1 displays all the Loan numbers within the Master, and Loan Details shows the history with any notes, payments, etc. I select Loan #1, I see the details for Loan #1, Select 2 ... Details 2, etc.

Although I do not have to keep it in 2 subforms, I would like to ... any ideas?
0
Comment
Question by:aelliso3
  • 5
  • 4
9 Comments
 
LVL 7

Expert Comment

by:coffeeshop
Comment Utility
There are two approaches:
1) You can change the endless form (LOAN numbers) to datasheetview. Place the detail (LoanDetails) onto this form and keep the view normal (form). When clicking the "+" in the datasheetview the number line expands and you can see the whole detail-form. This doesn't work with endless-forms, but with datasheet you can switch the views to normal view. 3 level cascaded subs are no problem.

2) Place some code in the Current-Event of form "LOAN numbers": remember the Loan-ID and requery "LoanDetails" with the saved Loan-ID as where-clause in your query. I can give you an example if you need.
0
 
LVL 7

Expert Comment

by:coffeeshop
Comment Utility
1) will look like this:
CascadedSub.jpg
0
 
LVL 11

Author Comment

by:aelliso3
Comment Utility
This is what I had in subform2 prior to sending out this question, but I must have something wrong ...
I created a textbox (txtLoanID) on the Master form and updated it on Subform1's click event. The the code below is on subform2's current event
 

Private Sub Form_Current()

     strSQL = "SELECT * FROM LoanDetails WHERE"

     strSQL = strSQL & " LoanID=""" & Me.Parent.frmMASTER.txtLoanID.Value & """"

frmLoanDetails.Form.RecordSource = strSQL 

End Sub  

Open in new window

0
 
LVL 7

Expert Comment

by:coffeeshop
Comment Utility
I assume that your second sub isn't linked to the master via the txtLoanID, so no event is fired in that form after you change txtLoanID.

I prefer another easy way to do this, as stated in 2). Just a moment, I provide you some code.



0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 11

Author Comment

by:aelliso3
Comment Utility
That is correct ... Right now I have subform2 linked to subform1
Also, As far as the first option you suggested ... this would be tough becuase there are some calculated fields that I need to show in the details. That's basically the reason for the two subforms.
0
 
LVL 7

Accepted Solution

by:
coffeeshop earned 500 total points
Comment Utility
OK, so lets save the LoanID after changing the record in lLoanId and call it from the query through a property (or function). Just add the below code to your form. The advantage is that you always can get the actual LoadID with the new property.

If you need to add more where-clauses then change your sql as in your current event before the requery.
'best put this to a module'

Private lLoanID As Long
 

Public Property Get LoanID() As Long

  LoanID = lLoanID

End Property

Property Let LoanID(lpropVal As Long)

  lLoanID = lpropVal

End Property
 

'put this to the sub1'

Private Sub Form_Current()
 

  If IsNull(Me.txtLoanID) Then

    lLoanID = 0

  Else

    lLoanID = Me.txtLoanID

  End If

  Me.Parent!YourSub2.Form.Requery

  

End Sub
 

'change your query and add it to sub2, the only difference is that'

'you call the LoanID through a function/property'

SELECT * FROM LoanDetails WHERE LoanID=" & LoadID()

Open in new window

0
 
LVL 11

Author Comment

by:aelliso3
Comment Utility
I actually said the last comment wrong ... subform2 is linked to the main form using the same field as subform1 is (the LoanID)
0
 
LVL 11

Author Comment

by:aelliso3
Comment Utility
As soon as I used the "Me.Parent!YourSub2.Form.Requery" in subform1... the current event started to work like a charm in subform2
Thanks a million (too bad we can only offer 500 ... lol)
 
0
 
LVL 7

Expert Comment

by:coffeeshop
Comment Utility
Gladly! So it worked with a small change.

Although I suggest you taking a look at the property-solution. This helped me a lot in the past, because it gives you a better control what happens. And it is sometimes faster and easier to apply than changing the recordsoure.

0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

772 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

12 Experts available now in Live!

Get 1:1 Help Now