standard MDE and custom form/table

[Daniel Polak]

I have a standard application using a MDE as the frontend and a MDB as
the backend.
Some clients would like custom changes to the information stored and
displayed in the database.
I have a form with a tab control containing subforms that show
information linked to the record displayed on the main form.
I could create a new table and add an additional tab with a new subform
to show the custom information.

However I really want to avoid having different versions of the main MDE.
It would be nice if it was possible to use a form from another MDB as a
subform but as far as I know it isn't possible.

What would be a good way to leave the main application standard for all
clients but still allow custom information for specific clients to be
displayed?


Daniel

 

[Ruel Cespedes via AccessMonster.com]

It is possible to have a Standard Form that will not only display different
information but will also display and Enable Objects like Buttons, Subforms,
and Read/Write Permissions. It's going to take alot on your part to plan
ahead to make it work.

1. The data must be designed to be grouped for certain users. Example:
Let's say you have a State field with 'CA' for only California Users or 'TX'
for only Texas Users.

2. The Users must be clearly defined. 'RO' = READ ONLY, 'RW' = READ/WRITE,
and 'ADMIN' = ADMINISTRATOR (READ/WRITE/DELETE), USERGROUP = 'CA', 'TX'.
Create a USER Table with the following fields: USERID, USERNAME, PERMISSIONS,
USERGROUP.

Example Data:

USERID USERNAME PERMISSIONS USERGROUP
===============================================
rcespedes Ruel Cespedes ADMIN CA


3. Create a working logic for each user establishing what they can see and do.


Example Logic:

California 'Read Only Users' will only be able to Read California records.
California 'Read/Write User' will be able to Add/Edit but not Delete
California records.
California 'Admin' will only be able to Add/Edit/Delete California records.

4. Once you have everything planned out then you will create a Module named
basCurrentUser to get the USERID of the user that is logged into the computer.
The code will look like this:

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA"
_
(ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetCurrentUserName() As String
On Error GoTo Err_GetCurrentUserName
Dim lpBuff As String * 25
Dim ret As Long, UserName As String
ret = GetUserName(lpBuff, 25)
UserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
GetCurrentUserName = UserName & ""

Exit_GetCurrentUserName:
Exit Function

Err_GetCurrentUserName:
MsgBox Err.Description
Resume Exit_GetCurrentUserName
End Function

5. Then create an ON OPEN Form Procedure on the said form in question that
will validate each user's USERID against the USER Table you created in Step 2.
The code will look like this:

Private Sub Form_Open(Cancel As Integer)

Dim strUSERID As String
Dim strPERM As String
Dim strUG As String

strUSERID = DLookup("USERID", "USER", "[USERID]='" & GetCurrentUserName()
&"'")
strPERM = DLookup("strPERMISSION", "USER", "[USERID]='" & GetCurrentUserName()
&"'")
strUG = DLookup("strUSERGROUP", "USER", "[USERID]='" & GetCurrentUserName()
&"'")

Select Case strPERM
Case "RO"
Me.AllowAdditions = False
Me.AllowEdits = False
Me.AllowDeletions = False
Case "RW"
Me.AllowAdditions = True
Me.AllowEdits = True
Me.AllowDeletions = False
Case "ADMIN"
Me.AllowAdditions = True
Me.AllowEdits = True
Me.AllowDeletions = True
End Select

Select Case strUG
Case "CA"
Me.Filter = "((State='CA'))"
Me.FilterOn = True
Case "TX"
Me.Filter = "((State='TX''))"
Me.FilterOn = True
End Select

End Sub

The possibilities are endless to what you could Enable, Disable, Make Visible,
Change Subforms, etc.
I hope this helps you get started.