Me.Filter based on particular selection

[sah]

Hi
I have two tables one has project ids of projects and the other activities of these projects.
Table Project - Field Projectid
Table activities - Fields Projectid(Foreign from tabl project) and activity id
I have 2 forms - First form displays Project id's on clicking the project id i open the second form which contains the activities related to that project only.
Problem is that whatever i try to change in the Me.Filter it is showing all records of all projects in the form instead of only respective project's activities.

Private Sub Form_Load()
	CurrentFilter = Me.Filter
	Debug.Print CurrentFilter
End Sub

Private Sub Form_Open(Cancel As Integer)
	CurrentFilter = Me.Filter
	Debug.Print CurrentFilter
End Sub

Yields ProjectNo='Project1' on clicking Project1 from main form and opens second form with the six activities of project 1.



When i deselect the filter option in the record selector bar below in the form it shows eight records with activities of Project 2 and 3.

I cant set Me.AllowFilters = False because can filter certain activities based on his needs
Whenever the user tries to click the filtered option below in the bar, the form should again show all records of only the project with which the form was opened and not all projects


The second form is opened with the following command in Projectno click

DoCmd.OpenForm "frmProjectActivities", , , "ProjectNo='" & Me.ProjectNo.Value & "'"

I have tried Form_ApplyFilter and Form_Filter to change the filter to CurrentFilter but nothing works.
Whenever the user tries to filter then only records pertaining to that project should appear instead of the entire projectactivity table.
When tried to see what all options do filters have then i came across form by selection etc..but i am not reaching the goal.

Please help me sort this out.

[June7]

Have you considered a form/subform arrangement?

[sah]

No i dont have any sub forms.

The db flow is as follows :
Login form has usertype, userdept username and password.
After logging in a projctlist form is shown with fields pid, plname, ptitle (Splitform)
gm sees all projects, manager sees only projects of his dept , proj leader sees projects initiated by him
but any user of the db is easily able to view all the projects on removing the filter, this is totally not what the db is supposed to be.
Using Me.Filter = "" means that all filters are removed. On Form_applyFilter event, i used the code of

Same is the case for project activities (Splitform)
On clicking pid, activities of that proj are shown.
I used a textbox unbound with name projectContainer which the minute form is loaded this variable gets its value from form openargs

Me.Filter="" 
Me.Filter = "ProjectNo='" & Me.ProjectContainer & "'"

This code retains all records of particular project whatever filter is applied. Now within the same list if i wish to select certain activites then again i get to see all activities.

For sure i am going to go mad soon

Thanks for responding June 7.

[June7]

You considered and rejected subform or just don't know how to use?

Review http://office.microsoft.com/en-us/ac...010098674.aspx

[sah]

No never used it, totally new to access meaning started it 2 months back with this project.
Would now try to redesign the form.

[sah]

Wait a minute!
I'll have to use projectlist as main form then its subform would be project form of particular project then AGAIN a subform with project activities. Is it so ?
I'll skip dinner to accomplish trying to redesign.

[June7]

What is your table structure and relationships?

I was thinking a table of project records bound to main form and then a subform of the activities.

[sah]

Hi June7,

Sorry for responding so late but was trying to apply filters to some selected records and later trying all ways on disabling filters altogether.

I have provided part of project tables as project and activity schedule
Other Table is as follows :
Users Table : UserType, UserDept, UserName, Password
Based on user login projectlist is opened

If UserTypeString = "PL" Then
            DoCmd.OpenForm "ProjectList", , , "ProjectLeader='" & UserNameString & "'", , , "PL;" & UserNameString
End If

Filter button below in record selector bar of form shows filtered.
When i deselect it it shows unfiltered and displays all projects.
Project List form has bound fields from project table, projectid, title, projectleader, status etc.. All are enabled = False.
On clicking project id projectdetails form opens up showing more details of the project. Once changes made to this projectlist is requeried.
On project details form projectactivity button exists which takes user to activity split form to add activity to a project. Back button in activity form takes back to project detail form and on this form back button takes back to projectlist form.

Cycle
Login -> Project List (based on user login) -> ProjectDetails Form -> Project Activity -> Project Details Form -> Project List -> Logout -> Login Page

