?
Solved

Apply filter from one subform to another subform

Posted on 2009-05-04
9
Medium Priority
?
561 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

807 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