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

[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

 

[Pat Hartman]

If you are going to do this, you need to allow for supervisory override. I use a bound login form. The user enters his UID and password into unbound controls and the logon button validates. If the credentials are correct, the user's record populates the form. The login form is hidden and the switchboard is opened. You could include a "supervisor" flag. That way all your queries would include the following expression as part of their WHERE clause.

WHERE (.... regular selection criteris ...) AND (UserID = Forms!frmLogin!UserID OR Forms!frmLogin!SupervisorFlg = True)

 

[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