Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

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

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
John Sheehy
Asked:
John Sheehy
  • 3
  • 2
1 Solution
 
omgangCommented:
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
 
John SheehyCommunications EgineerAuthor Commented:
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
 
omgangCommented:
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
 
John SheehyCommunications EgineerAuthor Commented:
That did it.  Thank you.
0
 
John SheehyCommunications EgineerAuthor Commented:
The response was fast and accurate.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now