Solved

Filtering data on a form based on a field on another form

Posted on 2013-06-13
5
347 Views
Last Modified: 2013-06-13
So I have this form, frmMembersSwitchBoard,  On this form there are three choices.
They are Department, Division and Duty Section.

When you click on Department a combo box appears with a list of departments you can choose from.  Once you choose the department the execute button appears.  When you click the execut button I would like it to open the form frmPersonnelRows and filter that form based on the department field chosen on the form frmMembersSwitchBoard

Thank you
John
0
Comment
Question by:John Sheehy
  • 3
  • 2
5 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 39245477
DoCmd.OpenForm "frmPersonnelRows", , , "DepartmentID = '" & Me.DepartmentID & "'"

Use that command on the Execute button click event.  Change the first DepartmentID to the name of the department ID field in the source table/query for frmPersonnelRows.  Change the second DepartmentID to the name of the department field on the frmMembersSwitchboard form.

Search VBA Help for OpenForm and take a look at the DoCmd.OpenForm method.

OM Gang
0
 

Author Comment

by:John Sheehy
ID: 39245530
That works great but I get a run time error if no record are assigned to a department.  The run time error is:  Run-time error '2501':
The OpenForm action was Canceled

Any thoughts?

John
0
 
LVL 28

Expert Comment

by:omgang
ID: 39245601
A couple.

1)  Capture the error and respond accordingly

2)  Use OpenArgs instead and move the filtering to the Form Open event of the frmPersonnelRows form


Here's some sample code for option 1

Private Sub cmdExecute_Click()
    On Error Resume Next
    DoCmd.OpenForm "frmPersonnelRows", , , "DepartmentID = '" & Me.DepartmentID & "'"

    If Err.Number = 2501 Then           'no existing record for department
        DoCmd.OpenForm "frmPersonnelRows"  'open form without filter
    Else
        GoTo Err_Click
    End If

Exit_cmdExecute_Click:
    Exit Sub

Err_Click:
    MsgBox Err.Number & ", " & Err.Description, , "Error"
    Resume Exit_cmdExecute_Click

End Sub
0
 

Author Comment

by:John Sheehy
ID: 39245635
That did it.  Thank you.
0
 

Author Closing Comment

by:John Sheehy
ID: 39245640
The response was fast and accurate.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

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