different recordsets for different users 1

[Lhuffst]

I have to rewrite an old access database but need a temporary workaround until I can finish the task. (going to office 2010)

The original system has a separate table for each person with exactly the same fields.
They have approx 9 forms that they perform data entry where each form creates a new record in the specified table. Right now there are 5 inspectors but that may be growing which is why you don't want a table with the inspectors name.


I thought I could
1. Create a basic sub for each of the forms that was unbound until the user logs in.
2. Capture the users name at logon and then pass the name to teh recordset line for each sub as they get to it.
*as a test, I also put the users names into a variable to pass for the recordsets for each subform

I created a main form with a tab control (2 tabs so far). On the first tab, I have 2 subforms.
On the first subform, I set the recordset to equal the users last name and first name which is actually the table name.
It opens fine but I don't get any data.
this is what I put in On the subform on current
Me.RecordSource = Forms!frmShopInfo.txtdatasource.Value where frmShopInfo is the main form not the subform

Is it possible for me to dynamically change teh record set when a user logs in AND allows permissions like add, edit delete?
Thanks
lhuffst

 

[dhookom]

I would create a query like "qryCurrentUser" and set its SQL to "SELECT * FROM JohnSmith". Bind all of your subforms to qryCurrentUser. Then use a little DAO code in the first form that opens like:

CurrentDb.QueryDefs("qryCurrentUser").SQL = "SELECT * FROM [" & Forms!frmLoginForm!txtUserFirstLast & "]"

I would do all of this prior to the main form opening.

Duane
Hook'D on Access
MS Access MVP