Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-14-2019, 02:29 AM   #1
mba_110
Newly Registered User
 
Join Date: Jan 2015
Posts: 280
Thanks: 58
Thanked 0 Times in 0 Posts
mba_110 is on a distinguished road
Filter tasks for Login ID

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.

mba_110 is offline   Reply With Quote
Old 01-14-2019, 02:34 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,221
Thanks: 147
Thanked 1,675 Times in 1,647 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Filter tasks for Login ID

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Old 01-14-2019, 05:25 AM   #3
mba_110
Newly Registered User
 
Join Date: Jan 2015
Posts: 280
Thanks: 58
Thanked 0 Times in 0 Posts
mba_110 is on a distinguished road
Re: Filter tasks for Login ID

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 = )'

mba_110 is offline   Reply With Quote
Old 01-14-2019, 06:21 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,221
Thanks: 147
Thanked 1,675 Times in 1,647 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Filter tasks for Login ID

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Old 01-14-2019, 11:31 PM   #5
mba_110
Newly Registered User
 
Join Date: Jan 2015
Posts: 280
Thanks: 58
Thanked 0 Times in 0 Posts
mba_110 is on a distinguished road
Re: Filter tasks for Login ID

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 & ""), "")
Task = "SELECT * from tblTasks where ID =  " & MyLogin & ""
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.
mba_110 is offline   Reply With Quote
Old 01-15-2019, 01:16 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,221
Thanks: 147
Thanked 1,675 Times in 1,647 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Filter tasks for Login ID

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
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Old 01-15-2019, 01:25 AM   #7
mba_110
Newly Registered User
 
Join Date: Jan 2015
Posts: 280
Thanks: 58
Thanked 0 Times in 0 Posts
mba_110 is on a distinguished road
Re: Filter tasks for Login ID

Please have a look at database.

type mismatch error.
Attached Files
File Type: zip Test1.zip (95.4 KB, 15 views)

mba_110 is offline   Reply With Quote
Old 01-15-2019, 02:35 AM   #8
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,221
Thanks: 147
Thanked 1,675 Times in 1,647 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Filter tasks for Login ID

Where is your login form?

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Password Login Screen with session login recording DCrake Sample Databases 6 01-24-2019 02:25 AM
Question Filter data based on the User Login MickCun1 General 35 05-30-2018 07:57 AM
Launch Access from scheduled tasks without interactive login? dpelizzari Modules & VBA 7 09-06-2013 12:04 PM
Login Form that brings back certain records according to the user login. mcsrta Forms 1 11-24-2012 06:06 PM
login then filter by user alkrm Forms 6 01-01-2004 09:48 PM




All times are GMT -8. The time now is 05:37 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World