Solved

Apply filter from one subform to another subform

Posted on 2009-05-04
9
550 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

820 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