Filter tasks for Login ID (1 Viewer)

mba_110

Registered User.
Local time
Today, 01:33
Joined
Jan 20, 2015
Messages
280
Hi
I want to filter the task datasheet based on the user who logged in database as per their loginID.


I have subform called frmMyTasks which will show the task related to employee on their login once he logged in.

I want procedure to check that LoginID with tblUserSecurity LoginID and filter the tasks which is assigned to him in tblTask [TaskAssignTo] field equals EmpID of that user only.

Code:
Private Sub Form_Load()
Dim MyLogin As String
Dim Task As String
Dim TaskAssignTo As Integer

MyLogin = DLookup("LoginID", "tblUserSecurity", "EmpID = '" & TaskAssignTo & "'")
Task = "SELECT * from DueDate where TaskAssignTo = '" & MyLogin & ")"
Me.RecordSource = Task

End Sub

"tblTask".[TaskID] is PK numeric
"tblTask".[TaskAssignTo] is numeric
"tblUserSecurity".[LoginID] is PK text
"tblEmployees".[EmpID] is PK numeric

if incase you required on navigation form there is text box called txtLoginID which will be filled with user's loginID when he successfully logged in database.
 

Minty

AWF VIP
Local time
Today, 08:33
Joined
Jul 26, 2013
Messages
10,355
You've made task assign and empId a string, when both are numeric data type.
Remove the ' ' around both criteria's.

However if you set a global property in a module called glbEmpID that was set at login you could simply refer to that on any form or query.
 

mba_110

Registered User.
Local time
Today, 01:33
Joined
Jan 20, 2015
Messages
280
I have change the delimiters but not working.

May be the procedure is wrong.

Code:
Private Sub Form_Load()
Dim MyLogin As String
Dim Task As String
Dim TaskAssignTo As Integer

MyLogin = Nz(DLookup("LoginID", "tblUserSecurity", "EmpID = " & TaskAssignTo & ""), "")
Task = "SELECT * from DueDate where TaskAssignTo = " & MyLogin & ")"
Me.RecordSource = Task

End Sub

run time error '3075'
extra ) in query expression TaskAssignTo = )'
 

Minty

AWF VIP
Local time
Today, 08:33
Joined
Jul 26, 2013
Messages
10,355
The error is telling you exactly what is wrong.

Add debug.print task before your Me.RecordSource and have a look in the immediate window.

Also you are assigning MyLogin as a string , but it's a numeric data type. Then just to compound that you then revert to using it as a numeric type in the Select statement.

Take a step back and slowly look at the data types and what you are doing with them. There are only really 3 you regularly need to deal with.
Text use ' ' around it.
Dates use # # around them
Numbers - don't need a delimiter.
 

mba_110

Registered User.
Local time
Today, 01:33
Joined
Jan 20, 2015
Messages
280
I did try a lot but not able to reach.

it is still giving error of "Date Type Mismatch"

Code:
Option Compare Database

Private Sub Form_Load()
Dim MyLogin As String
Dim Task As Integer
Dim TaskAssignTo As Integer

MyLogin = Nz(DLookup("LoginID", "tblUserSecurity", "EmpID = " & TaskAssignTo & ""), "")
[SIZE="5"]Task = "SELECT * from tblTasks where ID =  " & MyLogin & ""[/SIZE]
debug.print Me.RecordSource = Task

End Sub

Its out of my understanding now.

tblTask.ID is PK and number field
tblUserSecurity.LoginID is PK and text field
tblUserSecurity.EmpID is FK and number field and same for tblTasks.EmpID FK number field to PK of tblEmployees

I hope you understand i am not programmer and doing the small exercise database to check something.
 

Minty

AWF VIP
Local time
Today, 08:33
Joined
Jul 26, 2013
Messages
10,355
You aren't assigning TaskAssign to anything?
I think you have really confused yourself here.

Is tblTasks.ID the primary key? Should you not be using
Code:
Task = "Select * From tblTasks WHERE EmpID =" & TaskAssign
 

mba_110

Registered User.
Local time
Today, 01:33
Joined
Jan 20, 2015
Messages
280
Please have a look at database.

type mismatch error.
 

Attachments

  • Test1.zip
    95.4 KB · Views: 83

Users who are viewing this thread

Top Bottom