Question Filter data based on the User Login

[MickCun1]

Hi all,

I have a created a large access database to be used for multiple users. Each user has an employeeID and I want to filter the data they see when they login, based on the data linked to their employeeID.

I know the functionality can be done with this on the login form but I am unsure as to the most efficient way of going about this.

Some pseudocode (I have a background in Java! Ha) for the login form is as follows;

If(userID = 1(Admin))
SELECT * FROM tblJobCosts
else
SELECT * FROM tblJobCosts WHERE USERID = userID.


I would appreciate any advice!! I have just been stuck on this for a few hours now and Im unsure as to the best and most efficient way of going about this.

 

[NauticalGent]

Have you considered using the “tag”property for each control on the form? I have a UDF that loops through each control and makes them visible based on the user’s credentials.

I would share it with you now but it is on my computer at work. If you are still interested, I can post it here once I get back to work...

 

[MickCun1]

Yes Please! Thanks NauticalGent!

 

[isladogs]

To save waiting till NG is back at work, have a look at my example database that does exactly what NG suggested
https://www.access-programmers.co.uk/forums/showthread.php?t=293439

The idea can also be used to enable/disable or lock/unlock a group of controls

 

[MickCun1]

Colin,

Thanks very much! I will get back to you after I have a look at your db. I know I am so close to getting the data filtered based on the user, but it is just so frustrating getting over the line!

 

[NauticalGent]

In addition to Ridder’s excellant example, here is a video that have me the idea I eventually decided on:

https://youtu.be/V1o4HVfiilU

 

[bastanu]

Similar functionality, slightly different approach (using system table rather than control tags):http://forestbyte.com/ms-access-utilities/fba-custom-access-levels/

Cheers,
Vlad

 

[NauticalGent]

Good morning Vlad, and thanks for the link - lots of useful stuff there! However, any link I chose gives me a 404 error. I attached a screen shot for reference.

 

[Uncle Gizmo]

Good morning Vlad, and thanks for the link - lots of useful stuff there! However, any link I chose gives me a 404 error. I attached a screen shot for reference.

Link works ok for me.

Sent from my SM-G925F using Tapatalk

 

[NauticalGent]

Thanks Tony, it could be my .mil domain. I will give it a shot when I get home.

 

[Minty]

@Vlad - I can't get to the sample databases either - same 404 error.

 

[Gasman]

And moi, and I think I have accessed it in the past?

 

[static]

Replace linked tables with queries.
When you 'install' the application run code to update the SQL to

select * from [\\server\share\blah\mydb.accdb].table1 where employeeID=12345

Job done.

 

[bastanu]

Hi everyone, sorry my web host deleted all my files again! I have them back now so please go ahead and try again, they should all work.

Cheers,
Vlad

 

[NauticalGent]

I think I have misunderstood your requirement. Good thing static and Pat’s reading comprehension is better than mine.

 

[static]

NP NG. I deleted my post when I saw everyone talking about fields/controls and reposted again. Your solution may still be relevant anyway.

 

[MickCun1]

Hi Pat and Static,

I maybe have not been as informative as I still cannot solve this problem. When a user logs in he/she is directed to a page (frmJobs) which has three subforms, one of which is frmSearch with all the job data in it.

I want to filter this frmSearch when a user logs in. This List is pulled off a vwJobList in the DB. So frmSearch has no control source and is unbound, it just has a query in its row source as it is a list box. Here is the code in VBA for it at the minute;

If IsSubform(Me) Then
Select Case Me.Parent.Name
Case "frmJobs", "frmDesktop"
Me.lstList.RowSource = "SELECT v.JobID, v.JobNumber as [Job #], v.JobDescription as [Description] FROM vwJobList AS v ORDER BY v.JobNumber;"
Me.lstList.Value = DMin("JobID", "tblJobs")
End Select
End If

In the Login form then the code is; (when a user logs in)
DoCmd.OpenForm "frmJobs", acNormal, , , acFormReadOnly

Im thinking is there anyway I can place code under this DoCmd in the login form VBA, to apply a filter to the frmSearch when the frmJobs form opens?? Or is this more difficult than it sounds?

 

[isladogs]

I think I have misunderstood your requirement. Good thing static and Pat’s reading comprehension is better than mine.

@NG
I just read your comment without reading the first post properly & added my link!
I also don't think its relevant now as the latest post is suggesting something very different

@MickCun1
So do you just want to filter the subform depending on the login details?

 

[MickCun1]

Yes Ridders!!

So each user has a userID and when they log in I want to return the job list from frmSearch that corresponds to their user ID.

I have the code here but I'm unsure as to where to place this;

Me.lstList.RowSource = "SELECT v.JobID, v.JobNumber as [Job #], v.JobDescription as [Description] FROM vwJobList AS v WHERE PMEmployeeID = 4576 ORDER BY v.JobNumber;"
'Then place a variable in instead of the hardcoded 4576

 

[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