Query with Current User Criteria

[naungsai]

Dear FriendsI have a file attached with workgroup file.I want to load a Query depand on the user. If the current user is "SKHQAE" or "Manager" I would like to run the query without criteria. else with criteria.Can somebody have a look through it.Thanks in advance.

 

[HiTechCoach]

If you were trying to attach a file, it did not make it.

 

[naungsai]

I mean my Access file has a workgroup file. I have restricted some users to view it. the following script is that I am trying to run on loading the file.

Private Sub Form_Load()Dim strCurrentUser As StringDim QueryName As StringDim qryDef As DAO.QueryDefDim strSelect As StringstrCurrentUser = Me.CurrentUserQueryName = "StaffQ11"strNewT = newTablestrQueryName = QueryNameIf strCurrentUser.Value = "SKHQAE" Or "Manager" ThenstrSelect = "SELECT StaffQ00.StaffID, StaffQ00.SName, StaffQ00.UserName, StaffQ00.Rank, StaffQ00.OName, StaffQ00.Project, StaffQ00.Status, StaffQ00.TSP FROM StaffQ00" Else strSelect = "SELECT StaffQ00.StaffID, StaffQ00.SName, StaffQ00.UserName, StaffQ00.Rank, StaffQ00.OName, StaffQ00.Project, StaffQ00.Status, StaffQ00.TSP FROM StaffQ00 WHERE (((StaffQ00.UserName)=CurrentUser()));" End IfSet qryDef = CurrentDb.QueryDefs(strQueryName)qryDef.SQL = strSelectqryDef.CloseEnd Sub

 

[HiTechCoach]

When posting VBA code, please use the code tags ( click the #). Your code is very hard to read. Would you please edit your post and change the quote tags to code or repost in a new reply.

 

[naungsai]

When posting VBA code, please use the code tags ( click the #). Your code is very hard to read. Would you please edit your post and change the quote tags to code or repost in a new reply.

Dear HiTexhCoach

I do aware of layout of VBA Code. the # button do not ative yesterday. Sorry for inconvience. I could make it today.

Private Sub Form_Load()
Dim strCurrentUser As String
Dim QueryName As String
Dim qryDef As DAO.QueryDef
Dim strSelect As String
 
strCurrentUser = Me.Current
UserQueryName = "StaffQ11"
strNewT = newTable
strQueryName = QueryName
 
If strCurrentUser.Value = "SKHQAE" Or "Manager" Then
 
strSelect = "SELECT StaffQ00.StaffID, StaffQ00.SName, StaffQ00.UserName, StaffQ00.Rank, StaffQ00.OName, StaffQ00.Project, StaffQ00.Status, StaffQ00.TSP FROM StaffQ00"                  
 
Else                
 
strSelect = "SELECT StaffQ00.StaffID, StaffQ00.SName, StaffQ00.UserName, StaffQ00.Rank, StaffQ00.OName, StaffQ00.Project, StaffQ00.Status, StaffQ00.TSP FROM StaffQ00 WHERE (((StaffQ00.UserName)=CurrentUser()));"                
 
End If
 
Set qryDef = CurrentDb.QueryDefs(strQueryName)
qryDef.SQL = strSelectqryDef.Close
End Sub

 

[HiTechCoach]

Thanks for reposting the VBA code.

First thing I see is this:

If strCurrentUser.Value = "SKHQAE" Or "Manager" Then

must be written like this:

If strCurrentUser = "SKHQAE" Or strCurrentUser = "Manager" Then

Also:

UserQueryName = "StaffQ11"     ' <<<<< this variable is not defined

strQueryName = QueryName     '  <<<<   QueryName is never set

I think may need to be:

strQueryName = "StaffQ11"

Try:

Option Compare Database
Option Explicit

Private Sub Form_Load()

Dim strCurrentUser As String
Dim QueryName As String
Dim qryDef As DAO.QueryDef
Dim strSelect As String
 
strCurrentUser = CurrentUser()

strQueryName = "StaffQ11"   


strSelect = "SELECT StaffQ00.StaffID, StaffQ00.SName, StaffQ00.UserName, StaffQ00.Rank, StaffQ00.OName, StaffQ00.Project, StaffQ00.Status, StaffQ00.TSP FROM StaffQ00"                  

 
If strCurrentUser <> "SKHQAE" and strCurrentUser <> "Manager" Then
 
strSelect = strSelect &  "WHERE (((StaffQ00.UserName)= """ & CurrentUser() & """));"                
 
End If
 
Set qryDef = CurrentDb.QueryDefs(strQueryName)
qryDef.SQL = strSelect
qryDef.Close


End Sub

 

[naungsai]

Dear HiTech

Thanks for your reply.
When I run it, a message box appear. It reads

Run-time error '3131':
Syntax error in FROM clause.


When I click the debug, it stuck at the following line.

qryDef.SQL = strSelect

How should I repair it?

Best

 

[JANR]

Run-time error '3131':
Syntax error in FROM clause.

You ar missing a space before WHERE.

strSelect = strSelect &  [COLOR=red]" WHERE[/COLOR] (((StaffQ00.UserName)= """ & CurrentUser() & """));"

If you debug.print strSelect you will see this:

StaffQ00.TSP [COLOR=red]FROM StaffQ00WHERE[/COLOR] (((StaffQ00.UserName)= "Admin"));

JR

 

[naungsai]

Thank you.
It works beautifully.

 

[naungsai]

Dear HiTech

Finally, you make it works.

thanks for you help.