Using MS Access/VBA Code for Unfiltering

Advertisement


Question
QUESTION: Hi Scott,

I am using Access 2010, and I am an advanced user (some VBA).  I have a parent and child form.  The parent form contains a command button that I need to open the child form.  The child form has a command button that returns to the parent form when clicked.  The following code works perfectly, but the forms keep opening up filtered.  I have turned off the filter on load property on all forms, but it is still being filtered. What can I add to this code so it becomes unfiltered?

On Error GoTo Err_cmdDependent_Click

   Dim stDocName As String
   Dim stLinkCriteria As String

       stDocName = "DependentInformation"
   
   If IsNull(Me.CID) Or Me.CID = "" Then
       MsgBox "This record is empty", vbInformation, "No Data"
       Me.CID.SetFocus
   Else
       stLinkCriteria = "[CID]=" & Me![CID]
       DoCmd.OpenForm stDocName, , , stLinkCriteria
   End If

Exit_cmdDependent_Click:
   Exit Sub

Err_cmdDependent_Click:
   MsgBox Err.Description
   Resume Exit_cmdDependent_Click


Thanks for your time.
Rose

ANSWER: Your problem is that you are opening the form filtered. By using the stLinkCriteria you are using the WHERE clause of the OpenForm method. This applies a filter.

But this appears to be what you would want to do. You want to open the form to the specific records on the form you are opening from.

Hope this helps,
Scott<>

---------- FOLLOW-UP ----------

QUESTION: Hi Scott,

Yes, it does make sense, but it is not what I want to do.  My parent record will have multiple records, so I want the form to only show all the records associated to that one parent record.  

I have looked everywhere on the Internet for VBA Coding for this matter.  Can it be done?  If so, would you please be able to assist?

Thanks

Answer
Sure it can be done, you just need to adjust the WHERE clause in the OpenForm method.

I don't know what CID represents. But the WHERE clause should refer to to the Parent record's PK. Based on the name of the form, it is supposed to show dependents of the parent. That means the parent ID should be a foreign key in the dependent records. So your WHERE clause should be that FK.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.