?
Solved

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

Posted on 2013-06-13
5
Medium Priority
?
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

765 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