Access 2002 Help required please......

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Simonglencross - 06 Nov 2004 15:13 GMT

I am currently in the process of designing a database whic is going to be
used to record sales, I need some advice on the following.

a) each user (Sales Person) will have there own Customers, Orders, Tasks.
Appointments and Sales Takgets. I have setup the individual tables but I am
unsure what I should do with regards to the individual users being able to
only see there data (Customers, Sales,Appointments ) etc. Can anyone suggest
any ideas maybe point me in the right direction or have any example dbs?

b) I spoke to someone who advised that I should use the Currentuser()
function as criteria on the owned by field where is the 'owned by field'?

C)I understand that I need to use Access security in order to secure the
database, but I am usure about a certain aspect I know you can restrict
users with regards to forms which is fine but what I need is once the user
logs on and they start using the database I need it to the identify in
access whiuch user it is and then display the currebt users data
accordingly. any advise suggestions or examples?

I know I have lotes of question which I appologise for I learning fast but
know very little if anything about vba....

Thanks in advance

Simon

Reply to this Message

Joan Wild - 06 Nov 2004 19:28 GMT

> a) each user (Sales Person) will have there own Customers, Orders,
> Tasks. Appointments and Sales Takgets. I have setup the individual
[quoted text clipped - 6 lines]
> function as criteria on the owned by field where is the 'owned by
> field'?

You would need to add this field to your tables - call it OwnedBy or
anything you want.

Use forms for all data entering/updating. You would base your forms on
queries rather than on the tables. You would have a control on your form
(hidden if you like) that is bound to the OwnedBy field. Set the control's
default value to =CurrentUser() so that any new records they create will
automatically store their Access username. If you have existing records,
you'll need to enter the Access username in this field for all current
records.

The queries that are recordsources for your forms/reports would be SELECT *
FROM Customers WHERE OwnedBy = CurrentUser()

Only records where the OwnedBy field is populated with the user's Access
login name will be displayed.

Signature

Joan Wild
Microsoft Access MVP

Reply to this Message

Simonglencross - 07 Nov 2004 11:44 GMT

Joan,

Thank you for that information it makes things much more clearer now!

Regards

Simon G

> > a) each user (Sales Person) will have there own Customers, Orders,
> > Tasks. Appointments and Sales Takgets. I have setup the individual
[quoted text clipped - 23 lines]
> Only records where the OwnedBy field is populated with the user's Access
> login name will be displayed.

Reply to this Message

Simonglencross - 07 Nov 2004 14:20 GMT

Joan is there any way I can implement this without have to scrap the forms
which I have done and starting again?

Reply to this Message

Joan Wild - 07 Nov 2004 14:59 GMT

> Joan is there any way I can implement this without have to scrap the
> forms which I have done and starting again?

There is no need to delete your existing forms and start over.

I'll assume your forms are based on tables. Create a RWOP query for each of
your tables. You can set the run permissions property of the query in
design view to Owners. This will give you a SQL statement like:

SELECT * FROM Orders WITH OWNERACCESS OPTION;

Change the recordsource of your form to the query. In design view of your
form, show the field list and drag the new OwnedBy field from the list to
your form. Set this control's default property to =CurrentUser() and hide
it if you wish.

Signature

Joan Wild
Microsoft Access MVP

Reply to this Message

Simonglencross - 07 Nov 2004 16:43 GMT

Joan,

Whats a RWOP query? you've lost me a little here.

Simon

> > Joan is there any way I can implement this without have to scrap the
> > forms which I have done and starting again?
[quoted text clipped - 11 lines]
> your form. Set this control's default property to =CurrentUser() and hide
> it if you wish.

Reply to this Message

Joan Wild - 07 Nov 2004 16:57 GMT

> Joan,
>
> Whats a RWOP query? you've lost me a little here.

Sorry Simon, that means 'run with owner permissions'. If you implement
security, you can change the 'run permissions' property of the query in
design view to 'Owner'. Doing so means that you can deny users all
permissions on the underlying tables, and give them permission on the query
instead. When they run the query, they are 'running it with the query
owner's permissions', meaning that they will have the permissions on the
tables that the owner has.

However, they are still restricted by the permissions they have on the
query.

For example, the query owner may have read/update/insert permission on the
table (not delete). They create a RWOP query based on this table, and give
users permission to read data on the query (note not update or insert or
delete). Users do not have permission on the underlying table. When they
run the query, they can read the data in the table (because they are running
the query as though the owner is, and the owner has read/update/insert
permission on the table). They are not able to update/insert/delete data in
the table, because they are still restricted based on the permission they
have on the query. Make sense?

Note that if the query owner gave delete permission on the RWOP query, users
still couldn't delete data, because the query owner doesn't have delete
permission on the table.

You can get more information on RWOP in the security FAQ and whitepaper.
Security FAQ
http://support.microsoft.com/?id=207793

Security Whitepaper
http://support.microsoft.com/?id=148555

Signature

Joan Wild
Microsoft Access MVP

Reply to this Message

Simonglencross - 07 Nov 2004 17:50 GMT

Thanks a million Joan think I get where you are comming from I have created
RWOP's for all or the table and done as you explained I have not implemented
the sercurity yet but will be shortly or would you recommend doing that now?

I noticed that you have recently released a new book what sort of user is it
aimed at?

Regards

Simon

> > Joan,
> >
[quoted text clipped - 31 lines]
> Security Whitepaper
> http://support.microsoft.com/?id=148555

Reply to this Message

Joan Wild - 07 Nov 2004 19:39 GMT

> Thanks a million Joan think I get where you are comming from I have
> created RWOP's for all or the table and done as you explained I have
> not implemented the sercurity yet but will be shortly or would you
> recommend doing that now?

Well the RWOP setting is pointless in an unsecured database. In this
setting you have been logged in as 'Admin', so that user is the owner of the
query.

When you implement security, you'll remove all permissions/ownership for the
Admin user, and you'll create a new user to be the owner. You'll want to
change the setting to owner while logged in as that user, and save the
queries.

> I noticed that you have recently released a new book what sort of
> user is it aimed at?

I did? No wonder I'm so tired! <g> No I haven't written a book - what did
you see?

Signature

Joan Wild
Microsoft Access MVP

Reply to this Message

Simonglencross - 07 Nov 2004 16:56 GMT

Joan,

I had an unbound list box which displayed my active customers, you could
click once on one of the active customers and there details would populate
my center form but since making the currentuser() changes the list box is
now bound and has loat this functionality, how could I correct this?

Many Thanks

Simon

Reply to this Message

Joan Wild - 07 Nov 2004 19:36 GMT

> Joan,
>
[quoted text clipped - 3 lines]
> changes the list box is now bound and has loat this functionality,
> how could I correct this?

Making the currentuser() changes should not have done this. You can just
delete the control source for the list box to make it unbound again.

There is something more happening here, though. What code is attached to
the unbound list box?

Signature

Joan Wild
Microsoft Access MVP

Reply to this Message