By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Filter Form based on certain Criteria

P: n/a
I need for a form to display certain records based on the user.

If current user = user1 or user2 then open frmNotesAll
If current user = user 3 then open frmNotes where counselor id = 2 or
6 or 9 or user3 ID
If current user = user 4 then open frmNotes where counselor id = 1 or
3 or 8 or user4 ID
If current user = user 5 then open frmNotes where counselor id = 4 or
5 or 7 or user5 ID
If current user = user6 or user7 or user8 then close form
Else openform frmNotes

I started to put something together but got a run-time error 2501 "The
openform action was cancelled."
NoNotesfrmAll and NoNotesfrm are the same form but the NoNotesfrm is
filtered by the username so they only see their own records and if
there are no records they get a pop up message telling them their
notes are up to date. The query for this form looks at another form
where it pulls the username. I didn't know how else to do it using one
form.

If (CurrentUser = "user1") Or (CurrentUser = "user2") Then
DoCmd.OpenForm "NoNotesfrmAll"
ElseIf (CurrentUser = "user3") Then
DoCmd.OpenForm "NoNotesfrm", , , "CounselorsID = '17' "

End If

Any help would be greatly appreciated.
Thanks

Feb 9 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Feb 9, 4:57 pm, "mchlle" <mch...@austin.rr.comwrote:
I need for a form to display certain records based on the user.

If current user = user1 or user2 then open frmNotesAll
If current user = user 3 then open frmNotes where counselor id = 2 or
6 or 9 or user3 ID
If current user = user 4 then open frmNotes where counselor id = 1 or
3 or 8 or user4 ID
If current user = user 5 then open frmNotes where counselor id = 4 or
5 or 7 or user5 ID
If current user = user6 or user7 or user8 then close form
Else openform frmNotes

I started to put something together but got a run-time error 2501 "The
openform action was cancelled."
NoNotesfrmAll and NoNotesfrm are the same form but the NoNotesfrm is
filtered by the username so they only see their own records and if
there are no records they get a pop up message telling them their
notes are up to date. The query for this form looks at another form
where it pulls the username. I didn't know how else to do it using one
form.

If (CurrentUser = "user1") Or (CurrentUser = "user2") Then
DoCmd.OpenForm "NoNotesfrmAll"
ElseIf (CurrentUser = "user3") Then
DoCmd.OpenForm "NoNotesfrm", , , "CounselorsID = '17' "

End If

Any help would be greatly appreciated.
Thanks

Can easily be done with one form.

One way to do it is to use the Where condition of the OpenForm method.
DoCmd.OpenForm "NoNotesfrm", acNormal, , "[FieldName]='userX'"

Another would be to open the form and apply a different SQL statement
to the recordsource property >
Forms("FormName").RecordSource=SQLstring

Another is to pass the user id to the form using OpenArgs, the last
argument of the OpenForm argument. The Load event of the newly opened
form could evaluate the its OpenArgs property and apply a filter,
change the recordsource etc.

Feb 12 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.