A form that does not allow to browse records

[Marko_13]

Hi there


I got a trouble finding a solution for my problem. The case is:
- I want to design a database for my workers so that they can enter their KPI's there
- I'd like to see those changes in real time (not only in the end of a month) in order to monitor what's going on in my team
- None of them should see their collegues's KPI's. Means, that they cannot see the table - the only person who sees the table should be me ;]

I figured out that I can divide my database into 2: inner and outer database. The inner database will be the actual table with data while the outer one will be the data-entry form.
The thing is, even if my employees have the access to the form only - they can still navigate through the records. I'd like to disable that. I do not know how.
Is there a way to do it?
Any better solutions?

[CJ_London]

rather than your form recordsource being a table, use a query but you will need to backup a bit

Each user will need to login (or you can use the windows login name) and your table needs to have a user field populated with the relevant userid

your query can then have criteria to limit the records to those of the logged in user

[Marko_13]

rather than your form recordsource being a table, use a query but you will need to backup a bit

I would appreciate if you explain this concept a bit more in detail. I cannot imagine how to do it

[CJ_London]

at the moment you probably have a form recordsource like 'myTable'

instead you have a query

SELECT * FROM myTable WHERE username=loginname

[Marko_13]

Yes, but I'm not sure whether how it possible to add records to a database by using a form which is binded to a query and not the actual database (table).

Does this article describe your advice for me? https://support.office.com/en-us/article/Use-a-query-as-the-record-source-for-a-form-or-report-e54251f3-57ca-4a7d-8e77-e498966cd41b

[CJ_London]

Yes - the recordsource can either refer an existing query (per your link) or you can put the sql to the query in the recordsource in the form - which is my personal preferred option since (with criteria) it reduces traffic so is faster.

but I'm not sure whether how it possible to add records to a database by using a form which is binded to a query

providing the query is updateable yes. queries which are not updateable include action queries (append/update etc), crosstabs and complex queries - the rule is one form, one table - the query aspect is to filter the records in some way - in your case, by user

[Marko_13]

your query can then have criteria to limit the records to those of the logged in user

OK! I'm starting to get it. Now, I don't know how to make access recognize the user and automatically use this information as query criteria...

[CJ_London]

you'll need to use vba

investigate Environ("username") - this will return the windows login name for the user currently logged in

you'll need a public function in a module

Function GetUsername() as String
GetUsername=Environ("username")
end function

then your query will be something like

SELECT * FROM myTable WHERE username=getusername()

so you will have in your table a field called username and as new records are created you will need to populate it - to do this put this code in the beforeupdate event of the form

username=environ("username")

[Marko_13]

Wow! Sounds awesome. Will try to try it.But let's look towards the future. Even if everything works fine, I'm wondering how the hell will I be able to actually see the whole query? I mean, I have a username as well so..I assume that if open the query I will see...nothing? Will I need to alter the code everytime I need to monitor my employees work?I tried a way around: I opened a blank access project and tried to connect (not import) the query to it (so that I would have my own file, password protected). But I sadly discovered that one can connect tables but not queries.

[Marko_13]

I tried a way around: I opened a blank access project and tried to connect (not import) the query to it (so that I would have my own file, password protected). But I sadly discovered that one can connect tables but not queries.

Well actually I tried again and this works just fine. But this solution doesn't seem like the best one: now any smartass can connect to the query and see all the table data :/

[CJ_London]

recommend you find some online lessons to understand how it all works.

Once developed, users should only have access to forms and reports, everything else hidden away. Protecting data is important but your strategy has to be built around who and why - any system can be broken by someone with the right skillsets and determination.

Developing a secure and effective db is not an easy task, if you are the business owner you should consider employing a professional to develop your database, they will build it in a fraction of the time it will take your - and whilst you are building it, you are neglecting your business