On load form show specific buttons for user? (1 Viewer)

[Simonhtc4]

Hi Guys and Girls,

I have created a Database which we call a filechecker, its to check users are doing there job correctly.
Everything i have done on the databse is from the internet and a few videos so please be gentle with how its built

I would like some help with VBA, module or Macro with the following please:

Please see attached database.
Here are step i hope can help understand what i need help with:

1. On the HomePage_frm select the File Checker btn.
2. this will open the FileChecker_frm (There is some VBA code for this form upon loading to Name the btns as you can see(these names are from the Users_tbl Authority=Supervisors)
3. when you click one of the btns ( eg.Richard Bartlett) it will open the FileCheckSelection_frm, this form has hidden btns ( you can see these in design view).
4. what i want to do between the FileChecker_frm and the FileCheckSelection_frm is open the FileCheckSelection_frm but only display the btns that the user has a yes against in the Users_tbl.
5 Also when the FileCheckSelection_frm loads with there buttons they can access, when they click the btns of choice it will open the form for them (eg. BrokerFileCheck_frm)
6. when the eg.BrokerFileCheck_frm opens then it only open for the supervisor that was selected from the FileChecker_frm.

i hope it all makes sense and any help would greatly appreciated.

I tried the following code but im doing something wrong somewhere and i can see it.

Private Sub Form_Load()
Dim SQL As String
SQL = "Select * " & _
"FROM Users_tbl " & _
"WHERE 'Broker = -1 AND Executive = -1 AND Arborisk = -1 AND Claims = -1 AND Private_Client = -1 AND Client_Direct= -1'"

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(SQL)

Dim strText As String

Dim myNum As Integer
myNum = 4

Do While Not rs.EOF

strText = rs.Fields("Broker")
strText1 = rs.Fields("Executive")
strText2 = rs.Fields("Arborisk")
strText3 = rs.Fields("Claims")
strText4 = rs.Fields("Private_Client")
strText5 = rs.Fields("Client Direct")

'Make Buttons here
'Set newButton = CreateControl(Me.Name, acCommandButton)

Dim buttonName As String
Dim actualButton As Control

buttonName = "Command" & myNum

UpdateButton buttonName, strText

myNum = myNum + 1

rs.MoveNext

Loop

End Sub

Public Function MyOpenForm(FormName As String)
DoCmd.OpenForm FormName
End Function

Private Sub UpdateButton(buttonName As String, strText As String)

Dim cCont As Control

For Each cCont In Me.Controls

If TypeName(cCont) = "CommandButton" Then

If cCont.Name = buttonName Then

'cCont.Name = "gbtn_" & strText
cCont.Visible = True
cCont.Enabled = True
cCont.Caption = strText
cCont.ControlTipText = strText
'cCont.OnClick = "=MyOpenForm(""BrokerFileCheck_frm"")"
'.Width = 100
'.Height = 30
'.Top = 12 + (curRow * 1584)
'.Left = 12 + (curCol * 1584)
'.BackThemeColorIndex = 1
'.HoverThemeColorIndex = 4 'Accent 1
'.HoverShade = 0
'.HoverTint = 40 '60% Lighter
'.PressedThemeColorIndex = 4 'Accent 1
'.PressedShade = 0
'.PressedTint = 20 '80% Lighter
End If

End If

Next cCont
End Sub

Thanks
Simon

End Sub

 

[The_Doc_Man]

Your post is half of what it needs to be.

I tried the following code but im doing something wrong somewhere and i can see it.

What we ALSO need is a description of what it is or isn't doing. I.e. how do you know - IN ENGLISH, not techie-talk - that you are doing something wrong?

 

[Simonhtc4]

hi Doc,
i think i need to use VBA to get to my end goal, the steps are as follows:
1.When the user(eg. Richard Bartlett) select himself on the FileCheckers_frm the button will open a another form called FileCheckerSelection_frm.

2. On the FileCheckerSelection_frm i have created the btns but in the Proprty sheet i have turn the btns Visible to No..

3. what im want these buttons to do is show them selves if they have a yes at the side of there name from the Users_tbl. if they dont have a yes then they stay hidden from the supervisor so they dont have access.

4. then if i click the btn that is shown for (eg Richard Bartlett) Broker, as he has acces the that form the the form BrokerFileCheck_frm will open for him.

5. to note when the BrokerFileCheck_frm opens then it need to open just for the specific person ( this will be derived by the supervisor in the Questions_tbl.

P.S, it was meant to say i cant see it, where im going wrong that is.

I hope this help.

 

[static]

Your sql just seems to be selecting users where all buttons are true.
If the fields except "UserID","Employee_Name" and "Email" are all the button names, use the field name as the control name and the field's value to set visibility.

Private Sub Form_Load()
    Dim  fld as dao.field

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select * FROM Users_tbl WHERE userid=" & userid)
	
    for each fld in rs.fields
        select case fld.name
        case "UserID","Employee_Name","Email"
        case else
            me(fld.name).visible = fld.value
        end select
    next
End Sub

 

[Simonhtc4]

Hi Static,

Thanks for looking at this for me, i get the following error.

Sorry Im still a novice at Access so may need walk through talk through.

I have attached image.

 

[static]

UserID (or the username) needs passing into the query to select that specific user. I didn't see a control or other variable for user in your code so you need to add it in. How do you know who is logged in, or, how do you select the user you are interested in?

 

[Simonhtc4]

There isnt a login, the Database i kept in a central location and all supervisors have access and use the DB when they have to do there checks.
im new to access as you may tell by the way its built.

Will this mean i have build a query for every specific user?

 

[static]

Well, it's possible that I got hold of the wrong end of the stick. It's happened before. I'll try to have another look later.

 

[static]

View attachment zip.zip

Changes

FileCheckers_frm

I changed the buttons into a list - this means you don't need to change the form every time you add a new user.
The click event of the list opens the other form and displays/hides the buttons.

Users_Tbl

Removed spaces in field names used for buttons

FileCheckSelection_frm

Renamed the buttons so they match the fields in the table but prefixed with "btn", e.g. btnArborisk.


Because I renamed buttons, any code might no long work. Because I changed field names in the tables other stuff like queries might be broken.

 

[Simonhtc4]

that's great work man, only thing its showing buttons for users that don't have access this area of the form and also it only needs to show supervisors. ill try tweek it to suit.

 

[static]

Oh, yes, I changed some of the values in the users table to test it a bit. So you should copy it back from a backup.

Change the where part of the sql
where authority='supervisor' and userid=" & listWHATEVERITWAS

 

[Simonhtc4]

Ill take a look and make some changes thanks Static