Command button only visible based on environ user? (1 Viewer)

djspod

Registered User.
Local time
Today, 08:58
Joined
Mar 15, 2015
Messages
18
Hi,

I want to create a button is only visible to a select admin username list.

I'm not sure how to do it?

I imagine the code would check against a table of users, if a name matches the logged in user (environ user) the button becomes visible.
Can anyone help with the code or point me in the right direction please.

Thanks

Sent from my SM-G935F using Tapatalk
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:58
Joined
Aug 30, 2003
Messages
36,118
Since it hasn't been answered yet, something like:

Code:
If DCount(...) > 0 Then
  'Make it visible
Else
  'make it invisible
End If

http://www.mvps.org/access/general/gen0018.htm

I certainly agree with Allan regarding cross-posting.
 

djspod

Registered User.
Local time
Today, 08:58
Joined
Mar 15, 2015
Messages
18
Hi ,

Thanks for the answer i will look try that.

I agree with the Cross Posting issue, apologies to all, I did not realise and I did not mean to cause offence.

I have now read the Cross Posting disclaimer and I am now more clued up on the Forum rules.

Thanks

Sent from my SM-G935F using Tapatalk
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:58
Joined
Aug 30, 2003
Messages
36,118
No problem, post back if you get stuck.
 

djspod

Registered User.
Local time
Today, 08:58
Joined
Mar 15, 2015
Messages
18
Hi,
I have so far worked out that i need to put the environ user value into a temporary variable (tempvars i think)
Then on form load event, the code checks me.user button against a field in a table of selected users, if one matches then the button is visible else it is false (dlookup)

Am i on the right lines?
Can any help with the code structure as i am still very new with vba, my access skills are at Intermediate level.

Thanks for the help so far.

Sent from my SM-G935F using Tapatalk
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:58
Joined
Aug 30, 2003
Messages
36,118
I posted the code structure already. You don't need a variable, though you can use one
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:58
Joined
Feb 28, 2001
Messages
26,996
When I had this problem, what I did, in essence, was to put code in the Form_Open routine to determine whether the user in question was an administrator. I did this by determining the username via Environ("Username") and doing a DLookup of the user's role from a table of users and roles (and other data not relevant to this question). In this same routine, if the user's account had been disabled, I cancelled the Open event, which effectively blocked the user. If the user was OK, I left behind a variable in the form's Class Module general declaration area to remember the role from that lookup.

In the Form_Load event, I checked the user's role and, for every control that would be restricted for that role, I either disabled it (which "greys it out") or "vanished" it by making it not visible at all.

The reason for doing it that way is that the Form_Open event occurs first in sequence and details of the form are not entirely available - but you can do a DLookup or other tests at that time for screening. The next event is Form_Load, and there you can do all the formatting you need. But you don't even waste the time if the Form_Open event gets cancelled because in that case Form_Load doesn't occur. Then, once the Open and Load events are done, Access populates the form with data from the underlying record and fires the Form_Current event. By that time, the form is already formatted, color coded, or whatever I needed to do. Which meant that my Form_Load routine didn't do very much, just a couple of minor overhead functions to record the status of the form.

This is also an easy way to do a "divide and conquer" for the work to be done if you are trying to build a really smooth user interface.
 
Last edited:

djspod

Registered User.
Local time
Today, 08:58
Joined
Mar 15, 2015
Messages
18
Hi,
Thanks for great help everyone, much appreciated.

The last reply was absolutely spot on to what I am trying to achieve with my DB.

I get the logic of the process of DLOOK, checking a table to match a name with the Environ Username, if true then the button becomes visible.

However because I'm quite new to VBA code I'm unsure how to line out the code to the open or load events?
The process step order it goes in, I'm getting red error text so I'm obviously getting it wrong but at the same time it is valuable learning for myself :)

To help you see what I'm seeing I have created a quick mock DB and created a word doc page of screen shots.

If anyone can point me in the right direction I would be most grateful.

Thanks
 

Attachments

  • Button Visible.accdb
    564 KB · Views: 82
  • Button Invisible DB.jpg
    Button Invisible DB.jpg
    78.2 KB · Views: 84

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:58
Joined
Aug 30, 2003
Messages
36,118
Did you see the link I posted? The Environ() function would Replace a form reference.
 

djspod

Registered User.
Local time
Today, 08:58
Joined
Mar 15, 2015
Messages
18
Hi Pbaldy,

I had a look at your dlookup link, thanks for that.

But i was struggling to work out how to implement it with the environ without getting error codes


Sent from my SM-G935F using Tapatalk
 

djspod

Registered User.
Local time
Today, 08:58
Joined
Mar 15, 2015
Messages
18
DLookup("FieldName", "TableName", "Criteria = '" & forms!FormName!ControlName & "'")


Sent from my SM-G935F using Tapatalk
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:58
Joined
Aug 30, 2003
Messages
36,118
Like I said, replace the form reference with the function.
 

djspod

Registered User.
Local time
Today, 08:58
Joined
Mar 15, 2015
Messages
18
Hi,

Tried a few different combinations but my format is incorrect on the code.

Dlookup checks a recordset in my tblUsergroup table, finds one that matches the environ name button is then visible, Yeah?

else it is not visible if there is no name in the list I'm presuming.

However my lack of programming skills and thought path, my code is screwed up with errors.

Just attached a screenshot.

Replaced the form with a control on the form

Thanks
 

Attachments

  • Invisible button on a form.jpg
    Invisible button on a form.jpg
    90.8 KB · Views: 96

djspod

Registered User.
Local time
Today, 08:58
Joined
Mar 15, 2015
Messages
18
Last attempt.

Getting there as the button has disappeared but does not appear when my name is in the table so something is still not quite right?

Any further help would be very welcome.

I have attached a screen shot
 

Attachments

  • Invisible button on a form.jpg
    Invisible button on a form.jpg
    96.9 KB · Views: 86

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:58
Joined
Aug 30, 2003
Messages
36,118
Without a criteria it looks at the first record. The word "criteria" needs to be your field name.
 

djspod

Registered User.
Local time
Today, 08:58
Joined
Mar 15, 2015
Messages
18
Okay I have now got it working but using another method :D:)

Thanks to everybody for your help, pbaldy - I need to explore that dlookup option again as it appears to be useful for lots of things but I kept getting the code wrong!

Or in the wrong order, tried adding Criteria but it was still wrong.

Here's how I solved it:

Code:
Dim rst As DAO.Recordset
 
Set rst = CurrentDb.OpenRecordset("Select * From tblAuthUsers)
 
Do Until rst.EOF
   If rst!NetworkLogin = VBA.Environ("username") Then
       Me.Command11.Visible = True
       Exit Sub
   Else
       Me.Command11.Visible = False
   End If
   rst.MoveNext
Loop
 
rst.Close
Set rst = Nothing

I just changed the controls and file names to fit my DB and this works really well.

Thanks to boblarson for this great tip on another thread and you guys for your time and help.

Cheers
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:58
Joined
Aug 30, 2003
Messages
36,118
Glad you got it working, though I'm surprised Bob would post inefficient code like that.
 

Users who are viewing this thread

Top Bottom