I decided to remove filters all together then tried the option of Me.ProjectLeader.FilterLookup = False. In this case only the filter for projectleader on projectlist is disabld and the other fields have filter lookups. But there was another problem even though i kept the filterlookup = false there was an option of Clear filters from projectleader which again gave the same problem of showing projects of other projectleaders.

Then i totally switched off all filters for all forms. Me.AllowFilters = False.
Still the option "Advanced" in ms access ribbon clears all filters irrespective of Me.AllowFilters = False.
So i disabled the ribbon only for project list form - on load and open events : DoCmd.ShowToolbar "Ribbon", acToolbarNo
On close : DoCmd.ShowToolbar "Ribbon", acToolbarYes
So all filters are totally disabled and if a user wishes to see selected records based on his choice i am creating reports for project list fields.
All tedious work...
But there has to be some user level and user groups for access 2007.
What would you suggest????????

[sah]

Hi everyone,

My projectlist form was based directly on Project Table so on removing or clearing all filters, all the projects were shown.
Since I also had the user, dept(workgroup) problem so i tried this way:

1. Created a table named CurrentUser, when a person logs into the db from the login form, all data in currentuser table is deleted and the new logged in user with dept,type name and password are stored in the table i.e. at any point of time only one record of currentUser exists.
2. Created a Query with projectleader criteria = currentuser name from currentuser table.
3. Made the projectlist fields based on the query.
4. Filters working as needed.

Now i have to work around for the manager that based on dept and name he should get projects of his dept only, which means i have to modify the query for dept and usertype as well.

If this db is put on a LAN network what other problems would surface is what i wish to know.
Write conflict is one thing which might surface. Please suggest as many problems that can make a db worthless so that i work on these problems.

Splitting of DB - Can we split the DB in such a way that all tables excepting CurrentUser table be in the backend and all forms,reports etc and CURRENTUSER table be in the front end?
So that each system user has his local copy of currentuser table which need not disturb other system DB currentuser table.

Please reply, your replies are all valuable to me.

[June7]

You could disable the ribbon and Navigation bar so users won't have filter toggle available. Or instead of WHERE CONDITION of DoCmd.OpenForm, the form RecordSource is an SQL statement with WHERE clause parameterized (reference to login) for the user criteria.

Write conflict has caused an issue maybe twice in 4 years with my split db.

I don't understand the question about currentuser table. Why would it not be in BE?

[sah]

Hi,

With the help of my table i track only local user of DB and according to his record various project ID checks of dept code etc. are done.
His view of DB is limited to the system record he inputs as login.
If Current User Table is in backend then my code of validatating data would face problems of many current users of various types across the network and not retrieving particular user type(GM, Manager,PL) records.

For PL i use the following query

SELECT Project.ProjectNo, Project.Title, Project.ProjectLeader, Project.Status, Project.StartDate, Project.EndDate
FROM Project INNER JOIN CurrentUser ON Project.ProjectLeader = CurrentUser.UserName
WHERE (((Project.ProjectLeader)=[CurrentUser].[UserName]));

For Manager i use another kind of query and during runtime , based on login the Forms!ProjectList.Recordset = Query(PL or Manager) is performed. So each user gets his
copy of data not interfering with others.

I have tried splitting the DB only after a backup of it, All tables were moved to the backend including current user. Now to track current user i have to check all fields of current user instead of only one field "(Project.ProjectLeader)=[CurrentUser].[UserName])" from the local copy containing one record.
This also optimizes in not having data transfer from back to front end just for usr view.
I searched for Create View option but access 2007 doesnt let us access it.
Since the front end had no table after splitting up i imported currentuser from the back end and deleted it totally from there. All tables have links excepting currentuser and everything is currently working fine.

Do you have more views? Please tell them and i would also wish to know what way did you try in solving the write conflict problem that occured and under what circumstances did it arise. Did it cause any inconsistency in the database ?

Thanks for continued support

[June7]

One case of conflict was actually caused by some bad coding which I fixed. Another was users just happened to try to edit same record. Doesn't occur often enough to worry about.

I have my 'user' table in BE. I determine who the user is by grabbing their username from their network login. With that info I can get the user specifications from the 'user' table. I presume if they can login to the network they are valid user. Regardless how you get user name (network or db login) you have the user ID to use as filter criteria for the 'user' table to get that user's specifications.

