Hide data col in subform based access right & store data in table

[Wellie]

I have a subform named "Detail Part" within a Mainform "Auto". In the
subform, I have the following data in a table format:

Part_No Source_Name Qty Unit_Price Total_Price

This db appl is accessed by different people and the access of information
is based on each user's individual access right assigned to its logon ID to
this Access appl.

In this case, Can someone please tell me
1 - how can I hide or show the "Unit_Price" and "Total_Price" column of data
in the subform based on user access right ? That 's Manager can see the
"Unit_Price" and "Total_Price" columns while Operator CANNOT see them.

2 - how can I store the user ID on the first record of a "DB_Log" table
using VBA code or other method ? I plan on store the logged on user ID into
the "Logon_ID" field in the "DB_Log" table such that #1 can retrieve it to
check for access right

Your assistance is greatly appreciated.

 

[Jeff C]

I would make a duplicate of your mainform-subform combination calling them
each something different and unique. Then change the subform to display the
information you want for the other set of users. This way you have one
form/subform for one set of users and another for another set of users.

Build a new table listing your users, whatever uniquely identifies them when
they log into your application and include a field for the name of the form
you want them to have when the program opens up.

Build a new form that includes the code below in it's OnOpen Event. Make
sure to name this new form as the form that opens when the application opens
up.


'The following code creates a new log of when each user opens the database

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.Open "tbl_UserLog", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
.AddNew
![Name] = [txtName]
![User] = [txtUSER]
![Date] = [txtDate]
![Time] = [txtTime]
.Update
.Close
End With

'This code below references the table you created listing all the users
and the form you want them to have.
Dim User As DAO.Recordset
Set User = Me.RecordsetClone

User.FindFirst "[rsUSER] = '" & Me.txtUSER & "'"

If User.NoMatch Then

DoCmd.Quit

Else

DoCmd.OpenForm User![rsForm], acNormal

End If

DoCmd.Close acForm, "startup"


See if this helps get you on the way. You can designate an unlimited number
of forms for individuals this way when the program opens, or if the users are
not named then they can get a default form.