Filter Forms Record Source based on Computer User Name (1 Viewer)

[gsrajan]

I have a form with a text box with control source to "Assigned By" (name of the person assigning the task ). It's default value is the User Name of the computer. I am able to do this. After that, I wish to have the form's record source criteria with the user name of the computer. In other words, the users can see only the records with their name as "Assigned by". Please let me know how to do this.

Thank you.

 

[theDBguy]

Hi. You could add a criteria in the query source of your form to filter the data based on the current user. Can you post the SQL statement of your query?

 

[gsrajan]

Thank you theDBbuy. I do not have a query yet. Otherwise I have a fields with the names Ticket No, Task Status, Assigned By, Assigned To, Start Date, Due Date, Completed Date, Task Title and Task Description in my tblTask. How to add criteria to current user in a query? Please let me know. Thank you.

 

[theDBguy]

Thank you theDBbuy. I do not have a query yet. Otherwise I have a fields with the names Ticket No, Task Status, Assigned By, Assigned To, Start Date, Due Date, Completed Date, Task Title and Task Description in my tblTask. How to add criteria to current user in a query? Please let me know. Thank you.

What is the Record Source of your Form? Is it a table? If so, you could try changing it to something like:

SELECT * FROM TableName WHERE AssignedTo=Environ("Username")

Hope that helps...

 

[gsrajan]

Thank you. It helps. It is a table. Thanks

 

[theDBguy]

Thank you. It helps. It is a table. Thanks

In the design view of the form, when you go to the Record Source property, you can click on the three dots next to the table's name to create a query.

 

[gsrajan]

Ok. I will It helps me very much. Thanks.

 

[theDBguy]

Ok. I will It helps me very much. Thanks.

Good luck!

 

[gsrajan]

The above code I tried with this
SELECT * FROM SubmissionFrm WHERE [Assignedby]= Environ("UserName") in the form record source property
Got error message: ......specified on this form or report does not exist.

Please let me know how to fix this.

Thank you.

 

[theDBguy]

The above code I tried with this
SELECT * FROM SubmissionFrm WHERE [Assignedby]= Environ("UserName") in the form record source property
Got error message: ......specified on this form or report does not exist.

Please let me know how to fix this.

Thank you.

Hi. What was the error message? Also, exactly what code are you using to get the user's name?

 

[gsrajan]

Hi theDBGuy, thanks so much. Just completed this one. It seems it is working. If I need to change anything, please let me know. Thanks for your help and time. I will be adding error handler.

Private Sub Form_Load()
Dim Dbs As Database
Dim Rst As Recordset
Dim MyString As String

Set Dbs = CurrentDb()
MyString = "Select * from PendingQry where [AssignedTo]='" & Environ("UserName") & "'"
Set Rst = Dbs.OpenRecordset(MyString)
List12.RowSource = MyString
Exit Sub

 

[theDBguy]

Hi theDBGuy, thanks so much. Just completed this one. It seems it is working. If I need to change anything, please let me know. Thanks for your help and time. I will be adding error handler.

Private Sub Form_Load()
Dim Dbs As Database
Dim Rst As Recordset
Dim MyString As String

Set Dbs = CurrentDb()
MyString = "Select * from PendingQry where [AssignedTo]='" & Environ("UserName") & "'"
Set Rst = Dbs.OpenRecordset(MyString)
List12.RowSource = MyString
Exit Sub

Hi. Nice work. Glad to hear you got it sorted out. Good luck with your project.

 

[gsrajan]

Thank you.

 

[gsrajan]

I added (AssignedBy] also in the select query. It fetches all the records, ignoring Environ criteria.

MyString = "Select * from PendingQry where [AssignedTo] OR [AssignedBy]='" & Environ("UserName") & "'"

What I am doing wrong? Thank you.

 

[gsrajan]

Thank you I found out my mistake. You can ignore. Thanks.

 

[theDBguy]

Thank you I found out my mistake. You can ignore. Thanks.

Hi. Glad to hear you figured it out. Cheers!