Mulitple User Issue (1 Viewer)

[ertweety]

I have a table in a database that I need approximately 50 people to update at any given time. I only want to give them access to update their own records not everybody elses. A rep ID will establish what records they own. They need to be able to modify and add new records.

Does anybody have any ideas on the best way to do this?

 

[RevTDC]

It sounds like you will need to have the User Id as a primary key and restrict access to the table by filtering on the User Id.

 

[DevastatioN]

If you each record in the table can be accessed by only ONE person (never two people or more can access the record), then make a field in the table called UserID, or CreatedBy, AccessedBy etc, whatever name you feel is appropriate.

Everytime they open the view/edit form, it must filter to show only the records where UserID in the table = UserID currently logged in.

Everytime they add a record, you need to make sure in your routine somewhere that UserID gets set to be the UserID that is adding it (currently logged in).

If multiple people need access to the same record, create a junction table between that table, and the User table. On the form, have a subform where you as the admin (or whoever has the power) can select multiple users from a dropdown to have access. And as above, the form needs to be run on a query where the UserID is present in that junction table, for that record.

 

[smig]

Normally Access will locks edited record only.
If each user only can see his oun records, using techniques as suggested, you should have no problem

 

[ertweety]

Thanks for the help!

Really like your idea. However, I just found out that these 50 reps don't have access....ugh. Have you ever used the access email feature in access 2010 to collect data? I want each of their reps to be able to update each of their sales leads each month and add any new ones they might have.