But if table in frontend works for you, fine.

[sah]

Hi Again,

How can we take user login from network and include it into our table? I never thought of this. Please suggest some sites or something which might help me in your suggestion. So far this DB is working fine, but i would also like to know the networking option. Sounds Better.
Also, if i had to use network login, what should be the user table structure in DB ?

[June7]

Login, password are common topics. Search forum or google.

For starters
https://www.accessforums.net/forms/u...gin-22943.html
http://forums.aspfree.com/microsoft-...7t-406737.html


I have a table 'Users' with fields:
UserNetworkID
UserInitials
ComputerName

I get the user name from network with

VBA.Environ("USERNAME") - yes, type it EXACTLY as shown.

Access will not recognize the Environ function but VBA will. Code from my db:

Option Compare Database
Option Explicit
Private Declare Function WinExec Lib "kernel32" (ByVal lpCmdLine As String, ByVal nCmdShow As Long) As Long

Private Sub Form_Load()
Dim Shell
If Me.tbxVersion <> Me.lblVersion.Caption Then
    If VBA.Environ("UserName") <> "MY USERNAME IS HERE" Then
        'Because administrator opens the master development copy, only run this for non-administrator users
        'Check for updates to the program on start up
        'If values don't match then there is a later version
        Set Shell = CreateObject("WScript.Shell")
        Shell.Run CurrentProject.Path & "\Update.vbs"
        Dim WAIT As Double
        WAIT = Timer
        While Timer < WAIT + 3
            DoEvents
        Wend
        Set Shell = Nothing
        Application.Quit
    End If
Else
    Me.tbxVersion.Visible = False
    Call UserLogin
End If
End Sub

Private Sub tbxUser_AfterUpdate()
If Me.tbxUser Like "[A-Z][A-Z][A-Z]" Or Me.tbxUser Like "[A-Z][A-Z]" Then
    CurrentDb.Execute "INSERT INTO Users(UserNetworkID, UserInitials) VALUES('" & VBA.Environ("UserName") & "', '" & UCase(Me.tbxUser) & "')"
    Call UserLogin
Else
    MsgBox "Not an appropriate entry.", vbApplicationModal, "EntryError"
End If
End Sub

Private Sub UserLogin()
Me.tbxUser = DLookup("UserInitials", "Users", "UserNetworkID='" & VBA.Environ("UserName") & "'")
If Not IsNull(Me.tbxUser) Then
    'user in User table, continue to MainMenu
    CurrentDb.Execute "UPDATE Users SET ComputerName='" & VBA.Environ("ComputerName") & "' WHERE UserInitials='" & Me.tbxUser & "'"
    DoCmd.OpenForm "Menu", acNormal, , , , acWindowNormal, Me.tbxUser
    DoCmd.Close acForm, Me.Name, acSaveNo
End If
End Sub

[sah]

Hi June7,

Trying to understand the code, looks like it has vb script (.vbs) etc. elements and shell scripts (Googled these words). Totally new to me. If i am not wrong does the form have tbxUser and tbxVersion as textboxes, is there no need for password setup or does the network login manage the logging in automatically.

Parts of the code which i didnt understand

1.

If Me.tbxUser Like "[A-Z][A-Z][A-Z]" Or Me.tbxUser Like "[A-Z][A-Z]" Then

2.

If Me.tbxVersion <> Me.lblVersion.Caption Then
                     If VBA.Environ("UserName") <> "MY USERNAME IS HERE" Then

3.

Private Declare Function WinExec Lib "kernel32" (ByVal lpCmdLine As String, ByVal nCmdShow As Long) As Long

4. Why is update required for this program?

Can you explain it a bit more? And attach login form snapshot?
I have never worked in a networking environ. First time that i'll be doing it with this project hence i never got the idea of using network logging.
As far as i know, if i am not wrong, once the system is booted, a system on the network prompts the network login, the desktop appears on correct login, if ms access front end application is clicked the network login window appears again and based on its entry the application starts working??? A bit confused in understanding networking, i feel like taking a course for network administration. Sorry for bothering too much.

Thanks for the help.