Apply filter from one subform to another subform

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?
LVL 11
aelliso3Asked:
Who is Participating?
 
coffeeshopConnect With a Mentor Commented:
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
 
coffeeshopCommented:
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
 
coffeeshopCommented:
1) will look like this:
CascadedSub.jpg
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
aelliso3Author Commented:
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
 
coffeeshopCommented:
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
 
aelliso3Author Commented:
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
 
aelliso3Author Commented:
I actually said the last comment wrong ... subform2 is linked to the main form using the same field as subform1 is (the LoanID)
0
 
aelliso3Author Commented:
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
 
coffeeshopCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.