Dynamic Menu in Access 2003

[Luthius]

Hello everybody, I'm wanting to set up a dynamic menu in MS Access 2003 according windows user. I was thinking of creating the menu bar and add the key (USERNAME) to that user via code. So the code will read the user logged into windows and create the corresponding menu. Anyone have any other ideas?
In usinga Access 2003.

Luthius

 

[SydneyGeek]

See if something like this helps.

Denis

 

[Luthius]

Unfortunately your example is not working.

 Run-Time error '2001':You cancelled the previous operation.

Well, my purpose is as follows:

I have 4 tables:

[COLOR="Blue"][B]1: tblMenuBars[/B][/COLOR]
   IDBar (PK)
   BarName (string)

[COLOR="Blue"][B]2: tblMenu[/B][/COLOR]
   IDMenu (PK)
   IDBar (FK) 
   Caption (string)
   Type (Long)
   Application (string)
   BeginGroup (string)
   BuiltIn (string)
   Creator (string)
   DescriptonText (string)
   Enable (string)
   FaceId (long)
   OnAction (string)
   State (long)
   Style (string)
   Width (long)
   Priority (long)

[COLOR="Blue"][B]3: tblUser[/B][/COLOR]
   idUser (PK)
   user (string)

[COLOR="Blue"][B]4: tblRelashionship[/B][/COLOR]
   idRecord (PK)
   idUser (FK)
   idBar (FK)

SQL to generate tables

[COLOR="Blue"][B]1: tblMenuBars[/B][/COLOR]
   
CREATE TABLE tblMenuBars(
  IDBar INTEGER NOT NULL,
  BarName VARCHAR(50) NOT NULL,

  PRIMARY KEY (IDBar)
);

[COLOR="Blue"][B]2: tblMenu[/B][/COLOR]

CREATE TABLE tblMenu(
  IDBar INTEGER NOT NULL,
  IDMenu INTEGER NOT NULL,
  Application VARCHAR(50),
  BeginGroup VARCHAR(50),
  BuiltIn VARCHAR(50),
  Caption VARCHAR(50) NOT NULL,
  Creator VARCHAR(50),
  DescriptonText VARCHAR(50),
  Enable VARCHAR(50),
  FaceId INTEGER NOT NULL,
  OnAction VARCHAR(50),
  Priority INTEGER,
  State INTEGER,
  Style VARCHAR(50),
  Type INTEGER NOT NULL,
  Width INTEGER,

  PRIMARY KEY (IDBar,IDMenu),

  FOREIGN KEY (IDBar) REFERENCES tblMenuBars(IDBar)
);

[COLOR="Blue"][B]3: tblUser[/B][/COLOR]

CREATE TABLE tblUser(
  idUser INTEGER NOT NULL,
  user VARCHAR(50) NOT NULL,

  PRIMARY KEY (idUser)
);

[COLOR="Blue"][B]4: tblRelashionship[/B][/COLOR]
CREATE TABLE tblRelashionship(
  idTable INTEGER NOT NULL,
  idBar INTEGER NOT NULL,
  idUser INTEGER NOT NULL,

  PRIMARY KEY (idTable),

  FOREIGN KEY (idBar) REFERENCES tblMenu(IDBar),
  FOREIGN KEY (idUser) REFERENCES tblUser(idUser)
);

I built a query with SQL statement:

SELECT tblUser.user, tblMenu.*
FROM tblUser INNER JOIN ((tblMenuBars INNER JOIN tblRelashionship ON
tblMenuBars.IDBar = tblRelashionship.idBar) INNER JOIN tblMenu ON 
tblMenuBars.IDBar = tblMenu.IDBar) ON tblUser.idUser = 
tblRelashionship.idUser;

What I want is:
According to the windows user, the routine will read its menu for the user.
I tried something like that, but I had problems to generate according user.

Option Compare Database
'Microsoft Office xx Object Library
Sub mnu()
Dim cmdBar As CommandBar
Dim popup As CommandBarPopup
Dim btn As CommandBarButton
Dim barName As String
Dim cn As Object
Dim rs As Recordset
Dim sql As String
Set cn = Application.CurrentProject.Connection
sql = "select * from [qryMenu]"
Set rs = CreateObject("adodb.recordset")
rs.Open sql, cn, 1
'I need to add my bars according windows user
barName = "My Bar"
On Error Resume Next
CommandBars(barName).Delete
'Here I need the barName according the values of my table (tblMenuBars)
'In this case, the name is on directly on routine
Set cmdBar = Application.CommandBars.Add _
(Name:="Main", Position:=msoBarTop, Temporary:=True)
If (rs.EOF) Then
MsgBox "Theres nothing to add", vbInformation
CommandBars(barName).Delete
Else
While (Not (rs.EOF))
menuType = rs![Type]
Select Case menuType
Case 1
Set popup = cmdBar.Controls.Add(Type:=msoControlPopup)
With popup
.Caption = rs![Caption]
.Width = rs![Width]
End With
Case 2
Set btn = popup.Controls.Add(Type:=msoControlButton)
With btn
.BeginGroup = rs![BeginGroup]
.Caption = rs![Caption]
.FaceId = rs![FaceId]
.OnAction = rs![OnAction]
.State = rs![State]
.Width = rs![Width]
.Priority = rs![Priority]
End With
End Select
rs.MoveNext
Wend
End If
cmdBar.Visible = True
cmdBar.Protection = msoBarNoCustomize + msoBarNoChangeDock + _
msoBarNoHorizontalDock
rs.Close
Set rs = Nothing
Set cn = Nothing
End Sub

Can someone help me with this 'Loop' to generate Dynamic menus?

Thanks
Luthius

 

[SydneyGeek]

Is qryMenu filtered by the current user?
If so, does the query give you the expected result when you just open it without trying to run the code?
And will you need to create a different menu for each user, or just for each category of user (eg Data Entry, Manager, Admin)?

Denis

 

[Luthius]

Is qryMenu filtered by the current user?

Yes...

And will you need to create a different menu for each user, or just for each category of user (eg Data Entry, Manager, Admin)?

Yes. I'll do it

Name of the bars as follows

Administrator
Planner
General

Any user not registered, you will get 'General' Bar
I will create a different menu for each user, and not registered will get 'General' Bar with its respective menu.

Thanks for you help

 

[Luthius]

SQL statement in my query to get user:

SELECT tblUser.user, tblMenu.*
FROM tblUser INNER JOIN ((tblMenuBars INNER JOIN tblRelashionship ON tblMenuBars.IDBar = tblRelashionship.idBar) 
INNER JOIN tblMenu ON tblMenuBars.IDBar = tblMenu.IDBar) ON tblUser.idUser = tblRelashionship.idUser
WHERE (((tblUser.user)=(IIf([USER]<>Environ("username"),"Ordinay User",Environ("USERNAME")))));

Luthius

 

[SydneyGeek]

Another question; are you building the menubar in code, based on the user? If so, you may be making it too hard for yourself. Instead I'd recommend building the menubars manually, then displaying the desired one using code.

There's some sample code that you could adapt here.

Denis