Form opens based on unique ID

[Guest]

I'm developing a database that is being used by multiple departments. When
the database is launched, the end-users are required to enter their agent id
# in the login screen, which is unique to the department they are with. Here
is my problem:

Each dept has a unique set of fields that are based on their needs. For
example: Department A wants a comment field and Department B does not. In
addition, Dept B does not want to see the comment field that Dept A uses.
How can I please both departments without having to create two separate
forms? Is there a way to make the field visible based on the agent id# that
was entered in the login screen? If I have to create two separate forms, how
can I open the appropriate form based on the Agent ID#?

For example: Agent ID# = A1234 >> opens the Department A form. Or...
Agent ID# = A1234 >> unhides the comment field on a shared form.

Any assistance would be greatly appreciated.

Thanks,
Nyla

 

[George Nicholson]

Any solution you undertake would obviously require knowing what group any
given AgentID belongs to.

Assumption: you have table within the app that stores AgentIDs & their
GroupID.

Upon opening, use Dlookup to get the user's GroupID & store it in a app-wide
global variable (so it is available to all forms & reports). I called my
variable gstrGroup.


Sub Form_Load()
Select Case gstrGroup
Case "A"
Me.labelHeader.Caption = "Hello GroupA member!"
Me.txtCommentA.Visible = True
Case "B"
Me.labelHeader.Caption = "Hello GroupB member!"
Me.txtCommentA.Visible = False
Case Else
' Default appearance, even if it shouldn't happen
Me.labelHeader.Caption = "I don't know you, do I??"
Me.txtCommentA.Visible = True
End Select
End Sub

The above is just a tiny fraction of what would be possible. You could also
set it up so that forms open showing only data relevant to the group, etc.
(You'd need to create a Function that would return the current value of
gstrGroup. Then you can use that function as query criteria...)

HTH,

 

[Guest]

Thank you so much. I'll try your suggestion and, yes, your assumption is
correct. I do have a table that lists the agents # and their group. Thanks
again!