Question Filter data based on the User Login (1 Viewer)

isladogs

MVP / VIP
Local time
Today, 17:51
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 13:51
Joined
May 21, 2018
Messages
31
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

AWF VIP
Local time
Today, 10:51
Joined
Apr 13, 2010
Messages
1,401
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

Registered User.
Local time
Today, 13:51
Joined
May 21, 2018
Messages
31
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

AWF VIP
Local time
Today, 10:51
Joined
Apr 13, 2010
Messages
1,401
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:
Code:
"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

MVP / VIP
Local time
Today, 17:51
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 13:51
Joined
May 21, 2018
Messages
31
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

AWF VIP
Local time
Today, 10:51
Joined
Apr 13, 2010
Messages
1,401
Glad to hear you got it working. You never said how you identify the admin(s),

Cheers,
Vlad
 

MickCun1

Registered User.
Local time
Today, 13:51
Joined
May 21, 2018
Messages
31
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

AWF VIP
Local time
Today, 10:51
Joined
Apr 13, 2010
Messages
1,401
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

Registered User.
Local time
Today, 13:51
Joined
May 21, 2018
Messages
31
I dont have it split. I was just going to place it on the server and allow employees to log in?
 

isladogs

MVP / VIP
Local time
Today, 17:51
Joined
Jan 14, 2017
Messages
18,186
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

Ignore List Poster Boy
Local time
Today, 13:51
Joined
Apr 27, 2015
Messages
6,286
If you take anything away from this thread MickCun1, heed Ridder’s last post...
 

static

Registered User.
Local time
Today, 17:51
Joined
Nov 2, 2015
Messages
823
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. ;)
 

Users who are viewing this thread

Top Bottom