Creating filters in forms using Parameters

[whitneynf08]

I am creating a front end of a database that is the forms only version with ext. .accdr



I would like to create a way that each user of the front end can apply a filter using parameters, allowing the user to enter in their name when opening the front end.

Form and Table name is "Non-Student Tasks Completed Daily" and "Student Connection Task"
Field name is "SSC"

How can I get a box to appear when someone opens the database that askes them to enter their name to apply the filter?

[June7]

You could have a form set as the default when database opens. From ribbon > File > Options > CurrentDatabase > DisplayForm

[theosgood]

I use the the Environ function to get that information from the system so the users don't need to enter anything when opening the database.

FYI.. I am running Access 2007 but opening a database in Access 2003 format.

In Visual basic add this function, I have it in Globals:

Public Function Environ(Expression)
Environ = VBA.Environ(Expression)
End Function

Create a table: tblUsers
User Text 255
LoginTime Date/Time
(you can also add a logout time if appropriate and add it to your close database routine. Also requires changing the query below to append.)

Create a make table query: qryUserLogin
SELECT Environ("UserName") AS [User], Now() AS LoginTime INTO tblUsers

In my AutoExec macro I have the following:
Action = Open Query
Query Name:qryUserLogin
View: Datasheet
Data Mode: Edit

I then open a switchboard form that directs then to forms, reports etc.
Action = OpenForm
Form Name: Switchboard

I also use the Set Warnings = True or False Action from Access 2003 to stop the query message popups. I think you have to disable it in Access Options in 2007.

How to use this:

On the reports that are user specific I add the tblUsers to the report query as an outer join so that it only pulls report data for that user.

[June7]

I do something very similar.

I also use Environ function to get the USERNAME but if user is not already in table the login form pops up so user can input initials (we use initials elsewhere). User never sees the login again.

But why a make table query? Is that just a one-time action to set up the permanent table?

[theosgood]

Personal preference. I do the make table so that it only has one record, the current user. Then I don't need any additional criteria in the user specific queries, just the join. You could append each time and add max criteria to your query but eventually you'll have to maintenance the table. Just one less thing to do.