Filter for CurrentUser() on a Form

[iuianj07]

Hello,

I have a question, and I have already spent the whole day trying to figure it out but unfortunately couldn't get it to work, I am hoping that you could help me or guide me to the right direction...

I have a security enabled access database that has a Job Tracking Form, in the Job Tracking Form there is a DropDown Box for EntryAnalyst and ReviewAnalyst. What this do is for it to track down the work progress of whoever is assigned to a specific task. but I want it wherein when I open this form, it will filter for whoever it is assigned to either as an EntryAnalyst or a ReviewAnalyst.

Example if JohnSmith is assigned as EntryAnalyst, the ReviewAnalyst will be SamAdams. Then another task will be MillerLite is assigned as EntryAnalyst and the ReviewAnalyst will be JohnSmith.So given that I am JohnSmith [CurrentUser()] whenever I open the form, it should show that record that has my name in the EntryAnalyst dropdown box AND the record that has my name in the ReviewAnalyst dropdown box.

so basically the simple logical if statement for this query is that the Form will open IF EntryAnalyst OR ReviewAnalyst = CurrentUser()

Please help with my problem, I would highly appreciate it.

Thank you,

 

[MajP]

Base the form on a query.
In the criteria of the EntryAnalyst type currentUser(). In the criteria of the ReviewAnalyst type currentUser(). Your sql string would be something like

SELECT
fieldOne,
someOtherFields,
FROM
someTable
WHERE
reviewAnalyst = CurrentUser() OR
entryAnalyst = CurrentUser()

 

[iuianj07]

Hello MajP!

Thank you for your HELP! I was now able to filter the form with how I wanted it.

The form was really based on a query, sorry if I didn't mention that in my original post. The query I created/wrote however was incorrect in terms of the WHERE clause... I initially wrote it as:

WHERE
EntryAnalyst OR ReviewAnalyst = CurrentUser()

I didn't think that I should wrote it as how you wrote it.. silly me! Thanks man! I really appreciate it.

 

[PHV]

A simpler way:
WHERE CurrentUser() In (EntryAnalyst,ReviewAnalyst)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

Hello Guys!

I have a question again,, and its a continuation of my question above, So I was now able to show records if names is JohnSmith either on the EntryAnalyst or ReviewAnalyst dropdown box. my question however is that for some reason,, it locked up all the fields in the form that this query is bound to. I can't enter anything in any checkboxes, textfield, memo field etc. any idea what causes it and what to do to fix it?

Below is the query that is bound to the form, please help me re-write. thanks!

SELECT [Job Tracking].LoanNumber, [Job Tracking].PropertyNumber, [Job Tracking].ReportingPeriod, [Job Tracking].ReviewAnalyst, [Job Tracking].DateAssigned, [Job Tracking].ReviewAnalystCompleteDate,], [Job Tracking].EntryAnalyst, [Job Tracking].EntryAnalystCompleteDate

FROM [Job Tracking] INNER JOIN [Filter by Current User] ON [Job Tracking].ReviewAnalyst = [Filter by Current User].E_ID OR [Job Tracking].EntryAnalyst = [Filter by Current User].E_ID;


Any help will be greatly appreciated.

Thank you

 

[dhookom]

Your query is not updateable since the join is not on primary/foreign key fields. Your table is also not normalized but that might not be a problem at the moment.

This section of your SQL is clearly wrong:

[Job Tracking].ReviewAnalystCompleteDate,[b][red]],[/red][/b] [Job Tracking].EntryAnalyst

Try:

SELECT LoanNumber, PropertyNumber, ReportingPeriod, ReviewAnalyst, DateAssigned, ReviewAnalystCompleteDate, EntryAnalyst, EntryAnalystCompleteDate
FROM [Job Tracking]
WHERE [Job Tracking].ReviewAnalyst In (Select E_ID FROM [Filter By Current User]) OR [Job Tracking].EntryAnalyst In (Select E_ID FROM [Filter By Current User]);

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

Hello dhookom,

I just had a typo error with editing the field names with this SQL code to post here - [Job Tracking].ReviewAnalystCompleteDate,], [Job Tracking].EntryAnalyst -- this line is written correctly on the database.

the query that you suggest I try, will that fix the problem with regards to the form's fields locking up or not able to enter information?? if not, can you help me on how to fix it?

Thank you

 

[PHV]

http://support.microsoft.com/kb/148581

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

will that fix the problem

Seems to me it would take less than a minute to try it.

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

It worked, and now I can also update the form

Thank you dhookom,,