Question Filter data based on the User Login

[isladogs]

Do you have a button click event when the user logs in?
Alternatively an after_update event?

Whichever you use, I'm now unclear if its a listbox or a subform you want updated (as you've mentioned both)
You just need to set the subform record source or listbox row source to be filtered by PMEmployeeID value. Something like

Me.lstList.RowSource = "SELECT v.JobID, v.JobNumber as [Job #], v.JobDescription as [Description] FROM vwJobList AS v WHERE PMEmployeeID = " & Me.UserID & " ORDER BY v.JobNumber;"

Add code to the event code to make the subform/listbox visible & if necessary requery it

 

[MickCun1]

Yes the DoCmd is called in the click event of the login page. Would I need to call the after_update in the frmSearch??

One question if I call the after update in the frmSearch;
- Would I need to do an if else statement as I want all jobs to display when the admin logs in.

Sorry for being ambiguous, There is an unbound listbox with the frmSearch (literally all there is in the form, along with a small search box). Its all coded on VBA through a select statement. This form is then acting as a subform within the frmJobs. Does this clear things up a bit better?

I sincerely appreciate the help

 

[bastanu]

Do you keep your login form open? If yes you can simply make a reference to the userid on that form in the SQL feeding the list box instead of hard-coding the id. If you close the login form you can use a tempvar to store the login and use that in your list box SQL.

Cheers,
Vlad

 

[MickCun1]

Yes I close the login form Vlad. So just create a tempvar to store the login and then use this data to filter my list box? But how do I filter this subform when I want it to open to the full frmJobs form??

 

[bastanu]

In the login form you add something like TempVars("UserID") = Me.UserID before your Docmd.OpenForm call.

Create a new query to be used as the listbox rowsource:

"SELECT v.JobID, v.JobNumber as [Job #], v.JobDescription as [Description] FROM vwJobList AS v WHERE PMEmployeeID = [TempVars]![UserID] ORDER BY v.JobNumber;"

Set the query as the row source of the listbox and comment out any other code that touches it.

How to deal with the admin depends on how you have it set up. If you have an Admin boolean field in the users table you could link the user table to the view and use criteria on another row (OR):

"SELECT v.JobID, v.JobNumber as [Job #], v.JobDescription as [Description] FROM vwJobList AS v WHERE PMEmployeeID = [TempVars]![UserID] OR [Admin] = True ORDER BY v.JobNumber;"
[/CODE]

Cheers,
Vlad

 

[isladogs]

Sorry but I'm struggling to understand what you're saying.
It seems to change from one post to the next.
Suggest you post a stripped down copy of your database as what you want should be very straightforward.

 

[MickCun1]

Vlad,

A MILLION THANK YOUS!!!!!!

Got the data sorted there depending on the user. Ill just have to edit the code to select * when the admin opens the db. Thank you for helping me resolve the issue!!!!

 

[bastanu]

Glad to hear you got it working. You never said how you identify the admin(s),

Cheers,
Vlad

 

[MickCun1]

Yea I am looking into coding the admin now. Im thinking maybe the best would be to reset the tempVar after every time someone logs in? Then if the value is null (its the admin as every other employee has an ID). What do you think of that method of doing things?

Michael

 

[bastanu]

Not sure I follow you. Even if the admin has an id like any others, how do you know she/he is an admin? Easiest is to have a field in the employee column flagging the record as Admin (Yes/No type -checkbox).

Also, I hope you have your application split and every user has their own front-end, otherwise you are going to have problems with the temptvars.

Cheers,
Vlad

 

[MickCun1]

I dont have it split. I was just going to place it on the server and allow employees to log in?

 

[NauticalGent]

@NG
I just read your comment without reading the first post properly & added my link!

Blind leading the blind...!

 

[isladogs]

I dont have it split. I was just going to place it on the server and allow employees to log in?

Disaster waiting to happen.
Within a short time, you'll be back asking why your database is corrupt.

In a multi user environment, you MUST split the database, put the BE on the server, and give EACH user their OWN copy of the FE which needs to be on their own computer

 

[NauticalGent]

If you take anything away from this thread MickCun1, heed Ridder’s last post...

 

[static]

I'm not in the camp that thinks a shared front end won't work AT ALL, but my solution won't work that way, so I'm out.

 

[apr pillai]

I used this method in one of Projects, may be it is useful as a reference. The link is given below:

Filtering Data for Different Users