Solved

Apply filter from one subform to another subform

Posted on 2009-05-04
9
548 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
ID: 24294533
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
ID: 24294661
1) will look like this:
CascadedSub.jpg
0
 
LVL 11

Author Comment

by:aelliso3
ID: 24294694
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
ID: 24294736
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 11

Author Comment

by:aelliso3
ID: 24294760
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
ID: 24294909
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
ID: 24294914
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
ID: 24295115
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
ID: 24295351
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

911 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

21 Experts available now in Live!

Get 1:1 Help Now