John Sheehy
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
That did it. Thank you.
ASKER
The response was fast and accurate.
ASKER
The OpenForm action was Canceled
Any thoughts?
John